PostgreSQL audit logging with pgAudit: install it, choose what to log and keep the volume under control

#The goal

An auditor or security team asks: "Who changed this table, who granted that role, and who read the salary data?" log_statement = 'all' answers some of it, but it logs every query as plain text, does not tell you which tables a statement touched, and fills the disk. pgAudit writes structured AUDIT: lines to the normal PostgreSQL log, by class of statement (DDL, role changes, reads, writes) and optionally per table.

This post sets up a baseline that most teams can leave on in production:

• Session audit of DDL and role/privilege changes for everyone.
• Object audit of reads and writes on a short list of sensitive tables.
• Noise controls so the log stays usable.

#Step 1: Install and load the extension

pgAudit must be in shared_preload_libraries, which needs a restart. pgAudit releases are tied to a PostgreSQL major version, so install the build for your major version.

#Self-managed (PGDG packages)

# Debian/Ubuntu (PGDG repo); replace 16 with your major version
sudo apt-get install postgresql-16-pgaudit

# RHEL/Rocky/Oracle Linux (PGDG repo); package names vary by repo version
sudo dnf install pgaudit_16

Add it to postgresql.conf, keeping anything already listed:

shared_preload_libraries = 'pg_stat_statements,pgaudit'

Restart, then create the extension in each database you want to audit:

sudo systemctl restart postgresql-16    # service name varies by distro
psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pgaudit;"

#Amazon RDS and Aurora PostgreSQL

Edit the custom parameter group (the default group cannot be changed), then reboot:

aws rds modify-db-parameter-group \
  --db-parameter-group-name pg16-prod \
  --parameters "ParameterName=shared_preload_libraries,ParameterValue='pg_stat_statements,pgaudit',ApplyMethod=pending-reboot"

aws rds reboot-db-instance --db-instance-identifier orders-prod

For Aurora, the parameter is in the DB cluster parameter group (modify-db-cluster-parameter-group). Send the log to CloudWatch so audit lines leave the instance:

aws rds modify-db-instance --db-instance-identifier orders-prod \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql"]}'

#Azure Database for PostgreSQL Flexible Server

Allow-list the extension, preload it, restart:

az postgres flexible-server parameter set -g rg-data --server-name pg-orders-prod \
  --name azure.extensions --value PGAUDIT

az postgres flexible-server parameter set -g rg-data --server-name pg-orders-prod \
  --name shared_preload_libraries --value "pg_stat_statements,pgaudit"

az postgres flexible-server restart -g rg-data --name pg-orders-prod

If azure.extensions already has values, include them in the comma-separated list, or you will remove them. Then run CREATE EXTENSION pgaudit; in each database and configure a diagnostic setting that sends PostgreSQL server logs to a Log Analytics workspace.

Warning

shared_preload_libraries changes need a restart. Schedule it, and on HA setups follow the provider's normal maintenance path. A typo in this parameter can stop a self-managed server from starting; check the log if it does not come back.

Verify:

SHOW shared_preload_libraries;
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

#Step 2: Make the log line say who and from where

pgAudit lines carry the statement, not the user or client. Those come from log_line_prefix. Make sure it includes at least the timestamp, user, database, application and client host:

log_line_prefix = '%m [%p] user=%u db=%d app=%a client=%h '

On RDS and Azure, set log_line_prefix in the parameter group or server parameters; some managed services fix part of the prefix, so check what yours allows.

#Step 3: Session audit for everyone

Start with the classes that change structure and access. They are low-volume and they are what most audits ask for.

Set the server-wide baseline (in postgresql.conf, the parameter group or server parameters):

pgaudit.log = 'ddl, role'
pgaudit.log_catalog = off          # skip queries that only touch pg_catalog (psql \d, GUI tools)
pgaudit.log_relation = on          # one line per table referenced, so you can search by table
pgaudit.log_statement_once = on    # don't repeat the statement text on every sub-line
pgaudit.log_parameter = off        # see the caution below

You can scope extra auditing to a role or database instead of the whole server:

-- Audit every write made by the migration role
ALTER ROLE deploy_bot SET pgaudit.log = 'ddl, role, write';

-- Audit DDL and writes in the finance database only
ALTER DATABASE finance SET pgaudit.log = 'ddl, role, write';

These take effect for new sessions. Check what is set:

SELECT coalesce(d.datname, '(all)') AS db,
       coalesce(r.rolname, '(all)') AS role,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r   ON r.oid = s.setrole;

Caution

pgaudit.log_parameter = on writes bind parameter values into the log. That can put passwords, card numbers or personal data into log files and your SIEM. Leave it off unless you have a reason and have checked where the logs go.

#Step 4: Object audit for sensitive tables

Logging all READ statements server-wide is usually too much. Object audit logs only statements that touch tables an audit role has privileges on.

CREATE ROLE auditor NOLOGIN;

-- Log SELECTs on the salary table, and any write to the customer PII table
GRANT SELECT ON payroll.salary TO auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON crm.customer_pii TO auditor;

Then point pgAudit at that role:

pgaudit.role = 'auditor'

On Amazon RDS, the documented pattern is to create a role named rds_pgaudit and set pgaudit.role = rds_pgaudit in the parameter group; the grants work the same way.

The audit role never logs in. The grants are just a list: "audit these actions on these tables". Adding a table to the audit is one GRANT, removing it is one REVOKE.

#Step 5: Test it and read the output

CREATE TABLE public.audit_test (id int);
SELECT * FROM payroll.salary LIMIT 1;
DROP TABLE public.audit_test;

Expected log lines (prefix shortened):

user=dba db=appdb app=psql client=10.0.4.12 LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.audit_test,CREATE TABLE public.audit_test (id int);,<not logged>
user=dba db=appdb app=psql client=10.0.4.12 LOG:  AUDIT: OBJECT,2,1,READ,SELECT,TABLE,payroll.salary,SELECT * FROM payroll.salary LIMIT 1;,<not logged>

The fields are: audit type (SESSION or OBJECT), statement ID, substatement ID, class, command, object type, object name, statement, parameters.

Search them where the logs land:

# Self-managed
grep "AUDIT: " /var/lib/pgsql/16/data/log/postgresql-*.log | grep ",ROLE,"

# Amazon RDS via CloudWatch Logs
aws logs filter-log-events \
  --log-group-name /aws/rds/instance/orders-prod/postgresql \
  --filter-pattern '"AUDIT: SESSION" "DDL"'

On Azure, with diagnostic settings sending to Log Analytics (table and column names vary by setting type; check your workspace):

AzureDiagnostics
| where Category == "PostgreSQLLogs"
| where Message has "AUDIT:"
| project TimeGenerated, Message
| order by TimeGenerated desc

#Keep the volume under control

Tip

Measure before and after. Note the daily log size for a week with your baseline, then add one class or table at a time. Audit logging that fills the log volume is an outage, not a control.

#Prevent it next time

• Treat superusers as auditable but not trustworthy for the audit itself: a superuser can change pgaudit.* settings for their session. Ship logs off the server (syslog, CloudWatch, Log Analytics, a SIEM) so they cannot be edited locally.
• Add CREATE EXTENSION pgaudit; and the auditor role to your new-database template or provisioning code, so new databases are audited from day one.
• After a major version upgrade, install the matching pgAudit package before starting the new cluster, then run ALTER EXTENSION pgaudit UPDATE; in each database.
• Review the object-audit grant list each quarter: \dp payroll.* shows which tables auditor still covers.