Database Auditing on AWS Aurora PostgreSQL

What a mature audit pipeline looks like, why PostgreSQL on AWS requires more assembly to get there, and every sharp edge encountered along the way

Where This Came From

Most operational infrastructure starts this way: a requirement appears before the architecture does.

One day, the team needed a database audit solution. Not in a planning doc — someone asked, and I had to build something. I’d already owned the Oracle audit pipeline, so I knew what the destination looked like. The question was what it would take to get there on Aurora PostgreSQL and AWS.

The experience revealed something worth documenting: the gap between what Oracle Unified Auditing gives you natively and what you have to assemble yourself on PostgreSQL is real, and the assembly process has sharp edges that aren’t surfaced in documentation. This article is the honest account of both.

Defining the Audit Scope — and Why It Shapes Everything

Before discussing tools, the scope needs to be precise.

We are not auditing application traffic. Application service accounts are expected to read and write data — that’s their defined function. Auditing them generates noise at scale and produces no meaningful signal. The operational cost of sifting through application query logs looking for anomalies is prohibitive, and the likelihood of missing a real event in that volume is high.

We are auditing individual human users with direct database access. In a regulated environment, this is the actual risk surface: a developer connected via psql running an exploratory query against production data, a support engineer making an undocumented change, a credential that was provisioned for troubleshooting and never revoked. These are low-frequency, high-consequence events — exactly what a well-designed audit system is built to catch.

This scope decision drives the implementation: pgAudit is enabled per individual user account, not cluster-wide. Service accounts are excluded entirely. The expected audit volume is low, which keeps overhead minimal and signal quality high.

The Oracle Reference: What a Mature Audit Pipeline Looks Like

Oracle audit pipeline

Oracle Unified Auditing, an Enterprise-tier feature, provides policy-based audit configuration at the intersection of three independently filterable dimensions: user, action, and object. The combination is powerful because each dimension is an independent variable:

-- Audit INSERT/UPDATE/DELETE on a specific table by all users except the service account
CREATE AUDIT POLICY user_dml_changes
  ACTIONS INSERT, UPDATE, DELETE ON app_schema.orders
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''APP_SVC_ACCOUNT'''
  EVALUATE PER SESSION;

AUDIT POLICY user_dml_changes;

You can audit all writes by a specific user on any table, or all deletes across an entire schema, or a single sensitive table by everyone — defined in one statement. The audit record lands immediately in UNIFIED_AUDIT_TRAIL, a queryable SQL view:

SELECT db_user, action_name, object_name, sql_text, event_timestamp
FROM unified_audit_trail
WHERE action_name IN ('INSERT','UPDATE','DELETE')
  AND event_timestamp > SYSDATE - INTERVAL '5' MINUTE;

From there, export to any downstream platform on a schedule — the data is already structured. An Elasticsearch Watcher, a Splunk scheduled search, a query against a dedicated audit database — the downstream tooling choice is flexible precisely because the upstream is clean SQL. Most teams already operate a log analytics platform. The export is a standard query and a bulk insert.

One critical operational risk with this approach: if the Oracle audit tablespace reaches capacity, the database halts rather than silently dropping audit records. This guarantees audit completeness at the cost of availability — a deliberate design choice that ensures the audit trail is never partially truncated. It means tablespace monitoring is not optional; it becomes a hard operational dependency. pgAudit writing to the PostgreSQL log stream sidesteps this failure mode entirely: if log delivery has issues, the database keeps running.

On PostgreSQL with pgAudit, there’s no policy-based targeting at the object or action level. You set a log class per user: read, write, ddl, or all. The audit records flow into the PostgreSQL log stream — on Aurora, that means CloudWatch Logs. There's no queryable view. The raw output is text:

2025-10-27 16:38:22 UTC:10.0.1.15(54321):john_doe@production:[1234]:LOG:
AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.orders,
"INSERT INTO orders (customer_id, amount) VALUES ($1, $2)"

It works. But extracting a structured, alertable signal from a log stream requires deliberate engineering — which is what the rest of this article covers.

The Full Architecture

Complete AWS pipeline

Aurora PostgreSQL  (pgAudit per-user logging)
        │  pgAudit → PostgreSQL log stream
        ▼
CloudWatch Logs   /aws/rds/cluster/<cluster>/postgresql
        │
        ├──► CloudWatch Dashboard  (real-time Log Insights view)
        │
EventBridge  rate(5 minutes)
        │
        ▼
Lambda Function
  ├── Runs Log Insights query against last 5-minute window
  ├── Applies noise filter (tool queries, system catalogs excluded)
  ├── Counts remaining user DML operations
  └── Publishes UserDMLOperationCount → RDSAudit/UserActivity
        │
        ▼
CloudWatch Custom Metric  UserDMLOperationCount
        │
        ▼
CloudWatch Alarm  threshold: count > 0
        │
        ▼
SNS Topic  rds-user-audit-alerts
  ├── HTTPS endpoint → incident management platform
  └── Email → engineering team

The Lambda layer exists because of a fundamental limitation: CloudWatch Alarms cannot evaluate Log Insights query results directly. They evaluate metrics. Log Insights queries produce result sets. There is no native integration between them. Lambda bridges that gap: it runs the query, extracts the count, and publishes it as a standard metric that the alarm evaluates on the next cycle.

The Lambda invocation logs are also a second audit artifact. Every execution is recorded in CloudWatch with its output — the count of detected operations, the query window, the timestamp. This provides an independent, durable record of detection separate from the raw pgAudit stream, which matters when a compliance review asks “when was this activity first detected and by what mechanism?”

Full implementation: Lambda code, IAM policies, SNS resource policy, KMS key policy, CloudWatch alarm definition, and Terraform module → github.com/pcraavi/PostgreSQL-Audit

Enabling pgAudit — and the Silent Failure Mode

pgAudit setup sequence

Adding pgaudit to shared_preload_libraries in the cluster parameter group and rebooting is step one. What isn't obvious: shared_preload_libraries loads the library binary into PostgreSQL shared memory. It does not install the extension into the database catalog. That requires a separate statement:

CREATE EXTENSION pgaudit;

If this statement is skipped, the result is zero audit records and zero error messages. Nothing in CloudWatch. No warnings. The cluster appears healthy. The production issue this caused: the parameter was confirmed set, the cluster had been rebooted, log export was enabled, and queries against the CloudWatch log group returned nothing. The diagnostic was straightforward once we thought to look:

SELECT * FROM pg_extension WHERE extname = 'pgaudit';
-- Returned 0 rows. That was the entire problem.

One missing CREATE EXTENSION statement.

After the extension is installed, enable per-user logging for each individual human account:

ALTER USER john_doe SET pgaudit.log TO 'all';

-- Confirm the configuration is applied
SELECT usename, useconfig FROM pg_user WHERE usename = 'john_doe';
-- useconfig: {pgaudit.log=all}

Apply this to every individual human user with direct database access. Service accounts and application roles are deliberately excluded.

The Noise Problem: Why Raw pgAudit Output Isn’t Enough

Signal vs noise funnel

The first query against raw audit logs in a production environment is clarifying. The signal-to-noise ratio is poor:

AUDIT: SESSION,...,READ,SELECT,,,"SELECT version()"
AUDIT: SESSION,...,READ,SELECT,,,"SELECT * FROM pg_shdescription JOIN pg_description..."
AUDIT: SESSION,...,READ,SELECT,,,"SET application_name='DBeaver 23.2.0'"
AUDIT: SESSION,...,READ,SELECT,,,"SELECT oid, typarray FROM pg_type WHERE typname=$1"
AUDIT: SESSION,...,READ,SELECT,,,"SELECT current_schema()"

Every database client tool fires a sequence of catalog queries on connect. DBeaver, DataGrip, pgAdmin — each one runs multiple pg_* queries before displaying the schema tree. Every JDBC driver runs initialization SELECTs on connection establishment. Connection pools run health check queries continuously.

If you alert on the raw stream, you’re alerting on tool initialization constantly. Alert fatigue develops immediately, engineers start ignoring notifications, and the audit system stops serving its purpose. The filter query is the operational core of this architecture:

fields @timestamp, @message, @logStream, @log
| filter @message like /AUDIT:/
| filter (
    @message like /SELECT/ or @message like /INSERT/ or
    @message like /UPDATE/ or @message like /DELETE/
  )
| filter @message not like /SELECT version()/
| filter @message not like /pg_shdescription/
| filter @message not like /pg_catalog/
| filter @message not like /information_schema/
| filter @message not like /SET application_name/
| filter @message not like /SHOW search_path/
| filter @message not like /SELECT current_schema/
| filter @message not like /DBeaver/
| filter @message not like /PostgreSQL JDBC Driver/
| filter @message not like /datname = \$1/
| sort @timestamp desc

This was built incrementally — not designed upfront. Each exclusion pattern corresponds to a real noise source observed in production. Your environment will have additional patterns specific to your application stack. The exclusion list grows over the first few weeks as new noise sources are identified.

The KMS + CloudWatch Alarms Incompatibility

SNS topic security in a HIPAA-covered environment requires encryption at rest. The natural choice is alias/aws/sns, the AWS-managed SNS key. Simple, no additional management overhead.

The CloudWatch alarm stopped delivering after the key was applied:

CloudWatch Alarms does not have authorization to access the SNS topic encryption key

AWS-managed KMS keys have immutable key policies. The key is managed by AWS; you cannot modify the policy to grant additional principals access. CloudWatch Alarms needs kms:GenerateDataKey and kms:Decrypt to publish to an encrypted SNS topic. With a managed key, there's no mechanism to grant it.

The fix is a customer-managed KMS key with an explicit CloudWatch service principal grant in the key policy. The AWS-managed key works correctly when Lambda publishes directly to SNS — the limitation is specific to the CloudWatch Alarms → SNS delivery path.

The SNS topic also requires:

• DenyInsecureTransport: explicit deny of HTTP delivery, enforcing HTTPS at the policy level
• Source account restriction: prevents cross-account confused deputy attacks where a service in another AWS account publishes through a permissive principal and limit the topic creation only to trusted AWS services, in this case it is CloudWatch

Full SNS policy, KMS key policy, and the reasoning behind each statement: github.com/pcraavi/PostgreSQL-Audit/tree/main/sns

The Incident Platform Deduplication Problem

The first alarm trigger fired an alert in the incident management platform correctly. The second, third, and fourth did not.

The integration logs showed requests arriving and the “Create Alert” action starting — but no alert created. The issue: incident management platforms (Opsgenie, PagerDuty, VictorOps) use the alarm name as the alert deduplication key (alias). If an alert with that key is already open or acknowledged, subsequent triggers are suppressed — they update the existing alert rather than creating a new incident.

This is correct, desirable behavior for most alarm types. A disk-full alarm should not create 500 separate incidents while the disk remains full. For an audit alert that should fire independently on every detection window, it requires explicit handling.

Three approaches:

Timestamp the alarm name at deploy time. Using $(date +%s) as a suffix ensures each deployment creates a unique alarm identity. Deduplication keys don't collide across deployments.

Configure auto-close on alarm resolution. When the CloudWatch alarm transitions back to OK state (no user DML detected in the next window), the incident platform closes the alert. The next ALARM transition creates a fresh incident with no deduplication collision.

Override the alias template in the integration configuration. Most platforms allow the deduplication key to be templated from the incoming payload. Including a timestamp field from the CloudWatch alert payload makes every alert definitionally unique.

Why Not the Native Alternatives?

Experienced AWS engineers will have questions at this point. Database Activity Streams is the obvious one.

Database Activity Streams (DAS) provides near-real-time activity streaming to Kinesis with structured, decryptable output. It’s a more capable solution in several respects. The reason this architecture doesn’t use it: DAS requires Kinesis as a dependency, adds per-event cost, and needs a consumer layer for decryption and event processing. For teams without an existing Kinesis pipeline, that’s meaningful additional operational surface area.

The guiding principle here was simplicity and portability. pgAudit is part of PostgreSQL. CloudWatch is where Aurora logs land regardless of whether you audit. Lambda and SNS are general-purpose services that most AWS teams already operate. There is no proprietary tooling, no specialized operational knowledge required beyond standard AWS familiarity. When deploying across multiple accounts, that portability compounds in value — the same Terraform module, the same operational playbook, the same team knowledge applies everywhere.

GuardDuty RDS Protection is complementary rather than equivalent — it addresses threat detection (credential anomalies, known malicious IPs) rather than structured audit trails. Both can and should coexist.

OpenSearch subscription filters would work technically but introduce an OpenSearch cluster as a hard dependency. Additional cost, additional operational surface, and a specialized operational skill requirement for a problem that CloudWatch + Lambda already solves within existing infrastructure.

Operational Considerations

pgAudit overhead

At the audit scope defined here — individual human users, not application accounts — overhead is minimal. Human users in a regulated production environment are not expected to generate high transaction volumes. The audit target is ad-hoc access, not application traffic. Per-session logging overhead at this scope is negligible.

For high-volume clusters where audit is a compliance requirement and cannot be scoped narrowly: account for logging overhead in instance sizing decisions upfront. This is an operational fact of regulated database environments, not a deficiency of pgAudit specifically.

Log volume and retention

Audit log volume at this scope is low. Set CloudWatch log retention explicitly — the default is indefinite, which accumulates cost. Retention window should match your compliance requirement (90 days, 1 year, 7 years). Apply and enforce it:

aws logs put-retention-policy \
  --log-group-name /aws/rds/cluster/YOUR-CLUSTER/postgresql \
  --retention-in-days 365

Log Insights query behavior

Log Insights queries are asynchronous. The Lambda polls with a 60-second timeout on a 300-second function timeout. On first run against a large log group, queries may be slower as CloudWatch builds its index — subsequent runs are faster. Lambda concurrency at a 5-minute EventBridge schedule is inherently low: one invocation per cluster, per 5 minutes.

What I’d Do Differently

Structure the audit store. CloudWatch Log Insights is a query tool, not an audit database. The Lambda architecture makes it natural to extend: in addition to publishing the count metric, write each filtered record to DynamoDB or an RDS audit table. This gives you the closest equivalent to Oracle’s UNIFIED_AUDIT_TRAIL — a structured, indexed, long-term queryable audit history. Kinesis Firehose → S3 → Athena is an alternative path for columnar performance over large historical windows.

Terraform from the first deployment. The full stack — Lambda, EventBridge rule, SNS topic, IAM role, CloudWatch alarm, KMS key — fits cleanly in a parameterized Terraform module. Deploying to a new account should be terraform apply with environment-specific variables. CLI commands are useful for understanding the mechanics; they're not a deployment strategy at scale.

Tag every resource. Environment, ClusterName, Owner on every Lambda, alarm, SNS topic, and KMS key. Untagged audit infrastructure across multiple accounts becomes, practically speaking, unauditable infrastructure — you lose track of what's deployed where and whether it's functioning correctly.

Closing Observation

The individual components here — pgAudit, CloudWatch, Lambda, SNS — each have clean, well-documented interfaces. The engineering complexity is in the integration contracts between them, none of which are prominently documented: Log Insights results cannot flow to alarms without mediation; AWS-managed KMS keys break CloudWatch delivery; incident platform deduplication suppresses repeated alarm transitions. Each constraint is resolvable, but none surfaces until the components are operating together under production conditions.

What made the Oracle approach simpler isn’t that Oracle is a better database — it’s that the audit system there was designed as a cohesive feature rather than assembled from general-purpose infrastructure components. The PostgreSQL approach trades that coherence for flexibility, portability, and freedom from proprietary tooling. Whether that trade is worth it depends on what your team already operates and what your compliance requirements actually mandate.

For most AWS-native engineering organizations with standard CloudWatch and Lambda operational knowledge: this architecture works, it’s maintainable, and it costs under $5 per cluster per month.

Build the Log Insights query first. Validate the signal before instrumenting anything around it. The filtering logic is the foundation — everything else is plumbing on top.

Full implementation: Lambda function, IAM policies, SNS topic policy, KMS key policy, CloudWatch alarm, Terraform module → github.com/pcraavi/PostgreSQL-Audit

Questions or different approaches to long-term audit retention on Aurora? Comments open. Particularly interested in experience with the Lambda → structured audit table extension or Kinesis Firehose → Athena for compliance retention.