pg_roast

pg_roast : Opinionated PostgreSQL database auditor

Overview

Packages

Source

pig build pkg pg_roast;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_roast;		# install via package name, for the active PG version

pig install pg_roast -v 18;   # install for PG 18
pig install pg_roast -v 17;   # install for PG 17
pig install pg_roast -v 16;   # install for PG 16
pig install pg_roast -v 15;   # install for PG 15
pig install pg_roast -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_roast';

Create this extension with:

CREATE EXTENSION pg_roast;

Usage

Sources:

• Upstream README
• Version 1.0 install SQL
• Background-worker implementation

pg_roast runs opinionated PostgreSQL health checks and stores findings in its fixed roast schema. It checks configuration, schema design, indexes, vacuum and bloat indicators, security posture, replication, connections, and workload signals. Version 1.0 targets PostgreSQL 14 and later.

Manual audits

Manual mode does not require preloading:

CREATE EXTENSION pg_roast;

SELECT * FROM roast.run();
SELECT severity, check_name, object_name, roast
FROM roast.latest
ORDER BY severity, check_name;

SELECT * FROM roast.summary;

Each run persists audit history and findings. Use the latest view for the newest run and the summary view for grouped results.

Scheduled audits

The optional background worker requires preload configuration and a restart:

shared_preload_libraries = 'pg_roast'
pg_roast.database = 'mydb'
pg_roast.interval = 3600

The database setting is fixed at server start. Review the upstream settings before enabling automatic audits across a production workload.

Caveats

• Findings are heuristic advice, not automatic proof of a defect. Review workload context, maintenance windows, and PostgreSQL documentation before applying any recommendation.
• Audits execute catalog and statistics queries and write history tables. Measure overhead on large catalogs and protect the roast schema from untrusted users.
• Results can expose object names, configuration, security findings, and query-related operational details. Apply least privilege and an explicit retention policy.
• Preloading is unnecessary for manual runs but mandatory for the background worker; changing startup-only settings requires a restart.