RLS Templates

Production-ready Row Level Security policy templates for Supabase applications, with focus on AI application patterns (multi-tenant chat, RAG systems, user-specific embeddings).

Instructions

1. Applying RLS Policies

Apply policies to tables:

# Apply user isolation policies
bash scripts/apply-rls-policies.sh user-isolation conversations messages

# Apply multi-tenant policies
bash scripts/apply-rls-policies.sh multi-tenant organizations org_members documents

# Apply AI-specific policies
bash scripts/apply-rls-policies.sh ai-chat conversations messages message_embeddings

Generate custom policy:

# Generate policy from template
bash scripts/generate-policy.sh user-isolation my_table user_id

# Generate with custom column
bash scripts/generate-policy.sh multi-tenant projects organization_id

2. Testing RLS Enforcement

Test policies work correctly:

# Test all policies on a table
bash scripts/test-rls-policies.sh conversations

# Test specific user context
bash scripts/test-rls-policies.sh messages --user-id "user-uuid-here"

# Test multi-tenant isolation
bash scripts/test-rls-policies.sh documents --org-id "org-uuid-here"

3. Auditing Security

Audit tables for missing RLS:

# Audit all tables in public schema
bash scripts/audit-rls.sh

# Audit specific tables
bash scripts/audit-rls.sh conversations messages embeddings

# Generate audit report
bash scripts/audit-rls.sh --report audit-report.md

4. Policy Pattern Selection

Choose the right pattern:

• user-isolation.sql: User owns row directly (user_id column)

  • Use for: User profiles, settings, personal documents
  • Pattern: auth.uid() = user_id

• multi-tenant.sql: Organization/team-based isolation

  • Use for: SaaS apps, team workspaces, shared resources
  • Pattern: Check organization membership via join

• role-based-access.sql: Different permissions per role

  • Use for: Admin panels, hierarchical access, permission levels
  • Pattern: Check role from auth.jwt() claims

• ai-chat-policies.sql: Chat/conversation data

  • Use for: AI chat apps, message history, conversation threads
  • Pattern: User owns conversation + participants table

• embeddings-policies.sql: Vector/embedding data

  • Use for: RAG systems, semantic search, vector databases
  • Pattern: User owns source document that owns embeddings

Examples

Example 1: Secure Chat Application

-- Apply chat policies to tables
\i templates/ai-chat-policies.sql

-- Tables: conversations, messages, participants
-- Result: Users only see conversations they participate in

Example 2: Multi-Tenant RAG System

-- Apply organization isolation
\i templates/multi-tenant.sql

-- Apply embedding security
\i templates/embeddings-policies.sql

-- Tables: organizations, documents, document_embeddings
-- Result: Each org only sees their own documents and embeddings

Example 3: Role-Based Admin Panel

-- Apply role-based policies
\i templates/role-based-access.sql

-- Roles: admin (full access), editor (read/write), viewer (read-only)
-- Result: Different permissions based on user role

Requirements

Prerequisites

• Supabase project with database access
• PostgreSQL client (psql) installed
• Environment variables set:
  • SUPABASE_DB_URL: PostgreSQL connection string
  • SUPABASE_ANON_KEY: For testing anon access
  • SUPABASE_SERVICE_KEY: For admin operations

Security Checklist

• RLS enabled on all tables in public schema
• Policies test both authenticated and anonymous access
• Indexes created on columns used in policies (user_id, org_id, etc.)
• Service key never exposed to client applications
• Policies use (SELECT auth.uid()) for performance
• WITH CHECK clause included on INSERT/UPDATE policies
• Testing validates isolation between users/tenants

Performance Optimization

• Create indexes: CREATE INDEX idx_table_user_id ON table(user_id);
• Wrap auth functions: (SELECT auth.uid()) instead of auth.uid()
• Always filter queries: .eq('user_id', userId) in client code
• Use security definer functions for complex authorization logic
• Specify roles in policies: TO authenticated to skip anon checks

---

Best Practices:

1. Enable RLS before adding any data
2. Test with multiple user contexts
3. Use audit script regularly in CI/CD
4. Document policy decisions in migration files
5. Monitor query performance after adding policies