RLS Policy Optimization

Prevents CPU spikes and full table scans from poorly written RLS policies via index and wrapper enforcement.

Overview

Row Level Security policies execute per-row during query evaluation. Unoptimized policies — bare auth.uid() calls, missing indexes, inline multi-table JOINs — cause the Postgres planner to fall back to sequential scans and repeated function evaluation. This tile enforces three mechanical optimizations that eliminate these performance cliffs.

Reference

SELECT-Wrapped Auth Calls

Wrapping auth.uid() in a subselect tells the planner to evaluate it once and cache the result:

-- FATAL: bare call
CREATE POLICY "user_access" ON items USING (auth.uid() = user_id);

-- Correct: wrapped call
CREATE POLICY "user_access" ON items USING ((SELECT auth.uid()) = user_id);

Mandatory Indexes

Every column in a policy expression MUST have an index:

CREATE INDEX IF NOT EXISTS idx_items_user_id_rls ON items (user_id);

SECURITY DEFINER Encapsulation

Deep JOINs MUST move into a function:

CREATE OR REPLACE FUNCTION check_item_access(item_id UUID)
RETURNS BOOLEAN LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (
    SELECT 1 FROM items i
    JOIN teams t ON t.id = i.team_id
    JOIN members m ON m.team_id = t.id
    WHERE i.id = item_id AND m.user_id = (SELECT auth.uid())
  );
$$;

Dependencies

• supabase-mcp-verification — validates migration application.
• tenant-isolation-rls — provides base RLS patterns this tile optimizes.

Composition Position

Runs after tenant-isolation-rls has established base policies. This tile optimizes existing policies; it does not create them from scratch. Output feeds into any tile that queries RLS-protected tables.