Export Database Audit Logs to Object Storage

Enable pgaudit for DDL and role changes, ship the log stream to object storage, and keep tamper-resistant copies for compliance review.

~40 minauditpostgresqlcompliance

Task

Produce a durable, queryable trail of privileged database activity that survives log rotation and local administrator mistakes.

When to use this

Compliance asks who dropped an index and who granted a superuser role last quarter. PostgreSQL logs exist, but they rotate after seven days and any DBA with root can delete them, so there is no reliable answer.

Prerequisites

PostgreSQL 15 or newer with permission to edit postgresql.conf
The pgaudit extension installed on the server
A log shipper such as Vector or Fluent Bit
An object storage bucket with object lock or versioning

Steps

1. Load pgaudit and scope it to privileged statement classes to control log volume.

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'
pgaudit.log_relation = on
log_line_prefix = '%m [%p] %u@%d '
logging_collector = on

Expected output: After a restart, CREATE, ALTER, DROP, GRANT, and REVOKE statements emit AUDIT log lines with user and database.

2. Create the extension in each database that must be audited.

psql -U postgres -d appdb -c 'CREATE EXTENSION IF NOT EXISTS pgaudit;'

Expected output: CREATE EXTENSION

3. Ship PostgreSQL log files to a versioned bucket with server-side encryption.

[sources.pg]
type = "file"
include = ["/var/log/postgresql/*.log"]

[sinks.s3]
type = "aws_s3"
inputs = ["pg"]
bucket = "acme-audit-logs"
region = "us-east-1"
encoding.codec = "json"
compression = "gzip"

Expected output: New log lines arrive as compressed JSON objects under a date-based prefix within one minute.

4. Prove the trail captures a privileged change and can be retrieved by user and statement.

sudo -u postgres psql -d appdb -c "CREATE ROLE auditor LOGIN;" && grep "AUDIT: SESSION" /var/log/postgresql/postgresql-16-main.log | tail -n 1

Expected output: A line containing statement:CREATE ROLE auditor LOGIN, the postgres user, and the database name.

Troubleshooting

Problem

No audit lines appear after the configuration change

Solution

shared_preload_libraries requires a full restart, not a reload; confirm with SHOW shared_preload_libraries and check the server log for extension load errors.

Problem

Log volume grows faster than the bucket budget

Solution

Narrow pgaudit.log to ddl, role and add specific relations only where required; avoid auditing all reads on high-traffic tables.

Limitations

pgaudit records statements and relation names, not the rows read or changed.
A local administrator can delete files before the shipper reads them; near-real-time shipping and bucket object lock reduce but do not eliminate that risk.

Audit what matters

Full statement logging is expensive and noisy. pgaudit lets you select classes such as ddl, role, and write, so you capture schema changes, privilege grants, and data modifications without logging every SELECT. pgaudit.log_relation adds relation names to write statements, which makes the trail useful when investigating a specific table.

Centralize immediately

Logs stored only on the database host are vulnerable to rotation, disk loss, and local deletion. A shipper should read the files and write compressed objects to a bucket with versioning or object lock. Keep the pipeline close to real time; a five-minute delay is acceptable, a daily batch is not, because the exposure window equals the delay.

pgaudit.log = 'ddl, role, write'
pgaudit.log_relation = on

Make it reviewable

Store objects under a partitioned prefix such as year=2026/month=10/day=21/ so a query engine can scan a date range cheaply. Retain at least the compliance window, often one year, and move older data to cold storage with lifecycle rules. Restrict bucket write access to the shipper identity and read access to the audit team; the application role should have neither.

Test the trail

Perform a privileged change and follow it from the database log to the object in the bucket. Search by user, statement, and time. If the log line exists locally but not remotely, the shipper is broken. If neither exists, the extension is not loaded. Run this test after every PostgreSQL major upgrade, because preload settings and extension versions change.