skip to content

In PostgreSQL, which events can an event trigger fire on, how does it differ from a table trigger, and how do you recover when a login event trigger blocks every connection?

level: seniorimportance: should knowfreq 24%

answer

  1. per database, DDL not DML
  2. RETURNS event_trigger, superuser only
  3. start, end, sql_drop, rewrite
  4. login arrived in 17
  5. event_triggers off, or single-user

basics

~20 s

Event triggers are per-database, superuser-created hooks on login, ddl_command_start, ddl_command_end, sql_drop and table_rewrite, not on table rows. If a login trigger fails, a superuser connects with event_triggers off, or uses single-user mode, and fixes it.

solid answer

~50 s

A PostgreSQL event trigger is created with `CREATE EVENT TRIGGER`, only by a superuser, and calls a function declared `RETURNS event_trigger`. It is global to one database and fires on events rather than table rows: `ddl_command_start` and `ddl_command_end` around DDL, `sql_drop` for dropped objects, `table_rewrite` before ALTER TABLE or ALTER TYPE rewrites a table, and since 17 `login`. Helper functions expose the details: `pg_event_trigger_ddl_commands()` at `ddl_command_end`, `pg_event_trigger_dropped_objects()` in `sql_drop`, and `pg_event_trigger_table_rewrite_oid()` in `table_rewrite`; `WHEN TAG IN (...)` filters by command tag. They skip shared objects such as roles and databases. A failing login trigger blocks every login, superusers included; recovery is a superuser connection with `event_triggers` set to off in the connection options (a 17+ setting only superusers or roles granted SET on it can change), or single-user mode, then fixing or dropping the trigger.

code

sql · 13 lines
sql
CREATE FUNCTION guard_ledger_rewrite() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF pg_event_trigger_table_rewrite_oid() = 'public.stock_movement'::regclass
     AND extract(hour FROM now()) BETWEEN 6 AND 22 THEN
    RAISE EXCEPTION 'stock_movement may only be rewritten at night';
  END IF;
END;
$$;

CREATE EVENT TRIGGER guard_ledger_rewrite
  ON table_rewrite
  EXECUTE FUNCTION guard_ledger_rewrite();

go deeper

for a junior

Know that event triggers fire on DDL and logins rather than row changes, and that their functions return event_trigger.

for a middle

Name the five events and the helper function each one offers, the TAG filter, the superuser-only rule and the shared-object exclusion.

for a senior

Show the failure semantics (start blocks the command, end rolls it back) and the login-trigger hazards: standbys, lockout, no superuser bypass, and recovery through event_triggers off or single-user mode.

for a principal

Decide whether DDL governance belongs in event triggers, in migration review and statement logging, or in an extension such as pgAudit, weighing lockout risk against enforcement.

## Event triggers versus table triggers PostgreSQL has two trigger systems that share a word and little else. | | Table trigger | Event trigger | |---|---|---| | Created with | `CREATE TRIGGER` | `CREATE EVENT TRIGGER` | | Attached to | one table, view or foreign table | the whole database it is created in | | Fires on | INSERT, UPDATE, DELETE, TRUNCATE | DDL commands and logins | | Function returns | `trigger` | `event_trigger` | | Who may create | a role with the TRIGGER privilege on the table | superusers only | | PL/pgSQL context | `NEW`, `OLD`, `TG_OP` | `TG_EVENT`, `TG_TAG` | Event trigger functions can be written in any procedural language with event-trigger support, or C, but not plain SQL. Several event triggers on one event fire in alphabetical order by name, and `ALTER EVENT TRIGGER ... DISABLE` or `ENABLE [ REPLICA | ALWAYS ]` controls them. ## The five events | Event | Fires | Useful function | |---|---|---| | `ddl_command_start` | just before CREATE, ALTER, DROP, COMMENT, GRANT, REVOKE, REINDEX, REFRESH MATERIALIZED VIEW and similar | `TG_TAG` only; nothing is executed yet | | `ddl_command_end` | just after the same commands, before commit | `pg_event_trigger_ddl_commands()`: `command_tag`, `object_type`, `object_identity` and more | | `sql_drop` | just before `ddl_command_end` for anything that dropped objects | `pg_event_trigger_dropped_objects()`, which errors if called elsewhere | | `table_rewrite` | just before ALTER TABLE or ALTER TYPE rewrites a table | `pg_event_trigger_table_rewrite_oid()`, `pg_event_trigger_table_rewrite_reason()` | | `login` (17+) | when an authenticated user connects | session context such as `session_user` | `ddl_command_start` and `ddl_command_end` do **not** fire for commands on shared, cluster-wide objects (databases, roles and memberships, tablespaces, parameter privileges, `ALTER SYSTEM`) or for commands on event triggers themselves. `table_rewrite` does not fire for `CLUSTER` or `VACUUM`, which also rewrite tables. ## Writing one 1. Create a function `RETURNS event_trigger` with no arguments; it returns no value. 2. Create the trigger, for example `CREATE EVENT TRIGGER guard_rewrites ON table_rewrite EXECUTE FUNCTION ...`. 3. Narrow DDL triggers with `WHEN TAG IN ('ALTER TABLE', 'DROP TABLE')`; `TAG` is the only filter variable. 4. Test it in a scratch database first, because a broken event trigger affects every session in that database. ## Failure semantics - An error in a `ddl_command_start` trigger stops the DDL before it runs and no further event triggers fire. - An error in a `ddl_command_end` trigger rolls back the DDL with the transaction. - If the DDL itself fails, `ddl_command_end` triggers never run, because nothing executes in an aborted transaction. - Typical uses are guards (refuse rewrites of the large `stock_movement` ledger during business hours), DDL logging from `pg_event_trigger_ddl_commands()`, and enforcing naming rules. ## Login triggers and how to get back in The `login` event, added in PostgreSQL 17, runs after authentication for every new session in that database. The manual's warnings are concrete: a bug in the function can prevent anyone from logging in; the event also fires on standby servers, so a trigger that writes must check `pg_is_in_recovery()` first or the standby becomes inaccessible; and long-running work is a bad idea, because cancelling the connection in psql does not cancel an in-progress login trigger. There is no superuser exemption. Recovery, in order of preference: 1. Connect as a superuser with the `event_triggers` setting turned off for that session, for example through libpq `options`, then run `ALTER EVENT TRIGGER ... DISABLE` or `DROP EVENT TRIGGER`. The `event_triggers` setting was added in 17, defaults to on, and only superusers or roles granted SET on it may change it. 2. Set `event_triggers = off` in the configuration file and reload, if no superuser session is possible. 3. Start the server in single-user mode, where event triggers never fire; before 17 this was the only escape from a broken event trigger. ## Inspecting and managing event triggers - In psql, `\dy` lists the event triggers in the current database with their event, owner, enabled state and tags. - The catalog `pg_event_trigger` holds the same facts: `evtname`, `evtevent`, `evtowner`, `evtfoid` (the function), `evtenabled` and `evttags`. - `ALTER EVENT TRIGGER ... DISABLE` keeps the definition but stops it firing, which is the safest first step during an incident; `DROP EVENT TRIGGER` removes it. - Because event triggers are per database, a guard created in the `warehouse` database does nothing for DDL run in any other database of the same cluster; create it in each database that needs it, or in `template1` before new databases are created. - Keep event trigger functions short and free of external calls: they run inside the DDL transaction or the login path, so their latency becomes every migration's and every connection's latency.

  • Why must a login event trigger that writes to a table check pg_is_in_recovery() first?
    The `login` event also fires on standby servers, which are read-only. A write there raises an error inside the login trigger, and an error in a login trigger rejects the connection, so the standby becomes inaccessible. The manual's example returns early when `pg_is_in_recovery()` is true and only writes on the primary.
  • A DDL-logging trigger on ddl_command_end never records CREATE ROLE statements. Is it broken?
    No. `ddl_command_start` and `ddl_command_end` deliberately skip commands on shared objects: databases, roles and role memberships, tablespaces, parameter privileges and `ALTER SYSTEM`. Those objects belong to the cluster, not one database, so a per-database event trigger never sees them. Capture them with statement logging instead.

  • Login event triggers do not run for superusers, so a superuser can always log in.
  • Any role that owns a table can create an event trigger to audit its DDL.
  • Event triggers fire on INSERT, UPDATE and DELETE across the whole database.
  • A ddl_command_end trigger can be used to log CREATE DATABASE and CREATE ROLE.
  • A login trigger can insert audit rows on a standby without any recovery check.

Assumes PostgreSQL 18; the login event and the event_triggers setting arrived in 17, so 16 and older have neither and rely on single-user mode.