Row-Level Security (RLS)

PostgreSQL 的“行级权限”总开关，开了以后每行都要通过策略检查才能被看见/改写

-- 启用
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;

-- 关闭
ALTER TABLE conversations DISABLE ROW LEVEL SECURITY;

Policies

为某张表、某种操作（SELECT、INSERT、UPDATE、DELETE）定义布尔规则，决定哪些行可见或可修改。

-- 语法
CREATE POLICY policy_name
  ON table_name
  FOR { SELECT | INSERT | UPDATE | DELETE | ALL }
  [ TO role [, ...] ]
  [ USING ( boolean_expression ) ]
  [ WITH CHECK ( boolean_expression ) ];

-- 示例
CREATE POLICY "members-can-access"
  ON conversations
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM conversation_members
      WHERE conversation_id = conversations.id
        AND user_id = auth.uid()
    )
  );

基本结构

CREATE POLICY policy_name
  ON table_name
  FOR { ALL | SELECT | INSERT | UPDATE | DELETE }
  [ TO { role_name | PUBLIC | authenticated | anon | ... } [, ...] ]
  [ USING ( expression ) ]
  [ WITH CHECK ( expression ) ];

参数解释

• policy_name：策略名称
• ON table_name：作用于哪个表
• FOR：指定动作（查询/插入/更新/删除）
• TO：哪些角色可以触发（默认是 PUBLIC，也就是所有人）
• USING (expression)：对于 SELECT / DELETE / UPDATE，判断“谁能看到/修改/删除这行”
• WITH CHECK (expression)：对于 INSERT / UPDATE，判断“谁能插入/更新这行”

注意

• alter table table_name enable row level security; 必须开启才有效
• 多个 policy 之间是 OR 关系，只要有一个通过就可以访问

示例

CREATE POLICY policy_name
  ON projects
  FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM project_members
    WHERE
      project_members.project_id = projects.id AND
      project_members.user_id = auth.uid()
  )
)