row-level-security

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Row Level Security (RLS)

行级安全（RLS）

Database-level data isolation for multi-tenant applications.

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

When to Use This Skill

何时使用该方案

Building multi-tenant SaaS applications
Ensuring users can only access their own data
Implementing organization-based data isolation
Adding defense-in-depth security layer

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

Why RLS?

为什么选择RLS？

Application-level filtering can be bypassed. RLS enforces access at the database level:

❌ 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在数据库层面强制实施访问控制：

❌ 应用层过滤：SELECT * FROM posts WHERE user_id = ?
   （代码漏洞可能导致数据泄露）

✅ RLS策略：用户仅能查看user_id匹配的行
   （由数据库强制执行，无法绕过）

Basic Setup

基础配置

Enable RLS on Tables

在表上启用RLS

sql

-- 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;

sql

-- 启用RLS（必须的第一步）
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 强制对表所有者应用RLS（非常重要！）
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

User-Based Policies

基于用户的策略

sql

-- 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());

sql

-- 用户仅能查看自身的帖子
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

-- 用户可插入属于自己的帖子
CREATE POLICY "Users can create own posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- 用户可更新自身的帖子
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- 用户可删除自身的帖子
CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (user_id = auth.uid());

Organization-Based Multi-Tenancy

基于组织的多租户模式

Schema Setup

架构配置

sql

-- 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;

sql

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

-- 组织成员表
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)
);

-- 项目表（属于组织）
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()
);

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

Organization Policies

组织策略

sql

-- 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')
    )
  );

sql

-- 辅助函数：获取用户所属的组织
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;

-- 用户可查看自己所属的组织
CREATE POLICY "Members can view organization"
  ON organizations FOR SELECT
  USING (id IN (SELECT get_user_organizations()));

-- 用户可查看所属组织下的项目
CREATE POLICY "Members can view org projects"
  ON projects FOR SELECT
  USING (organization_id IN (SELECT get_user_organizations()));

-- 仅管理员可创建项目
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')
    )
  );

Role-Based Policies

基于角色的策略

sql

-- 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));

sql

-- 定义角色类型
CREATE TYPE user_role AS ENUM ('viewer', 'editor', 'admin', 'owner');

-- 角色层级辅助函数
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;

-- 查看者可读取数据
CREATE POLICY "Viewers can read"
  ON projects FOR SELECT
  USING (has_role('viewer', organization_id));

-- 编辑者可更新数据
CREATE POLICY "Editors can update"
  ON projects FOR UPDATE
  USING (has_role('editor', organization_id))
  WITH CHECK (has_role('editor', organization_id));

-- 管理员可删除数据
CREATE POLICY "Admins can delete"
  ON projects FOR DELETE
  USING (has_role('admin', organization_id));

Supabase-Specific Setup

Supabase专属配置

Auth Helper Functions

认证辅助函数

sql

-- 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;

sql

-- 获取当前用户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;

-- 获取当前用户邮箱
CREATE OR REPLACE FUNCTION auth.email()
RETURNS TEXT AS $$
  SELECT current_setting('request.jwt.claims', true)::json->>'email'
$$ LANGUAGE sql STABLE;

Service Role Bypass

服务角色绕过

sql

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

sql

-- 允许服务角色绕过RLS（用于管理员操作）
CREATE POLICY "Service role bypass"
  ON projects FOR ALL
  USING (auth.role() = 'service_role');

TypeScript Integration

TypeScript集成

Supabase Client Setup

Supabase客户端配置

typescript

// 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!
);

typescript

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

// 客户端（遵循RLS规则）
export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// 服务端（使用服务角色，绕过RLS）
export const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

Querying with RLS

结合RLS进行查询

typescript

// 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;
}

typescript

// 自动应用RLS策略过滤
async function getUserProjects() {
  const { data, error } = await supabase
    .from('projects')
    .select('*');
  
  // 仅返回用户有权访问的项目
  return data;
}

// 管理员操作（绕过RLS）
async function getAllProjects() {
  const { data, error } = await supabaseAdmin
    .from('projects')
    .select('*');
  
  // 返回所有项目
  return data;
}

Testing RLS Policies

测试RLS策略

sql

-- 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;

sql

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

-- 执行查询（结果会被过滤）
SELECT * FROM projects;

-- 重置
RESET request.jwt.claims;

Automated Tests

自动化测试

typescript

// __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 })
    );
  });
});

typescript

// __tests__/rls.test.ts
describe('RLS Policies', () => {
  it('user can only see own projects', async () => {
    // 创建两个测试用户
    const user1 = await createTestUser();
    const user2 = await createTestUser();
    
    // 用户1创建一个项目
    const project = await createProject(user1.id, 'Secret Project');
    
    // 用户2尝试访问
    const client = createClientAsUser(user2);
    const { data } = await client.from('projects').select('*');
    
    // 用户2不应看到用户1的项目
    expect(data).not.toContainEqual(
      expect.objectContaining({ id: project.id })
    );
  });
});

Performance Considerations

性能注意事项

Index for RLS Columns

为RLS相关列创建索引

sql

-- 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);

sql

-- 务必为RLS策略中使用的列创建索引
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);

Avoid Expensive Functions

避免使用高开销函数

sql

-- ❌ 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()));

sql

-- ❌ 不佳：每行检查都执行子查询
CREATE POLICY "slow_policy"
  ON posts FOR SELECT
  USING (user_id IN (SELECT user_id FROM complex_view));

-- ✅ 推荐：使用带缓存的SECURITY DEFINER函数
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()));

Best Practices

最佳实践

Enable RLS on ALL tables with user data: Don't forget any table
Use FORCE ROW LEVEL SECURITY: Applies to table owners too
Create helper functions: Reuse logic across policies
Index RLS columns: Critical for performance
Test policies thoroughly: Verify isolation works

为所有包含用户数据的表启用RLS：不要遗漏任何表
使用FORCE ROW LEVEL SECURITY：对表所有者同样生效
创建辅助函数：在多个策略中复用逻辑
为RLS相关列创建索引：对性能至关重要
全面测试策略：验证隔离机制有效

Common Mistakes

常见错误

Forgetting to enable RLS (table is wide open)
Not using FORCE (table owner bypasses policies)
Complex subqueries in policies (performance killer)
Not indexing policy columns
Trusting application-level filtering alone

忘记启用RLS（表完全开放）
未使用FORCE（表所有者可绕过策略）
策略中使用复杂子查询（性能杀手）
未为策略相关列创建索引
仅依赖应用层过滤

row-level-security

Original

Translation

Row Level Security (RLS)

行级安全（RLS）

When to Use This Skill

何时使用该方案

Why RLS?

为什么选择RLS？

Basic Setup

基础配置

Enable RLS on Tables

在表上启用RLS

User-Based Policies

基于用户的策略

Organization-Based Multi-Tenancy

基于组织的多租户模式

Schema Setup

架构配置

Organization Policies

组织策略

Role-Based Policies

基于角色的策略

Supabase-Specific Setup

Supabase专属配置

Auth Helper Functions

认证辅助函数

Service Role Bypass

服务角色绕过

TypeScript Integration

TypeScript集成

Supabase Client Setup

Supabase客户端配置

Querying with RLS

结合RLS进行查询

Testing RLS Policies

测试RLS策略

Automated Tests

自动化测试

Performance Considerations

性能注意事项

Index for RLS Columns

为RLS相关列创建索引

Avoid Expensive Functions

避免使用高开销函数

Best Practices

最佳实践

Common Mistakes

常见错误

Security Checklist

安全检查清单