PostgreSQL中如何仅授予用户函数/存储过程权限而非底层表权限？

先说结论：完全可以做到，而且你提到的SECURITY DEFINER其实完全支持事务——大概率是你之前对它的某些特性有误解，这才是PostgreSQL里实现这种权限封装的标准方案。下面给你具体的实现方法，以及替代方案：

一、用SECURITY DEFINER（最推荐的标准方案） #

这是PostgreSQL官方推荐的“通过函数封装底层数据权限”的方式，步骤很清晰：

1. 让函数/存储过程的所有者拥有底层表的必要权限（比如SELECT、INSERT、UPDATE等）
2. 创建函数时加上SECURITY DEFINER参数，同时指定search_path防止注入风险
3. 只给普通用户授予执行该函数的权限，底层表的权限一概不授

举个实际例子：

-- 假设你有个有权限操作users表的用户db_owner
CREATE OR REPLACE FUNCTION get_user_details(user_id INT)
RETURNS TABLE(user_id INT, user_name TEXT, email TEXT)
SECURITY DEFINER
SET search_path = public, pg_temp  -- 限定搜索路径，避免恶意对象注入
AS $$
BEGIN
  RETURN QUERY 
    SELECT id, name, email FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 给普通应用用户app_user授予函数执行权限
GRANT EXECUTE ON FUNCTION get_user_details(INT) TO app_user;
-- 确保其他用户不能随便执行
REVOKE EXECUTE ON FUNCTION get_user_details(INT) FROM PUBLIC;

注意事项： #

• 必须设置search_path：如果不指定，函数可能会优先执行攻击者创建的同名对象，导致注入风险
• 函数所有者的权限要最小化：别给超级用户权限，只给它需要操作的表的必要权限就行
• 关于事务的误解：SECURITY DEFINER函数完全可以在事务中执行，也可以在函数内部开启/控制事务（比如用BEGIN/COMMIT，或者调用其他事务相关的函数），你之前遇到的问题可能是特定场景下的误用，而非特性限制

二、替代方案：视图+行级安全（RLS）+函数封装 #

如果确实不想用SECURITY DEFINER，可以用视图做数据暴露层，配合行级安全限制访问范围，再用函数封装操作逻辑：

1. 创建只暴露必要字段的视图
2. 给底层表启用行级安全（RLS），限制用户只能访问自己有权限的数据
3. 创建函数来操作视图，给用户授予函数执行权限和视图的必要权限

示例：

-- 创建视图，只暴露允许用户看到的字段
CREATE VIEW user_public_view AS 
  SELECT id, name FROM users;

-- 给users表启用行级安全
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- 定义安全规则：用户只能看自己的数据（这里假设用户ID和数据库用户名关联，按需调整）
CREATE POLICY user_self_access ON users 
  FOR SELECT USING (id = current_user::TEXT::INT);

-- 创建更新用户名称的函数
CREATE OR REPLACE FUNCTION update_user_name(user_id INT, new_name TEXT)
RETURNS BOOLEAN
AS $$
BEGIN
  UPDATE user_public_view SET name = new_name WHERE id = user_id;
  RETURN FOUND; -- 返回是否更新成功
END;
$$ LANGUAGE plpgsql;

-- 给app_user授予函数执行权限和视图更新权限
GRANT EXECUTE ON FUNCTION update_user_name(INT, TEXT) TO app_user;
GRANT UPDATE ON user_public_view TO app_user;

这种方式的好处是不需要依赖SECURITY DEFINER，但缺点是复杂逻辑的封装会比较麻烦，而且视图的权限控制不如函数灵活。

三、角色分层管理（更细粒度的权限控制） #

如果需要更严谨的权限隔离，可以用角色分层的方式：

1. 创建专门的角色来拥有底层表的权限
2. 创建函数所有者角色，继承这个表权限角色
3. 普通用户只拥有执行函数的权限，不接触任何表权限

示例：

-- 1. 创建拥有表权限的角色
CREATE ROLE table_ops_role;
GRANT SELECT, INSERT, UPDATE ON users TO table_ops_role;

-- 2. 创建函数所有者角色，继承表权限角色
CREATE ROLE func_owner_role INHERIT table_ops_role;

-- 3. 切换到func_owner_role创建SECURITY DEFINER函数
SET ROLE func_owner_role;
CREATE OR REPLACE FUNCTION add_user(user_name TEXT, user_email TEXT)
RETURNS INT
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  new_user_id INT;
BEGIN
  INSERT INTO users(name, email) VALUES(user_name, user_email) 
  RETURNING id INTO new_user_id;
  RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;
RESET ROLE;

-- 4. 给普通用户app_user授予函数执行权限
CREATE ROLE app_user;
GRANT EXECUTE ON FUNCTION add_user(TEXT, TEXT) TO app_user;

这种方式可以把表权限、函数权限完全隔离，避免权限过度授予。

验证权限是否生效 #

设置完后，一定要验证普通用户不能直接访问底层表：

-- 切换到普通用户
SET ROLE app_user;

-- 尝试直接查询底层表，应该报错（权限不足）
SELECT * FROM users;

-- 执行函数，应该正常返回结果
SELECT get_user_details(1);

如果上面的查询表报错，执行函数正常，说明权限配置成功了。

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