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.
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 = nonein the parameter group;ALTER ROLE ... SET pgaudit.log = 'all'for selected roles. -
Baseline:
pgaudit.log = 'ddl,role,write,function'in the parameter group; the samealloverride 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.
| Statement class | Flagged role (all) |
New role, opt-in | New role, baseline |
|---|---|---|---|
| DDL (CREATE / ALTER / DROP) | ✅ Logged | ❌ Not logged | ✅ Logged |
| WRITE (INSERT / UPDATE / DELETE) | ✅ Logged | ❌ Not logged | ✅ Logged |
| ROLE (GRANT) | ✅ Logged | ❌ Not logged | ✅ Logged |
FUNCTION (DO block) |
✅ Logged | ❌ Not logged | ✅ Logged |
| READ (SELECT) | ✅ Logged | ❌ Not logged | ❌ Not logged |
MISC (DISCARD PLANS) |
✅ Logged | ❌ Not logged | ❌ Not logged |
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:
pcraavi
/
PostgreSQL-Audit
Setup PostgreSQL Audit in AWS and get alerted based on Cloudwatch ,Lambda and SNS
Aurora PostgreSQL User Audit Monitor
Detect and alert on individual user DML activity on Aurora PostgreSQL clusters using pgAudit, Lambda, CloudWatch, and SNS.
Audit scope: Individual human users making direct DML changes (SELECT, INSERT, UPDATE, DELETE) on application tables. Application service accounts are deliberately excluded — their DML is expected. The risk surface is direct human access: a developer connected via psql, a support engineer running an ad-hoc update, a credential that should never have had direct database access.
Repository Structure
├── 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 attachOne 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_superusermembers are exempt, so test as the actual target role. -
pg_dumprun 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:
- Preload
pgauditand set a cluster-wide baseline ofddl, role, write, function, notnone. - Reserve
pgaudit.log = 'all'for the human and break-glass roles you name. - Leave
pgaudit.log_parameteroff unless an auditor needs values, and then scope it to a role. - Turn on
pgaudit.log_relationwhere you need per-table answers. - Set
log_statement = none; setlog_connectionsandlog_disconnectionson, at the cluster level. - If you move to a baseline, scope alerts to human accounts or to DDL and ROLE statements, or application writes will page you.
- 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.
Top comments (5)
The baseline point is the one that travels. I work mostly with MySQL and Laravel, and the same trap shows up there: audit only the accounts you remembered to flag, and the service account nobody configured is exactly the one doing the writes. Same with log_parameter — Laravel's query log with bindings switched on in production leaks the same PII into logs, and then the log store becomes a data store with weaker access controls than the database. Redacting at the source, like pg_columnmask does, is the only fix that holds; cleaning it up downstream never quite catches everything.
One thing I’d add: pg_columnmask only masks query results. It doesn’t touch the logs. If log_parameter is on, the bind values still end up in CloudWatch, so keeping log_parameter on or off is the real decision. And yes, cleaning it up downstream never catches everything.
Another thing to keep in mind is CloudWatch retention. Log groups never expire by default, and the cost quietly adds up over the months across every stack.
I manage Oracle too, and as far as I know there’s no way to turn off bind capture in Unified Auditing. Data Redaction doesn’t help with the audit records either. The difference is where the trail lives. pgAudit writes to a log file that gets shipped to CloudWatch, so the values end up outside the database’s access controls. Oracle keeps the trail inside the database in the AUDSYS schema, so the PII is still there but has the same protection as the data itself. With pgAudit, you get that only if you lock down the log group yourself. (Oracle can also write audit records out to OS files, but I haven’t seen anyone go that route.)
That said, most orgs can’t keep audit data in the database forever. It gets moved somewhere for regulatory retention, and that’s where it matters most: secure wherever the audit data ends up, and let the business and audit requirements decide whether bind values should be kept at all.
pg_columnmask is definitely a nice feature .
One more thought on Database Activity Streams, since that’s the usual answer to “make it tamper-proof.”
DAS captures everything that hits the cluster: every SELECT and INSERT from the application accounts too, with the full SQL and the bind values. You can’t scope it to users or statement types; it’s on or off for the whole cluster, and you filter after the fact. In a busy environment with dozens of clusters, that easily gets into terabytes, plus the Kinesis and KMS costs that come with it.
And at the end of the day, it still lands in S3, which you have to secure the same way you’d secure the CloudWatch log group for pgAudit. So the storage problem doesn’t go away.
The real difference is who can change what gets captured. With pgAudit, the DB team can turn auditing down. With DAS, nothing inside the database can. So the bigger question is whether the audit trail needs to be separated from the DB team at all. If DBAs are trusted operators, pgAudit with a locked-down log group is enough. If DBA activity needs to be audited too, the archive has to live somewhere the DB team doesn’t control: a separate account, its own KMS key, and write-once storage. Once that’s in place, pgAudit gets you most of the way without the terabytes.
That’s a call for security and compliance, not the DB team.
That's a really clear way to frame it — DAS doesn't remove the storage or the securing work, it just moves it to S3, and the all-or-nothing capture is a real cost at scale. I like the way you put the real question: whether the audit trail needs to be separated from the DB team at all. If the DBAs are trusted operators, pgAudit with a locked-down log group does the job; if their activity needs auditing too, then the archive has to live somewhere they don't control. That distinction helps a lot.
You're right, and the CloudWatch retention point is the one that stings in practice — log groups never expiring by default means the PII just sits there quietly getting more expensive and more exposed. Locking down the log group with its own KMS key and explicit retention is exactly the kind of thing that's easy to skip when you're just trying to get auditing turned on. The Oracle comparison is useful too — same bind-value problem, but at least the trail stays inside the database under the same access controls as the data. Thanks for adding this.