TL;DR
These prompts help you write advanced RLS policies for complex authorization scenarios. They cover role-based access, hierarchical permissions, shared resources, and performance-optimized policies.
Role-Based Access Control
Create RLS policies with role-based access control.
Roles in my system:
- admin: full access to everything
- editor: can read and write content
- viewer: read-only access
Tables: posts, comments, settings
Create policies where:
- Admins can do anything
- Editors can CRUD posts and comments they create
- Viewers can only SELECT
Store user roles in a profiles table or JWT claims. Show both approaches and explain trade-offs.
Hierarchical Access
Create RLS for hierarchical data access.
Structure:
- Organizations have multiple teams
- Teams have multiple projects
- Projects have multiple tasks
A user should access:
- Tasks in projects they belong to
- Projects in teams they belong to
- Teams in organizations they belong to
Create efficient policies that don't require multiple subqueries. Consider using helper functions for membership checks.
Shared Resources
Create RLS for resources that can be shared with other users.
Table: documents Columns: id, owner_id, content
Table: document_shares Columns: document_id, shared_with_user_id, permission (view/edit)
Policies needed:
- Owner has full access
- Users with 'view' share can SELECT
- Users with 'edit' share can SELECT and UPDATE
- Only owner can DELETE
- Only owner can manage shares
Make sure performance is good with proper indexes.
Watch out for recursive policies: If Policy A references Table B, and Table B has a policy referencing Table A, you can create infinite loops. Use security definer functions to break these cycles.
Performance Optimization
Optimize my RLS policies for better performance.
Current issues:
- Policies are slow with large tables
- Multiple subqueries in each policy
- Checking membership in several tables
Help me:
- Create a security definer function for membership checks
- Use materialized views or caching for role lookups
- Add proper indexes for policy conditions
- Simplify complex policy logic
Show before/after with EXPLAIN ANALYZE to verify improvement.
Pro tip: Store commonly checked permissions in the user's JWT claims using Supabase custom claims. This avoids database lookups for every policy check.
Should I use one policy per operation or combine them?
Separate policies are clearer and easier to maintain. PostgreSQL combines them with OR logic for the same operation, so having multiple policies doesn't hurt performance.
How do I test RLS policies?
Use Supabase's SQL editor with SET ROLE to test as different users. You can also write automated tests that create test users and verify access.