Supabase provides helper functions for RLS policies:
-- Current user's ID (null if not authenticated)auth.uid()-- Current user's role: 'anon', 'authenticated', or 'service_role'auth.role()-- Current user's JWT claimsauth.jwt()
-- Enable RLSALTER TABLE profiles ENABLE ROW LEVEL SECURITY;-- Users can read their own profileCREATE POLICY "Users can view own profile" ON profiles FOR SELECT USING (auth.uid() = id);-- Users can update their own profileCREATE POLICY "Users can update own profile" ON profiles FOR UPDATE USING (auth.uid() = id) WITH CHECK (auth.uid() = id);-- Public profiles are viewable by anyone (for referrals)CREATE POLICY "Public profiles readable" ON profiles FOR SELECT USING (true);-- Only service role can create profiles (via auth trigger)CREATE POLICY "Service role can insert profiles" ON profiles FOR INSERT TO service_role WITH CHECK (true);
Masters can CRUD all partners. Partners can read their own data.
ALTER TABLE partners ENABLE ROW LEVEL SECURITY;-- Masters have full accessCREATE POLICY "Masters can manage all partners" ON partners FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );-- Partners can read their own dataCREATE POLICY "Partners can view own data" ON partners FOR SELECT USING ( user_id = auth.uid() );-- Partners can update their own configCREATE POLICY "Partners can update own config" ON partners FOR UPDATE USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;-- Partners can CRUD their own clientsCREATE POLICY "Partners can manage own clients" ON clients FOR ALL USING ( partner_id IN ( SELECT id FROM partners WHERE user_id = auth.uid() ) );-- Masters see all clientsCREATE POLICY "Masters can view all clients" ON clients FOR SELECT USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );
Critical for the B2B2C model - supports public read for QR code access.
ALTER TABLE events ENABLE ROW LEVEL SECURITY;-- PUBLIC READ: Anyone can read active events (for QR code guests)CREATE POLICY "Public can read active events" ON events FOR SELECT USING (is_active = true);-- Partners can manage their own eventsCREATE POLICY "Partners can manage own events" ON events FOR ALL USING ( partner_id IN ( SELECT id FROM partners WHERE user_id = auth.uid() ) );-- Clients can view and update their events (via PIN)CREATE POLICY "Clients can manage assigned events" ON events FOR ALL USING ( client_id IN ( SELECT id FROM clients WHERE email = auth.email() ) );-- Masters see everythingCREATE POLICY "Masters can manage all events" ON events FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );
Critical Policy: "Public can read active events" enables Zero Friction event mode.Guests scan a QR code, load /?event=slug, and the app fetches event config without authentication.
Users see their own generations. Event guests see event-specific photos.
ALTER TABLE generations ENABLE ROW LEVEL SECURITY;-- Users can view their own generationsCREATE POLICY "Users can view own generations" ON generations FOR SELECT USING (user_id = auth.uid());-- Anyone can view generations from public eventsCREATE POLICY "Public can view event generations" ON generations FOR SELECT USING ( event_id IN ( SELECT id FROM events WHERE is_active = true ) );-- Service role can insert (from edge functions)CREATE POLICY "Service role can insert generations" ON generations FOR INSERT TO service_role WITH CHECK (true);-- Partners can view generations from their eventsCREATE POLICY "Partners can view own event generations" ON generations FOR SELECT USING ( event_id IN ( SELECT id FROM events WHERE partner_id IN ( SELECT id FROM partners WHERE user_id = auth.uid() ) ) );-- Partners can delete generations from their events (moderation)CREATE POLICY "Partners can delete own event generations" ON generations FOR DELETE USING ( event_id IN ( SELECT id FROM events WHERE partner_id IN ( SELECT id FROM partners WHERE user_id = auth.uid() ) ) );
ALTER TABLE api_key_pool ENABLE ROW LEVEL SECURITY;-- Service role only (used by edge functions)CREATE POLICY "Service role only" ON api_key_pool FOR ALL TO service_role USING (true) WITH CHECK (true);
ALTER TABLE payment_notifications ENABLE ROW LEVEL SECURITY;-- Users can view their own paymentsCREATE POLICY "Users can view own payments" ON payment_notifications FOR SELECT USING (user_id = auth.uid());-- Service role can insert (webhook handler)CREATE POLICY "Service role can insert payments" ON payment_notifications FOR INSERT TO service_role WITH CHECK (true);-- Masters can view all paymentsCREATE POLICY "Masters can view all payments" ON payment_notifications FOR SELECT USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );
ALTER TABLE styles_metadata ENABLE ROW LEVEL SECURITY;-- Anyone can read stylesCREATE POLICY "Public can read styles" ON styles_metadata FOR SELECT USING (true);-- Masters can manage stylesCREATE POLICY "Masters can manage styles" ON styles_metadata FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );-- Service role can update usage countsCREATE POLICY "Service role can update styles" ON styles_metadata FOR UPDATE TO service_role WITH CHECK (true);
Public read for prompts (needed by edge functions).
ALTER TABLE identity_prompts ENABLE ROW LEVEL SECURITY;-- Anyone can read promptsCREATE POLICY "Public can read prompts" ON identity_prompts FOR SELECT USING (true);-- Masters can manage promptsCREATE POLICY "Masters can manage prompts" ON identity_prompts FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );
-- Users can insert their own photosCREATE POLICY "Users can upload own photos"ON storage.objects FOR INSERTTO authenticatedWITH CHECK ( bucket_id = 'user_photos' AND auth.uid()::text = (storage.foldername(name))[1]);-- Users can read their own photosCREATE POLICY "Users can view own photos"ON storage.objects FOR SELECTTO authenticatedUSING ( bucket_id = 'user_photos' AND auth.uid()::text = (storage.foldername(name))[1]);
-- Anyone can read generated imagesCREATE POLICY "Public can view generations"ON storage.objects FOR SELECTUSING (bucket_id = 'generations');-- Service role can insertCREATE POLICY "Service can upload generations"ON storage.objects FOR INSERTTO service_roleWITH CHECK (bucket_id = 'generations');
-- Partners can upload event assetsCREATE POLICY "Partners can upload event assets"ON storage.objects FOR INSERTTO authenticatedWITH CHECK ( bucket_id = 'event_assets' AND (storage.foldername(name))[1] IN ( SELECT id::text FROM events WHERE partner_id IN ( SELECT id FROM partners WHERE user_id = auth.uid() ) ));-- Anyone can view event assets (logos)CREATE POLICY "Public can view event assets"ON storage.objects FOR SELECTUSING (bucket_id = 'event_assets');
-- Set JWT claims (for testing)SET request.jwt.claims = '{"sub": "user-id-here", "role": "authenticated"}'::jsonb;-- Run query as that userSELECT * FROM events;-- ResetRESET request.jwt.claims;
-- List all policies for a tableSELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_checkFROM pg_policiesWHERE tablename = 'events';
CREATE POLICY "Masters can do anything" ON table_name FOR ALL USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_master = true ) );
CREATE POLICY "Partners only" ON table_name FOR SELECT USING ( EXISTS ( SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.role = 'partner' ) );
-- Clients can view generations from their eventsCREATE POLICY "Clients can view their generations" ON generations FOR SELECT USING ( event_id IN ( SELECT e.id FROM events e INNER JOIN clients c ON e.client_id = c.id WHERE c.email = auth.email() ) );
-- Verify RLS is enabledSELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public' AND tablename = 'your_table';-- Check policies for your roleSELECT * FROM pg_policies WHERE tablename = 'your_table';
Policy not applying
Ensure you’re using the correct role:
Frontend queries use anon or authenticated role
Edge functions use service_role (bypasses RLS by default)
Use USING for SELECT, UPDATE, DELETE and WITH CHECK for INSERT, UPDATE.
Performance issues with RLS
Complex policies can slow queries. Optimize:
-- Add index on columns used in policiesCREATE INDEX idx_events_partner_id ON events(partner_id);-- Simplify policy logic-- Bad: Multiple subqueries-- Good: Single JOIN
Testing policies locally
Use the SQL Editor in Supabase Dashboard or psql:
# Connect as specific rolepsql postgres://postgres:password@localhost:54322/postgres# Set roleSET ROLE authenticated;# Test querySELECT * FROM events;