Event Triggers & DDL Auditing

Capture every schema change automatically — and discover firsthand that DROP needs a completely different event to see it

Every trigger this course has used so far — Lab 2.7.1's pg_notify trigger included — is a row-level trigger: it fires on INSERT, UPDATE, or DELETE against one specific table's data. An event trigger is a completely different mechanism, firing on DDL — schema changes — cluster-wide, regardless of which table or schema is affected, with no per-table CREATE TRIGGER needed anywhere.\n\nThis lab builds a DDL audit log the honest way: get a first version working, discover exactly what it misses by testing it against a real DROP TABLE, and only then fix it — rather than presenting the correct two-trigger design as if it were obvious from the start. The gap is real and specific: ddl_command_end fires for every DDL command, but pg_event_trigger_ddl_commands() genuinely does not report DROP operations at all. Seeing this happen firsthand is worth far more than being told about it in advance.

Row-Level Triggers vs Event Triggers

| | Row-level trigger | Event trigger | |---|---|---| | Fires on | INSERT/UPDATE/DELETE against one table's rows | DDL commands, cluster-wide | | Attached to | One specific table | The whole database (or cluster, for some events) | | Typical use | Firing pg_notify, maintaining a derived column | Auditing schema changes, blocking dangerous DDL |

The First Version — and What It Misses

CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger AS $
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity)
    VALUES (current_user, r.command_tag, r.object_type, r.object_identity);
  END LOOP;
END;
$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_audit ON ddl_command_end EXECUTE FUNCTION log_ddl();

This correctly logs CREATE TABLE and ALTER TABLE — and silently logs nothing at all for DROP TABLE, because pg_event_trigger_ddl_commands() simply does not enumerate dropped objects.

The Fix: A Separate sql_drop Trigger

CREATE OR REPLACE FUNCTION log_ddl_drop() RETURNS event_trigger AS $
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity)
    VALUES (current_user, 'DROP', r.object_type, r.object_identity);
  END LOOP;
END;
$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_audit_drop ON sql_drop EXECUTE FUNCTION log_ddl_drop();

A complete audit needs both event triggers — one for creates and alters, a separate one specifically for drops.

Blocking Dangerous DDL Outright

CREATE OR REPLACE FUNCTION guard_protected() RETURNS event_trigger AS $
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_identity LIKE 'public.protected\_%' THEN
      RAISE EXCEPTION 'Refusing to drop protected object: %', r.object_identity;
    END IF;
  END LOOP;
END;
$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER guard_drop ON sql_drop EXECUTE FUNCTION guard_protected();

Unlike an audit log, this event trigger has teeth: RAISE EXCEPTION inside it aborts the entire DROP statement, cluster-wide, for every session — no per-table permission trick required.

Event Trigger

A trigger that fires on DDL events (schema changes) rather than DML changes to a specific table's rows. Event triggers are cluster-wide by nature — one CREATE EVENT TRIGGER covers every table and schema in the database, with no per-table attachment needed, which is exactly what makes them suitable for auditing or governance that must apply universally.

ddl_command_end vs sql_drop

ddl_command_end fires after most DDL commands complete and exposes what was created or altered via pg_event_trigger_ddl_commands() — but this function does not enumerate objects that were dropped. sql_drop is a separate event specifically for drop operations, paired with pg_event_trigger_dropped_objects() to see exactly what was removed. A complete DDL audit needs both.

📝 Build the First Version

Create an audit function and a ddl_command_end event trigger that logs every DDL command.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity) VALUES (current_user, r.command_tag, r.object_type, r.object_identity); END LOOP; END; \$\$ LANGUAGE plpgsql;"

psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER ddl_audit ON ddl_command_end EXECUTE FUNCTION log_ddl();"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity) VALUES (current_user, r.command_tag, r.object_type, r.object_identity); END LOOP; END; \$\$ LANGUAGE plpgsql;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER ddl_audit ON ddl_command_end EXECUTE FUNCTION log_ddl();" SET CREATE EVENT TRIGGER

🔬 Test It Against Real DDL

Run a CREATE, an ALTER, and a DROP, then check what actually made it into the audit log.

psql -U postgres -d beer_db -c "CREATE TABLE test_audit1 (id int);" -c "ALTER TABLE test_audit1 ADD COLUMN name text;" -c "DROP TABLE test_audit1;"

psql -U postgres -d beer_db -c "SELECT username, command_tag, object_type, object_identity FROM ddl_audit_log ORDER BY id;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE test_audit1 (id int);" -c "ALTER TABLE test_audit1 ADD COLUMN name text;" -c "DROP TABLE test_audit1;" SET CREATE TABLE ALTER TABLE DROP TABLE student@lab:~$ psql -U postgres -d beer_db -c "SELECT username, command_tag, object_type, object_identity FROM ddl_audit_log ORDER BY id;" SET username | command_tag | object_type | object_identity ----------+--------------+-------------+-------------------- postgres | CREATE TABLE | table | public.test_audit1 postgres | ALTER TABLE | table | public.test_audit1 (2 rows)

🔧 Fix It With sql_drop

Add a second event trigger specifically for drops, using pg_event_trigger_dropped_objects() instead.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION log_ddl_drop() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity) VALUES (current_user, 'DROP', r.object_type, r.object_identity); END LOOP; END; \$\$ LANGUAGE plpgsql;"

psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER ddl_audit_drop ON sql_drop EXECUTE FUNCTION log_ddl_drop();"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION log_ddl_drop() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP INSERT INTO ddl_audit_log (username, command_tag, object_type, object_identity) VALUES (current_user, 'DROP', r.object_type, r.object_identity); END LOOP; END; \$\$ LANGUAGE plpgsql;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER ddl_audit_drop ON sql_drop EXECUTE FUNCTION log_ddl_drop();" SET CREATE EVENT TRIGGER

✅ Confirm the Gap Is Closed

Repeat a real DROP TABLE and confirm it now appears in the audit log.

psql -U postgres -d beer_db -c "CREATE TABLE test_audit2 (id int);" -c "DROP TABLE test_audit2;"

psql -U postgres -d beer_db -c "SELECT username, command_tag, object_type, object_identity FROM ddl_audit_log WHERE object_identity LIKE '%test_audit2%' ORDER BY id;"

🛡️ Block Drops on Protected Tables

Write a guard that raises an exception whenever anyone tries to drop a table prefixed protected_, then prove it actually stops the drop.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION guard_protected() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP IF r.object_identity LIKE 'public.protected\\_%' THEN RAISE EXCEPTION 'Refusing to drop protected object: %', r.object_identity; END IF; END LOOP; END; \$\$ LANGUAGE plpgsql;"

psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER guard_drop ON sql_drop EXECUTE FUNCTION guard_protected();"

psql -U postgres -d beer_db -c "CREATE TABLE protected_customers (id int);"

psql -U postgres -d beer_db -c "DROP TABLE protected_customers;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION guard_protected() RETURNS event_trigger AS \$\$ DECLARE r record; BEGIN FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP IF r.object_identity LIKE 'public.protected\\_%' THEN RAISE EXCEPTION 'Refusing to drop protected object: %', r.object_identity; END IF; END LOOP; END; \$\$ LANGUAGE plpgsql;" SET CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "CREATE EVENT TRIGGER guard_drop ON sql_drop EXECUTE FUNCTION guard_protected();" SET CREATE EVENT TRIGGER student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE protected_customers (id int);" SET CREATE TABLE student@lab:~$ psql -U postgres -d beer_db -c "DROP TABLE protected_customers;" SET ERROR: Refusing to drop protected object: public.protected_customers CONTEXT: PL/pgSQL function guard_protected() line 1 at RAISE

Lab 2.7.4 complete. A DDL audit log built the honest way — tested, found incomplete, and fixed — plus a real drop guard:\n\n\n ddl_command_end trigger : ✅ CREATE/ALTER captured correctly\n Gap discovered by testing : ✅ DROP TABLE silently missed\n sql_drop trigger added : ✅ drops now fully captured\n RAISE EXCEPTION guard : ✅ genuinely blocks a real DROP TABLE\n

Enable JavaScript to run the live terminal and track your progress.