row-level-security

行级安全（RLS）

为多租户应用提供数据库级数据隔离。

何时使用此技能

• 构建多租户 SaaS 应用
• 确保用户只能访问自己的数据
• 实现基于组织的数据隔离
• 增加纵深防御安全层

为什么使用 RLS？

应用层筛选可能被绕过。RLS 在数据库层强制执行访问控制：

❌ Application Filter: SELECT * FROM posts WHERE user_id = ?
   (Bug in code = data leak)

✅ RLS Policy: User can ONLY see rows where user_id matches
   (Database enforces, impossible to bypass)

基本设置

在表上启用 RLS

-- Enable RLS (required first step)
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owners too (important!)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

基于用户的策略

-- Users can only see their own posts
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

-- Users can insert posts as themselves
CREATE POLICY "Users can create own posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Users can update their own posts
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- Users can delete their own posts
CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (user_id = auth.uid());

基于组织的多租户

架构设置

-- Organizations table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Organization memberships
CREATE TABLE organization_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(organization_id, user_id)
);

-- Projects belong to organizations
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

组织策略

-- Helper function: Get user's organizations
CREATE OR REPLACE FUNCTION get_user_organizations()
RETURNS SETOF UUID AS $$
  SELECT organization_id 
  FROM organization_members 
  WHERE user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Users can see organizations they belong to
CREATE POLICY "Members can view organization"
  ON organizations FOR SELECT
  USING (id IN (SELECT get_user_organizations()));

-- Users can see projects in their organizations
CREATE POLICY "Members can view org projects"
  ON projects FOR SELECT
  USING (organization_id IN (SELECT get_user_organizations()));

-- Only admins can create projects
CREATE POLICY "Admins can create projects"
  ON projects FOR INSERT
  WITH CHECK (
    organization_id IN (
      SELECT organization_id 
      FROM organization_members 
      WHERE user_id = auth.uid() 
      AND role IN ('admin', 'owner')
    )
  );

基于角色的策略

-- Define roles
CREATE TYPE user_role AS ENUM ('viewer', 'editor', 'admin', 'owner');

-- Role hierarchy helper
CREATE OR REPLACE FUNCTION has_role(
  required_role user_role,
  org_id UUID
) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE user_id = auth.uid()
    AND organization_id = org_id
    AND role::user_role >= required_role
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Viewers can read
CREATE POLICY "Viewers can read"
  ON projects FOR SELECT
  USING (has_role('viewer', organization_id));

-- Editors can update
CREATE POLICY "Editors can update"
  ON projects FOR UPDATE
  USING (has_role('editor', organization_id))
  WITH CHECK (has_role('editor', organization_id));

-- Admins can delete
CREATE POLICY "Admins can delete"
  ON projects FOR DELETE
  USING (has_role('admin', organization_id));

Supabase 专属设置

Auth 辅助函数

-- Get current user ID (Supabase)
CREATE OR REPLACE FUNCTION auth.uid()
RETURNS UUID AS $$
  SELECT COALESCE(
    current_setting('request.jwt.claims', true)::json->>'sub',
    (current_setting('request.jwt.claims', true)::json->>'user_id')
  )::UUID
$$ LANGUAGE sql STABLE;

-- Get current user's email
CREATE OR REPLACE FUNCTION auth.email()
RETURNS TEXT AS $$
  SELECT current_setting('request.jwt.claims', true)::json->>'email'
$$ LANGUAGE sql STABLE;

服务角色绕过

-- Allow service role to bypass RLS (for admin operations)
CREATE POLICY "Service role bypass"
  ON projects FOR ALL
  USING (auth.role() = 'service_role');

TypeScript 集成

Supabase 客户端设置

// lib/supabase.ts
import { createClient } from '@supabase/supabase-js';

// Client-side (respects RLS)
export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Server-side with service role (bypasses RLS)
export const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

使用 RLS 查询

// This automatically filters by RLS policies
async function getUserProjects() {
  const { data, error } = await supabase
    .from('projects')
    .select('*');
  
  // Only returns projects user has access to
  return data;
}

// Admin operation (bypasses RLS)
async function getAllProjects() {
  const { data, error } = await supabaseAdmin
    .from('projects')
    .select('*');
  
  // Returns ALL projects
  return data;
}

测试 RLS 策略

-- Test as specific user
SET request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Run query (should be filtered)
SELECT * FROM projects;

-- Reset
RESET request.jwt.claims;

自动化测试

// __tests__/rls.test.ts
describe('RLS Policies', () => {
  it('user can only see own projects', async () => {
    // Create two users
    const user1 = await createTestUser();
    const user2 = await createTestUser();
    
    // User1 creates a project
    const project = await createProject(user1.id, 'Secret Project');
    
    // User2 tries to access
    const client = createClientAsUser(user2);
    const { data } = await client.from('projects').select('*');
    
    // Should not see user1's project
    expect(data).not.toContainEqual(
      expect.objectContaining({ id: project.id })
    );
  });
});

性能注意事项

为 RLS 列创建索引

-- Always index columns used in RLS policies
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_projects_org_id ON projects(organization_id);
CREATE INDEX idx_org_members_user_org ON organization_members(user_id, organization_id);

避免使用开销大的函数

-- ❌ Bad: Subquery in every row check
CREATE POLICY "slow_policy"
  ON posts FOR SELECT
  USING (user_id IN (SELECT user_id FROM complex_view));

-- ✅ Good: Use SECURITY DEFINER function with caching
CREATE OR REPLACE FUNCTION get_accessible_user_ids()
RETURNS SETOF UUID AS $$
  SELECT user_id FROM simple_lookup WHERE condition
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY "fast_policy"
  ON posts FOR SELECT
  USING (user_id IN (SELECT get_accessible_user_ids()));

最佳实践

1. 在所有包含用户数据的表上启用 RLS：不要遗漏任何表
2. 使用 FORCE ROW LEVEL SECURITY：表所有者也会受到限制
3. 创建辅助函数：在各项策略中复用逻辑
4. 为 RLS 列创建索引：这对性能至关重要
5. 彻底测试策略：验证隔离是否有效

常见错误

• 忘记启用 RLS（表将完全开放）
• 未使用 FORCE（表所有者可绕过策略）
• 在策略中使用复杂子查询（严重影响性能）
• 未为策略列创建索引
• 仅依赖应用层筛选

安全检查清单

• 已在所有包含用户数据的表上启用 RLS
• 已设置 FORCE ROW LEVEL SECURITY
• 策略涵盖 SELECT、INSERT、UPDATE、DELETE
• 服务角色密钥仅在服务端使用
• 辅助函数使用 SECURITY DEFINER
• 已使用多个用户测试策略
• 已为所有 RLS 列创建索引