pgAudit

What pgAudit does

PostgreSQL has built-in statement logging via log_statement, but the output is unstructured text — a pile of receipts tossed into a drawer. Useful if you remember what you are looking for. Useless if someone else is asking the questions. pgAudit replaces this with structured audit log entries prefixed with AUDIT: and containing labeled fields: the audit type (session or object), statement ID, substatement ID, command class (READ, WRITE, DDL), command tag, object type and name, the full SQL text, and optionally the bound parameter values.

This structure is what makes pgAudit useful for compliance. Auditors need to answer specific questions — "who accessed the customers table last Tuesday?" or "were any DDL changes made to the payments schema?" — and pgAudit's formatted output makes those questions answerable by log analysis tools without custom parsing.

pgAudit classifies statements into categories: READ (SELECT, COPY from), WRITE (INSERT, UPDATE, DELETE, TRUNCATE), DDL (CREATE, ALTER, DROP), ROLE (GRANT, REVOKE), FUNCTION (function calls, DO blocks), and MISC (VACUUM, SET, etc.). You choose which classes to log based on your compliance requirements.

When to use pgAudit

pgAudit is the right choice when you need audit logging that goes beyond basic troubleshooting.

• Regulatory compliance — SOC 2, HIPAA, PCI-DSS, and GDPR all require audit trails of access to sensitive data. pgAudit is the standard PostgreSQL tool for this.
• Auditing access to specific tables — object audit logging lets you monitor exactly who reads or modifies sensitive tables (customer PII, payment records, health data) without logging every query in the database.
• Change tracking for DDL — log all schema changes (CREATE, ALTER, DROP) to maintain a record of who changed what and when.
• Security incident investigation — structured audit logs with statement classification make it possible to reconstruct what happened during a suspected breach.
• Separation of duties — demonstrate to auditors that database access is logged independently of the application layer, providing a tamper-evident record.

Installation and setup

pgAudit must be loaded as a shared library at server startup. This means a one-time restart is required when enabling it for the first time. On most Linux distributions, install the package first (postgresql-{version}-pgaudit on Debian/Ubuntu, pgaudit_{version} on RHEL/CentOS), then configure PostgreSQL to load it.

-- 1. Add to postgresql.conf (requires restart)
shared_preload_libraries = 'pgaudit'

-- 2. Restart PostgreSQL, then create the extension
CREATE EXTENSION pgaudit;

-- 3. Configure which statement classes to log
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl';
SELECT pg_reload_conf();

After the restart and configuration reload, pgAudit begins logging immediately. The default value of pgaudit.log is none — everything off until you say otherwise. A sensible default. One does not begin recording before agreeing on what to record.

Session audit logging

Session audit logging captures all statements matching the pgaudit.log classes, regardless of which tables they affect. It is the simpler of the two modes and the right starting point for most deployments.

-- Session audit logging: logs all statements matching pgaudit.log classes
-- Configure at the system level
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();

-- Or configure per database
ALTER DATABASE myapp SET pgaudit.log = 'read, write, ddl';

-- Or configure per role
ALTER ROLE audited_user SET pgaudit.log = 'all';

Session logging can be configured at the system, database, or role level. This flexibility lets you log all DDL changes globally while only logging reads for specific high-risk databases or roles.

Object audit logging

Object audit logging provides fine-grained control by logging only statements that affect specific tables. It works through PostgreSQL's GRANT mechanism: you create a dedicated audit role, grant it permissions on the tables you want to monitor, and pgAudit logs any statement that would require those permissions.

-- Object audit logging: logs statements on specific objects via GRANT
-- 1. Create a dedicated audit role (no login needed)
CREATE ROLE audit_role NOLOGIN;

-- 2. Tell pgAudit to use this role for object auditing
ALTER SYSTEM SET pgaudit.role = 'audit_role';
SELECT pg_reload_conf();

-- 3. Grant permissions on sensitive tables to the audit role
-- Any statement that would require these permissions will be logged
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO audit_role;
GRANT SELECT ON payment_methods TO audit_role;

-- Now any SELECT, INSERT, UPDATE, or DELETE on these tables
-- will generate an audit log entry, regardless of who runs the query

The elegance of this approach is worth appreciating. You are not writing audit rules — you are expressing them through permissions, a mechanism PostgreSQL already understands intimately. Object audit logging focuses coverage on tables containing protected data — customer PII, payment information, health records — without generating logs for every routine query in the database. Session and object logging can be used simultaneously.

Log output format

pgAudit entries appear in the standard PostgreSQL log with a structured AUDIT: prefix. Each entry includes the audit type, statement and substatement IDs, command class, command tag, object type and name, the SQL text, and optionally the parameter values.

-- Example audit log entries (from PostgreSQL log)
-- Session audit log entry:
-- AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM customers WHERE id = 42,<not logged>

-- Object audit log entry:
-- AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.customers,SELECT * FROM customers WHERE id = 42,<not logged>

-- Enable parameter logging to capture bound values
ALTER SYSTEM SET pgaudit.log_parameter = on;
SELECT pg_reload_conf();

-- With log_parameter enabled:
-- AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM customers WHERE id = $1,42

Enable pgaudit.log_parameter when you need to capture the actual values passed to queries, not just the SQL text. An audit trail that records "someone queried the customers table" is adequate. An audit trail that records "someone queried customer 42 at 14:07 UTC" is thorough. Compliance auditors, in my experience, have a marked preference for thorough.

Log class reference

The pgaudit.log parameter accepts a comma-separated list of statement classes to audit. Classes can be subtracted with a minus prefix for fine-grained control.

-- pgaudit.log class reference
-- READ      SELECT and COPY when source is a relation
-- WRITE     INSERT, UPDATE, DELETE, TRUNCATE, COPY to a relation
-- FUNCTION  Function calls and DO blocks
-- ROLE      GRANT, REVOKE, CREATE/ALTER/DROP ROLE
-- DDL       CREATE, ALTER, DROP (except ROLE statements)
-- MISC      DISCARD, FETCH, CHECKPOINT, VACUUM, SET
-- MISC_SET  SET and RESET commands specifically
-- ALL       All of the above
-- NONE      No logging (default)

-- Combine classes with commas
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl';

-- Subtract classes with a minus prefix
ALTER SYSTEM SET pgaudit.log = 'all, -misc, -misc_set';

Cloud availability

How Gold Lapel relates

Allow me to be candid about the relationship here, because overstating it would do neither of us any favours. pgAudit adds overhead — every logged statement requires additional I/O to write structured entries, and on write-heavy workloads with broad logging classes, this becomes measurable. The more queries your database handles, the more audit logging costs.

Gold Lapel helps offset this cost, though indirectly. By optimizing query patterns at the proxy level — reducing redundant queries, improving execution plans, eliminating unnecessary round trips — Gold Lapel reduces the total number of statements that reach PostgreSQL. Fewer statements means fewer audit log entries for the same application workload, which reduces both the I/O overhead and the log storage volume.

I would not list this as a reason to adopt Gold Lapel. But if you are already running pgAudit on a high-throughput database and the logging overhead concerns you, the query reduction is a genuine side benefit. The best way to reduce the cost of recording everything is to have less that needs recording.