supabase-security-expert

name

supabase-security-expert

description

Relational database auditing and Row-Level Security (RLS) best practices for Supabase. Use whenever the user is working with Supabase security, RLS policies, database auditing, PostgREST security, Supabase Auth, service role key protection, or securing Supabase APIs. Trigger on mentions of Supabase, RLS policies, anon key, service_role key, PostgREST, Supabase Auth, or database security audits. Also trigger when the user asks "is my Supabase secure" or wants to review their database policies.

Supabase Security Expert

Comprehensive security practices for Supabase: RLS, auth, API keys, and auditing.

---

The #1 Supabase Security Mistake

Never expose the service_role key on the client. It bypasses ALL RLS.

// ❌ CRITICAL VULNERABILITY — service_role in browser
const supabase = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!)

// ✅ Correct — anon key on client, service_role only on server
// Client-side
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!)

// Server-side only (Server Actions, Route Handlers, API routes)
import { createClient } from "@supabase/supabase-js"
const adminClient = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Never NEXT_PUBLIC_
  { auth: { persistSession: false } }
)

---

Key Management

# .env.local
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co        # ✅ public
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...                    # ✅ public (RLS protects)
SUPABASE_SERVICE_ROLE_KEY=eyJ...                        # ❌ NEVER prefix NEXT_PUBLIC_
SUPABASE_JWT_SECRET=your-jwt-secret                     # ❌ server only

---

RLS Policy Checklist

Enable RLS on Every Table

-- Check which tables DON'T have RLS enabled
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = FALSE;

-- Enable on all tables
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;
ALTER TABLE your_table FORCE ROW LEVEL SECURITY;

Policy Templates

-- 1. Users can only CRUD their own rows
CREATE POLICY "users_own_data" ON profiles
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- 2. Public read, authenticated write
CREATE POLICY "public_read" ON posts
  FOR SELECT USING (published = true);

CREATE POLICY "author_write" ON posts
  FOR ALL USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

-- 3. Organization-scoped access
CREATE POLICY "org_members_access" ON documents
  USING (
    org_id IN (
      SELECT org_id FROM org_members WHERE user_id = auth.uid()
    )
  );

-- 4. Admin bypass (use sparingly)
CREATE POLICY "admin_full_access" ON documents
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid() AND role = 'admin'
    )
  );

Test RLS Policies

-- Test as a specific user (in Supabase SQL editor)
SET request.jwt.claims = '{"sub": "USER_UUID_HERE", "role": "authenticated"}';
SET ROLE authenticated;

-- Now run your queries — RLS should apply
SELECT * FROM documents; -- should only return user's docs

-- Reset
RESET ROLE;

---

Auth Security

Server-Side Auth (Next.js)

// lib/supabase/server.ts
import { createServerClient } from "@supabase/ssr"
import { cookies } from "next/headers"

export async function createSupabaseServer() {
  const cookieStore = await cookies()
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll: () => cookieStore.getAll(),
        setAll: (cs) => cs.forEach(({ name, value, options }) =>
          cookieStore.set(name, value, options)
        ),
      },
    }
  )
}

// Usage in Server Component / Action
export async function getUser() {
  const supabase = await createSupabaseServer()
  const { data: { user }, error } = await supabase.auth.getUser()
  // NOTE: use getUser() NOT getSession() — getUser() validates with server
  if (error || !user) redirect("/login")
  return user
}

Auth Hooks — Sync to Custom Table

-- Supabase: auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  INSERT INTO public.profiles (id, email, name)
  VALUES (
    new.id,
    new.email,
    COALESCE(new.raw_user_meta_data->>'name', split_part(new.email, '@', 1))
  );
  RETURN new;
END;
$$;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

---

API Security Audit

Check for Exposed Sensitive Data

-- Find tables with no RLS
SELECT tablename FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = FALSE;

-- Find tables with RLS but no policies (blocks all access — may be intentional)
SELECT tablename FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = TRUE
AND tablename NOT IN (
  SELECT DISTINCT tablename FROM pg_policies WHERE schemaname = 'public'
);

-- List all policies
SELECT tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, cmd;

PostgREST / API Security

-- Revoke public access to sensitive functions
REVOKE EXECUTE ON FUNCTION your_sensitive_function FROM anon, authenticated;

-- Grant only what's needed
GRANT SELECT ON public.posts TO anon;
GRANT ALL ON public.profiles TO authenticated;

-- Never grant to public schema from anon unless intentional

---

Storage Security

// Supabase Storage — bucket policies
// In Supabase dashboard → Storage → Policies

// Private bucket (default — good)
// Public bucket — only for truly public assets (avatars with obfuscated names)

// Upload with user prefix (enforce in RLS)
const { data, error } = await supabase.storage
  .from("avatars")
  .upload(`${user.id}/avatar.png`, file, { upsert: true })

-- Storage RLS: users can only access their own folder
CREATE POLICY "user_owns_folder" ON storage.objects
  FOR ALL USING (
    bucket_id = 'avatars'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

---

Security Audit Checklist

Database

• RLS enabled on ALL public schema tables
• Every table has explicit policies (not relying on "deny all" default)
• SECURITY DEFINER functions have SET search_path = ''
• No raw SELECT * from sensitive tables in functions
• Audit log table for sensitive operations (login, delete, export)

Auth

• getUser() used on server — NOT getSession() (getSession doesn't validate)
• Email confirmation enabled for sign-ups
• Password minimum length ≥ 8 (ideally 12)
• Rate limiting on auth endpoints (Supabase does this, but verify)
• Magic links expire in ≤ 1 hour

Keys & Secrets

• service_role key only in server env vars (no NEXT_PUBLIC_)
• JWT_SECRET rotated if ever exposed
• API keys in Supabase Vault (not raw in DB)
• .env.local in .gitignore

Storage

• Buckets are private by default
• Storage RLS policies scoped to user ID folders
• File type validation on upload (MIME type check)
• Max file size set in bucket config

---

Key Rules

1. service_role = root access — treat it like a database root password
2. getUser() not getSession() — only getUser() validates the JWT with the server
3. Test policies with SET ROLE authenticated in SQL editor
4. SECURITY DEFINER + SET search_path = '' on all auth-related functions
5. RLS is not enough alone — add application-level checks for critical operations
6. Supabase Vault for secrets — don't store API keys in plain text columns
7. Audit log for sensitive ops — who deleted what and when
8. Bucket = private by default — opt-in to public, never the reverse
9. Confirm emails — prevent enumeration attacks with consistent responses
10. Monitor for anomalies — Supabase dashboard shows API request logs