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

1
CREATE OR REPLACE FUNCTION rls_auto_enable()
2
RETURNS EVENT_TRIGGER
3
LANGUAGE plpgsql
4
SECURITY DEFINER
5
SET search_path = pg_catalog
6
AS $$
7
DECLARE
8
  cmd record;
9
BEGIN
10
  FOR cmd IN
11
    SELECT *
12
    FROM pg_event_trigger_ddl_commands()
13
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
14
      AND object_type IN ('table','partitioned table')
15
  LOOP
16
     IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
17
      BEGIN
18
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
19
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
20
      EXCEPTION
21
        WHEN OTHERS THEN
22
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
23
      END;
24
     ELSE
25
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
26
     END IF;
27
  END LOOP;
28
END;
29
$$;
30
31
DROP EVENT TRIGGER IF EXISTS ensure_rls;
32
CREATE EVENT TRIGGER ensure_rls
33
ON ddl_command_end
34
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
35
EXECUTE FUNCTION rls_auto_enable();

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