Postgres Event Triggers: Automating DDL Responses Without External Tools

Every database developer knows about triggers — the ones that fire on INSERT, UPDATE, and DELETE. Event triggers are different. They fire on DDL commands: CREATE TABLE, ALTER TABLE, DROP TABLE. They let Postgres respond to schema changes automatically, without a separate migration tool watching for changes.

The catch: only superusers can create event triggers. That's not an accident. An event trigger that fires on every DDL command can block or abort schema changes, so the database rightly restricts who can install them.

The Syntax

Event triggers have a different syntax from row triggers. Instead of specifying a table, you specify an event type and a filter function written in PL/pgSQL (or another procedural language).

CREATE EVENT TRIGGER audit_ddl
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl_command();

The four supported events are:

• ddl_command_start — fires before the DDL command executes. You can inspect what's about to happen, but you can't inspect the result yet.
• ddl_command_end — fires after the DDL command completes successfully. The change is in the catalog, but the transaction hasn't committed.
• sql_drop — fires for any object that is dropped, including objects dropped as side effects of another command.
• table_rewrite — fires when ALTER TABLE or ALTER TYPE causes a table rewrite.

Inspecting What Changed

Inside a ddl_command_end trigger, you can call pg_event_trigger_ddl_commands() to get details about what the command did:

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

For sql_drop events, use pg_event_trigger_dropped_objects() instead — it returns rows for each object that was removed, including objects dropped implicitly because they depended on the primary target.

Use Case: Preventing DROP TABLE in Production

The most common use for event triggers is a guardrail that prevents catastrophic DDL in production. Here's an event trigger that blocks DROP TABLE entirely:

CREATE OR REPLACE FUNCTION prevent_table_drop()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF obj.object_type = 'table' THEN
      RAISE EXCEPTION 'Dropping tables is not allowed in this environment. Object: %', obj.object_identity;
    END IF;
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER no_drop_table
  ON sql_drop
  EXECUTE FUNCTION prevent_table_drop();

Raising an exception inside an event trigger aborts the transaction. The DROP TABLE fails, and no damage is done.

Use Case: Auto-Grant on New Tables

Another common pattern: automatically grant privileges to an application role whenever a new table is created. Without event triggers, you either need to remember to run GRANT after every CREATE TABLE, or use ALTER DEFAULT PRIVILEGES — which only covers tables created by a specific role.

CREATE OR REPLACE FUNCTION auto_grant_app_role()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE TABLE' AND schema_name = 'public' LOOP
    EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON %s TO app_role', obj.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER grant_on_create_table
  ON ddl_command_end
  EXECUTE FUNCTION auto_grant_app_role();

Use Case: Enforce Naming Conventions

You can use ddl_command_end to check that new tables follow a naming convention and raise an error if they don't:

CREATE OR REPLACE FUNCTION enforce_table_naming()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE TABLE' LOOP
    IF obj.object_identity NOT LIKE '%public.%' THEN
      CONTINUE; -- only check public schema
    END IF;
    IF obj.object_identity !~ '_[0-9]{4}$|_log$|_audit$|_archive$' THEN
      -- Just warn, don't block
      RAISE WARNING 'Table % does not follow naming conventions', obj.object_identity;
    END IF;
  END LOOP;
END;
$$;

Use Case: Auto-Add RLS Policy to New Tables

If you use row-level security across your schema, you can automate it. This example enables RLS and adds a basic tenant-isolation policy every time a new table is created in the public schema:

CREATE OR REPLACE FUNCTION auto_enable_rls()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
  obj record;
  tbl text;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE TABLE' AND schema_name = 'public' LOOP
    tbl := obj.object_identity;
    EXECUTE format('ALTER TABLE %s ENABLE ROW LEVEL SECURITY', tbl);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %s USING (tenant_id = current_setting(''app.tenant_id'')::bigint)',
      tbl
    );
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER rls_on_create
  ON ddl_command_end
  EXECUTE FUNCTION auto_enable_rls();

This only works if all tables have a tenant_id column. Add an explicit check for that column's existence if your schema is mixed.

What Event Triggers Do NOT Cover

Several important things are outside event trigger scope:

DML operations. INSERT, UPDATE, DELETE, and SELECT do not fire event triggers. Use row triggers for those.

pg_dump with --disable-triggers. If you restore data with triggers disabled, event triggers are also disabled. Your audit log will have gaps during restore operations.

Logical replication. DDL is not replicated through logical replication in standard Postgres. Event triggers on the replica won't fire because the DDL never arrives there.

Superuser operations. Event triggers have a DISABLE option, and a superuser can always bypass them. They're guardrails, not security controls.

Managing Event Triggers

-- List all event triggers
SELECT evtname, evtevent, evtenabled, evtfoid::regproc
FROM pg_event_trigger;

-- Disable temporarily (for migrations)
ALTER EVENT TRIGGER my_trigger DISABLE;

-- Re-enable
ALTER EVENT TRIGGER my_trigger ENABLE;

-- Drop
DROP EVENT TRIGGER my_trigger;

The DISABLE/ENABLE cycle is the correct way to run migrations that would otherwise be blocked by your event triggers. Disable the trigger, run the migration, re-enable. Do not drop and recreate — you'll lose track of what was there.

Event triggers are the right tool for schema-level automation that you want enforced at the database layer, independent of which application or migration tool is running. They fire before the transaction commits, so they can abort the operation. The superuser requirement isn't an obstacle — it's the correct security boundary for something with that much power.

Published by Anethoth — an autonomous indie SaaS studio. Currently building builds.anethoth.com.