PostgreSQL Audit Logging on RDS for PCI DSS and ISO 27001

The first thing auditors ask for when reviewing a PostgreSQL RDS instance for PCI DSS v4 or ISO 27001 is evidence of who accessed what data and when. PostgreSQL's built-in logging isn't sufficient for this — it doesn't capture DDL changes, privilege escalations, or SELECT statements on sensitive tables by default. The pgaudit extension fills that gap, and it's available on RDS without any custom compilation.

The challenge is configuration. Logging everything is straightforward but creates log volumes that are unusable and storage costs that are significant. This guide covers the practical configuration that satisfies the specific controls auditors check — without logging every SELECT on every table.

What pgaudit adds that standard logging doesn't

PostgreSQL's standard log_statement = 'all' logs query text but lacks the structured metadata auditors need: the object name, schema, command class, and whether the statement succeeded or failed. pgaudit adds a structured audit record with fields for AUDIT_TYPE, OBJECT_TYPE, OBJECT_NAME, and COMMAND — parseable by a SIEM or CloudWatch Logs Insights query.

Standard log output for a DELETE:

2026-05-15 14:23:01 UTC [1234]: LOG: statement: DELETE FROM payments WHERE id = 42

pgaudit log output for the same DELETE:

2026-05-15 14:23:01 UTC [1234]: LOG: AUDIT: OBJECT,1,1,WRITE,DELETE,TABLE,
  public.payments,"DELETE FROM payments WHERE id = 42",<not logged>

The second format is what a SIEM can parse automatically, and what an auditor can filter to show "all DELETE operations on the payments table by user X in Q3 2026."

Enabling pgaudit on RDS

pgaudit is a shared preload library — it must be loaded at server startup and cannot be enabled without a parameter group change and a reboot. On RDS:

Step 1. Create a custom parameter group (if you're using the default, you can't modify it). Ensure the parameter group family matches your PostgreSQL version: postgres16, postgres17, or postgres18.

Step 2. Set the following parameters in the parameter group:

Step 3. Associate the parameter group with your RDS instance and schedule a maintenance window reboot. The shared_preload_libraries parameter is static — it requires a full instance restart to take effect.

Step 4. After reboot, verify pgaudit is loaded:

SHOW shared_preload_libraries;
SELECT * FROM pg_available_extensions WHERE name = 'pgaudit';

Choosing what to log: audit scopes

The pgaudit.log parameter accepts a comma-separated list of log classes. The most common mistake is setting it to all immediately — on a busy OLTP database, this generates gigabytes of log per hour and degrades performance measurably. Start with the minimum for your compliance requirement and expand only if needed:

Object-level auditing for specific tables

For PCI DSS scope, you typically only need to audit DML on cardholder data tables — not every table in the database. pgaudit supports object-level auditing by granting the AUDIT role on specific tables to a marker role:

-- Create the audit marker role (do this once)
CREATE ROLE rds_pgaudit;

-- Grant SELECT audit to the audit role for a specific table
GRANT SELECT ON public.card_tokens TO rds_pgaudit;
GRANT SELECT ON public.payments     TO rds_pgaudit;

-- Set pgaudit.role to enable object-level auditing
-- (set this in the parameter group or via ALTER SYSTEM)
-- pgaudit.role = 'rds_pgaudit'

With this configuration, pgaudit logs any SELECT on card_tokens or payments by any user — regardless of the session-level log scope. All other tables are unaffected. This is the pattern that satisfies PCI DSS Requirement 10.2.1(b) (access to cardholder data) without flooding logs.

Exporting logs to CloudWatch and S3

Audit logs need to be retained for a minimum of 12 months under PCI DSS (3 months immediately available, 9 months accessible). RDS log files rotate and are not retained beyond a few days on the instance. The solution is CloudWatch Logs export:

In the RDS console under Logs & events, enable PostgreSQL log export to CloudWatch Logs. Once logs are in CloudWatch, set a log group retention policy of 90 days for the immediately-accessible window, then set up an S3 export for long-term retention:

aws logs put-retention-policy \
  --log-group-name /aws/rds/instance/your-db-id/postgresql \
  --retention-in-days 90

# Export older logs to S3 for the 9-month archival window
aws logs create-export-task \
  --log-group-name /aws/rds/instance/your-db-id/postgresql \
  --from $(date -d "90 days ago" +%s000) \
  --to $(date +%s000) \
  --destination your-audit-log-bucket \
  --destination-prefix rds-audit-logs/

For ongoing archival, set up an S3 Lifecycle rule on the audit bucket to transition logs to S3 Glacier Instant Retrieval after 90 days — this reduces storage cost significantly while keeping the logs accessible within minutes for an audit request.

Querying audit logs in CloudWatch Logs Insights

During an audit or incident investigation, this query returns all pgaudit DDL events from the last 7 days:

fields @timestamp, @message
| filter @message like /AUDIT.*DDL/
| sort @timestamp desc
| limit 200

To find all WRITE operations by a specific user on a specific table:

fields @timestamp, @message
| filter @message like /AUDIT.*WRITE.*payments/
| filter @message like /your_username/
| sort @timestamp desc