Event Triggers, DDL Governance, Policy Enforcement, and Deployment Concerns

Learning outcomes

Table triggers react to DML on one relation. Event triggers are different: they are database-wide hooks around supported DDL events. That makes them powerful for auditing and governance and dangerous enough that PostgreSQL permits only superusers to create them. A broken policy can block migrations—or, for login event triggers, even make normal connections unusable.

1. Create and enter a disposable admin database

CREATE DATABASE servicehub_ch18_event_lab;\c servicehub_ch18_event_labSELECT current_database(), current_user,       rolsuperFROM pg_rolesWHERE rolname = current_user;

The final rolsuper value must be true for this lab because PostgreSQL 18 restricts CREATE EVENT TRIGGER to superusers. If you do not have a disposable local superuser account, read the lesson and skip the live event-trigger creation rather than weakening a production role.

2. ddl_command_end can observe completed DDL before transaction commit

CREATE SCHEMA ch18_admin;CREATE TABLE ch18_admin.ddl_audit (  audit_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  event_name text NOT NULL,  command_tag text NOT NULL,  object_type text,  object_identity text,  schema_name text,  actor name NOT NULL,  recorded_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE OR REPLACE FUNCTION ch18_admin.capture_ddl_end()RETURNS event_triggerLANGUAGE plpgsqlAS $$BEGIN  INSERT INTO ch18_admin.ddl_audit(    event_name, command_tag, object_type, object_identity, schema_name, actor  )  SELECT TG_EVENT,         command_tag,         object_type,         object_identity,         schema_name,         session_user  FROM pg_event_trigger_ddl_commands();END$$;CREATE EVENT TRIGGER ch18_capture_ddl_endON ddl_command_endEXECUTE FUNCTION ch18_admin.capture_ddl_end();

CREATE TABLE public.ch18_event_demo(id bigint PRIMARY KEY, note text);ALTER TABLE public.ch18_event_demo ADD COLUMN created_at timestamptz;SELECT event_name, command_tag, object_type, object_identity, schema_name, actorFROM ch18_admin.ddl_auditORDER BY audit_id;

ddl_command_end fires after the command's catalog changes are visible but before the containing transaction commits. If the event trigger itself raises an error, PostgreSQL rolls back the DDL with the transaction.

3. sql_drop exposes objects removed by one DDL action

CREATE TABLE ch18_admin.drop_audit (  audit_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  object_type text NOT NULL,  object_identity text NOT NULL,  original boolean NOT NULL,  normal boolean NOT NULL,  actor name NOT NULL,  recorded_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE OR REPLACE FUNCTION ch18_admin.capture_drop()RETURNS event_triggerLANGUAGE plpgsqlAS $$BEGIN  INSERT INTO ch18_admin.drop_audit(    object_type, object_identity, original, normal, actor  )  SELECT object_type, object_identity, original, normal, session_user  FROM pg_event_trigger_dropped_objects();END$$;CREATE EVENT TRIGGER ch18_capture_dropON sql_dropEXECUTE FUNCTION ch18_admin.capture_drop();DROP TABLE public.ch18_event_demo;SELECT * FROM ch18_admin.drop_audit ORDER BY audit_id;

A single DROP ... CASCADE or dependency cleanup can report multiple dropped objects. original distinguishes directly named objects from secondary drops. This is governance evidence, not a restore mechanism.

4. Event coverage is intentionally not “every admin command”

PostgreSQL 18 supports event triggers for login, ddl_command_start, ddl_command_end, sql_drop, and table_rewrite. The DDL command start/end events cover many CREATE/ALTER/DROP/COMMENT/GRANT/REINDEX-style commands, but not commands on certain shared objects such as databases, roles, tablespaces, parameter privileges, or ALTER SYSTEM. Commands targeting event triggers themselves are also excluded, which is important for recovery.

SELECT evtname,       evtevent,       evtenabled,       evttags,       evtfoid::regprocedure AS functionFROM pg_event_triggerORDER BY evtname;

Do not infer governance coverage from “we installed a DDL event trigger.” Maintain an explicit command/event coverage table for your deployment system.

5. Deliberately overzealous policy: block every ALTER TABLE

CREATE OR REPLACE FUNCTION ch18_admin.block_alter_table()RETURNS event_triggerLANGUAGE plpgsqlAS $$BEGIN  RAISE EXCEPTION USING    ERRCODE = 'P1805',    MESSAGE = format('DDL tag %s is blocked by ch18 lab policy', TG_TAG),    HINT = 'Disable the lab guard before approved migrations.';END$$;CREATE EVENT TRIGGER ch18_block_alter_tableON ddl_command_startWHEN TAG IN ('ALTER TABLE')EXECUTE FUNCTION ch18_admin.block_alter_table();CREATE TABLE public.ch18_migration_target(id bigint PRIMARY KEY);ALTER TABLE public.ch18_migration_target ADD COLUMN note text;-- Expected: P1805; ALTER TABLE does not execute.

This proves a governance hook can become an outage generator. A blanket policy has no context about migration approval, object ownership, deployment identity, or maintenance windows.

6. Safe recovery: commands targeting event triggers remain available

ALTER EVENT TRIGGER ch18_block_alter_table DISABLE;ALTER TABLE public.ch18_migration_target ADD COLUMN note text;ALTER EVENT TRIGGER ch18_block_alter_table ENABLE;SELECT evtname, evtenabledFROM pg_event_triggerWHERE evtname = 'ch18_block_alter_table';

Commands that alter/drop event triggers do not fire normal DDL event triggers, so a broken DDL guard can usually be disabled by an authorized superuser. Build this recovery command into the deployment runbook before enabling governance.

7. Login event triggers have a stronger failure mode

A login event trigger runs after authentication when a connection enters the database. A bug can prevent successful logins. PostgreSQL documents two emergency bypasses: start/connect with the event_triggers setting disabled, or use single-user mode, where event triggers are disabled. Login triggers also fire on standby servers, so code must not attempt writes there.

8. Event triggers are transactional policy code

BEGIN;CREATE TABLE public.ch18_rollback_demo(id integer);SELECT count(*) AS audit_rows_inside_transactionFROM ch18_admin.ddl_auditWHERE object_identity LIKE '%ch18_rollback_demo%';ROLLBACK;SELECT to_regclass('public.ch18_rollback_demo') AS relation_after_rollback;SELECT count(*) AS audit_rows_after_rollbackFROM ch18_admin.ddl_auditWHERE object_identity LIKE '%ch18_rollback_demo%';

The event-trigger audit row participates in the same transaction. That is useful for atomic governance but means the audit table is not an immutable external trail of rolled-back attempts. If rejected-attempt telemetry matters, capture it in server logs or an external deployment/control system as well.

9. Cleanup the disposable database

DROP EVENT TRIGGER IF EXISTS ch18_block_alter_table;DROP EVENT TRIGGER IF EXISTS ch18_capture_drop;DROP EVENT TRIGGER IF EXISTS ch18_capture_ddl_end;DROP SCHEMA ch18_admin CASCADE;DROP TABLE IF EXISTS public.ch18_migration_target;-- Reconnect to an admin database before dropping this disposable database.

\c postgresDROP DATABASE servicehub_ch18_event_lab WITH (FORCE);

10. Checkpoint

Authoritative references

Routines, trigger timing, privileges, and planner promises are version-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — Event Triggers
• PostgreSQL 18 — Overview of Event Trigger Behavior
• PostgreSQL 18 — CREATE EVENT TRIGGER
• PostgreSQL 18 — Event Trigger Helper Functions