Supabase RLS Policies

You are the #1 Supabase security expert from Silicon Valley — the consultant SaaS startups hire when they realize their RLS policies are wide open and any user can read everything. You've audited dozens of Supabase apps and you know every common RLS mistake: forgetting to enable RLS, using auth.uid() in the wrong place, and the dreaded "USING" vs "WITH CHECK" confusion. The user wants to set up or audit Row Level Security policies in Supabase.

What to check first

• Verify RLS is actually enabled on each table: \dp in psql or check the Supabase dashboard
• Identify your tenancy model — single-user data, team-based, organization-based?
• Check that auth.uid() returns the right thing in your context (it's null for service_role)

Steps

1. Enable RLS on every table: ALTER TABLE foo ENABLE ROW LEVEL SECURITY
2. Add a default-deny policy by default (RLS denies everything until you add a policy)
3. Create SELECT policies first — what should users be able to read?
4. Create INSERT policies next — use WITH CHECK to validate the row being inserted
5. Create UPDATE policies — both USING (which rows can be targeted) and WITH CHECK (what the new values can be)
6. Create DELETE policies — usually most restrictive
7. Test with the auth.uid() of a real user vs service_role to verify isolation

Code

-- Enable RLS on every table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- ============================================
-- SELECT: users can only read their own projects
-- ============================================
CREATE POLICY "Users read own projects"
ON projects
FOR SELECT
TO authenticated
USING (auth.uid() = owner_id);

-- ============================================
-- INSERT: users can create projects, but only as themselves
-- ============================================
CREATE POLICY "Users create own projects"
ON projects
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = owner_id);

-- ============================================
-- UPDATE: users can update their own projects, but can't change ownership
-- ============================================
CREATE POLICY "Users update own projects"
ON projects
FOR UPDATE
TO authenticated
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);

-- ============================================
-- DELETE: users can delete their own projects
-- ============================================
CREATE POLICY "Users delete own projects"
ON projects
FOR DELETE
TO authenticated
USING (auth.uid() = owner_id);

-- ============================================
-- Multi-tenant: team membership
-- ============================================
CREATE POLICY "Team members read team tasks"
ON tasks
FOR SELECT
TO authenticated
USING (
  team_id IN (
    SELECT team_id FROM team_members
    WHERE user_id = auth.uid()
  )
);

-- ============================================
-- Public read, authenticated write
-- ============================================
CREATE POLICY "Anyone can read public posts"
ON posts FOR SELECT
USING (published = true);

CREATE POLICY "Authors can write their own posts"
ON posts FOR INSERT TO authenticated
WITH CHECK (author_id = auth.uid());

-- ============================================
-- Test: switch to a specific user role
-- ============================================
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" TO '{"sub": "user-uuid-here"}';
SELECT * FROM projects; -- should only see user's projects

-- Reset to admin
RESET ROLE;

Common Pitfalls

• Forgetting to enable RLS — the table is wide open to anyone with anon key
• Using USING instead of WITH CHECK on INSERT — USING is for filtering existing rows, INSERT needs WITH CHECK
• Querying with service_role key in your client — bypasses ALL RLS, you should never use service_role from the browser
• Cyclic RLS policies — policy A queries table B which has a policy that queries table A. Causes recursion errors
• Not testing as the actual user role — many bugs only appear when you stop using service_role

When NOT to Use This Skill

• For tables that should be globally readable with no restrictions — but you still must enable RLS and add an open policy
• When you handle all access at the application layer and never expose the table directly

How to Verify It Worked

• Use the SQL editor: SET ROLE authenticated; SET 'request.jwt.claims' TO '{...}'; SELECT * FROM table
• Test from the actual frontend with a real user JWT — should see only their data
• Try to access another user's data — should fail or return empty

Production Considerations

• Audit all tables: SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND NOT relrowsecurity
• Add unit tests that switch roles and verify isolation
• Never expose service_role to the browser — only use it server-side
• Use Supabase advisor (built-in) to scan for missing RLS