RDS Postgres13 pg_cron作业提示rds_super角色无调度权限排查

问题现象 #

在AWS RDS上部署Postgres 13.4、pg_cron 1.3与pg_partman时，出现pg_cron作业执行失败问题，报错信息如下：

ERROR: The protected role of rds_super doesn't provide permission to schedule a job.

使用的自定义角色rds_super已运行多年，已被授予RDS默认无登录权限的rds_superuser角色权限。pg_cron扩展默认安装在postgres库下，自动生成cron schema。

最简复现场景 #

构造如下测试SQL：

DROP TABLE IF EXISTS cron.foo;

CREATE TABLE IF NOT EXISTS cron.foo (
    bar text
);

GRANT INSERT ON TABLE cron.foo TO rds_super;

INSERT INTO cron.foo VALUES (now()::text);

select * from cron.foo;

-- 每分钟执行一次
SELECT cron.schedule('postgres.populate.foo','*/1 * * * *',
$$INSERT INTO cron.foo VALUES (now()::text) $$);

直接以rds_super用户连接执行INSERT语句正常，但定时任务运行后，cron.job_run_details记录显示任务执行失败，返回上述权限错误。

已执行的排查操作 #

尝试重新安装pg_cron、显式配置权限均未解决，随后做了如下权限检查：

• 查询cron schema下表的权限，确认rds_super拥有所有表的全量权限：

select grantor,
       grantee,
       table_schema,
       table_name,
       string_agg (privilege_type, ',' order by privilege_type) as grants

  from information_schema.role_table_grants
where table_catalog = 'postgres'
  and table_schema  = 'cron'
  and grantee       = 'rds_super'

group by 1,2,3,4

order by 1,2,3,4;

返回结果显示rds_super拥有cron.foo、cron.job、cron.job_run_details等所有表的DELETE、INSERT、SELECT等全量权限。

• 检查schema权限，确认rds_super拥有cron schema的CREATE和USAGE权限：

select pg_catalog.has_schema_privilege('rds_super', 'cron', 'CREATE') AS create,
       pg_catalog.has_schema_privilege('rds_super', 'cron', 'USAGE')  AS usage;

返回结果两项均为true。

• 检查函数执行权限，确认rds_super拥有cron schema下schedule、unschedule等所有函数的EXECUTE权限。

最终解决方案 #

查询pg_stat_activity发现pg_cron scheduler后台进程已运行超过1天出现卡住问题，重启RDS实例后，切换为pg_cron scheduler进程运行身份dbadmin用户重新配置所有作业，调整该用户的search_path和对应权限后问题解决。

内容的提问来源于stack exchange，提问作者Morris de Oryx