PostgreSQL Event Triggers as a DDL Audit Trail: Who Dropped That Column

On a Friday at 16:40 the legacy_flags column disappeared from billing_events. The application noticed first, as ERROR: column b.legacy_flags does not exist, SQLSTATE 42703, on about four hundred requests per minute. Git blamed the wrong person within ten minutes — a migration touching that table had merged that morning — but that migration was additive, and reverting it changed nothing. The actual session had come from a contractor’s ad-hoc psql window tidying "dead columns," and we only reconstructed that on Monday, from a connection log line that survived by luck, because the statement log had already rotated. Seven days of log retention is fine for performance incidents and useless for "who changed the schema nine days ago."

The durable answer we built is a DDL audit trail inside the database itself, driven by event triggers. It has now answered "who did this" four times in a year, in under a minute each time. Here is the design, the drop-catching half everyone forgets, and the things event triggers genuinely cannot see.

Why can’t the server log be your schema audit?

Because the log is optimized for operators, not for evidence — it rotates, it samples, it truncates statements, and on a busy primary the interesting line is drowned in a million uninteresting ones. You can set log_statement = ‘ddl’ and get every DDL statement logged, and we do, but that buys you a text file whose retention is an ops decision and whose searchability is grep. What the incident actually demanded was a query: show me every DDL against billing_events in the last ninety days, with the role, the client address, the application name, and the full statement. That is a table, not a log. Event triggers are the mechanism that fills the table: they fire inside the database on DDL events, run a function you write, and can insert a row into an audit table in the same transaction as the change itself. Rotation stops being your problem, because the evidence lives next to the data and inherits its backup and retention story.

How do you build the audit trail with event triggers?

You write one logging function, attach it to ddl_command_end, and let PostgreSQL call it after every schema change completes. The function reads tg_tag for the command tag, current_query() for the full statement text, and the ordinary session functions for attribution — session_user for the authenticated role, inet_client_addr() for the source host, current_setting(‘application_name’) for whichever service or human tool connected. The whole thing is small enough to review in one screen:

CREATE SCHEMA audit;

CREATE TABLE audit.ddl_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  username text NOT NULL,
  application_name text,
  client_addr inet,
  command_tag text NOT NULL,
  statement text NOT NULL
);

CREATE OR REPLACE FUNCTION audit.record_ddl() RETURNS event_trigger
LANGUAGE plpgsql AS $func$
BEGIN
  INSERT INTO audit.ddl_log (username, application_name, client_addr, command_tag, statement)
  VALUES (session_user,
          current_setting('application_name', true),
          inet_client_addr(),
          tg_tag,
          current_query());
END;
$func$;

CREATE EVENT TRIGGER ddl_audit
ON ddl_command_end
EXECUTE FUNCTION audit.record_ddl();

Creating an event trigger requires superuser or the database owner, and the trigger is per-database — a cluster with nine databases needs the trigger installed nine times, which our provisioning now does from the same migration that creates the schema. Note also the transactionality working for you: the audit row lands in the same transaction as the DDL, so a schema change that rolls back takes its audit row with it, which is precisely correct — no phantom entries for migrations that never happened.

How do you catch DROPs, which ddl_command_end won’t describe?

You attach a second trigger to the sql_drop event, because by the time ddl_command_end fires for a DROP TABLE, the object is gone and the ordinary catalogs cannot tell you what it was. sql_drop fires at the end of any command that drops database objects and exposes the victims through pg_event_trigger_dropped_objects() — a set-returning function giving schema, object name, object type, and whether the drop was original or cascade collateral. That second half matters more than the first in practice: the column in our incident did not fall to DROP COLUMN at all, it went with a DROP TABLE … CASCADE on a table the contractor believed was unreferenced. The cascade victims are exactly what you want in evidence:

CREATE OR REPLACE FUNCTION audit.record_drop() RETURNS event_trigger
LANGUAGE plpgsql AS $func$
BEGIN
  INSERT INTO audit.ddl_log (username, application_name, client_addr, command_tag, statement)
  SELECT session_user,
         current_setting('application_name', true),
         inet_client_addr(),
         tg_tag || ': ' || d.object_type || ' ' ||
           coalesce(d.schema_name || '.', '') || d.object_name ||
           CASE WHEN NOT d.original THEN ' (cascade)' ELSE '' END,
         current_query()
  FROM pg_event_trigger_dropped_objects() d;
END;
$func$;

CREATE EVENT TRIGGER ddl_audit_drop
ON sql_drop
EXECUTE FUNCTION audit.record_drop();

With both triggers in place, "who dropped that column" becomes SELECT * FROM audit.ddl_log WHERE statement ILIKE ‘%billing_events%’ ORDER BY occurred_at DESC — the query that would have ended our Friday incident in one minute instead of one weekend. Keep the audit table boring: insert-only grants for the function’s owner, no UPDATE or DELETE for anyone routine, and fold it into the same schema-change review discipline as everything else — the migration review checklist is where our "does this migration preserve the audit triggers" line lives, right next to the ownership rules from the default privileges notes.

What do event triggers not see?

Three blind spots, and you should write them into the runbook rather than learn them during an incident. First, shared objects: CREATE ROLE, ALTER ROLE, CREATE DATABASE, and tablespace changes are cluster-level, not database-level, and no event trigger fires for them — role changes need the connection and statement logs, or a compliance tool like pgaudit, which is the right answer when an auditor, rather than an engineer, is asking the questions. Second, DML is invisible by design: event triggers are a schema-change mechanism, so a DELETE that empties a table leaves no trace here; that is what row triggers (with their own costs, covered in the trigger performance notes) or logical decoding are for. Third, the audit row is only as good as the connection’s honesty: application_name is client-supplied and anyone with psql can set it to anything, which is why the audit captures the client address and session role alongside it — attribution rests on the credential, not the label. None of these are reasons not to build the trail; they are reasons to know exactly which questions it can answer.

Watching schema churn with MonPG

The audit table tells you who; monitoring tells you when and what it cost. Schema changes show up in operational series as lock waits, cache invalidations, query-plan flips, and error spikes like our 42703 storm. MonPG monitors PostgreSQL in production today, and its PostgreSQL monitoring puts statement errors, lock activity, and query latency on one timeline, so the 16:40 DDL and the 16:41 error rate share a screen instead of living in two investigations. Correlation first, attribution from the audit table second — that order ends incidents faster.

Related documentation

• PostgreSQL: ALTER TABLE