Skip to content
Database

事件触发器

Automatically execute SQL on database events.

在 Postgres 中，事件触发器 类似于 触发器，只是它是由数据库级别的事件触发的（通常仅供 超级用户 使用）

🌐 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)

通过我们的 Supautils 扩展（所有 Supabase 项目都会自动安装），postgres 用户可以创建和管理事件触发器。

🌐 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:

  • 捕获数据定义语言（DDL）更改——这些是对你的数据库模式的更改（尽管 pgAudit 插件提供了更完整的解决方案）
  • 执行/监控/防止操作——比如防止在生产环境中删除表，或在所有新表上强制实现行级安全（RLS）

这个指南涵盖了两个示例事件触发器：

🌐 The guide covers two example event triggers:

  1. 防止桌子意外掉落
  2. 在 public 模式下的新表上自动启用行级安全

创建一个事件触发器 #

🌐 Creating an event trigger

只有 postgres 用户可以创建事件触发器，所以确保你已以他们的身份认证。像触发器一样，事件触发器由两部分组成

🌐 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. 一个在触发事件发生时会执行的函数
  2. 实际的事件触发器对象，带有关于触发器何时运行的参数

示例触发函数 - 防止删除表 #

🌐 Example trigger function - prevent dropping tables

这个示例可以保护任何表不被删除。你可以通过临时禁用事件触发器来覆盖它：ALTER EVENT TRIGGER dont_drop_trigger DISABLE;

🌐 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;

-- Function
CREATE OR REPLACE FUNCTION dont_drop_function()
  RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
    tbl_name text;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF obj.object_type = 'table' THEN
            RAISE EXCEPTION 'ERROR: All tables in this schema are protected and cannot be dropped';
        END IF;
    END LOOP;
END;
$$;
-- Event trigger
CREATE EVENT TRIGGER dont_drop_trigger
ON sql_drop
EXECUTE FUNCTION dont_drop_function();

示例触发函数 - 自动启用行级安全 #

🌐 Example trigger function - auto enable Row Level Security

如果你希望新表能自动启用行级安全，可以创建一个在表创建后运行的事件触发器，并对每个新创建的表调用 ALTER TABLE ... 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.

CREATE OR REPLACE FUNCTION rls_auto_enable()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$
DECLARE
  cmd record;
BEGIN
  FOR cmd IN
    SELECT *
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
      AND object_type IN ('table','partitioned table')
  LOOP
     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
      BEGIN
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
      EXCEPTION
        WHEN OTHERS THEN
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
          RAISE;
      END;
     ELSE
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
     END IF;
  END LOOP;
END;
$$;
DROP EVENT TRIGGER IF EXISTS ensure_rls;
CREATE EVENT TRIGGER ensure_rls
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
EXECUTE FUNCTION rls_auto_enable();

注意，这只适用于触发器安装后创建的表。已有的表仍然需要手动启用 RLS。

🌐 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 - 几乎在模式内所有对象的 DDL 命令之前发生
  • ddl_command_end - 几乎所有模式内的对象在执行 DDL 命令后都会发生
  • sql_drop - 在任何对数据库对象进行 DROP 的 DDL 命令之前发生 ddl_command_end（注意，修改表可能会导致它被删除）
  • table_rewrite - 在使用 ALTER TABLE 命令重写表之前发生

在每个事件触发器中，都有一些辅助函数可以查看正在被修改的对象或正在运行的命令。例如，我们的示例调用 pg_event_trigger_dropped_objects() 来查看被删除的对象。想要更全面地了解这些函数，请阅读官方事件触发器定义文档

🌐 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

你可以使用 alter event trigger 命令来禁用一个事件触发器：

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

ALTER EVENT TRIGGER dont_drop_trigger DISABLE;

触发事件 #

🌐 Dropping an event trigger

你可以使用 drop event trigger 命令删除触发器：

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

DROP EVENT TRIGGER dont_drop_trigger;

资源 #

🌐 Resources