PostgreSQL触发器函数权限设置及安全调用问题咨询

1. 更优、更安全的替代方案 #

SECURITY DEFINER是可行方案，但要遵循安全最佳实践规避风险，同时也有其他选项：

• 最小权限的SECURITY DEFINER配置：
  不要用超级用户作为函数所有者，创建一个仅拥有目标模式CREATE TABLE权限的专用角色，将触发器函数的所有者设为这个角色。同时在函数开头设置安全的search_path，防止恶意对象注入：

  CREATE OR REPLACE FUNCTION create_table_from_trigger()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  SECURITY DEFINER
  AS $$
  BEGIN
      -- 限制搜索路径，避免使用不可信的模式
      SET search_path = target_schema, pg_catalog;
      -- 你的创建表逻辑，示例：
      EXECUTE format('CREATE TABLE %I (id INT)', NEW.table_name);
      RETURN NEW;
  END;
  $$;
  -- 撤销public的执行权限，仅授予需要插入表A的用户/角色
  REVOKE EXECUTE ON FUNCTION create_table_from_trigger() FROM public;
  GRANT EXECUTE ON FUNCTION create_table_from_trigger() TO authorized_user;
  

• 直接授予用户特定权限：如果插入表A的用户确实需要在目标模式创建表的权限，直接授予CREATE权限而非依赖SECURITY DEFINER：

  GRANT CREATE ON SCHEMA target_schema TO inserting_user;
  

  这种方式更透明，但仅适用于用户本身需要该权限的场景。

2. 触发器函数能否在触发器之外被调用？ #

可以。触发器函数本质是普通的PostgreSQL函数，只要调用者拥有函数的EXECUTE权限，就能直接调用——哪怕调用时没有触发器上下文。

比如，攻击者可以尝试构造调用（如果函数依赖NEW字段，直接调用会报错，但如果函数逻辑有漏洞，比如动态SQL未正确转义，攻击者可能通过伪造参数执行恶意操作）：

-- 尝试直接调用触发器函数（若函数未做上下文检查）
SELECT create_table_from_trigger();

如果函数里有动态SQL且未用format()或quote_ident()处理用户输入，攻击者可能借此注入SQL，大量创建表耗尽资源，甚至破坏数据库。

3. 能否仅允许触发器运行该函数？ #

可以，两种有效方式结合使用：

• 检查触发器调用深度：在函数开头加入pg_trigger_depth()检查，非触发器调用直接抛出错误：

  CREATE OR REPLACE FUNCTION create_table_from_trigger()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  SECURITY DEFINER
  AS $$
  BEGIN
      -- 仅允许触发器调用（触发器深度大于0）
      IF pg_trigger_depth() = 0 THEN
          RAISE EXCEPTION '此函数仅能通过触发器调用';
      END IF;
      SET search_path = target_schema, pg_catalog;
      EXECUTE format('CREATE TABLE %I (id INT)', NEW.table_name);
      RETURN NEW;
  END;
  $$;
  

• 严格控制函数执行权限：撤销public的EXECUTE权限，仅授予需要插入表A的用户/角色，避免无关用户调用函数。

内容的提问来源于stack exchange，提问作者Raziza O