PostgreSQL 14/PostgREST 基于策略实现行列组合级权限控制方案

方案基于PostgreSQL 12+版本提供的列级行级安全策略实现，不需要创建视图、不需要为每个业务用户创建数据库角色，完全匹配描述的权限模型。

第一步：配置基础角色权限 #

先做粗粒度的角色授权，后续通过RLS做细粒度行+列的管控：

-- 给普通用户角色授予USERS表基础读写权限，敏感字段访问由RLS控制
GRANT SELECT, UPDATE ON users TO normal_user_role;
-- 给管理员角色授予USERS表全操作权限
GRANT ALL ON users TO admin_role;
-- 给匿名访问角色授予公开字段的基础读权限
GRANT SELECT (user_id, username, avatar, public_bio /* 替换为实际业务的所有公开字段列表 */) ON users TO anon;
-- 开启USERS表行级安全
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

第二步：配置RLS策略 #

RLS策略分四类，覆盖所有要求的权限场景：

1. 公开字段全局可读策略

CREATE POLICY "public_read_open_fields" ON users
AS PERMISSIVE
FOR SELECT (user_id, username, avatar, public_bio /* 和上面授权给anon的公开字段保持一致 */)
TO PUBLIC
USING (true);

该策略生效后，所有人（包括未登录用户）都可以读取所有用户行的公开字段，不会被拦截。

2. 普通用户访问自身敏感字段策略

CREATE POLICY "user_access_own_sensitive_fields" ON users
AS PERMISSIVE
FOR SELECT (phone, id_card /* 替换为实际业务的所有敏感字段列表 */)
TO normal_user_role
USING (
  -- 匹配当前JWT里的user_id，和现有行级安全用的判断逻辑完全一致
  user_id = (current_setting('request.jwt.claims', true)::json->>'user_id')::uuid -- 字段类型根据实际表结构调整，比如int类型就转int
);

该策略仅对普通用户角色生效，只有访问行属于用户本人时，才会放行敏感字段的读取请求。普通用户访问其他用户行的敏感字段时，因为没有匹配的放行策略，会被直接拦截。

3. 普通用户更新自身数据策略

CREATE POLICY "user_update_own_data" ON users
AS PERMISSIVE
FOR UPDATE
TO normal_user_role
USING (
  user_id = (current_setting('request.jwt.claims', true)::json->>'user_id')::uuid
)
WITH CHECK (
  -- 禁止用户修改自身user_id，避免越权
  user_id = (current_setting('request.jwt.claims', true)::json->>'user_id')::uuid
);

该策略保证普通用户只能修改自己的行数据，不管是公开字段还是敏感字段，非本人行的更新请求会被直接拦截。

4. 管理员全权限策略

CREATE POLICY "admin_full_access" ON users
AS PERMISSIVE
FOR ALL
TO admin_role
USING (true)
WITH CHECK (true);

管理员角色匹配该策略后，对所有行的所有字段拥有完整读写删权限，不受其他策略限制。

场景验证 #

配置完成后所有需求都会被满足：

• 所有人可读取全表公开字段，无拦截
• 普通用户只能读取自身的敏感字段，访问他人敏感字段被拦截
• 普通用户只能更新自己的所有字段，无法修改他人数据
• 管理员可读写删所有行的所有字段
• 完全不需要创建视图，所有逻辑在数据库权限层完成，适配PostgREST的JWT身份识别逻辑

补充：如果使用PostgreSQL版本低于12（不支持列级RLS语法），可以通过新增RESTRICTIVE类型策略、结合列权限判断实现同等效果，但性能和可维护性不如上述方案，建议升级PG版本后使用列级RLS。

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