以定义者身份执行的存储过程中事务控制语句的限制问题

一、为什么SECURITY DEFINER存储过程不能在事务内执行COMMIT/ROLLBACK？ #

• 事务模型设计限制：Postgres的事务是会话级的，所有存储过程（无论SECURITY DEFINER还是SECURITY INVOKER）都运行在当前会话的顶级事务上下文中。存储过程本身无法创建或终止顶级事务，只能作为事务的一部分执行操作——这是Postgres核心事务模型的固有特性，和身份切换无关。
• 安全与事务完整性考量：SECURITY DEFINER存储过程会切换到定义者的高权限身份，若允许它随意提交/回滚，会破坏调用者的事务原子性。比如调用者启动了包含多步操作的事务，若SECURITY DEFINER过程中途提交，会导致部分操作永久生效，剩余操作失败时无法回滚已提交部分，引发数据不一致。同时，高权限的自主事务可能被滥用，绕过调用者的事务控制逻辑执行未授权的持久化操作。

二、如何在SECURITY DEFINER存储过程内实现自主事务控制？ #

Postgres原生不支持存储过程内的自主事务，但可通过以下方式模拟：

1. 使用dblink创建独立事务 #

通过dblink连接到本地Postgres实例，在连接内执行SECURITY DEFINER的操作，该操作会运行在独立事务中，不受当前会话事务影响。示例代码：

-- 先安装dblink扩展
CREATE EXTENSION IF NOT EXISTS dblink;

-- 创建支持自主事务的SECURITY DEFINER存储过程
CREATE OR REPLACE PROCEDURE sec_definer_autonomous_proc()
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- 连接到当前数据库
  PERFORM dblink_connect('dbname=' || current_database());
  
  -- 在dblink内执行操作并提交，这是独立的事务上下文
  PERFORM dblink_exec(
    'INSERT INTO sensitive_table (data) VALUES (''autonomous data''); COMMIT;'
  );
  
  PERFORM dblink_disconnect();
END;
$$;

2. 使用异步任务（如pg_cron） #

如果操作不需要立即执行，可通过pg_cron调度独立任务，任务会在新会话中运行，拥有独立事务上下文。示例：

-- 安装pg_cron扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 创建调度异步任务的SECURITY DEFINER存储过程
CREATE OR REPLACE PROCEDURE sec_definer_scheduled_proc()
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  task_id text := 'autonomous-task-' || gen_random_uuid();
BEGIN
  -- 调度立即执行的任务，执行自主事务操作
  PERFORM cron.schedule(
    task_id,
    '* * * * *', -- 匹配当前分钟，立即执行
    'INSERT INTO sensitive_table (data) VALUES (''scheduled autonomous data'');'
  );
  
  -- 任务执行后自动删除调度
  PERFORM cron.unschedule(task_id);
END;
$$;

三、为什么权限调整无法解决问题？ #

你尝试的schema权限、用户组、ACLs、RLS等设置都属于权限访问控制层面的配置，而事务提交/回滚的限制是Postgres事务模型的设计特性，和权限无关。无论调整多少权限，只要存储过程运行在当前会话的事务中，就无法突破顶级事务的边界。

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