基于表角色的Supabase(Postgres)行级安全策略编写咨询

你的思路方向是对的，只需要调整几个细节就能实现需求，以下是具体的策略写法和注意事项：

前提准备 #

首先务必开启program表的行级安全：

ALTER TABLE program ENABLE ROW LEVEL SECURITY;

策略1：允许同组用户执行SELECT操作 #

CREATE POLICY "同组用户可查看program" ON program
FOR SELECT USING (
  "group" IN (
    SELECT "group" FROM member WHERE member.id = auth.uid()::text
  )
);

说明 #

• 使用auth.uid()::text匹配member表的text类型id（如果member.id是uuid类型，直接用auth.uid()即可）
• group是PostgreSQL保留关键字，必须用双引号包裹避免语法错误
• 子查询会获取当前认证用户所在的组，只返回该组下的program行

策略2：允许同组管理员执行INSERT/UPDATE/DELETE操作 #

INSERT策略 #

CREATE POLICY "同组管理员可插入program" ON program
FOR INSERT WITH CHECK (
  "group" IN (
    SELECT "group" FROM member WHERE member.id = auth.uid()::text AND member.admin = 1
  )
);

UPDATE/DELETE策略 #

CREATE POLICY "同组管理员可修改删除program" ON program
FOR UPDATE USING (
  "group" IN (
    SELECT "group" FROM member WHERE member.id = auth.uid()::text AND member.admin = 1
  )
)
WITH CHECK (
  "group" IN (
    SELECT "group" FROM member WHERE member.id = auth.uid()::text AND member.admin = 1
  )
);

说明 #

• INSERT用WITH CHECK确保插入的program属于用户管理的组
• UPDATE/DELETE需要同时使用USING（验证现有行权限）和WITH CHECK（确保修改后的行仍符合权限规则），防止管理员将program转移到非管理组

额外建议 #

• 避免使用保留关键字作为字段名：group是PostgreSQL保留关键字，后续设计表结构时尽量替换为group_id或group_name这类非关键字名称
• 性能优化：给member表创建复合索引CREATE INDEX idx_member_id_admin_group ON member(id, admin, "group");，提升子查询的执行效率
• 测试验证：可以通过SET ROLE authenticated;切换到认证用户角色，执行查询或写操作验证策略是否生效

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