Logging DDL Events with Event Triggers

At 02:13 an index was dropped from orders, and query latency tripled. The migration history showed nothing: no migration had run that night. The server log was configured with log_statement = 'none'. It took most of a day to establish that a well-meaning engineer had dropped a “duplicate” index from a console session during unrelated maintenance. Schema changes are among the most consequential events in a database, and most teams record only the ones that went through the migration tool. PostgreSQL’s event triggers fire on DDL commands themselves, regardless of which client issued them, and can record exactly what changed, who changed it, and from which application. This guide sets up a DDL audit log with event triggers, flags changes made outside the migration pipeline, and wires it into alerting. It belongs to Migration Observability.

DDL Audit With an Event Trigger Any client issuing DDL — the migration pipeline, a console session, an extension — fires the ddl_command_end event trigger, which calls pg_event_trigger_ddl_commands and writes one row per command into an audit table with role, application_name and client address. A separate sql_drop trigger records dropped objects. An alerting job flags rows whose role or application is not the migration pipeline. DDL Audit With an Event Trigger Migration pipeline role migrator Console / scripts any other role Event triggers ddl_command_end + sql_drop audit.ddl_log command, object, role, app Alerting role ≠ migrator → notify DDL insert
Every DDL statement is recorded at the database, whatever path it came through, so out-of-band changes become visible.

Symptom / Error Signatures

Missing DDL auditing shows up during incident investigation:

  • A schema object changed or disappeared, and neither the migration history nor the deploy log explains it.
  • Drift checks report differences between environments without any record of when or how they were introduced, as in detecting production schema drift against a desired state.
  • Compliance or security review asks “who can change the schema, and how would we know?”, and the honest answer is “anyone with the password, and we wouldn’t”.

Root Cause Analysis

Migration tools record what they applied, in their own history tables. They cannot see DDL issued by other clients: console sessions, ad-hoc scripts, extensions, other tools, or a different migration tool used by another team. Server logging with log_statement = 'ddl' records statement text but is hard to query, may be rotated away, and does not decompose a statement into affected objects.

PostgreSQL event triggers run inside the database when DDL events occur. ddl_command_end fires after each DDL command and exposes pg_event_trigger_ddl_commands(), a set of rows describing each affected object (command tag, object type, schema-qualified identity). sql_drop fires for dropped objects and exposes pg_event_trigger_dropped_objects(). A trigger function can write those rows, plus session context — current_user, session_user, application_name, inet_client_addr() — into an audit table in the same transaction as the DDL. If the DDL rolls back, so does its audit row, so the log reflects what actually committed.

Capture method Sees all clients Structured per object Queryable history Survives log rotation
migration tool history no per migration yes yes
log_statement = 'ddl' yes no (text) via log pipeline depends
event triggers → audit table yes yes yes (SQL) yes
pgAudit extension yes class-based via log pipeline depends
What Happens on an Out-of-Band DROP INDEX Sequence between a console session, PostgreSQL, the sql_drop event trigger, the audit table and alerting. The console issues DROP INDEX. PostgreSQL fires sql_drop, which inserts a row with object identity, role and application into the audit table in the same transaction. The alerting job sees a row from a role other than the migrator and notifies the team. What Happens on an Out-of-Band DROP INDEX Console session PostgreSQL Audit table Alerting DROP INDEX orders_customer_idx sql_drop + ddl_command_end fire insert: DROP INDEX, role alice, app psql poll: role ≠ migrator? notify #db-changes
The audit row commits with the DDL, and the alert fires within minutes — instead of the change being discovered during an incident.

Immediate Mitigation

1. Turn on DDL statement logging now as a stopgap while the audit table is built. It requires only a configuration reload.

-- PostgreSQL · superuser · takes effect on reload; logs DDL statement text to the server log
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();
-- ROLLBACK PATH: ALTER SYSTEM RESET log_statement; SELECT pg_reload_conf();

2. Create the audit table and event triggers. Event triggers require superuser (or, on managed services, the provider’s equivalent privileged role).

-- PostgreSQL 11+ · superuser · audit table and ddl_command_end trigger
CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.ddl_log (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  happened_at    timestamptz NOT NULL DEFAULT now(),
  command_tag    text,
  object_type    text,
  object_identity text,
  role_name      text DEFAULT current_user,
  session_role   text DEFAULT session_user,
  application    text DEFAULT current_setting('application_name', true),
  client_addr    inet DEFAULT inet_client_addr()
);

CREATE OR REPLACE FUNCTION audit.log_ddl() RETURNS event_trigger
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    IF r.schema_name IS DISTINCT FROM 'audit' THEN
      INSERT INTO audit.ddl_log (command_tag, object_type, object_identity)
      VALUES (r.command_tag, r.object_type, r.object_identity);
    END IF;
  END LOOP;
END $$;
CREATE EVENT TRIGGER audit_ddl_end ON ddl_command_end EXECUTE FUNCTION audit.log_ddl();
-- ROLLBACK PATH: DROP EVENT TRIGGER audit_ddl_end; DROP FUNCTION audit.log_ddl();

3. Add the drop trigger, because dropped objects are reported separately.

-- PostgreSQL 11+ · superuser · records dropped objects
CREATE OR REPLACE FUNCTION audit.log_drop() RETURNS event_trigger
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() WHERE original LOOP
    INSERT INTO audit.ddl_log (command_tag, object_type, object_identity)
    VALUES ('DROP', r.object_type, r.object_identity);
  END LOOP;
END $$;
CREATE EVENT TRIGGER audit_ddl_drop ON sql_drop EXECUTE FUNCTION audit.log_drop();

Permanent Fix / Long-Term Pattern

Make the audit table part of the database’s standard setup, created by the same bootstrap that creates roles, and protect it: only the event-trigger function writes to it, application roles cannot modify it, and it is exported to the central log or security platform so a superuser cannot quietly erase history. Tag migration sessions clearly — a dedicated migrator role and an application_name such as flyway-deploy-<release> — so the alerting rule is simple: any DDL from another role or application raises a notification to the team channel with the command and object.

-- PostgreSQL · read-only · DDL in the last day not made by the migration pipeline
SELECT happened_at, command_tag, object_identity, role_name, application, client_addr
FROM audit.ddl_log
WHERE happened_at > now() - interval '1 day'
  AND role_name <> 'migrator'
ORDER BY happened_at DESC;

Keep the trigger functions small and robust: a failing event trigger fails the DDL itself, so test them in staging, and keep a documented break-glass procedure (ALTER EVENT TRIGGER ... DISABLE) for emergencies. Correlate the audit log with deploy records to see each release’s actual DDL, and with the metrics in tracking schema migration metrics and SLOs. MySQL has no event triggers; use the audit log plugin or general_log filtering, or proxy-level logging, to achieve the same visibility.

DDL Audit Log Uses Matrix of uses of the DDL audit log, the question each answers, and the query or signal used. DDL Audit Log Uses Use Question answered Signal incident response what changed before the regression? rows in the window before the alert out-of-band changes who bypassed the pipeline? role ≠ migrator drift root cause when did this environment diverge? object_identity history release audit what DDL did release X run? application = deploy tag
One table answers incident, compliance and drift questions that otherwise require log archaeology.

Verification Checklist

Frequently Asked Questions

Do event triggers slow down DDL? Negligibly. They run once per DDL command and insert a few rows. They do not fire for ordinary INSERT, UPDATE or DELETE, so application workload is unaffected.

What happens if the event trigger function fails? The DDL command fails with it, because the trigger runs in the same transaction. Keep the function simple, test it, and know how to disable the event trigger in an emergency.

Can event triggers be used on managed PostgreSQL services? Many managed services allow event triggers for their privileged administrative role; some restrict them. Check your provider’s documentation; where they are unavailable, use log_statement = 'ddl' or pgAudit with log export.

Does the audit log capture the full SQL text? pg_event_trigger_ddl_commands() provides structured information about affected objects, not the original text. current_query() can be recorded too, but it returns the whole client statement, which may include multiple commands.

Does MySQL have an equivalent? MySQL has no event triggers for DDL. Use the audit log plugin available in your distribution, filtered general logging, or a proxy that records DDL, and ship those logs centrally.

Related Articles