supabase-rls-policy_skill

Overview

This skill provides expert guidance for writing production-ready Supabase PostgreSQL row-level security (RLS) policies. It focuses on correct policy structure, Supabase-specific conventions (auth roles, auth.uid(), auth.jwt()), and practical patterns for owner-, team-, and public-access models. Use it to create, modify, or troubleshoot RLS rules safely and efficiently.

How this skill works

The skill inspects requested access patterns and generates explicit CREATE POLICY statements per operation (SELECT, INSERT, UPDATE, DELETE), applying Supabase role conventions and correct USING/WITH CHECK usage. It recommends performance optimizations—wrapping auth functions in SELECT, adding indexes, and avoiding per-row joins—and outputs descriptive policy names plus brief explanations. It flags risky choices (FOR ALL, misuse of raw_user_meta_data, unnecessary RESTRICTIVE policies) and provides a validation checklist.

When to use it

• Creating new table-level RLS rules for Supabase projects
• Converting access requirements into secure SQL policies
• Troubleshooting why queries are permitted or blocked by RLS
• Implementing owner-based, team-based, or public-read/authenticated-write patterns
• Optimizing policy performance and index usage

Best practices

• Create one policy per operation (no FOR ALL); name policies with clear sentences.
• Always specify roles with TO after FOR and prefer PERMISSIVE policies.
• Use (SELECT auth.uid()) not auth.uid() directly to enable plan caching.
• Read authorization data from auth.jwt() -> 'app_metadata' for secure metadata; never trust raw_user_meta_data.
• Add indexes on columns used in USING/WITH CHECK conditions and minimize joins in policy logic.

Example use cases

• Owner-only access: generate SELECT/UPDATE policies that compare (SELECT auth.uid()) to user_id and include WITH CHECK for updates.
• Team membership: produce a USING clause that checks team_id IN (SELECT team_id FROM team_user WHERE user_id = (SELECT auth.uid())).
• Public read, authenticated write: create SELECT policy TO anon, authenticated USING (true) and INSERT policy TO authenticated WITH CHECK (true).
• MFA-protected actions: add a RESTRICTIVE FOR update policy requiring (SELECT auth.jwt()->>'aal') = 'aal2' for high-security updates.