What is a Postgres audit log table and what must it answer?
A Postgres audit log table is an append-only record of row-level changes. Each INSERT, UPDATE, or DELETE on an audited table adds one row describing the change. Audit rows are never updated or deleted by the application. If they could be edited, the trail would not be trustworthy evidence.
Teams use it for three recurring jobs:
- Compliance logging: showing an auditor who modified a customer record and when.
- Incident debugging: reconstructing the state of a row before a bad deploy or migration touched it.
- Support questions: answering "why did my plan change?" without guessing.
This approach fits best when a monolith owns its Postgres database. All writes go through one schema and one codebase, so triggers see every change and the application can pass its context in one consistent way.
The trail must answer four questions. The hardest is who. The database role is usually a shared application login, so current_user alone tells you almost nothing. The application must pass the real user ID to the database for each transaction.
| Question | Column | Source of the value |
|---|---|---|
| Who | app_user_id, db_role |
Session setting from the app; current_user |
| What | table_name, row_pk, operation, old_data, new_data |
Trigger context; row images as jsonb |
| When | changed_at, txid |
transaction_timestamp() or statement_timestamp(); current transaction ID |
| Why / where | request_id, source |
Session settings: request ID, plus a label such as web, job, or console |
Recording the transaction ID lets you group every row changed by a single business action.
How does trigger-based audit logging work in Postgres?
The flow has four steps, and all of them run inside one database transaction.
- The request opens a transaction and sets context. Before touching any audited table, the application calls
set_config('app.current_user_id', '42', true)and does the same forapp.request_id. The third argument,true, makes the setting transaction-local, which is equivalent toSET LOCAL. The value disappears at commit or rollback. - The application writes as usual. It issues ordinary
INSERT,UPDATE, orDELETEstatements against the audited table. It does not need to know that auditing exists. - An
AFTER ... FOR EACH ROWtrigger fires. The trigger runs after the row change has been applied, so it has access to the finalOLDandNEWvalues. It reads the context withcurrent_setting('app.current_user_id', true). Thetrueargument tells Postgres to return NULL instead of raising an error when the setting is absent. A session that has used the setting earlier can also return an empty string, so normalize it withNULLIF(..., ''). - The trigger inserts the audit row in the same transaction. If the transaction commits, the change and its audit record commit together. If it rolls back, both disappear. You never end up with an audit row for a change that didn't happen, or a change with no audit row.
Why transaction-local scope matters with pooling
A pooled connection serves many requests in turn. Suppose the application runs a plain session-level SET app.current_user_id = '42'. That value stays on the server connection after the request finishes. The next request to borrow that connection inherits it. If that request forgets to set its own value, or runs as a background job with no user, its changes are attributed to user 42. The audit row looks valid, so this kind of error is hard to spot later.
Poolers that hand out a connection per transaction add a second problem. A session SET can land on a different backend from the statements that follow it.
Transaction-local settings avoid both problems. Their lifetime matches the unit of work being audited.
+--------------------------------------------------------------+
| Request borrows a pooled connection and runs BEGIN |
+--------------------------------------------------------------+
|
v
+--------------------------------------------------------------+
| set_config('app.current_user_id', '42', true) |
| set_config('app.request_id', 'req-abc', true) (txn-local) |
+--------------------------------------------------------------+
|
v
+--------------------------------------------------------------+
| INSERT / UPDATE / DELETE on audited table |
+--------------------------------------------------------------+
| row change applied
v
+--------------------------------------------------------------+
| AFTER ... FOR EACH ROW trigger |
| uid := NULLIF(current_setting('app.current_user_id', |
| true), '') |
| INSERT INTO audit_log (OLD, NEW, uid, request_id) |
+--------------------------------------------------------------+
|
v
+-----------------------------+ +----------------------------+
| COMMIT: change + audit row | | ROLLBACK: neither persists |
+-----------------------------+ +----------------------------+
| |
+----------------+----------------+
v
settings cleared; next borrower sees no user (session SET leaks 42)
Context set with set_config(..., true) and the audit row written by an AFTER row trigger share one transaction, so they commit or roll back together, and the settings clear before the pooled connection is reused.
How do you implement a generic audit trigger step by step?
of work being audited, so a pooled connection cannot leak one request's actor into the next.
Create the audit table
The migration creates a single audit.log table with these columns:
idas a bigint identityoccurred_at timestamptzdefaulting tonow()table_namequalified with its schemarecord_key jsonb, so composite keys fitoperationold_dataandnew_dataasjsonbchanged_fields text[]actor_idandrequest_idtxid bigint, populated fromtxid_current()
Snapshots taken with to_jsonb(OLD) and to_jsonb(NEW) beat a shadow table per audited table. A shadow table has to be altered in lockstep with every schema migration, or inserts start failing. A jsonb snapshot absorbs added and dropped columns without any change, and one table means one set of indexes and one retention job.
Write one trigger function
A single PL/pgSQL function handles every table:
- Branch on
TG_OPto decide whetherOLD,NEW, or both exist. - Build
table_namefromTG_TABLE_SCHEMAandTG_TABLE_NAME. - Remove sensitive and noisy keys from both snapshots with the
jsonb - text[]operator. This covers password hashes, API tokens, secrets, andupdated_at. - For updates, compare the stripped snapshots. If they are equal, return without writing a row. This skips no-op updates and touch-only writes.
- Otherwise, store only the keys whose values differ, and list them in
changed_fields. - Read the actor and request with
current_setting('app.actor_id', true). Thetrueargument returns NULL instead of raising an error when the setting is unset.
Attach the function per table with CREATE TRIGGER ... AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW. The trigger runs AFTER, so it sees final values, including values from defaults and other triggers.
Set the actor per request
The application sets the actor and request ID at the start of each request transaction. Run SELECT set_config('app.actor_id', $1, true) immediately after BEGIN. The third argument makes the setting transaction-local. set_config is preferable to SET LOCAL here because it accepts bind parameters.
CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
-- Never logged: secrets, credentials, and touch-only columns
skip text[] := ARRAY['password_hash', 'api_token', 'secret', 'updated_at'];
o jsonb; n jsonb; k jsonb; keys text[];
BEGIN
IF TG_OP <> 'INSERT' THEN o := to_jsonb(OLD) - skip; END IF;
IF TG_OP <> 'DELETE' THEN n := to_jsonb(NEW) - skip; END IF;
k := jsonb_build_object('id', coalesce(n, o) -> 'id'); -- take key before trimming
IF TG_OP = 'UPDATE' THEN
IF o = n THEN RETURN NULL; END IF; -- no-op or updated_at-only write
SELECT array_agg(key) INTO keys FROM jsonb_each(n) WHERE n -> key IS DISTINCT FROM o -> key;
-- Keep only the keys whose values changed
SELECT jsonb_object_agg(key, value) INTO o FROM jsonb_each(o) WHERE key = ANY (keys);
SELECT jsonb_object_agg(key, value) INTO n FROM jsonb_each(n) WHERE key = ANY (keys);
END IF;
INSERT INTO audit.log (table_name, record_key, operation, old_data, new_data,
changed_fields, actor_id, request_id, txid)
VALUES (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, k, TG_OP, o, n, keys,
-- true = return NULL if unset; NULLIF covers '' left on pooled sessions
NULLIF(current_setting('app.actor_id', true), ''),
NULLIF(current_setting('app.request_id', true), ''),
txid_current());
RETURN NULL; -- return value is ignored for AFTER row triggers
END;
$$;
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH ROW EXECUTE FUNCTION audit.log_change();
-- Application side, at the start of each request transaction:
BEGIN;
SELECT set_config('app.actor_id', '4821', true); -- true = transaction-local, like SET LOCAL
SELECT set_config('app.request_id', 'req-7f3a9c2e', true);
UPDATE public.orders SET status = 'shipped' WHERE id = 1001;
COMMIT;
A single generic AFTER row trigger function that strips sensitive and noisy keys, skips no-op updates, and logs only changed fields. It is attached to one table here, and the application sets the transaction-local actor and request context that the function reads.
Hardening, indexing, and retention for a growing audit trail
Lock down who can write and read
Revoke UPDATE, DELETE, and TRUNCATE on the audit table from the application role, and grant it no direct INSERT either. Rows should arrive only through the trigger function. Make that function SECURITY DEFINER, owned by a dedicated non-login role that holds INSERT on the audit table. Pin its search_path with a SET clause on the function. Without it, a caller could put an object earlier in the search path and have the function resolve to it. Grant SELECT only to the roles that review history.
Row triggers don't fire on TRUNCATE. Either withhold that privilege on audited tables or add a statement-level TRUNCATE trigger that records the event. Two bypass paths remain. Setting session_replication_role to replica stops ordinary triggers from firing. Bulk loads run with --disable-triggers or after ALTER TABLE ... DISABLE TRIGGER produce no audit rows. Both require elevated privileges, so restrict those privileges and treat their use as an auditable event.
Index and partition for growth
Index (table_name, record_key, occurred_at) to support "show this row's history," and index actor_id to support "what did this person change." Range-partition the table by occurred_at, for example monthly. Retention then becomes detaching or dropping a partition instead of running a large DELETE that bloats the table.
Plan for redaction
Retention rules and erasure requests can conflict. Audit history holds copies of personal data that may need to be erased before its partition ages out. Decide early which columns the log stores in full, which it hashes, and which it excludes. Also provide one narrowly granted, audited routine that can rewrite those values, so redaction is a controlled exception to the append-only rule rather than a way around it.
-- Run as a migration/admin role. app_user = application login, audit_reader = reviewers.
CREATE ROLE audit_writer NOLOGIN; -- owns the trigger function; nobody logs in as it
CREATE SCHEMA audit;
CREATE TABLE audit.log (
id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL DEFAULT now(),
table_name text NOT NULL,
record_key text NOT NULL,
actor_id text,
op text NOT NULL,
row_data jsonb
) PARTITION BY RANGE (occurred_at); -- retention = detach/drop a partition
CREATE TABLE audit.log_2026_11 PARTITION OF audit.log
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE INDEX ON audit.log (table_name, record_key, occurred_at); -- one row's history
CREATE INDEX ON audit.log (actor_id); -- one person's changes
-- The app gets no direct access; only the function owner may insert.
REVOKE ALL ON SCHEMA audit FROM PUBLIC;
REVOKE ALL ON audit.log FROM PUBLIC, app_user;
GRANT USAGE ON SCHEMA audit TO audit_writer, audit_reader;
GRANT INSERT ON audit.log TO audit_writer;
GRANT SELECT ON audit.log TO audit_reader;
CREATE FUNCTION audit.capture() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp -- callers cannot shadow objects
AS $$
DECLARE r jsonb;
BEGIN
IF TG_OP = 'DELETE' THEN r := to_jsonb(OLD); ELSE r := to_jsonb(NEW); END IF;
INSERT INTO audit.log (table_name, record_key, actor_id, op, row_data)
VALUES (TG_TABLE_NAME, r->>'id', current_setting('app.actor_id', true), TG_OP, r);
RETURN NULL; -- AFTER trigger: return value is ignored
END $$;
ALTER FUNCTION audit.capture() OWNER TO audit_writer;
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH ROW EXECUTE FUNCTION audit.capture();
REVOKE TRUNCATE ON public.orders FROM app_user; -- row triggers do not fire on TRUNCATE
A minimal setup where audit rows can only be written through a SECURITY DEFINER trigger function with a pinned search_path, stored in a monthly range-partitioned table indexed for per-row and per-actor lookups, while the application role is denied direct writes and TRUNCATE.
How do you query and test the audit history?
With the log in place, three queries cover most questions. To reconstruct one record, filter on the table name and primary key and order by the change timestamp, with the transaction ID as a tiebreaker so that multiple writes in one transaction stay in sequence. Read top to bottom, new_data at each step shows the row's state after that change. To list everything one user changed, filter on the actor column and a changed_at range. That composite index is what keeps this query fast once the table holds months of rows. For field-level diffs, expand old_data and new_data with jsonb_each, join the two on key, and keep the keys where the values are IS DISTINCT FROM each other. This returns only the columns that actually changed, so nobody has to compare two full JSON documents by eye.
Test the trigger through the application, not with raw SQL. Each integration test sends a real request as a known user and then asserts three things about the resulting audit row: the actor, the operation, and the exact diff. To check that identity doesn't leak between pooled connections, set the pool size to one. Write as user A, then write through an unauthenticated or system code path. The second audit row must record no actor or the system actor, never A. If it shows A, the identity was set at session level instead of with SET LOCAL inside the transaction.
These queries and tests pay off when you're under time pressure. During an incident, the record-history query tells you which deploy or which person corrupted a row in minutes, without searching through application logs. During an audit, the actor-window query is the evidence, and the passing tests show that the actor attribution in it can be trusted.
-- Assumed schema: audit_log(id bigserial, table_name text, record_id text, operation text,
-- actor text, changed_at timestamptz, txid bigint, old_data jsonb, new_data jsonb)
-- 1. Full history of one record; new_data is the row state after each change.
SELECT changed_at, txid, operation, actor, new_data
FROM audit_log
WHERE table_name = 'orders' AND record_id = '42'
ORDER BY changed_at, txid, id; -- txid and id keep writes in one transaction in order
-- 2. Everything one user changed in a window (served by an index on (actor, changed_at)).
SELECT changed_at, table_name, record_id, operation
FROM audit_log
WHERE actor = 'user-1234'
AND changed_at >= '2026-10-01' AND changed_at < '2026-11-01'
ORDER BY changed_at;
-- 3. Field-level diff: only the keys whose values changed.
-- FULL JOIN also catches keys present on just one side; on INSERT old_data is NULL,
-- so jsonb_each returns no rows and every new key appears as an added field.
SELECT a.changed_at, a.operation, a.actor,
d.key AS field, d.old_value, d.new_value
FROM audit_log a
CROSS JOIN LATERAL (
SELECT key, o.value AS old_value, n.value AS new_value
FROM jsonb_each(a.old_data) AS o
FULL JOIN jsonb_each(a.new_data) AS n USING (key)
) AS d
WHERE a.table_name = 'orders' AND a.record_id = '42'
AND d.old_value IS DISTINCT FROM d.new_value
ORDER BY a.changed_at, a.txid, a.id, d.key;
Three queries against an assumed audit_log schema: one rebuilds a single record's history, one lists a user's changes in a time window, and one uses jsonb_each with IS DISTINCT FROM to return only the fields that changed.
When should you avoid trigger-based audit logging, and what does it cost?
Every audited write becomes at least two writes: the target row and an audit row that usually holds both the old and new versions as jsonb. The audit insert runs inside the same transaction, so locks are held longer and WAL volume grows.
Illustrative estimate (assumed numbers, not a benchmark). Assume a bulk UPDATE touches 2,000,000 rows, each row serializes to about 1 KB of jsonb, and the audit row stores old and new images:
extra audit data ≈ rows × (old_size + new_size) = 2,000,000 × 2 KB ≈ 4 GB
That figure excludes WAL and audit-table index entries, which add more. Replace the inputs with your own row counts and sizes.
The usual workaround for backfills is to disable the trigger or audit in batches. Disabling it leaves a gap in the record, so document every gap with a manual entry. Batching keeps the record complete but stretches the job over a longer window.
Other limits:
- Row effects, not intent. A trigger records that
statuschanged. It cannot record that a refund policy caused the change. - Identity depends on propagation. Background jobs and ad-hoc
psqlsessions often do not setapp.current_user_id. In that case you must choose between two options. You can reject the write, which is safer but can break operations. Or you can log it with a flagged "unknown actor," which keeps the system running but weakens the evidence. - Schema drift. When columns are renamed or dropped, old
jsonbrows no longer match the current table shape, so reading them requires knowing the schema history. - Tamper resistance. Superusers and table owners can disable triggers. Strict compliance may therefore require shipping audit rows off-box to append-only external storage.
This approach is the wrong fit for very high write rates, for frequent bulk rewrites, and for regulatory regimes that require evidence the database administrator cannot alter.
What are the alternatives to a trigger-based Postgres audit log?
The four main approaches differ mainly in where the record is produced: inside the writing transaction, from the WAL after commit, or in application code before the SQL runs.
| Approach | User identity | Intent | Bypass risk | In-transaction overhead | Operational cost |
|---|---|---|---|---|---|
| Generic jsonb trigger | Good if the app sets a session variable; otherwise only the database role | None | Low; superusers and session_replication_role can skip it |
Extra insert per row | Low; one table and one function |
| Per-table shadow tables | Same as generic trigger | None | Low | Extra insert per row | High; every schema migration must update the shadow table |
| Logical decoding / CDC | Weak; only what is stored in the row itself | None | Very low; all WAL-logged changes appear | Near zero; WAL is written regardless | High; requires wal_level = logical, and a lagging consumer causes the replication slot to retain WAL until the disk fills |
| Application audit events | Strong; the full request context is available | Strong, e.g. "refund issued" rather than UPDATE payments |
High; psql sessions, migrations, and other services skip it | Depends on whether it is written in the same transaction | Medium; discipline is needed at every call site |
No single row in the table is strong on everything. Triggers and CDC give completeness, because they see changes regardless of which code path made them. Application events give meaning. A common combination is a generic trigger as the safety net plus application events for business actions. To correlate the two, have the application set a request ID as a session variable, have the trigger copy that ID into each audit row, and include the same ID in the event. Investigators can then join the two records.
Consider CDC when trigger overhead on hot tables becomes measurable, or when the audit stream must feed systems outside Postgres. If you adopt it, write the actor into the row itself, for example as a modified_by column, because the decoded change stream will not carry session state.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to postgres audit log table, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.