# Pathrule Pattern: Supabase + RLS (1.0.0) # ::pathrule:package:supabase-rls ### [RULE] RLS enabled on every table, deny by default (path: /supabase) Every user-facing table in the public schema must have Row Level Security enabled with a deny-by-default posture. A table with RLS disabled is readable and writable by any authenticated user. - Enable RLS in the same migration that creates the table: `ALTER TABLE ENABLE ROW LEVEL SECURITY`. Never rely on the application layer to enforce access at the row level. - Start from no policies (which denies everything) and add the minimum set of policies per operation (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) that the feature actually requires. A catch-all `USING (true)` policy is equivalent to no RLS. - A migration that creates a table without enabling RLS and adding policies is incomplete and must not merge to a shared environment. - Internal or log tables used only by server-side jobs that run as the service role may skip per-user policies, but must still have RLS enabled and an explicit `USING` expression that allows only the service role, not a blanket bypass. --- ### [RULE] Service role key stays server-side; never in client paths (path: /supabase) The Supabase service role key grants full unrestricted access to every row in every table, bypassing all RLS policies. Leaking it to a browser or a client-accessible path renders all RLS useless. - Never include the service role key in any environment variable exposed to the browser (for example `NEXT_PUBLIC_*` in Next.js, or any variable bundled by Vite/webpack for the client). - Edge functions that handle per-user requests must create their Supabase client with the user's JWT from the `Authorization` header, not the service role: `createClient(url, anonKey, { global: { headers: { Authorization: req.headers.get('Authorization') } } })`. This ensures RLS evaluates under the correct user identity. - Use the service role key only in trusted server-side contexts (scheduled functions, admin scripts, server-to-server calls) where bypassing RLS is the deliberate intent and the scope is understood. - Never derive or reconstruct the service role key from a client request, environment variable pattern, or any value accessible to end users. --- ### [RULE] Index every column used in RLS policy predicates (path: /supabase/migrations) Postgres evaluates each RLS policy's `USING` and `WITH CHECK` expressions once per row that the query touches. An expression that references an unindexed column forces a sequential scan on every row access, not just on the query plan — the cost is invisible in `EXPLAIN` for the outer query but shows up as degraded performance under load. - Add a B-tree index on every column that appears in a `USING (...)` or `WITH CHECK (...)` expression, particularly `user_id`, `workspace_id`, `tenant_id`, and any other access-boundary column. - For policies that call `auth.uid()`, the `user_id` column they compare against must be indexed; the `auth.uid()` itself is a function call that Postgres inlines as a constant per statement, so it is not the bottleneck, but the column scan is. - If using a `SECURITY DEFINER` helper function (see the access-helper memory), index the columns inside that function's WHERE clause, not just the surface-level expression in the policy. - Run `EXPLAIN ANALYZE` on representative queries before and after adding RLS to confirm the policy predicates are not adding full-table sequential scans. --- ### [MEMORY] Centralize access checks in a SECURITY DEFINER helper (path: /supabase) Writing the same `EXISTS (SELECT 1 FROM memberships WHERE ...)` check inline in multiple policies creates drift: tables diverge, a bug fix in one policy misses others, and refactoring access logic means touching every policy. - Express membership and ownership in one `SECURITY DEFINER` function, for example: ```sql CREATE OR REPLACE FUNCTION has_workspace_access(p_workspace_id uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER AS $$ SELECT EXISTS ( SELECT 1 FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = auth.uid() ); $$; ``` - Reference the helper in all relevant policies: `CREATE POLICY "workspace read" ON documents FOR SELECT USING (has_workspace_access(workspace_id))`. - `SECURITY DEFINER` runs the function as the function owner (typically `postgres`), which avoids RLS recursion if `workspace_members` itself is RLS-protected. Mark it `STABLE` so the planner can cache the result per statement. - Grant `EXECUTE` only to `authenticated` and `service_role`; do not grant to `anon` unless anonymous access is intentional. --- ### [MEMORY] auth.uid(), auth.jwt(), and custom claims in policies (path: /supabase) Supabase injects the authenticated user's identity into the Postgres session so RLS policies can reference it without a subquery to `auth.users`. - `auth.uid()` returns the `sub` claim of the JWT as a `uuid`. Use it directly in policy `USING` expressions: `USING (user_id = auth.uid())`. - `auth.jwt()` returns the full decoded JWT as `jsonb`. Access top-level claims with `->>`: `auth.jwt() ->> 'role'`. For nested claims (for example `app_metadata`): `(auth.jwt() -> 'app_metadata') ->> 'plan'`. - Custom claims (roles, plan tier, org ID) are the recommended way to encode authorization context without an extra DB lookup per row. Add them via a Supabase Auth Hook (the `custom_access_token` hook in the Dashboard), which runs a Postgres function that appends claims to every JWT before it is issued. - Never call `SELECT ... FROM auth.users` inside a policy `USING` expression just to read a claim that is already in the JWT. It issues a subquery per row. Use `auth.jwt()` to read the claim from the already-present token instead. - When a custom claim must be refreshed (for example after a plan upgrade), call `supabase.auth.refreshSession()` on the client to force a new token with updated claims; old tokens keep the stale claims until they expire. --- ### [MEMORY] Supabase migration workflow (path: /supabase/migrations) Supabase migrations are plain SQL files managed by the Supabase CLI. The workflow keeps the database and generated types in sync. - Generate a new migration with `supabase migration new `, which creates a timestamped file in `supabase/migrations/`. Write forward-only SQL; there are no down steps. - Enable RLS and add all required policies in the same migration file that creates the table. Never leave the table in a state where it exists but RLS is not yet enabled, even briefly across separate migrations. - After applying a migration that changes the schema, regenerate TypeScript types: `supabase gen types typescript --linked > src/types/supabase.ts` (or equivalent). Commit the updated types file in the same PR as the migration. - Apply locally with `supabase db reset` (for local dev) or `supabase db push` (for remote preview branches). Never edit a migration file that has already been applied to any shared environment; add a new migration instead. - For Supabase hosted projects, use branching (`supabase branches`) to develop and test schema changes on an isolated preview branch before merging to production. --- ### [SKILL] supabase-rls-review (path: /) --- name: supabase-rls-review description: Review a Supabase migration or schema change for RLS correctness, JWT claim usage, access-helper design, policy-predicate indexing, and migration workflow safety. --- # Supabase RLS review ## RLS and policies - [ ] RLS is enabled on every new table in the same migration that creates it. - [ ] Policies exist per operation (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) and the default is deny (no `USING (true)` catch-all). - [ ] Policies use the shared `SECURITY DEFINER` access helper, not ad-hoc inline subqueries that would differ across tables. - [ ] Internal/job tables accessed only via the service role have explicit `USING` expressions limiting to the service role, not a blanket bypass. ## Performance - [ ] Every column referenced in a `USING` or `WITH CHECK` expression (especially `user_id`, `workspace_id`, `tenant_id`) has a B-tree index. - [ ] No policy calls `SELECT ... FROM auth.users` per row to read a claim that is already available via `auth.jwt()`. ## JWT and custom claims - [ ] Policies use `auth.uid()` for user identity and `auth.jwt()` for custom claims; no unnecessary subqueries to `auth.users`. - [ ] Custom claims added via the Auth Hook are documented and the client calls `refreshSession()` after claim-changing events (plan upgrade, role change). ## Service role - [ ] No service role key appears in any browser-accessible environment variable or in edge function code acting on behalf of a user. - [ ] Edge functions acting on behalf of a user forward the `Authorization: Bearer ` header to the Supabase client, not the service role key. ## Migration workflow - [ ] TypeScript types are regenerated and committed alongside the migration. - [ ] Migration is forward-only and no already-applied file was edited; a new file was added.