When we moved a set of Oracle workloads to Aurora PostgreSQL, the compliance ask sounded simple: audit every statement that touches sensitive tables. Set pgaudit.log = 'all' everywhere and you have it covered, right?

Not quite. Turning auditing all the way up creates two new problems. The log stream gets so noisy that nobody reads it, so a real incident hides among thousands of driver and IDE catalog queries. And with the wrong parameter, the audit log quietly becomes another copy of the sensitive data you were trying to protect.

This is a follow-up to part 1, which covered the alerting pipeline: per-user pgAudit, the Logs Insights noise filter, and the Lambda that turns query results into a CloudWatch alarm.

What It Actually Takes to Audit Aurora PostgreSQL on AWS

Pranay Ravi

Pranay Ravi

Pranay Ravi

Follow

May 21

What It Actually Takes to Audit Aurora PostgreSQL on AWS

#aws #postgres #security #devops

Add Comment

7 min read

This post covers what came next: testing two pgAudit configurations side by side, four parameters that behaved differently than we expected, and the layers that protect the data itself.

Cluster names, accounts, users and data in every example are placeholders.

The architecture at a glance

No single tool both logs everything and protects sensitive text, so the design has three layers: capture (pgAudit), protect (masking and access control) and store and alert (CloudWatch, S3 and friends).

                    Aurora PostgreSQL cluster
          (pgAudit writes AUDIT lines · pg_columnmask masks reads)
                 │                                   │
            log export                        activity stream
                 ▼                                   ▼
          CloudWatch Logs                 Database Activity Streams
                 │                        (Kinesis, KMS, DBAs can't alter)
        ┌────────┴─────────┐                         │
        ▼                  ▼                         ▼
 Lambda (every 5 min)   Firehose                  Firehose
        │                  │                         │
        ▼                  ▼                         ▼
 Metric + alarm      S3 audit archive         S3 activity archive
        │            (SSE-KMS, Glacier)       (separate KMS key)
        ▼                  │
       SNS                 ▼
                     Athena + Macie

The left two columns share one CloudWatch log group: one path turns it into alerts, the other into a long-term archive. The right column runs alongside and depends on nothing a database user can configure.

Setup, briefly

Part 1 walks through enabling pgAudit, including the silent failure when CREATE EXTENSION pgaudit is missing. The short version: preload pgaudit in the cluster parameter group, reboot, create the extension in each database, set pgaudit.log per role, and export the postgresql log to CloudWatch Logs.

Audit lines land in the log group /aws/rds/cluster/<cluster>/postgresql, one stream per instance. Each one starts with AUDIT: SESSION, followed by the statement class (READ, WRITE, DDL, ROLE, FUNCTION, MISC), the command, the object and the statement text.

Opt-in or baseline? We tested both

A cluster-wide baseline of ddl, role, write, function plus all for a few named roles beat opt-in auditing in our tests. Part 1 scoped auditing to named human accounts only. Another team's cluster used a cluster-wide baseline instead, so we ran the same script against both.

• Opt-in: pgaudit.log = none in the parameter group; ALTER ROLE ... SET pgaudit.log = 'all' for selected roles.
• Baseline: pgaudit.log = 'ddl,role,write,function' in the parameter group; the same all override for selected roles.

The script created a schema and table, ran INSERT/UPDATE/DELETE, a SELECT, a CREATE/ALTER/DROP, an anonymous DO block, a GRANT and DISCARD PLANS. We ran it as a flagged role and as a brand-new application role nobody had configured.

Opt-in has a blind spot exactly where you least want one: any account nobody remembered to flag is invisible, including one created yesterday. The baseline still records every schema change, data change and privilege change from every role, and keeps high-volume SELECT logging to the roles you name.

One caveat applies to both: these are role and session settings. Anyone with enough privilege to change role settings can turn their own auditing down, which is the gap Database Activity Streams (covered below) closes.

Four parameters that changed our design

pgaudit.log_parameter: the audit log becomes a data store

With log_parameter = on, pgAudit writes bind values into the last field of each line. That is what makes the audit trail useful for reconstructing who changed what. It is also how a patient name or card number ends up in CloudWatch Logs. The same prepared INSERT, logged both ways:

# log_parameter = on
AUDIT: SESSION,17,1,WRITE,INSERT,,,"PREPARE test_insert(INT, TEXT) AS
    INSERT INTO pgaudit_test.audit_test (id, name) VALUES ($1, $2);","101,Jane Example"

# log_parameter = off
AUDIT: SESSION,17,1,WRITE,INSERT,,,"PREPARE test_insert(INT, TEXT) AS
    INSERT INTO pgaudit_test.audit_test (id, name) VALUES ($1, $2);",<not logged>

We keep it off by default. Where an auditor needs values, we enable it per role with ALTER ROLE ... SET pgaudit.log_parameter = on, and treat that log group like the source table: KMS-encrypted, narrow IAM read access, short retention.

⚠️ Statements written with inline literals carry their values in the statement text regardless of this setting.

pgaudit.log_relation: answering "who touched this table?"

With it off, the object-type and object-name fields of a SELECT or DML line are empty (WRITE,INSERT,,,). With it on, pgAudit fills them (WRITE,INSERT,TABLE,pgaudit_test.customer) and logs a line per relation the statement references.

"Who touched this table in the last 24 hours" becomes a field filter instead of a regex over SQL text. It can be scoped per database (ALTER DATABASE app_db SET pgaudit.log_relation = on); the cost is extra lines for joins and multi-table statements.

log_statement: everything logged twice

Native log_statement and pgAudit run independently. On a cluster with log_statement = 'ddl', every CREATE and DROP appeared twice: once as a plain LOG: statement: line and once as an AUDIT: line. Set it to none in the cluster parameter group. For a debugging session, scope it and clean up afterwards:

ALTER ROLE app_admin IN DATABASE app_db SET log_statement = 'all';
-- ...debug...
ALTER ROLE app_admin IN DATABASE app_db RESET log_statement;

log_connections and log_disconnections: cluster level only

These are read when a session starts, so in our tests per-database and per-role settings had no effect. They have to be on in the cluster parameter group. One of our two clusters had them off, which meant its audit trail showed statements but not session start, end or duration.

What a baseline means for alerting

The alerting path is the one from part 1: EventBridge runs a Lambda every 5 minutes, the Lambda runs the Logs Insights noise filter and publishes a count, and a CloudWatch alarm notifies SNS. Code, IAM and SNS/KMS policies and a Terraform module are in the repo:

├── README.md
├── deploy-all.sh                        # Full CLI deployment sequence (single account)
│
├── lambda/
│   ├── lambda_function.py               # Core audit monitor — Log Insights query, metric publish
│   └── deploy-lambda.sh                 # Package and deploy / update Lambda
│
├── iam/
│   ├── lambda-trust-policy.json         # Lambda execution role trust policy
│   ├── lambda-permissions-policy.json   # Least-privilege: Log Insights read + CW metric write
│   └── deploy-iam.sh                    # Create role and attach

One thing changes if you adopt the baseline: application accounts now log their writes too. A count > 0 alarm built for human-only auditing would fire on normal traffic. Either filter on the human accounts' user names (they appear in each line's user@database prefix), or alert on DDL and ROLE statements only for everyone else.

Protecting the data and keeping the trail

pgAudit answers "what happened." Three more pieces cover "who could see it" and "can we prove it later."

Mask at query time with pg_columnmask

Aurora PostgreSQL 16.10+ and 17.6+ ship pg_columnmask, which masks column values at query time based on the caller's role and leaves stored data unchanged (AWS docs). A support role can see a partial email while the application role sees the real value, and weights decide which policy wins when several apply. Pair it with PostgreSQL row-level security when you also need to limit which rows a role sees.

Three details from the AWS troubleshooting guide are worth knowing before rollout:

• Table owners and rds_superuser members are exempt, so test as the actual target role.
• pg_dump run as a masked role produces masked data, which can break constraints on restore.
• A materialized view is masked according to its owner's policy at refresh time, regardless of who reads it.

Close the insider gap with Database Activity Streams

Anyone who can change role settings can turn pgAudit down for themselves. Part 1 explained why we passed on Database Activity Streams for alerting: Kinesis as a dependency, per-event cost and a consumer layer to build. For the insider case they are still the answer, because the stream is KMS-encrypted and DBAs can't read or alter it.

Aurora PostgreSQL supports synchronous mode, where a session waits until its event is durable, trading latency for completeness. Before turning it on, check that your instance class is supported, and plan for a restart: starting the stream with "apply immediately" restarts the cluster.

Keep the trail queryable, and scan it

CloudWatch Logs is a poor long-term archive. A subscription filter sends the PostgreSQL log through Firehose to an S3 bucket encrypted with a dedicated KMS key, where Athena can query years of history and a lifecycle rule moves old objects to Glacier.

Point an Amazon Macie classification job at that bucket on a schedule. If sensitive values slip into the logs through log_parameter or inline literals, Macie is how you find out before an auditor does.

# Bucket policy essentials: deny unencrypted writes and non-TLS access
# "Condition": {"StringNotEquals": {"s3:x-amz-server-side-encryption": "aws:kms"}}
# "Condition": {"Bool": {"aws:SecureTransport": "false"}}

aws macie2 create-classification-job \
  --name audit-log-pii-scan \
  --job-type SCHEDULED \
  --schedule-frequency '{"weeklySchedule":{"dayOfWeek":"MONDAY"}}' \
  --s3-job-definition '{"bucketDefinitions":[{"accountId":"111122223333","buckets":["my-audit-log-bucket"]}]}'

The short version

If you are about to turn on auditing for Aurora PostgreSQL, this is the checklist we would hand you:

1. Preload pgaudit and set a cluster-wide baseline of ddl, role, write, function, not none.
2. Reserve pgaudit.log = 'all' for the human and break-glass roles you name.
3. Leave pgaudit.log_parameter off unless an auditor needs values, and then scope it to a role.
4. Turn on pgaudit.log_relation where you need per-table answers.
5. Set log_statement = none; set log_connections and log_disconnections on, at the cluster level.
6. If you move to a baseline, scope alerts to human accounts or to DDL and ROLE statements, or application writes will page you.
7. Encrypt, retain and scan the archive; add masking for readers and Activity Streams for the insider case.

Auditing is easy to switch on and hard to make useful. Most of the work is deciding what not to log, and making sure the log itself doesn't become the next thing you need to protect.

Have you run pgAudit with a cluster-wide baseline, or put pg_columnmask in front of a support team yet? Drop a comment. I'm curious how others are handling log_parameter in regulated environments.