Postgres-Native Audit History

Sooner or later, most applications get asked a question that the current state of the database can’t answer. Who changed this subscription? What did this API key look like before it was rotated? Was this user deleted by the user, by support, or by a background job? The row as it exists today only tells you where things ended up, not how they got there.

When I was building a webhook delivery service a few years ago, I wanted that history for a handful of tables, and I wanted it without standing up anything new. No extra service, no message broker, and no requirement that every code path remember to write an audit record. What I ended up with lives entirely inside Postgres: a trigger per table that writes the before and after state of every row into a companion _audit_logs table, tagged with the user and request that made the change.

The Shape of It#

There are three pieces. An audit table for each table being tracked, a trigger function that writes to it, and some per-transaction context from the application so that each record knows who it belongs to.

The audit table holds the old and new versions of the row as jsonb, which means it doesn’t need to change when the source table does:

CREATE TYPE operation_type AS ENUM ('INSERT', 'UPDATE', 'DELETE');

CREATE TABLE subscription_audit_logs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    entity_id varchar(27) NOT NULL,
    operation operation_type NOT NULL,
    old_data jsonb,
    new_data jsonb,
    request_id varchar(255),
    user_id varchar(255),
    created_at timestamptz NOT NULL DEFAULT now()
);

The trigger function is where the work happens. It fires after every insert, update, and delete, and records both images of the row along with whatever context the application has set:

CREATE OR REPLACE FUNCTION subscription_audit_trigger()
RETURNS trigger AS $body$
BEGIN
    INSERT INTO subscription_audit_logs (
        entity_id, operation, old_data, new_data, request_id, user_id
    )
    VALUES (
        CASE WHEN TG_OP = 'DELETE' THEN OLD.id ELSE NEW.id END,
        TG_OP::operation_type,
        CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
        CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END,
        NULLIF(current_setting('app.request_id', true), ''),
        NULLIF(current_setting('app.user_id', true), '')
    );
    RETURN NULL;
END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER subscription_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON subscriptions
FOR EACH ROW EXECUTE FUNCTION subscription_audit_trigger();

History is almost always read one record at a time, so the audit table wants an index to match:

CREATE INDEX ON subscription_audit_logs (entity_id, created_at);

In my case all of this was generated in a single Alembic migration that looped over the five tables I cared about, so adding audit history to another table is one more name in a list.

A couple of small details are worth calling out. The second argument to current_setting is missing_ok, so a write from a migration or a psql session that never set any context gets NULL rather than an error. The NULLIF is there because once a custom setting has been set on a connection, it reads back as an empty string after it goes out of scope, and only reads as NULL on a connection that has never seen it. Behind a pooler, “has this connection seen it” depends on whoever used the connection before you. The return value of an AFTER row trigger is ignored, so RETURN NULL is fine.

Who Made the Change#

The row images on their own are change data capture. What makes them an audit log is knowing who was responsible, and the database has to be told who that was.

Postgres lets you define your own configuration parameters, as long as the name has a prefix (app.user_id, myservice.request_id, whatever you like). The caller sets these at the start of its work, and the trigger reads them back with current_setting. The request ID is particularly nice to have, since it ties an audit record back to the logs and traces for the exact request that caused it.

Nothing about this is tied to a language or a framework. The whole mechanism lives in Postgres, and the only thing asked of the caller is one extra statement at the start of each transaction, which any Postgres client can send:

BEGIN;
SELECT set_config('app.request_id', $1, true),
       set_config('app.user_id', $2, true);
-- the transaction's normal writes
COMMIT;

The third argument to set_config (is_local) set to true makes it the function form of SET LOCAL, so the values only last until the end of the transaction. My first version didn’t do this. It set the values once per request with SET SESSION, from a Flask before_request hook, which works on a laptop and is the wrong scope, for reasons I’ll get to below.

The application I was building happened to be Flask and SQLAlchemy, so as one example, the version I’d write now there looks like this:

import os
import sys

import flask
import sqlalchemy as sa
from sqlalchemy.orm import Session


def audit_context():
    if flask.has_app_context():
        return flask.g.get("request_id", ""), flask.g.get("user_id", "")
    return "", f"job:{os.path.basename(sys.argv[0])}"


@sa.event.listens_for(Session, "after_begin")
def set_audit_context(session, transaction, connection):
    request_id, user_id = audit_context()
    connection.execute(
        sa.text(
            "SELECT set_config('app.request_id', :request_id, true),"
            "       set_config('app.user_id', :user_id, true)"
        ),
        {"request_id": request_id, "user_id": user_id},
    )

Whatever the stack, two parts of this carry over. The first is where the hook goes. A transaction-local setting disappears at COMMIT, so a request that commits twice would otherwise write its second batch of audit records with no user attached. Hooking the start of every transaction (after_begin, in SQLAlchemy’s case) means every transaction carries the context, no matter how many there are in a request.

The second is that not every write comes from a request. A worker, a CLI command, or a scheduled job usually has no request to read a user from (in Flask, touching flask.g outside an app context raises an error, hence the has_app_context check). Those writes still deserve a name in the history, so they get a job identity instead of a user. Which job deleted a row is often exactly the question being asked.

Using set_config with bound parameters also matters. SET doesn’t accept parameters, which makes it tempting to build the statement with string formatting. When one of the values is a request header, that’s an injection hole.

The Restriction: Context Has to Live in the Transaction#

This is the part of the approach that needs the most care, and it’s what bit my first version.

A session-level SET sticks to the physical database connection until something resets it, and most things that hand connections around don’t reset it. That includes the pool inside the application. SQLAlchemy’s, for example, returns a connection with a ROLLBACK, which leaves session settings in place, so the next request in the same process to check out that connection starts with the previous request’s values. My before_request hook overwrote them on every request, which hid this, but any code path that skipped the hook would have inherited a stranger’s user ID. An external pooler takes the same problem across processes.

PgBouncer in transaction pooling mode (and most other poolers that multiplex clients onto server connections) hands a server connection to a client for one transaction, and then gives it to someone else. A session-level setting doesn’t follow the client. It stays on the server connection, where the next client to pick that connection up inherits it. Audit records still get written with a user ID, but it’s the user from somebody else’s request. Nothing errors, and the history looks complete until somebody relies on it.

The rule I’d state for this pattern is that the context has to be set inside the same transaction as the write it describes. Transaction-local settings satisfy that, and they’re safe under transaction pooling because they’re gone before the connection is handed back. That carries a few consequences:

• Autocommit writes need care. A write outside an explicit transaction is its own transaction, and it won’t see context that was set in an earlier one, so wrap the write in a transaction. It’s tempting to try to squeeze the set_config into the write itself with a WITH clause, but Postgres doesn’t evaluate a WITH query the main statement never reads, and doesn’t promise an order when it does.
• Statement pooling doesn’t work. If the pooler refuses multi-statement transactions, there’s no transaction to put the context in.
• Session pooling is fine, with either scope. If every client gets its own server connection for its whole session, session-level settings behave. Transaction-local ones are still the safer default, since nobody has to remember that the pooler mode matters.

There are a few other limits that come with doing this in triggers, which are worth knowing about going in:

• The audit write is synchronous. Every tracked write becomes two writes, in the same transaction. For the tables I was tracking (configuration-shaped data, changed by people), that cost was negligible. For a hot, high-volume table, it’s likely not.
• The audit table only grows. For configuration-shaped tables, that growth is small enough to ignore for a long time. For anything busier, partitioning the audit table by created_at makes dropping old history cheap.
• The context is asserted, not proven. Anyone with a connection can set app.user_id to whatever they want. The audit log is as trustworthy as the roles that are allowed to write to the database, which for most applications means “the application”. The audit table itself should also be insert-only for the application role.
• Triggers can be skipped. TRUNCATE doesn’t fire row-level triggers, and a role with enough privilege can disable triggers outright (or set session_replication_role to replica). In practice, triggers guard against a code path in the application that forgets to write history. They don’t guard against an administrator who decides to skip it.

In return, the history is transactionally consistent with the data. If the write rolls back, so does its audit record. There’s nothing that can lag behind, drop events, or need replaying, and querying history is a SELECT against a table that sits right next to the data.

Compared to pgaudit#

pgaudit is the extension most people reach for when “audit” comes up in a Postgres context, and it’s solving a related but different problem.

pgaudit logs statements. It can log every statement of a given class (reads, writes, DDL, role changes) for a session, or every statement that touches a given table, and it writes those entries to the Postgres server log. What it records is the SQL that ran, and (with %u in log_line_prefix) the database role that ran it. It doesn’t record the row before and after, and the user it knows about is the database user, which for a typical web application is the same service account on every request.

That makes it a strong fit when the question is about database access. Which roles ran DDL? Did anyone read the customer table outside of the application? Compliance regimes often want exactly that, and pgaudit answers it from a place the application can’t tamper with. It’s a weaker fit when the question is “what did this record used to look like, and which of our users changed it”, because the answer is spread across log lines rather than sitting in a table. The application user can show up, if pgaudit is logging the set_config call itself, but it isn’t attached to the write. You’d have to stitch it back together from the session’s earlier statements.

The two also aren’t mutually exclusive. pgaudit watching the database roles, plus trigger-based history recording application-level changes, covers both questions.

Compared to Logical Replication#

The other common approach is to read changes from the write-ahead log through logical decoding, usually with Debezium streaming them into Kafka. This is change data capture in the most literal sense, and for moving data between systems, it’s very likely the right tool.

Logical decoding has some real advantages over triggers. It adds very little to the write path, since the changes are read from the WAL after the fact. The cost there is the extra WAL from running with wal_level = logical, and more once a table is set to REPLICA IDENTITY FULL. It captures changes from every source, including ones that bypass triggers. And it’s built to feed other systems, so the same stream can drive a search index, a warehouse, and a cache invalidator at once.

The costs are on the other side. It needs infrastructure: a replication slot, something consuming it (Kafka Connect, in Debezium’s case), and something storing the results. By default, an unconsumed replication slot causes Postgres to retain WAL indefinitely, so a stalled consumer can fill the disk. Setting max_slot_wal_keep_size caps that, at the cost of invalidating the slot. Old row images also aren’t there by default. With the default replica identity, a delete carries only the old primary key, and an update that leaves the key alone usually carries no old image at all. Full before images need the table altered to REPLICA IDENTITY FULL.

The bigger gap for auditing is the same one pgaudit has: the WAL doesn’t know who your user is. It’s possible to bridge that by writing the context into the WAL yourself, with pg_logical_emit_message (which Debezium surfaces as message events, using the pgoutput plugin on Postgres 14 or later), and correlating it with the row changes by transaction downstream. At that point, though, the application is doing the same per-transaction context work as the trigger approach, plus the correlation, plus running the pipeline.

Where logical replication is a fit is when the change stream is needed somewhere outside of Postgres anyway. If you already run Debezium for other reasons, adding the context message is a small step. If the only consumer would be an audit table in the same database, that’s a lot of moving parts to rebuild something a trigger writes directly.

When I’d Use It#

For history on a modest number of tables, mostly changed by people through an application, triggers with transaction-local context are the approach I keep coming back to. It’s a single migration, it lives with the data, and it answers the question that actually gets asked, which is usually about a specific record and a specific user. What I give up is a second synchronous write on every change, and a user ID that is only as trustworthy as the application that sets it. For the tables I was tracking, both were easy trades.