Extensions and pgaudit

pg_available_extensions

A system view listing every extension whose control file is present on this server's filesystem — the hard ceiling on what CREATE EXTENSION can install, regardless of role, database, or privilege. An extension missing from this list cannot be installed by any means short of adding its files to the server itself.

shared_preload_libraries

A postmaster-context configuration parameter listing shared libraries loaded into memory once, at server startup, before any connection is accepted. Extensions that hook deeply into query execution (pgaudit, pg_stat_statements) require their library listed here. Because the loading happens only at process start, changing this list always requires a full restart, never a reload.

log_statement

A built-in, always-available setting controlling which statement classes PostgreSQL writes to its log: none, ddl, mod, or all. It is coarse — a single server-wide switch — compared to pgaudit's ability to log specific statement classes for specific roles or specific objects independently.

🔌 Connect as postgres

Connect as postgres to audit what extensions this server actually supports.

psql -U postgres

SET psql (18.4) Type "help" for help. postgres=#

📦 Check What Is Already Loaded

Query pg_extension to see which extensions are active in the current database right now.

SELECT extname, extversion FROM pg_extension;

extname | extversion ---------+------------ plpgsql | 1.0 (1 row)

🔍 Check What Could Be Installed

Query pg_available_extensions to see how many extensions this server actually has the files to install, in any database — then check specifically for anything audit-related, rather than eyeballing a long alphabetical list.

SELECT count(*) FROM pg_available_extensions;

SELECT name FROM pg_available_extensions WHERE name ILIKE '%audit%';

pg_available_extensions includes pgaudit

📜 Install pgAudit

CREATE EXTENSION pgaudit succeeds because its files are installed and the library was preloaded.

CREATE EXTENSION pgaudit;

CREATE EXTENSION

🔧 Confirm the Preloaded Libraries

Verify shared_preload_libraries includes pgaudit and pg_stat_statements.

SELECT name, setting, context FROM pg_settings WHERE name = 'shared_preload_libraries';

shared_preload_libraries | pg_stat_statements,pgaudit | postmaster

Configure Built-In DDL Logging

Enable log_statement=ddl and reload to compare built-in logging with pgAudit read auditing.

ALTER SYSTEM SET log_statement = 'ddl';

SELECT pg_reload_conf();

ALTER SYSTEM pg_reload_conf ---------------- t (1 row)

🧪 Generate a Sensitive-Table Scenario

Reproduce the exact sequence compliance asked about: create a table, read from it, then drop it.

CREATE TABLE sensitive_table (id serial PRIMARY KEY, ssn text);

SELECT * FROM sensitive_table;

DROP TABLE sensitive_table;

CREATE TABLE id | ssn ----+----- (0 rows) DROP TABLE

📄 Grep the Log — See What Was Captured

Inspect the logging collector output through the compatibility startup.log link.

\! as-postgres tail -n 100 /var/lib/postgresql/18/data/pg_log/startup.log | grep -i "statement:"

2026-06-26 16:02:49.699 UTC [133] STATEMENT: CREATE EXTENSION pgaudit; 2026-06-26 16:02:55.269 UTC [133] LOG: statement: CREATE TABLE sensitive_table (id serial PRIMARY KEY, ssn text); 2026-06-26 16:02:59.228 UTC [133] LOG: statement: DROP TABLE sensitive_table;

🔎 Capture Reads with pgAudit

Enable pgAudit read logging in this session, create and read a sensitive table, then inspect its AUDIT log entry.

SET pgaudit.log = 'read';

CREATE TABLE sensitive_table (id serial PRIMARY KEY, ssn text);

SELECT * FROM sensitive_table;

\! as-postgres tail -n 100 /var/lib/postgresql/18/data/pg_log/startup.log | grep -i 'AUDIT.*SELECT.*sensitive_table'

DROP TABLE sensitive_table;

AUDIT: SESSION ... READ,SELECT ... sensitive_table