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:

• Enable RLS before adding any data

• Test with multiple user contexts

• Use audit script regularly in CI/CD

• Document policy decisions in migration files

• Monitor query performance after adding policies