supabase-security-basics

# Supabase Security Basics ## Overview Supabase exposes a Postgres database directly to the internet via PostgREST. Every table without Row Level Security enabled is fully readable and writable by anyone with your project URL and anon key — both of which are public. This skill covers the three pillars of Supabase security: key separation (anon vs service_role), RLS policy enforcement, and API surface hardening. ## Prerequisites - Supabase project created (local or hosted) with Dashboard access - `@supabase/supabase-js` installed (`npm install @supabase/supabase-js`) - `SUPABASE_URL` and `SUPABASE_ANON_KEY` environment variables configured - Basic understanding of SQL and Postgres ## Instructions ### Step 1 — Understand the Two API Keys Supabase issues two keys per project. Confusing them is the most common security mistake: | Key | Environment Variable | Exposed to Client? | RLS Behavior | |-----|---------------------|-------------------|--------------| | **Anon key** | `SUPABASE_ANON_KEY` | Yes — browser-safe | Respects all RLS policies | | **Service role key** | `SUPABASE_SERVICE_ROLE_KEY` | **NEVER** expose | Bypasses ALL RLS | The anon key is a JWT that PostgREST uses to determine which RLS policies apply. It is safe to include in client-side bundles — it can only access data that RLS policies explicitly allow. The service role key bypasses every RLS policy and should only ever exist in server-side code (API routes, Edge Functions, cron jobs, migration scripts). ```typescript // CORRECT: anon key on the client import { createClient } from '@supabase/supabase-js' const supabase = createClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! ) ``` ```typescript // CORRECT: service role key ONLY in server-side code // e.g., app/api/admin/route.ts (Next.js server route) import { createClient } from '@supabase/supabase-js' const supabaseAdmin = createClient( process.env.SUPABASE_URL!, process.env.SUPABASE_SERVICE_ROLE_KEY!, { auth: { autoRefreshToken: false, persistSession: false } } ) ``` ```typescript // WRONG — service role key in client-side code // This bypasses ALL RLS and leaks your admin key to every user const supabase = createClient(url, process.env.NEXT_PUBLIC_SERVICE_ROLE_KEY!) // NEVER DO THIS ``` **Key rotation:** Regenerate keys in Dashboard > Settings > API. After rotation, update every environment variable and redeploy all services. Old keys are invalidated immediately — there is no grace period. ### Step 2 — Enforce Row Level Security on Every Table Without RLS, any table in the `public` schema is fully accessible via the REST API to anyone holding the anon key. RLS is not optional — it is the primary access control layer. ```sql -- Audit: find tables missing RLS SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename; -- Enable RLS on every public table ALTER TABLE public.users ENABLE ROW LEVEL SECURITY; ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY; ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY; -- CRITICAL: enabling RLS with NO policies blocks ALL access via the API. -- You MUST add at least one policy per table per operation (SELECT, INSERT, UPDATE, DELETE). ``` **Policy pattern — users read/write their own rows:** ```sql -- SELECT: user can only read their own rows CREATE POLICY "Users read own data" ON public.todos FOR SELECT USING (auth.uid() = user_id); -- INSERT: user can only insert rows for themselves CREATE POLICY "Users insert own data" ON public.todos FOR INSERT WITH CHECK (auth.uid() = user_id); -- UPDATE: user can only update their own rows CREATE POLICY "Users update own data" ON public.todos FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id); -- DELETE: user can only delete their own rows CREATE POLICY "Users delete own data" ON public.todos FOR DELETE USING (auth.uid() = user_id); ``` **Policy pattern — public read, authenticated write:** ```sql CREATE POLICY "Anyone can read posts" ON public.posts FOR SELECT USING (true); CREATE POLICY "Authenticated users can insert" ON public.posts FOR INSERT WITH CHECK (auth.uid() IS NOT NULL); ``` **Policy pattern — role-based access via custom JWT claims:** ```sql -- Admin-only policy using app_metadata CREATE POLICY "Admins have full access" ON public.settings FOR ALL USING ( (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' ); ``` To set custom claims server-side: ```typescript // Server-side only — requires service role key const { error } = await supabaseAdmin.auth.admin.updateUserById(userId, { app_metadata: { role: 'admin' } }) ``` **Policy pattern — organization-scoped access:** ```sql CREATE POLICY "Org members can read projects" ON public.projects FOR SELECT USING ( EXISTS ( SELECT 1 FROM public.members WHERE members.organization_id = projects.organization_id AND members.user_id = auth.uid() ) ); ``` **Key distinction — USING vs WITH CHECK:** - `USING (expr)` — filters which existing rows the user can see (SELECT, UPDATE, DELETE) - `WITH CHECK (expr)` — validates new/modified row data (INSERT, UPDATE) - For UPDATE, you need both: USING controls which rows can be targeted, WITH CHECK controls what the new values can be ### Step 3 — Harden the API Surface **JWT verification:** Supabase verifies JWTs server-side automatically. The `auth.uid()` function in RLS policies extracts the authenticated user's ID from the verified JWT. You do not need to verify tokens manually in RLS policies — Supabase handles this. **SQL injection prevention:** The Supabase JS SDK uses parameterized queries internally. Never build raw SQL strings from user input — always use the SDK query builder: ```typescript // SAFE: SDK parameterizes automatically const { data } = await supabase .from('posts') .select('*') .eq('author_id', userId) .ilike('title', `%${searchTerm}%`) // DANGEROUS: raw SQL with string interpolation // Only use supabase.rpc() with parameterized functions, never template literals ``` **Network restrictions:** Restrict direct database connections to known IP ranges in Dashboard > Settings > Database > Network Restrictions. This does not affect the REST API (which goes through PostgREST) but protects direct Postgres connections. **CORS configuration:** Configure allowed origins per project in Dashboard > Settings > API > CORS. Default allows all origins (`*`) — restrict to your domains in production. **Disable unused auth providers:** Dashboard > Authentication > Providers. Disable any provider you are not actively using (email, phone, Google, GitHub, etc.) to reduce attack surface. **SSL enforcement:** Dashboard > Settings > Database > SSL Configuration. Enforce SSL for all direct database connections. **Statement timeouts:** Prevent long-running queries from exhausting database resources: ```sql ALTER ROLE authenticated SET statement_timeout = '10s'; ALTER ROLE anon SET statement_timeout = '5s'; ``` **Revoke default schema grants (verify only):** ```sql -- Supabase handles this by default, but verify: -- anon and authenticated roles should only access data through RLS policies SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_schema = 'public' AND grantee IN ('anon', 'authenticated') ORDER BY table_name, grantee; ``` ## Output After completing these steps you will have: - Anon key used exclusively in client-side code, service role key restricted to server-side - RLS enabled on every public table with explicit policies per operation - Custom JWT claims configured for role-based access patterns - Network restrictions, CORS, SSL, and statement timeouts hardened - Unused auth providers disabled ### Security Audit Checklist - [ ] RLS enabled on ALL public tables (`SELECT rowsecurity FROM pg_tables WHERE schemaname='public'`) - [ ] Every table has at least one RLS policy per needed operation - [ ] Service role key is NOT in any client-side or `NEXT_PUBLIC_*` environment variables - [ ] `.env` files are in `.gitignore` - [ ] Email confirmation enabled (Dashboard > Authentication > Settings) - [ ] OAuth redirect URLs restricted to your domains - [ ] Unused auth providers disabled - [ ] SSL enforcement enabled (Dashboard > Database > SSL) - [ ] Database password changed from default - [ ] Network restrictions configured for direct DB connections - [ ] `statement_timeout` set for `authenticated` and `anon` roles - [ ] MFA enabled for sensitive user operations - [ ] Point-in-time recovery (PITR) enabled for production - [ ] API keys rotated after any suspected exposure ## Error Handling | Error | Cause | Solution | |-------|-------|----------| | `42501: new row violates row-level security policy` | RLS policy missing or `WITH CHECK` condition fails | Add or fix the RLS policy for that operation; verify `auth.uid()` matches the row's user column | | Query returns empty `data` with no error | RLS `USING` clause filters out all rows | Verify `auth.uid()` in the policy matches the authenticated user; check JWT claims | | `PGRST301: JWSError` | Invalid or expired JWT token | Re-authenticate the user; verify `SUPABASE_ANON_KEY` matches the project | | `PGRST302: anonymous access disabled` | Anon key not provided in client init | Pass the anon key to `createClient()`; check environment variable is set | | `permission denied for table X` | RLS enabled but no matching policy | Create a policy for the specific operation (SELECT/INSERT/UPDATE/DELETE) | | `Could not find the function auth.uid()` | Running SQL outside PostgREST context | `auth.uid()` only works in RLS policies evaluated by PostgREST; use explicit user IDs in migrations | ## Examples **Minimal secure setup for a new table:** ```sql -- 1. Create table CREATE TABLE public.notes ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, user_id UUID REFERENCES auth.users(id) NOT NULL DEFAULT auth.uid(), content TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); -- 2. Enable RLS immediately ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY; -- 3. Add policies CREATE POLICY "Users manage own notes" ON public.notes FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id); ``` **Client-side query (anon key — RLS enforced):** ```typescript import { createClient } from '@supabase/supabase-js' const supabase = createClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! ) // This only returns notes belonging to the authenticated user // because the RLS policy filters by auth.uid() const { data: notes, error } = await supabase .from('notes') .select('*') .order('created_at', { ascending: false }) ``` ## Resources - [Row Level Security Guide](https://supabase.com/docs/guides/database/postgres/row-level-security) - [Securing Your API](https://supabase.com/docs/guides/api/securing-your-api) - [Security Overview](https://supabase.com/docs/guides/security) - [Production Checklist](https://supabase.com/docs/guides/deployment/going-into-prod) - [Custom Claims & RBAC](https://supabase.com/docs/guides/auth/custom-claims-and-role-based-access-control-rbac) - [@supabase/supabase-js Reference](https://supabase.com/docs/reference/javascript/introduction) ## Next Steps - Apply production hardening with `supabase-prod-checklist` - Set up auth flows with `supabase-auth-flows` - Configure database migrations with `supabase-migrations`