Auditing Sensitive Access in Postgres: Patterns That Survive Real Audits

Auditors ask specific questions. "Who accessed customer 47's PII in the last 90 days?" "Show me every query that selected from the salary column." "What admin operations happened during the incident window?" These questions need data that Postgres does not produce by default.

Setting up auditing is a one-time investment. Without it, audit responses are guesses. With it, they are queries.

Here is the framework I use.

What Postgres logs without auditing

By default, Postgres logs:

• Connection attempts (with log_connections = on).
• Queries above a duration threshold (with log_min_duration_statement).
• Errors and notices.
• Specific events (lock waits with log_lock_waits, autovacuum with log_autovacuum_min_duration).

This is enough for performance debugging. It is not enough for audit. Specifically, it does not log every SELECT, every INSERT, or any indication of who read what.

What pgaudit adds

pgaudit is a Postgres extension that adds detailed logging of database activity. It writes to the standard Postgres log, marked with AUDIT: prefix.

The configurations:

• Session-level audit: every statement executed by a specific role.
• Object-level audit: every operation on specific tables.
• Class-level audit: every operation of certain types (READ, WRITE, DDL, ROLE).

A practical pgaudit setup

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pgaudit'
pgaudit.log = 'ddl, role, write'  -- log schema changes, role changes, and writes globally
pgaudit.log_relation = on
pgaudit.log_parameter = on

For sensitive tables specifically:

-- Log all access to the customers table
ALTER TABLE customers SET (pgaudit.log_object = on);

-- Or assign object-level audit to a role
GRANT ALL ON customers TO audit_role;
ALTER ROLE audit_role SET pgaudit.log = 'read, write';

After restart, every operation matching the policy is logged.

What the logs look like

AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.customers,SELECT * FROM customers WHERE id = $1,"<bind: 47>"

Fields: log type, statement number, substatement, class (READ/WRITE/DDL), command (SELECT/INSERT), object type (TABLE), schema.table, the query, parameters.

Forwarding these to an SIEM (Splunk, ELK, CloudWatch Insights) makes them queryable.

Routing to the right place

The Postgres log can grow fast with audit enabled. For high-volume databases, the log file is gigabytes per day. Two patterns:

1. Log to file, ship to SIEM. Configure Postgres to log to a directory. A logging agent (Filebeat, Vector, fluentd) ships log lines to your SIEM. The SIEM has retention, search, and alerting.

2. Log to syslog, route from there. Postgres can log to syslog directly. Useful when your infrastructure is already syslog-centric.

For cloud-managed Postgres:

• RDS: logs go to CloudWatch Logs. Enable pgaudit via parameter group; set retention on CloudWatch.
• Cloud SQL: logs go to Cloud Logging. Similar.
• Azure Flex: logs go to Log Analytics. Same shape.

Budget for storage. Audit logs at 1KB per query × thousands of queries per second add up.

What to log vs not log

Logging everything is expensive and produces noise that obscures real signals. The pragmatic split:

• Always log: DDL (schema changes), role/permission changes, writes to sensitive tables (customers, payments, audit_log), failed connection attempts.
• Sometimes log: reads from sensitive tables, especially during audit-relevant time windows.
• Rarely log at scale: every read of every table. Too much data, low signal.

The middle category — reads from sensitive tables — is where most teams have to make decisions. Logging every SELECT on customers produces a lot of data; the value is the ability to answer "who read this PII when."

Application-level audit columns

For application-side audit context (which user, which IP, which feature), pgaudit alone is not enough. The application should set per-session GUCs:

-- Application sets these on every connection check-out
SET LOCAL app.user_id = '12345';
SET LOCAL app.session_id = 'abc-def';
SET LOCAL app.client_ip = '10.0.0.5';

A trigger on sensitive tables captures these values into an audit table:

CREATE TABLE customer_access_log (
  id BIGSERIAL PRIMARY KEY,
  accessed_at TIMESTAMPTZ DEFAULT now(),
  customer_id BIGINT,
  app_user_id TEXT,
  app_session_id TEXT,
  app_client_ip TEXT,
  db_user TEXT,
  query TEXT
);

CREATE OR REPLACE FUNCTION log_customer_access()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO customer_access_log (
    customer_id, app_user_id, app_session_id, app_client_ip, db_user, query
  ) VALUES (
    NEW.id,
    current_setting('app.user_id', true),
    current_setting('app.session_id', true),
    current_setting('app.client_ip', true),
    current_user,
    current_query()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_customer_select
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION log_customer_access();

This logs WRITES to customers with full application context. For SELECT logging, pgaudit is the tool — Postgres triggers do not fire on SELECT.

Common audit queries

The queries auditors actually want:

-- Who accessed customer 47 in the last 90 days?
SELECT app_user_id, accessed_at, query
FROM customer_access_log
WHERE customer_id = 47
  AND accessed_at > now() - interval '90 days'
ORDER BY accessed_at DESC;

-- Top 10 users by sensitive-table access volume
SELECT app_user_id, count(*)
FROM customer_access_log
WHERE accessed_at > now() - interval '30 days'
GROUP BY app_user_id
ORDER BY count(*) DESC
LIMIT 10;

-- Off-hours access (e.g., outside 9am-6pm)
SELECT app_user_id, accessed_at, query
FROM customer_access_log
WHERE accessed_at > now() - interval '7 days'
  AND extract(hour FROM accessed_at) NOT BETWEEN 9 AND 18
ORDER BY accessed_at DESC;

These are the questions you cannot answer without auditing in place.

What I commit to

For any production database with sensitive data:

1. pgaudit installed, configured to log DDL, role changes, and writes globally.
2. Sensitive tables logged for reads in addition.
3. Application sets per-session GUCs on every connection.
4. Audit triggers on sensitive tables capture these GUCs.
5. Logs forwarded to SIEM with appropriate retention (typically 1-7 years for compliance).
6. Quarterly audit-query review to confirm the data answers the questions.

This is several days of setup. Once done, audit responses become queries instead of investigations.