如何在PostgreSQL中无需创建用户即可配置行级访问

不需要为每个Cognito用户创建PostgreSQL角色，核心思路是利用PostgreSQL的行级安全策略（RLS），结合会话变量传递用户的Cognito ID，让数据库自行处理权限控制，无需后端自定义过滤逻辑。

具体步骤 #

1. 启用目标表的行级安全（RLS） #

首先给需要控制权限的表开启RLS，确保只有通过策略允许的操作才能执行：

ALTER TABLE your_target_table ENABLE ROW LEVEL SECURITY;

2. 创建通用的PostgreSQL连接角色 #

创建一个统一的PG角色（比如api_service_role），用于API连接数据库，只需要给这个角色授予目标表的基础操作权限，实际访问范围由RLS策略控制：

-- 创建角色（按需设置密码或用IAM认证）
CREATE ROLE api_service_role WITH LOGIN PASSWORD 'your_secure_password';

-- 授予基础操作权限
GRANT INSERT, SELECT, UPDATE, DELETE ON your_target_table TO api_service_role;

3. 在API连接时注入Cognito用户ID到会话变量 #

API处理请求时，先通过Cognito验证用户身份，解析出用户的sub字段（即Cognito用户ID）。在建立PG连接后，立即执行以下SQL，将用户ID存入会话变量：

-- 替换为实际解析出的Cognito用户ID
SET app.current_cognito_id = 'user-sub-from-cognito';

这个会话变量是当前连接独有的，多用户并发请求不会互相干扰。

4. 创建行级安全策略 #

针对插入、查询、更新、删除操作分别创建策略，基于会话变量控制用户只能访问自己的数据：

插入策略：强制关联用户ID

确保插入数据时，user_id字段（你用来存储Cognito ID的字段）自动匹配会话变量中的用户ID，防止用户伪造他人数据：

CREATE POLICY insert_own_data ON your_target_table
  FOR INSERT WITH CHECK (user_id = current_setting('app.current_cognito_id')::uuid);

如果希望自动填充user_id而不需要API传入，可以结合触发器：

CREATE OR REPLACE FUNCTION set_user_id()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_id = current_setting('app.current_cognito_id')::uuid;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_user_id
BEFORE INSERT ON your_target_table
FOR EACH ROW EXECUTE FUNCTION set_user_id();

查询策略：仅允许读取自己的行

CREATE POLICY select_own_data ON your_target_table
  FOR SELECT USING (user_id = current_setting('app.current_cognito_id')::uuid);

更新/删除策略：仅允许操作自己的行

CREATE POLICY update_own_data ON your_target_table
  FOR UPDATE USING (user_id = current_setting('app.current_cognito_id')::uuid);

CREATE POLICY delete_own_data ON your_target_table
  FOR DELETE USING (user_id = current_setting('app.current_cognito_id')::uuid);

5. 权限加固（可选） #

为了防止会话变量被恶意篡改，可以限制只有指定角色能设置这个变量：

ALTER ROLE api_service_role IN DATABASE your_db SET app.current_cognito_id = '';
REVOKE ALL ON FUNCTION current_setting(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION current_setting(text) TO api_service_role;

核心优势 #

• 无需为每个Cognito用户创建PG角色，减少运维负担
• 所有权限逻辑由PostgreSQL原生RLS处理，后端无需编写自定义过滤代码，避免人为漏洞
• 会话变量传递用户身份，并发安全且实现简单

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