Search across all documentation pages
Compliance programs need evidence: who connected, which tables were read or written, and which DDL ran. PostgreSQL native logging plus the pgaudit extension produces structured audit lines suitable for SIEM retention. This checklist covers enablement, scope, and volume control for PostgreSQL 18.4.
pgaudit.log scope, expand only where regulations requireCREATE EXTENSION pgaudit; on databases under audit scope.shared_preload_libraries includes pgaudit on self-hosted; restart once after change.log_line_prefix includes user, db, app, client IP, session id. Parseable in SIEM.log_connections and log_disconnections on. Baseline session audit without pgaudit row detail.log_statement = ddl minimum. Captures schema changes even if pgaudit misconfigured.pgaudit.log set per role, not globally all. Reduces noise and cost.write or ddl as required. OLTP often needs write on PII tables only via object audit.ddl, role. Track privilege and schema changes.pgaudit.log_relation or catalog-specific policies.read class) only on regulated tables. Full read logging is extremely verbose.postgres.log_statement=all detection.-- Confirm extension
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';
-- Role audit settings
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig IS NOT NULL
AND EXISTS (
SELECT 1 FROM unnest(rolconfig) c WHERE c LIKE 'pgaudit%'
);pgaudit logs access and changes at statement level in server logs. Triggers store before/after row images in tables. Use both when regulations require query text and row-level proof.
Often available as allowlisted extension. Enable via parameter group and CREATE EXTENSION.
Very. A single BI scan can emit thousands of lines. Scope read to specific tables via object audit.
Yes when COPY touches audited relations. Bulk loads need explicit runbook approval.
Superuser can. Prevent apps from superuser; monitor ALTER ROLE and config changes.
Audit logs may retain user ids under legal basis. Document in DPIA; do not delete audit evidence prematurely.
Publisher/subscriber DDL should be in migrator audit scope. Row changes replicate, audit stays on primary writer.
Moderate at high QPS with broad all logging. Tune scope; use sampling only if regulators accept (rare).
Session logs, DDL logs, admin actions, centralized retention, quarterly review evidence.
30 days of baseline logs before audit week proves ongoing operation, not last-minute toggle.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 19, 2026