supabase-security-basics（Supabase 安全 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:

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).

// 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!
)

// 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 } }
)

// 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.

-- 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:

-- 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:

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:

-- 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:

// Server-side only — requires service role key
const { error } = await supabaseAdmin.auth.admin.updateUserById(userId, {
  app_metadata: { role: 'admin' }
})

Policy pattern — organization-scoped access:

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:

// 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:

ALTER ROLE authenticated SET statement_timeout = '10s';
ALTER ROLE anon SET statement_timeout = '5s';

Revoke default schema grants (verify only):

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

Examples

Minimal secure setup for a new table:

-- 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):

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
• Securing Your API
• Security Overview
• Production Checklist
• Custom Claims & RBAC
• @supabase/supabase-js Reference

Next Steps

• Apply production hardening with supabase-prod-checklist
• Set up auth flows with supabase-auth-flows
• Configure database migrations with supabase-migrations