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

sql

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

sql

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

sql

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

sql

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

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

Verification

bash

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