如何在PostgreSQL中限制用户仅可执行存储过程、禁止直接访问表？

要实现让指定PostgreSQL用户只能执行存储过程、无法直接读写表的需求，核心是利用PostgreSQL的细粒度权限控制，结合存储过程的定义者权限来实现。下面是一步步的具体配置方案，以及更安全的优化思路：

1. 基础权限配置步骤 #

• 创建目标用户（如果还未创建）：

  CREATE ROLE app_user WITH LOGIN PASSWORD 'your_strong_password';
  

• 撤销用户对表所在模式的默认权限：
  PostgreSQL默认会给public模式下的用户赋予一些基础权限，必须先撤销这些权限，防止用户直接访问表：

  -- 针对public模式
  REVOKE ALL ON SCHEMA public FROM app_user;
  -- 如果你的表在自定义模式（比如`internal_data`），替换成对应模式名
  REVOKE ALL ON SCHEMA internal_data FROM app_user;
  

• 赋予用户访问存储过程所在模式的权限：
  用户需要能"看到"存储过程所在的模式才能执行它，所以要赋予USAGE权限：

  -- 允许用户使用存储过程所在的模式（比如`public`或`api_procs`）
  GRANT USAGE ON SCHEMA api_procs TO app_user;
  

• 赋予用户存储过程的执行权限：
  可以针对单个存储过程授权，也可以批量授权整个模式下的所有存储过程：

  -- 单个存储过程授权
  GRANT EXECUTE ON FUNCTION api_procs.get_user_details(user_id INT) TO app_user;
  -- 整个模式下的所有存储过程授权
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api_procs TO app_user;
  -- 可选：后续新创建的存储过程自动赋予执行权限
  ALTER DEFAULT PRIVILEGES IN SCHEMA api_procs GRANT EXECUTE ON FUNCTIONS TO app_user;
  

2. 关键适配：存储过程的权限穿透 #

因为用户没有直接访问表的权限，所以如果存储过程需要读写表，必须用SECURITY DEFINER属性定义存储过程——这样存储过程会以创建者的权限执行，而非调用者（目标用户）的权限。

安全的存储过程示例： #

CREATE OR REPLACE FUNCTION api_procs.get_user_details(user_id INT)
RETURNS TABLE(user_id INT, username TEXT, email TEXT)
SECURITY DEFINER -- 核心：以创建者权限执行
SET search_path = internal_data, pg_temp -- 限定搜索路径，防止安全注入
AS $$
BEGIN
    -- 这里可以直接访问internal_data模式下的表，因为创建者有对应权限
    RETURN QUERY 
        SELECT id, username, email 
        FROM internal_data.users 
        WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 重要：撤销public对该函数的执行权，只允许指定用户访问
REVOKE EXECUTE ON FUNCTION api_procs.get_user_details(INT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION api_procs.get_user_details(INT) TO app_user;

3. 更优实现方案（最小权限+安全隔离） #

为了进一步降低风险，建议遵循最小权限原则和模式隔离思路：

• 创建专用的存储过程所有者角色：
  不要用超级用户或拥有全表权限的用户创建存储过程，而是创建一个仅拥有必要表权限的角色（比如proc_owner），用它来创建SECURITY DEFINER存储过程：

  -- 创建无登录权限的专用角色
  CREATE ROLE proc_owner WITH NOLOGIN;
  -- 赋予该角色对表的最小必要权限（比如仅SELECT、INSERT）
  GRANT SELECT, INSERT ON internal_data.users TO proc_owner;
  -- 切换到该角色创建存储过程
  SET ROLE proc_owner;
  CREATE OR REPLACE FUNCTION api_procs.create_user(username TEXT, email TEXT)
  RETURNS INT
  SECURITY DEFINER
  SET search_path = internal_data, pg_temp
  AS $$
  DECLARE
      new_user_id INT;
  BEGIN
      INSERT INTO users(username, email) VALUES(username, email) RETURNING id INTO new_user_id;
      RETURN new_user_id;
  END;
  $$ LANGUAGE plpgsql;
  RESET ROLE;
  -- 给目标用户执行权限
  GRANT EXECUTE ON FUNCTION api_procs.create_user(TEXT, TEXT) TO app_user;
  

• 模式隔离策略：
  将业务表放在专用的内部模式（比如internal_data），存储过程放在对外的API模式（比如api_procs），目标用户仅能访问api_procs模式，完全看不到内部表，从根源上避免直接访问。
• 启用审计监控：
  开启PostgreSQL的审计功能（比如使用pgAudit扩展），监控目标用户的所有执行操作，及时发现违规行为。

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