Event Triggers

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#

If you want Row Level Security enabled automatically for new tables, create an event trigger that runs after table creation and calls ALTER TABLE ... ENABLE ROW LEVEL SECURITY on each newly created table.

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
          RAISE;
24
      END;
25
     ELSE
26
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
27
     END IF;
28
  END LOOP;
29
END;
30
$$;
31
32
DROP EVENT TRIGGER IF EXISTS ensure_rls;
33
CREATE EVENT TRIGGER ensure_rls
34
ON ddl_command_end
35
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
36
EXECUTE FUNCTION rls_auto_enable();

Note that this applies to tables created after the trigger is installed. Existing tables still need RLS enabled manually.

Event trigger Functions and firing events#

Event triggers can be triggered on:

• ddl_command_start - occurs before a DDL command for almost all objects within a schema
• ddl_command_end - occurs after a DDL command for almost all objects within a schema
• sql_drop - occurs 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 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#

• Official Postgres Docs: Event Trigger Behaviours
• Official Postgres Docs: Event Trigger Firing Matrix
• Supabase blog: Postgres Event Triggers without superuser access