Database

Event Triggers

Automatically execute SQL on database events.

In Postgres, an event trigger is similar to a trigger, except that it is triggered by database level events (and is usually reserved for superusers)

With our Supautils extension (installed automatically for all Supabase projects), the postgres user has the ability to create and manage event triggers.

Some use cases for event triggers are:

  • Capturing Data Definition Language (DDL) changes - these are changes to your database schema (though the pgAudit extension provides a more complete solution)
  • Enforcing/monitoring/preventing actions - such as preventing tables from being dropped in Production or enforcing RLS on all new tables

The guide covers two example event triggers:

  1. Preventing accidental dropping of a table
  2. Automatically enabling Row Level Security on new tables in the public schema

Creating an event trigger

Only the postgres user can create event triggers, so make sure you are authenticated as them. As with triggers, event triggers consist of 2 parts

  1. A Function which will be executed when the triggering event occurs
  2. The actual Event Trigger object, with parameters around when the trigger should be run

Example trigger function - prevent dropping tables

This example protects any table from being dropped. You can override it by temporarily disabling the event trigger: ALTER EVENT TRIGGER dont_drop_trigger DISABLE;

1
-- Function
2
CREATE OR REPLACE FUNCTION dont_drop_function()
3
  RETURNS event_trigger LANGUAGE plpgsql AS $$
4
DECLARE
5
    obj record;
6
    tbl_name text;
7
BEGIN
8
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
9
    LOOP
10
        IF obj.object_type = 'table' THEN
11
            RAISE EXCEPTION 'ERROR: All tables in this schema are protected and cannot be dropped';
12
        END IF;
13
    END LOOP;
14
END;
15
$$;
16
17
-- Event trigger
18
CREATE EVENT TRIGGER dont_drop_trigger
19
ON sql_drop
20
EXECUTE FUNCTION dont_drop_function();

Example trigger function - auto enable Row Level Security

See how to auto enable RLS for new tables.

Event trigger Functions and firing events

Event triggers can be triggered on:

  • ddl_command_start - occurs just before a DDL command for almost all objects within a schema
  • ddl_command_end - occurs just after a DDL command for almost all objects within a schema
  • sql_drop - occurs just before ddl_command_end for any DDL commands that DROP a database object (note that altering a table can cause it to be dropped)
  • table_rewrite - occurs just before a table is rewritten using the ALTER TABLE command

Within each event trigger, helper functions exist to view the objects being modified or the command being run. For example, our example calls pg_event_trigger_dropped_objects() to view the object(s) being dropped. For a more comprehensive overview of these functions, read the official event trigger definition documentation

To view the matrix commands that cause an event trigger to fire, read the official event trigger matrix documentation

Disabling an event trigger

You can disable an event trigger using the alter event trigger command:

1
ALTER EVENT TRIGGER dont_drop_trigger DISABLE;

Dropping an event trigger

You can delete a trigger using the drop event trigger command:

1
DROP EVENT TRIGGER dont_drop_trigger;

Resources