Supabase security expert

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