supabase-security-basics

What this skill does

# 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 ne