Back to Details

supabase-rls-policy-generator

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Supabase RLS Policy Generator

Supabase RLS 策略生成器

To generate comprehensive Row-Level Security policies for Supabase databases, follow these steps systematically.
要为Supabase数据库生成全面的行级安全（RLS）策略，请按照以下步骤系统操作。

Step 1: Analyze Current Schema

步骤1：分析当前架构

Before generating policies:
  1. Ask user for the database schema file path or table names
  2. Read the schema to understand table structures, foreign keys, and relationships
  3. Identify tables that need RLS protection
  4. Determine the security model: multi-tenant, role-based, or hybrid
生成策略前：
  1. 向用户索要数据库架构文件路径或表名
  2. 读取架构以了解表结构、外键和关系
  3. 确定需要RLS保护的表
  4. 确定安全模型：多租户、基于角色或混合模式

Step 2: Identify Security Requirements

步骤2：明确安全需求

Determine access patterns by asking:
  • Is this a multi-tenant application? (tenant_id isolation)
  • What roles exist in the system? (admin, user, viewer, etc.)
  • Are there public vs private resources?
  • Do users need to share resources across accounts?
  • Are there hierarchical permissions? (organization > team > user)
Consult 
references/rls-patterns.md
for common security patterns.
通过以下问题确定访问模式：
  • 这是一个多租户应用吗？（tenant_id隔离）
  • 系统中存在哪些角色？（管理员、普通用户、查看者等）
  • 是否区分公共资源和私有资源？
  • 用户是否需要跨账户共享资源？
  • 是否存在层级权限？（组织 > 团队 > 用户）
请查阅
references/rls-patterns.md
获取常见安全模式。

Step 3: Generate RLS Policies

步骤3：生成RLS策略

For each table requiring protection, generate policies following this structure:
对于每个需要保护的表，按照以下结构生成策略：

Enable RLS

启用RLS

sql
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
sql
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Policy Types to Generate

需生成的策略类型

SELECT Policies - Control read access:
  • User can view their own records
  • User can view records in their tenant
  • Role-based viewing (admins see all)
  • Public records accessible to all authenticated users
INSERT Policies - Control creation:
  • User can create records with their own user_id
  • User can create records in their tenant
  • Role-based creation restrictions
UPDATE Policies - Control modifications:
  • User can update their own records
  • Admins can update all records
  • Tenant-scoped updates
DELETE Policies - Control deletion:
  • User can delete their own records
  • Admin-only deletion
  • Tenant-scoped deletion
SELECT策略 - 控制读取权限：
  • 用户可查看自己的记录
  • 用户可查看其租户内的记录
  • 基于角色的查看（管理员可查看所有记录）
  • 已认证用户可访问的公共记录
INSERT策略 - 控制创建权限：
  • 用户可创建带有自己user_id的记录
  • 用户可在其租户内创建记录
  • 基于角色的创建限制
UPDATE策略 - 控制修改权限：
  • 用户可修改自己的记录
  • 管理员可修改所有记录
  • 租户范围内的修改
DELETE策略 - 控制删除权限：
  • 用户可删除自己的记录
  • 仅管理员可删除
  • 租户范围内的删除

Policy Templates

策略模板

Use templates from 
assets/policy-templates.sql
:
Basic User Ownership:
sql
CREATE POLICY "Users can view own records"
  ON table_name FOR SELECT
  USING (auth.uid() = user_id);
Multi-Tenant Isolation:
sql
CREATE POLICY "Tenant isolation"
  ON table_name FOR ALL
  USING (
    tenant_id IN (
      SELECT tenant_id FROM user_tenants
      WHERE user_id = auth.uid()
    )
  );
Role-Based Access:
sql
CREATE POLICY "Admins have full access"
  ON table_name FOR ALL
  USING (
    auth.jwt() ->> 'role' = 'admin'
  );
JWT Claims:
sql
CREATE POLICY "Organization access"
  ON table_name FOR SELECT
  USING (
    organization_id = (auth.jwt() -> 'app_metadata' ->> 'organization_id')::uuid
  );
使用
assets/policy-templates.sql
中的模板：
基础用户所有权：
sql
CREATE POLICY "Users can view own records"
  ON table_name FOR SELECT
  USING (auth.uid() = user_id);
多租户隔离：
sql
CREATE POLICY "Tenant isolation"
  ON table_name FOR ALL
  USING (
    tenant_id IN (
      SELECT tenant_id FROM user_tenants
      WHERE user_id = auth.uid()
    )
  );
基于角色的访问：
sql
CREATE POLICY "Admins have full access"
  ON table_name FOR ALL
  USING (
    auth.jwt() ->> 'role' = 'admin'
  );
JWT声明：
sql
CREATE POLICY "Organization access"
  ON table_name FOR SELECT
  USING (
    organization_id = (auth.jwt() -> 'app_metadata' ->> 'organization_id')::uuid
  );

Step 4: Generate Helper Functions

步骤4：生成辅助函数

Create PostgreSQL functions to support complex policies:
sql
-- Function to check user role
CREATE OR REPLACE FUNCTION auth.user_has_role(required_role TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (auth.jwt() ->> 'role') = required_role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check tenant membership
CREATE OR REPLACE FUNCTION auth.user_in_tenant(target_tenant_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_tenants
    WHERE user_id = auth.uid()
    AND tenant_id = target_tenant_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
创建PostgreSQL函数以支持复杂策略：
sql
-- Function to check user role
CREATE OR REPLACE FUNCTION auth.user_has_role(required_role TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (auth.jwt() ->> 'role') = required_role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check tenant membership
CREATE OR REPLACE FUNCTION auth.user_in_tenant(target_tenant_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_tenants
    WHERE user_id = auth.uid()
    AND tenant_id = target_tenant_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Step 5: Generate Testing Queries

步骤5：生成测试查询

Create test queries to verify policies work correctly:
sql
-- Test as authenticated user
SET request.jwt.claim.sub = 'user-uuid';
SELECT * FROM table_name; -- Should see only accessible records

-- Test as admin
SET request.jwt.claim.role = 'admin';
SELECT * FROM table_name; -- Should see all records

-- Test as different tenant
SET request.jwt.claim.sub = 'other-user-uuid';
SELECT * FROM table_name; -- Should see different tenant's records
创建测试查询以验证策略是否正常工作：
sql
-- Test as authenticated user
SET request.jwt.claim.sub = 'user-uuid';
SELECT * FROM table_name; -- Should see only accessible records

-- Test as admin
SET request.jwt.claim.role = 'admin';
SELECT * FROM table_name; -- Should see all records

-- Test as different tenant
SET request.jwt.claim.sub = 'other-user-uuid';
SELECT * FROM table_name; -- Should see different tenant's records

Step 6: Create Migration File

步骤6：创建迁移文件

Generate a migration file with proper structure:
sql
-- Migration: Add RLS policies
-- Created: [timestamp]

-- Enable RLS on tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if any
DROP POLICY IF EXISTS "policy_name" ON table_name;

-- Create new policies
[Generated policies here]

-- Create helper functions
[Generated functions here]

-- Grant necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO authenticated;
GRANT SELECT ON table_name TO anon; -- If public read needed
生成结构规范的迁移文件：
sql
-- Migration: Add RLS policies
-- Created: [timestamp]

-- Enable RLS on tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE items ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if any
DROP POLICY IF EXISTS "policy_name" ON table_name;

-- Create new policies
[Generated policies here]

-- Create helper functions
[Generated functions here]

-- Grant necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO authenticated;
GRANT SELECT ON table_name TO anon; -- If public read needed

Step 7: Document Generated Policies

步骤7：为生成的策略创建文档

Create documentation explaining:
  • What each policy does
  • Which users/roles have what access
  • Any special cases or exceptions
  • How to test the policies
  • Common troubleshooting tips
Use template from 
assets/policy-documentation-template.md
.
创建文档说明以下内容：
  • 每个策略的作用
  • 哪些用户/角色拥有何种访问权限
  • 任何特殊情况或例外
  • 如何测试策略
  • 常见故障排除技巧
使用
assets/policy-documentation-template.md
中的模板。

Implementation Guidelines

实施指南

Security Best Practices

安全最佳实践

  • Always enable RLS on tables with user data
  • Use auth.uid() for user-owned records
  • Use JWT claims for role-based access
  • Prefer SECURITY DEFINER functions for complex logic
  • Test policies with different user roles
  • Use USING clause for read access, WITH CHECK for write validation
  • 始终为包含用户数据的表启用RLS
  • 对用户拥有的记录使用auth.uid()
  • 对基于角色的访问使用JWT声明
  • 对于复杂逻辑，优先使用SECURITY DEFINER函数
  • 使用不同用户角色测试策略
  • 对读取权限使用USING子句，对写入验证使用WITH CHECK

Performance Considerations

性能注意事项

  • Add indexes on columns used in policies (user_id, tenant_id, role)
  • Keep policy logic simple for better performance
  • Use helper functions for reusable complex logic
  • Avoid subqueries in policies when possible
  • 为策略中使用的列添加索引（user_id、tenant_id、role）
  • 保持策略逻辑简洁以提升性能
  • 对可复用的复杂逻辑使用辅助函数
  • 尽可能避免在策略中使用子查询

Common Patterns

常见模式

Consult 
references/rls-patterns.md
for detailed examples of:
  • Multi-tenant isolation
  • Role-based access control (RBAC)
  • Attribute-based access control (ABAC)
  • Hierarchical permissions
  • Public/private resource splitting
  • Shared resource access
查阅
references/rls-patterns.md
获取以下场景的详细示例：
  • 多租户隔离
  • 基于角色的访问控制（RBAC）
  • 基于属性的访问控制（ABAC）
  • 层级权限
  • 公共/私有资源划分
  • 共享资源访问

Output Format

输出格式

Generate files in the following structure:
migrations/
  [timestamp]_add_rls_policies.sql
docs/
  rls-policies.md (documentation)
tests/
  rls_tests.sql (test queries)
按以下结构生成文件：
migrations/
  [timestamp]_add_rls_policies.sql
docs/
  rls-policies.md (documentation)
tests/
  rls_tests.sql (test queries)

Verification Checklist

验证清单

Before completing:
  • RLS enabled on all sensitive tables
  • Policies cover all operations (SELECT, INSERT, UPDATE, DELETE)
  • Policies tested with different user roles
  • Indexes added for policy columns
  • Helper functions created for complex logic
  • Documentation generated
  • Test queries provided
  • No policies accidentally grant excessive access
完成前请检查：
  • 所有敏感表均已启用RLS
  • 策略覆盖所有操作（SELECT、INSERT、UPDATE、DELETE）
  • 已使用不同用户角色测试策略
  • 已为策略列添加索引
  • 已创建用于复杂逻辑的辅助函数
  • 已生成文档
  • 已提供测试查询
  • 无策略意外授予过度权限

Consulting References

参考资料

Throughout generation:
  • Consult 
    references/rls-patterns.md
    for security patterns
  • Consult 
    references/supabase-auth.md
    for auth.uid() and JWT structure
  • Use templates from 
    assets/policy-templates.sql
生成过程中可参考：
  • 查阅
    references/rls-patterns.md
    获取安全模式
  • 查阅
    references/supabase-auth.md
    了解auth.uid()和JWT结构
  • 使用
    assets/policy-templates.sql
    中的模板

Completion

完成

When finished:
  1. Display the generated migration file
  2. Summarize the policies created
  3. Provide testing instructions
  4. Offer to generate additional policies or modify existing ones
完成后：
  1. 展示生成的迁移文件
  2. 总结已创建的策略
  3. 提供测试说明
  4. 可提供生成额外策略或修改现有策略的服务