RLS Optimization Rules

FATAL Constraints

• Bare auth function calls inside policies are FATAL. Every auth.uid(), auth.jwt(), or auth.role() call inside a USING or WITH CHECK expression MUST be wrapped in a subselect: (SELECT auth.uid()). Unwrapped calls force re-evaluation per row and cause full table scans.
• Missing indexes on policy-referenced columns are FATAL. Every column appearing in an RLS policy expression MUST have a corresponding B-Tree or GIN index. Policies without indexes degrade to sequential scans under load.
• Deep relational JOINs inside policy expressions are FATAL. Any JOIN traversing 2+ tables inside a policy body MUST be extracted into a SECURITY DEFINER function. Inline multi-table JOINs in policies create unpredictable query plans and lock escalation.

Mandatory Behaviors

• MUST name RLS-supporting indexes with the pattern idx_{table}_{column}_rls.
• MUST set SECURITY DEFINER functions with SET search_path = public to prevent search_path injection.
• MUST include IF NOT EXISTS on all CREATE INDEX statements to ensure idempotency.
• MUST verify optimization results with EXPLAIN ANALYZE after applying changes.
• MUST NOT add SECURITY DEFINER functions that accept arbitrary SQL or text input.
• MUST NOT disable RLS to work around performance issues.