如何让PostgreSQL只读角色执行含增删改操作的存储过程？

问题核心在于PostgreSQL存储过程的默认权限模式：默认是SECURITY INVOKER（调用者权限），执行时会沿用调用用户的权限，你的myuser因没有表的INSERT/UPDATE/DELETE直接权限，所以执行对应操作会报错。要实现用户仅通过存储过程操作表、不直接持有表权限，需改用**定义者权限（SECURITY DEFINER）**的存储过程。

具体操作步骤： #

1. 创建/修改存储过程为DEFINER模式

   • 新建存储过程时，在定义中加入SECURITY DEFINER，同时指定search_path规避路径安全风险：

     CREATE OR REPLACE PROCEDURE myschemaname.your_procedure_name(参数列表)
     SECURITY DEFINER
     SET search_path = myschemaname, pg_temp
     AS $$
     BEGIN
       -- 写入包含INSERT/UPDATE/DELETE的业务逻辑
       UPDATE tablename SET col1 = 'new_val' WHERE id = 1;
       DELETE FROM tablename WHERE id = 2;
       INSERT INTO tablename (col1) VALUES ('new_data');
     END;
     $$ LANGUAGE plpgsql;
     

   • 若已有存储过程，执行修改语句切换权限模式：

     ALTER PROCEDURE myschemaname.your_procedure_name(参数列表)
     SECURITY DEFINER
     SET search_path = myschemaname, pg_temp;
     

2. 确认存储过程创建者权限
   存储过程的创建者必须拥有对应表的INSERT/UPDATE/DELETE权限，否则即使是DEFINER模式，执行时也会因权限不足报错。

3. 安全注意事项

   • 必须设置search_path，防止恶意用户利用路径漏洞执行非预期数据库对象。
   • 仅给必要用户（如myuser）授予存储过程的EXECUTE权限，避免权限扩散。
   • 避免在DEFINER存储过程中执行未验证的用户输入，防范SQL注入风险。

验证 #

使用myuser执行目标存储过程，此时存储过程会以创建者的权限完成增删改操作，而myuser无需持有表的直接操作权限。

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