Supabase RLS Policy Generator

Step 1: Analyze Current Schema

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

Step 2: Identify Security Requirements

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

references/rls-patterns.md

Step 3: Generate RLS Policies

Enable RLS

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Policy Types to Generate

• 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

• User can create records with their own user_id
• User can create records in their tenant
• Role-based creation restrictions

• User can update their own records
• Admins can update all records
• Tenant-scoped updates

• User can delete their own records
• Admin-only deletion
• Tenant-scoped deletion

Policy Templates

assets/policy-templates.sql

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

CREATE POLICY "Tenant isolation"
  ON table_name FOR ALL
  USING (
    tenant_id IN (
      SELECT tenant_id FROM user_tenants
      WHERE user_id = auth.uid()
    )
  );

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

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

Step 4: Generate Helper Functions

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

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

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

• What each policy does
• Which users/roles have what access
• Any special cases or exceptions
• How to test the policies
• Common troubleshooting tips

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

references/rls-patterns.md

• Multi-tenant isolation
• Role-based access control (RBAC)
• Attribute-based access control (ABAC)
• Hierarchical permissions
• Public/private resource splitting
• Shared resource access

Output Format

migrations/
  [timestamp]_add_rls_policies.sql
docs/
  rls-policies.md (documentation)
tests/
  rls_tests.sql (test queries)

Verification Checklist

• 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

• 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

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