RDS PostgreSQL audit logging: log_statement vs pgaudit

rds-audit-logging.md

What both methods answer equally well

Both methods, with log_line_prefix configured, produce log lines that answer:

• Who — %u (authenticated session user at connect time)
• When — %m (timestamp with ms)
• What — full SQL text
• From where — %h (client IP)

Both write to the same PostgreSQL log stream. Both require enabled_cloudwatch_logs_exports = ["postgresql"] to ship logs to CloudWatch.

Neither method captures SET ROLE. %u is always session_user (the role that authenticated). SET ROLE changes current_user but not session_user. This is a PostgreSQL limitation, not specific to either method.

---

Parameter levels on RDS

RDS classifies parameters as dynamic (no reboot) or static (reboot required).

Parameter	RDS type	apply_method in parameter group
log_line_prefix	dynamic (sighup)	immediate
log_statement	dynamic (sighup)	immediate
log_connections	dynamic (superuser-backend)	immediate
log_disconnections	dynamic (superuser-backend)	immediate
pgaudit.log	dynamic (sighup)	immediate
pgaudit.log_catalog	dynamic (sighup)	immediate
pgaudit.log_parameter	dynamic (superuser)	immediate
shared_preload_libraries	static (postmaster)	pending-reboot

Dynamic parameters with apply_method = immediate are propagated by RDS to all associated running instances automatically — no reboot needed.

log_connections and log_disconnections take effect for new connections after the parameter is applied. They log every session start (who, when, from where) and end — relevant for any complete audit trail regardless of which method is chosen.

Reboot requirements:

• log_statement path: no reboot required
• pgaudit path, new instance: RDS automatically reboots the instance during the creation process when a custom parameter group is used — static parameters including shared_preload_libraries are active from first availability, no manual reboot needed
• pgaudit path, existing instance: one manual reboot required for shared_preload_libraries

---

How each method works internally

log_statement is built into the PostgreSQL server (postgres.c). It fires once at the top-level client request entry point, on the client-supplied query string, before execution begins. It has no visibility into anything that happens during execution. SPI — the internal interface used by PL/pgSQL to execute SQL — has no connection to the log_statement infrastructure.

pgaudit installs six hooks into the PostgreSQL execution engine: ExecutorStart, ExecutorCheckPerms, ExecutorRun, ExecutorEnd, ProcessUtility, and object_access. These fire deep inside the executor and utility pipeline, including for every SPI call made by PL/pgSQL. Source: pgaudit.c (_PG_init()).

---

Where the two methods actually differ

	log_statement	pgaudit
Log line payload	LOG: statement: <sql>	LOG: AUDIT: SESSION,<stmt_id>,<substmt_id>,<class>,<command>,<obj_type>,<obj_name>,<sql>,<params>
Operation class as discrete field (DDL / DML / SELECT)	No — must regex the SQL	Yes — CLASS field
Table name as discrete field	No — must parse SQL	Yes — OBJECT_NAME (fully qualified)
Bind parameter values	Included inline as text	Controlled by pgaudit.log_parameter — off by default
SQL inside stored procedures / DO blocks	Invisible — log_statement fires only on the client-submitted statement; every SPI call inside PL/pgSQL (both static SQL in function bodies and dynamic EXECUTE) is not seen	Logged — executor hooks fire for every SPI call; each inner statement is a numbered substatement with its own audit line
pg_catalog queries (ORM schema introspection, tooling)	Always logged, cannot suppress	Suppressible via pgaudit.log_catalog = off — suppresses statements where all referenced relations are in pg_catalog
Autovacuum	Not logged — autovacuum uses its own parameter (log_autovacuum_min_duration)	Not logged — same
Per-table filtering	No	Yes — object-level auditing via grants to the rds_pgaudit role (must use exactly this role name on RDS)
Per-operation filtering	Coarse — none, ddl, mod, all	Granular — any combination of read, write, ddl, role, function, misc
Password / sensitive data redaction	No	Upstream pgaudit source and README: No. The AWS RDS overview page claims yes; this is not supported by pgaudit source code, the pgaudit README, or the detailed AWS setup documentation.

---

CREATE EXTENSION pgaudit

Required for pgaudit. Must be run per database (not instance-wide) after shared_preload_libraries is loaded. The statement is idempotent with CREATE EXTENSION IF NOT EXISTS pgaudit. Without it, the library is loaded but the audit event triggers are not installed — object-level auditing will not function.

On RDS, CREATE EXTENSION must be run by a user with the rds_superuser role.

---

Concrete arguments for pgaudit over the simpler option

Only cases where pgaudit provides something log_statement genuinely cannot:

1. SQL inside stored procedures / DO blocks. log_statement logs only what the client submitted. Every SQL statement executed by PL/pgSQL — whether written statically in the function body or constructed dynamically via EXECUTE — is invisible. Verified from PostgreSQL source: log_statement fires in exec_simple_query() on the client query string; spi.c contains no reference to statement logging. pgaudit's executor hooks fire for each inner statement individually, producing a numbered substatement per operation.

2. Log volume / CloudWatch cost. log_statement = 'all' logs every client-submitted query with no filtering — including ORM schema introspection on startup and connection pooler health checks. pgaudit with log_catalog = off suppresses queries that only touch pg_catalog. Using pgaudit.log = 'ddl,write,role' (excluding read) reduces volume further. Both affect CloudWatch ingestion cost.

3. Querying logs by table or operation. pgaudit's CSV payload exposes OBJECT_NAME and CLASS as discrete fields, queryable directly in CloudWatch Logs Insights. With log_statement, extracting which table was accessed or what operation class ran requires regex against SQL text, which breaks on aliases, CTEs, case variation, and multi-table queries.

If none of those three apply, log_statement is sufficient.