求PostgreSQL RLS策略：仅允许员工查看posts表内容

问题背景 #

我正在使用Supabase和PostgreSQL，现有以下数据表：

• user_roles表：包含字段id, created_at, user_id, role_id
• roles表：包含字段id, created_at, role_name，对应关系为：
  • id 1 → admin
  • id 2 → employer
  • id 3 → employee
• profiles表：包含字段id及其他省略字段
• posts表：包含字段id, created_at, title, description及其他省略字段

我希望为posts表配置RLS策略，仅允许employee角色的用户查看其内容，但编写的如下代码无法生效：

alter policy "Enable select for employee role only"
ON posts
FOR SELECT
to public
using (
    EXISTS ( 
        SELECT 1
        FROM user_roles
            JOIN roles ON user_roles.role_id = roles.id
        WHERE user_roles.user_id = auth.uid() 
        AND roles.role_name = 'employee'
    )
);

可能的原因及修复方案 #

1. 未开启RLS #

首先确认posts表是否启用了行级安全（RLS），未开启的话所有策略都不会生效，执行以下命令开启：

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

2. 策略逻辑与需求不匹配 #

如果你的实际需求是employee只能查看自己发布的帖子，当前策略逻辑只验证了用户角色，没有关联帖子所属用户，需要调整：

-- 先确保posts表存在关联用户的user_id字段（无则添加）
ALTER TABLE posts ADD COLUMN user_id UUID REFERENCES auth.users(id);

-- 修改策略，增加帖子归属验证
ALTER POLICY "Enable select for employee role only"
ON posts
FOR SELECT
TO public
USING (
    EXISTS ( 
        SELECT 1
        FROM user_roles
            JOIN roles ON user_roles.role_id = roles.id
        WHERE user_roles.user_id = auth.uid() 
        AND roles.role_name = 'employee'
    )
    AND posts.user_id = auth.uid()
);

3. 用户角色关联数据错误 #

检查user_roles表中是否存在当前登录用户与employee角色的关联记录，确保user_id对应auth.uid()，且role_id指向roles表中role_name为employee的id（即3）。

4. 权限不足导致子查询失败 #

策略中的子查询需要读取user_roles和roles表，确保public角色拥有这两个表的SELECT权限：

GRANT SELECT ON user_roles TO public;
GRANT SELECT ON roles TO public;

5. 策略未正确创建 #

如果之前不存在同名策略，建议用CREATE POLICY替代ALTER POLICY，避免因策略不存在导致修改失败：

CREATE POLICY "Enable select for employee role only"
ON posts
FOR SELECT
TO public
USING (
    EXISTS ( 
        SELECT 1
        FROM user_roles
            JOIN roles ON user_roles.role_id = roles.id
        WHERE user_roles.user_id = auth.uid() 
        AND roles.role_name = 'employee'
    )
);

验证方法 #

可以在Supabase控制台的SQL编辑器中模拟用户上下文验证策略：

-- 切换到已认证用户角色
SET ROLE authenticated;
-- 设置当前用户UUID，替换为你的测试用户ID
SET auth.uid() = '你的用户UUID';

-- 查询posts表，验证是否返回符合条件的数据
SELECT * FROM posts;

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