Supabase RLS Policy Generator

To generate comprehensive Row-Level Security policies for Supabase databases, follow these steps systematically.

Step 1: Analyze Current Schema

Before generating policies:

• Ask user for the database schema file path or table names

• Read the schema to understand table structures, foreign keys, and relationships

• Identify tables that need RLS protection

• Determine the security model: multi-tenant, role-based, or hybrid

Step 2: Identify Security Requirements

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.

Step 3: Generate RLS Policies

For each table requiring protection, generate policies following this structure:

Enable RLS

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

Policy Templates

Use templates from assets/policy-templates.sql :

Basic User Ownership:

CREATE POLICY "Users can view own records" ON table_name FOR SELECT USING (auth.uid() = user_id);

Multi-Tenant Isolation:

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:

CREATE POLICY "Admins have full access" ON table_name FOR ALL USING ( auth.jwt() ->> 'role' = 'admin' );

JWT Claims:

CREATE POLICY "Organization access" ON table_name FOR SELECT USING ( organization_id = (auth.jwt() -> 'app_metadata' ->> 'organization_id')::uuid );

Step 4: Generate Helper Functions

Create PostgreSQL functions to support complex policies:

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

Create test queries to verify policies work correctly:

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

Generate a migration file with proper structure:

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

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 .

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

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

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

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)

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

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

Completion

When finished:

• Display the generated migration file

• Summarize the policies created

• Provide testing instructions

• Offer to generate additional policies or modify existing ones