如何在共享PostgreSQL连接池中实现安全的行级权限控制？

针对你的场景，以下几种方案可以在复用连接池的前提下，安全实现行级控制，同时彻底防止注入篡改用户ID：

方案一：封装用户ID获取为安全函数，限制参数修改权限 #

这种方案基于你提到的自定义设置myapp.user_id，但通过PostgreSQL的权限机制阻断攻击者篡改参数的可能：

1. 创建安全的用户ID获取函数

CREATE OR REPLACE FUNCTION get_current_user_id()
RETURNS INTEGER AS $$
BEGIN
  RETURN current_setting('myapp.user_id')::INTEGER;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public;

-- 仅允许应用的数据库角色设置该参数
REVOKE ALL ON FUNCTION get_current_user_id() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION get_current_user_id() TO app_db_role; -- 替换为你的应用连接角色

-- 禁止普通用户修改myapp.user_id参数
ALTER DATABASE your_db_name SET myapp.user_id = '0'; -- 默认值
REVOKE ALL ON DATABASE your_db_name FROM PUBLIC;
GRANT CONNECT ON DATABASE your_db_name TO app_db_role;

2. 修改行级策略使用该函数

CREATE POLICY only_see_own_expenses ON expenses
USING (expenses.user_id = get_current_user_id());

3. 应用层的连接使用方式
   从连接池取出连接后，在事务内执行SET LOCAL myapp.user_id = :user_id，然后执行生成的查询，最后提交/回滚事务。因为SET LOCAL仅在当前事务生效，连接放回池时自动重置该参数（事务结束后失效）。

安全性说明：

• 攻击者即使注入SET LOCAL myapp.user_id = ...，也会因为没有权限修改该参数而失败（PostgreSQL会抛出权限错误）。
• SECURITY DEFINER函数确保参数读取不受当前执行角色限制，仅应用角色有权设置。

方案二：使用SET ROLE结合细粒度权限控制 #

如果你倾向于用PostgreSQL角色对应应用用户，可以通过权限配置避免注入篡改角色：

1. 创建应用主角色与用户角色

-- 创建应用连接用的主角色
CREATE ROLE app_main_role WITH LOGIN PASSWORD 'your_password';

-- 为每个应用用户创建对应角色（可批量生成）
CREATE ROLE user_123 NOLOGIN;
CREATE ROLE user_456 NOLOGIN;

-- 授予主角色切换到用户角色的权限
GRANT user_123 TO app_main_role;
GRANT user_456 TO app_main_role;

-- 授予用户角色对expenses表的访问权限
GRANT SELECT, INSERT, UPDATE, DELETE ON expenses TO user_123, user_456;

2. 修改行级策略使用current_user

-- 假设用户角色名格式为user_<user_id>，提取ID
CREATE OR REPLACE FUNCTION get_user_id_from_role()
RETURNS INTEGER AS $$
BEGIN
  RETURN substring(current_user from 'user_(\d+)')::INTEGER;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE POLICY only_see_own_expenses ON expenses
USING (expenses.user_id = get_user_id_from_role());

3. 应用层连接复用逻辑

• 从连接池取出连接后，执行SET ROLE user_<user_id>
• 执行生成的客户端查询
• 连接放回池之前，执行RESET ROLE恢复为主角色

安全性说明：

• 用户角色（如user_123）没有SET ROLE权限，即使攻击者注入SET ROLE user_456也会失败。
• 连接池复用连接时，重置角色后不会影响下一个请求的用户身份。

方案三：严格过滤客户端生成的SQL语句 #

无论采用哪种用户ID传递方式，必须在应用层严格限制客户端生成的SQL范围：

• 仅允许生成SELECT/INSERT/UPDATE/DELETE这类DML语句，禁止任何SET/BEGIN/COMMIT/CREATE/ALTER等语句。
• 在SQL生成逻辑中，对客户端输入的所有标识符（如表名、字段名）进行白名单校验，或者通过PostgreSQL的quote_ident()函数安全转义，避免注入。

关键性能保障：
以上所有方案都复用了pg.Pool的连接，无需为每个请求新建连接，仅在连接取出/放回时执行少量权限控制语句，性能开销可以忽略。

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