如何用无LOGIN权限角色通过pg_cron执行多语句pg_partman维护？

问题原因 #

你遇到的报错是因为pg_cron默认会将整个任务SQL包裹在一个单独事务中执行，而partman.run_maintenance_proc()内部包含COMMIT语句（从报错上下文的line 43 at COMMIT可确认）。直接在pg_cron的任务字符串中用分号拼接SET ROLE和存储过程调用，会导致事务嵌套，触发invalid transaction termination错误。

解决方案 #

不要直接在pg_cron的任务中写多语句，而是创建一个PL/pgSQL包装函数，将切换角色和维护逻辑封装进去，再由pg_cron调用这个函数：

1. 创建包装函数

   CREATE OR REPLACE FUNCTION run_partman_maintenance()
   RETURNS void AS $$
   BEGIN
       -- 设置安全的search_path，避免路径攻击
       SET search_path = public;
       -- 切换到无LOGIN权限的自动化角色
       SET ROLE automation_role;
       -- 调用pg_partman的维护存储过程
       CALL partman.run_maintenance_proc();
   END;
   $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
   

2. 授予函数执行权限
   给pg_cron使用的执行角色（这里是postgres）授予函数执行权限：

   GRANT EXECUTE ON FUNCTION run_partman_maintenance() TO postgres;
   

3. 用pg_cron调度函数

   SELECT cron.schedule('Partition Maintenance', '0 1 1 * *', $$SELECT run_partman_maintenance();$$);
   

关键说明 #

• SECURITY DEFINER：让函数以创建者的权限执行（需由拥有LOGIN权限的角色创建，比如postgres），从而能够切换到无LOGIN的automation_role。
• VOLATILE：标记函数包含事务控制操作，确保PostgreSQL正确处理函数内的事务边界。
• 安全注意：务必限制run_partman_maintenance()的执行权限，仅授予必要角色，避免权限滥用。

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