PostgreSQL：如何为非特权用户配置pg_cron调度任务

针对pg_cron权限过大的问题，以下是几个抽象层面的安全解决方案，既满足用户自定义调度的需求，又避免直接开放全权限：

1. 封装操作函数（推荐） #

通过自定义封装函数，只暴露用户需要的调度操作（如创建、删除自己的任务），内部以高权限执行pg_cron的核心函数，但严格限制输入范围和操作边界。

示例实现： #

-- 创建自定义函数，仅允许用户创建归属自己的任务
CREATE OR REPLACE FUNCTION public.create_my_cron_job(
    schedule text,
    command text,
    job_name text DEFAULT NULL
) RETURNS integer AS $$
DECLARE
    job_id integer;
BEGIN
    -- 可选：限制命令只能操作指定模式（比如public），避免越权
    IF command !~* '^public\.' THEN
        RAISE EXCEPTION '仅允许执行public模式下的SQL命令';
    END IF;

    -- 调用pg_cron的调度函数，自动记录任务归属当前用户
    SELECT cron.schedule(job_name, schedule, command) INTO job_id;

    -- 可选：在自定义表中留存任务与用户的关联记录，方便审计和管理
    INSERT INTO public.user_cron_jobs(user_id, job_id, job_name, schedule, command)
    VALUES (current_user, job_id, job_name, schedule, command);

    RETURN job_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = cron, pg_temp;

-- 确保函数所有者为postgres（拥有pg_cron权限的用户）
ALTER FUNCTION public.create_my_cron_job(text, text, text) OWNER TO postgres;

-- 仅给需要的用户授予函数执行权限
GRANT EXECUTE ON FUNCTION public.create_my_cron_job(text, text, text) TO user1;

同理，可以封装delete_my_cron_job、update_my_cron_job等函数，严格控制用户只能操作自己的任务。

2. 行级安全（RLS）控制 #

给cron.job表开启行级安全，通过策略限制用户仅能访问和修改自己创建的任务，同时最小化授予表和函数的权限。

示例实现： #

-- 给cron.job表添加owner字段，标记任务创建者
ALTER TABLE cron.job ADD COLUMN owner text DEFAULT current_user;

-- 开启行级安全
ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;

-- 创建策略：用户只能操作自己归属的任务
CREATE POLICY user_cron_job_policy ON cron.job
    FOR ALL TO public
    USING (owner = current_user);

-- 授予最小必要权限
GRANT USAGE ON SCHEMA cron TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON cron.job TO user1;
-- 仅授予执行核心调度函数的权限
GRANT EXECUTE ON FUNCTION cron.schedule(text, text) TO user1;
GRANT EXECUTE ON FUNCTION cron.schedule(text, text, text) TO user1;
GRANT EXECUTE ON FUNCTION cron.unschedule(integer) TO user1;
GRANT EXECUTE ON FUNCTION cron.unschedule(text) TO user1;

注意：如果pg_cron是以postgres用户执行任务，需要确保任务中的命令不会超出用户权限，可配合SECURITY INVOKER的函数创建任务。

3. 代理角色+权限限制 #

创建一个专门的代理角色（如cron_operator），给该角色分配pg_cron的必要权限，然后通过包装函数让用户间接使用该角色，避免直接授予高权限。

示例实现： #

-- 创建代理角色
CREATE ROLE cron_operator NOLOGIN;

-- 给代理角色分配pg_cron权限
GRANT USAGE ON SCHEMA cron TO cron_operator;
GRANT SELECT, INSERT, UPDATE, DELETE ON cron.job TO cron_operator;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cron TO cron_operator;

-- 创建包装函数，切换到代理角色执行操作
CREATE OR REPLACE FUNCTION public.create_cron_job_via_proxy(
    schedule text,
    command text,
    job_name text DEFAULT NULL
) RETURNS integer AS $$
DECLARE
    job_id integer;
BEGIN
    -- 限制命令范围
    IF command !~* '^public\.' THEN
        RAISE EXCEPTION '仅允许执行public模式下的命令';
    END IF;

    -- 切换到代理角色执行调度
    SET ROLE cron_operator;
    SELECT cron.schedule(job_name, schedule, command) INTO job_id;
    RESET ROLE;

    -- 记录任务归属
    INSERT INTO public.user_cron_jobs(user_id, job_id, job_name, schedule, command)
    VALUES (current_user, job_id, job_name, schedule, command);

    RETURN job_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = cron, pg_temp;

ALTER FUNCTION public.create_cron_job_via_proxy(text, text, text) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.create_cron_job_via_proxy(text, text, text) TO user1;

这种方式避免用户直接持有高权限角色，通过函数控制操作边界。

4. 自定义视图+触发器 #

创建仅展示用户自身任务的视图，通过触发器处理任务的创建、修改请求，内部调用pg_cron函数实现权限隔离。

示例实现： #

-- 创建自定义视图，仅显示当前用户的任务
CREATE VIEW public.my_cron_jobs AS
SELECT jobid, schedule, command, nodename, nodeport, database, username, active, owner
FROM cron.job
WHERE owner = current_user;

-- 创建触发器函数，处理视图的INSERT请求
CREATE OR REPLACE FUNCTION public.my_cron_jobs_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- 限制命令范围
    IF NEW.command !~* '^public\.' THEN
        RAISE EXCEPTION '仅允许执行public模式下的命令';
    END IF;

    -- 调用pg_cron创建任务
    INSERT INTO cron.job(schedule, command, owner)
    VALUES (NEW.schedule, NEW.command, current_user)
    RETURNING jobid INTO NEW.jobid;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = cron, pg_temp;

-- 给视图添加INSERT触发器
CREATE TRIGGER my_cron_jobs_insert
INSTEAD OF INSERT ON public.my_cron_jobs
FOR EACH ROW EXECUTE FUNCTION public.my_cron_jobs_insert_trigger();

-- 同理可以添加UPDATE、DELETE触发器

-- 给用户授予视图的操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON public.my_cron_jobs TO user1;

用户通过视图操作任务，无法直接访问cron.job表，权限完全由触发器控制。

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