Keep role- and object-level audit logs with pgaudit

Command

-- Prerequisite: shared_preload_libraries = 'pgaudit' + restart, then:
CREATE EXTENSION pgaudit;

-- Session auditing: server-wide, only DDL and role/privilege changes
ALTER SYSTEM SET pgaudit.log = 'ddl, role';
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_parameter = off;
SELECT pg_reload_conf();

-- All reads/writes of a specific application role
ALTER ROLE web_app SET pgaudit.log = 'read, write';

-- Object auditing: privileges granted to the auditor role = operations to log
CREATE ROLE auditor NOLOGIN;
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();
GRANT SELECT, DELETE ON customer TO auditor;
GRANT UPDATE (balance) ON customer TO auditor;

What it does

Records server-wide DDL and privilege changes, all reads/writes of a specific role, and access to sensitive tables/columns granted to the auditor role, as structured log lines prefixed with 'AUDIT:'.

When to use it

To prove who accessed sensitive data in compliance audits such as GDPR, PCI-DSS or SOX.

Caution

Enabling pgaudit.log = 'all' or 'read' server-wide sharply increases log volume and I/O; narrow the scope with role or object auditing. A superuser can switch auditing off (ALTER SYSTEM/ALTER ROLE); ship the records to a central log system (syslog/SIEM) and store them independently of the database server. log_parameter = on also logs bind parameters (personal data). Role settings take effect in new sessions. The pgaudit major version must match the PostgreSQL major version (pgaudit 17.x = PG17).