supabase-rls-policies

name	supabase-rls-policies
description	Use when creating or modifying Row Level Security policies

Supabase Row Level Security (RLS) Policies

Row Level Security controls which rows users can access. Always enable RLS on tables with user-specific or organization-scoped data.

Core Syntax

ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "policy_name"
    ON schema.table_name
    FOR operation          -- SELECT, INSERT, UPDATE, DELETE, or ALL
    TO authenticated       -- REQUIRED: explicit role (never omit)
    [USING (condition)]    -- For SELECT, UPDATE, DELETE (existing rows)
    [WITH CHECK (condition)]; -- For INSERT, UPDATE (new/modified rows)

Organization-Scoped Policy Pattern

CREATE POLICY "Organization members can view projects"
    ON public.projects FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM public.organization_members
            WHERE organization_members.organization_id = projects.organization_id
            AND organization_members.user_id = (SELECT auth.uid())
        )
    );

Common Patterns

-- User-specific data
CREATE POLICY "Users can read own profile"
    ON public.profiles FOR SELECT TO authenticated
    USING ((SELECT auth.uid()) = id);

-- Public read, authenticated write
CREATE POLICY "Anyone can read" ON public.posts FOR SELECT TO public USING (true);
CREATE POLICY "Authenticated can create" ON public.posts FOR INSERT TO authenticated
    WITH CHECK ((SELECT auth.uid()) = user_id);

⚠️ CRITICAL: Performance Optimization

Always wrap auth.uid() in SELECT subquery - without it, evaluated per-row (99% slower):

-- ✅ Correct (evaluated once per query)
WHERE user_id = (SELECT auth.uid())

-- ❌ Wrong (evaluated per row - causes 0003_auth_rls_initplan warning)
WHERE user_id = auth.uid()

Real-world impact: 170ms → 178,000ms on 100K rows without optimization.

Best Practices

1. Always use explicit TO clause - TO authenticated not omitted (defaults to TO public)
2. Use (SELECT auth.uid()) - wrapped in subquery for performance
3. Create separate policies per operation - SELECT, INSERT, UPDATE, DELETE
4. Use EXISTS subqueries for organization/membership checks
5. Add indexes on policy columns - critical for performance
6. Test policies - as authenticated and anonymous users

Verification

cd spark/frontend/my-vite-app && supabase db lint

Check for 0003_auth_rls_initplan warning (auth.uid() not optimized).

Docs: https://supabase.com/docs/guides/database/database-advisors?lint=0003_auth_rls_initplan