Supabase行级安全策略 #

Row Level Security (行级安全)
├── PostgreSQL内置功能
├── 行级别的访问控制
├── 基于策略过滤数据
├── 每个用户只能看到授权的数据
└── 安全性在数据库层实现

查询请求
    │
    ▼
PostgreSQL
    │
    ├── 检查RLS是否启用
    │
    ├── 应用匹配的策略
    │
    ├── 过滤数据
    │
    ▼
返回结果

-- 启用RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- 禁用RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

-- 查看RLS状态
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public';

-- 强制表所有者也受RLS限制
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

-- 取消强制
ALTER TABLE posts NO FORCE ROW LEVEL SECURITY;

-- 允许所有人读取
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (true);

-- 允许认证用户插入
CREATE POLICY "Authenticated users can insert"
ON posts FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- 用户只能更新自己的数据
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id);

-- 用户只能删除自己的数据
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = author_id);

-- SELECT: 读取策略
CREATE POLICY "..." ON table FOR SELECT USING (condition);

-- INSERT: 插入策略
CREATE POLICY "..." ON table FOR INSERT WITH CHECK (condition);

-- UPDATE: 更新策略
CREATE POLICY "..." ON table FOR UPDATE 
USING (condition)        -- 哪些行可以更新
WITH CHECK (condition);  -- 更新后的值必须满足

-- DELETE: 删除策略
CREATE POLICY "..." ON table FOR DELETE USING (condition);

-- ALL: 所有操作
CREATE POLICY "..." ON table FOR ALL USING (condition);

-- 用户可以管理自己的文章
CREATE POLICY "Users can manage own posts"
ON posts FOR ALL
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

-- 所有人可读
CREATE POLICY "Public read"
ON products FOR SELECT
USING (true);

-- 认证用户可读
CREATE POLICY "Authenticated read"
ON posts FOR SELECT
USING (auth.role() = 'authenticated');

-- 匿名用户可读
CREATE POLICY "Anonymous read"
ON posts FOR SELECT
USING (auth.role() = 'anon');

-- 用户只能访问自己的数据
CREATE POLICY "User isolation"
ON profiles FOR ALL
USING (auth.uid() = id);

-- 管理员完全访问
CREATE POLICY "Admin full access"
ON posts FOR ALL
USING (
    EXISTS (
        SELECT 1 FROM user_roles
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- 普通用户只能访问自己的
CREATE POLICY "User own data"
ON posts FOR ALL
USING (
    auth.uid() = author_id
    OR EXISTS (
        SELECT 1 FROM user_roles
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- 已发布的内容公开可见
CREATE POLICY "Published content visible"
ON posts FOR SELECT
USING (
    published = true
    OR auth.uid() = author_id
);

-- 用户只能访问自己组织的数据
CREATE POLICY "Organization isolation"
ON documents FOR ALL
USING (
    organization_id IN (
        SELECT organization_id 
        FROM organization_members
        WHERE user_id = auth.uid()
    )
);

-- 获取当前用户ID
auth.uid()

-- 获取当前用户角色
auth.role()

-- 获取JWT声明
auth.jwt()

-- 获取JWT中的特定字段
auth.jwt() ->> 'email'

-- 检查用户是否是管理员
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM user_roles
        WHERE user_id = auth.uid() AND role = 'admin'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 使用
CREATE POLICY "Admin access"
ON posts FOR ALL
USING (is_admin());

-- 获取用户组织ID
CREATE OR REPLACE FUNCTION user_organization()
RETURNS UUID AS $$
BEGIN
    RETURN (
        SELECT organization_id 
        FROM organization_members
        WHERE user_id = auth.uid()
        LIMIT 1
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 使用
CREATE POLICY "Organization members"
ON documents FOR ALL
USING (organization_id = user_organization());

-- 查看表的所有策略
SELECT 
    policyname,
    cmd,
    qual,
    with_check
FROM pg_policies
WHERE tablename = 'posts';

-- 删除单个策略
DROP POLICY "Policy name" ON posts;

-- 删除所有策略
DROP POLICY "Public read" ON posts;
DROP POLICY "User isolation" ON posts;

-- 先删除再创建
DROP POLICY "Old policy" ON posts;
CREATE POLICY "New policy" ON posts FOR SELECT USING (true);

-- 以特定用户身份测试
SET ROLE authenticated;
SET request.jwt.claims = '{"sub": "user-uuid"}';

SELECT * FROM posts;

-- 重置
RESET ROLE;

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM posts;

RLS设计原则
├── 最小权限原则
├── 默认拒绝
├── 策略命名清晰
├── 避免过于复杂的策略
└── 定期审计策略

-- 为策略条件创建索引
CREATE INDEX idx_posts_author ON posts(author_id);

-- 为角色检查创建索引
CREATE INDEX idx_user_roles_user ON user_roles(user_id);

-- 错误：忘记启用RLS
-- 表创建后默认没有启用RLS

-- 错误：策略过于宽松
CREATE POLICY "Too permissive"
ON posts FOR ALL
USING (true);  -- 所有操作都允许

-- 错误：策略冲突
-- 多个策略是OR关系，可能导致意外访问

-- 启用RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- 文章策略
-- 已发布文章所有人可见
CREATE POLICY "Published posts visible"
ON posts FOR SELECT
USING (published = true OR auth.uid() = author_id);

-- 认证用户可创建文章
CREATE POLICY "Users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = author_id);

-- 作者可更新自己的文章
CREATE POLICY "Authors can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

-- 作者可删除自己的文章
CREATE POLICY "Authors can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = author_id);

-- 评论策略
-- 文章评论所有人可见
CREATE POLICY "Comments visible"
ON comments FOR SELECT
USING (true);

-- 认证用户可评论
CREATE POLICY "Users can comment"
ON comments FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- 用户可删除自己的评论
CREATE POLICY "Users can delete own comments"
ON comments FOR DELETE
USING (auth.uid() = user_id);