Supabase Auth: PostgreSQL-Based Authentication

Firebase wouldn't let me run a simple SELECT COUNT(*) on my own fucking users. Auth0 wanted $200/month just to export user data for a board meeting. AWS Cognito's documentation made me want to quit programming.

Supabase stores users in a regular PostgreSQL table (auth.users) that you can actually query. Want to know how many users signed up last week? SELECT COUNT(*) FROM auth.users WHERE created_at > now() - interval '7 days'. Done.

The genius move: put auth in PostgreSQL where you can actually debug it. Row Level Security enforces permissions at the database level - hack the API, bypass the middleware, connect directly to the database, and the security policies still apply.

Your Users Live in PostgreSQL

Every user lands in auth.users - a regular PostgreSQL table you can query, backup, and export like any other data. No special APIs, no vendor-specific formats.

This saved my ass when a client demanded user analytics with 2 hours notice. Instead of figuring out Auth0's export API, I wrote SELECT DATE(created_at), COUNT(*) FROM auth.users GROUP BY DATE(created_at) and had charts in 10 minutes.

-- Your actual users table in PostgreSQL
SELECT id, email, created_at, last_sign_in_at 
FROM auth.users 
WHERE created_at > now() - interval '7 days';

-- Join users with your application data
SELECT u.email, p.company_name 
FROM auth.users u
JOIN profiles p ON u.id = p.user_id
WHERE p.subscription_status = 'active';

I moved off Auth0 when they hit us with a $400/month bill for 15K users. The last straw was when exporting user data for GDPR compliance required upgrading to their "enterprise" tier. Fuck that. With Supabase, user export is COPY auth.users TO '/tmp/users.csv' CSV HEADER.

JWT Tokens You Can Actually Use

Supabase gives you standard JWT tokens, not some bullshit proprietary format. You can decode them with any JWT library, verify them anywhere, and they contain exactly what you expect:

{
  "aud": "authenticated",
  "exp": 1734567890,
  "iat": 1734564290,
  "iss": "https://your-project.supabase.co/auth/v1",
  "sub": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
  "email": "user@example.com",
  "role": "authenticated",
  "session_id": "session-uuid"
}

The tokens are signed with RSA keys (private/public pair), so no shared secrets to leak. They expire in 1 hour and refresh automatically. Unlike Auth0 tokens that are stuffed with random metadata you didn't ask for, Supabase tokens are clean.

Row Level Security (The Reason You Should Care)

This is where Supabase Auth gets scary good. PostgreSQL can read the user ID directly from your JWT token and enforce permissions at the database level. Bypass your API, write raw SQL, hack the frontend - doesn't matter. The database policies still apply.

-- Users can only see their own data
CREATE POLICY "users_own_data" ON profiles
  FOR ALL USING (auth.uid() = user_id);

-- Team members can see team data
CREATE POLICY "team_access" ON projects  
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM team_members 
      WHERE user_id = auth.uid() 
      AND team_id = projects.team_id
    )
  );

I've seen apps where a missing WHERE user_id = current_user in one endpoint leaked all user data. With RLS, that bug is impossible. The database enforces the rules regardless of how you access the data.

Watched a competitor spend 6 months building role-based permissions in their Node.js app. One forgotten authorization check in a GraphQL resolver exposed admin data. Their entire user base got compromised. RLS would have prevented that.

OAuth That Actually Works

OAuth setup usually means spending a weekend reading documentation and debugging redirect URLs. Supabase supports 20+ providers (Google, GitHub, Apple, Discord, etc.) and the setup is actually straightforward:

1. Enable the provider in your Supabase dashboard
2. Add your OAuth credentials from the provider
3. Configure redirect URLs (one line of code)
4. Call the sign-in method in your app

// Social login in 3 lines
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: { redirectTo: 'https://yourapp.com/dashboard' }
});

The key thing: social login users end up in the same auth.users table as email users. Same JWT format, same permissions, same everything. No special cases to handle.

Features That Don't Cost Extra

Stuff that costs $500+/month with Auth0 comes free with Supabase:

• Multi-Factor Authentication: TOTP, SMS, and authenticator apps
• Single Sign-On (SSO): SAML 2.0 for enterprise customers
• Phone Authentication: SMS-based login with Twilio, MessageBird, or Vonage
• Magic Links: Passwordless email authentication
• Anonymous Sign-in: Guest users that can convert to full accounts
• Web3 Authentication: Sign in with Solana wallets

This shit is included in the $25/month Pro plan. Auth0 charges $1,500+/month for the same features. The only extra cost is SMS for MFA at $0.05 per message, but TOTP apps are free and more secure anyway.

What You Actually Pay (September 2025 Pricing)

Based on current Supabase pricing:

Free Tier:

• 50,000 Monthly Active Users (MAU)
• All authentication methods included
• Perfect for MVPs and small projects

Pro Tier ($25/month):

• 100,000 MAU (then $0.00325 per additional MAU)
• Everything in free tier
• MFA, SSO, custom SMTP
• 99.9% uptime SLA

Enterprise (Custom pricing):

• Unlimited MAU with volume discounts
• Advanced audit logs
• Priority support and SLA
• Custom contracts and compliance

Real numbers: 25K active users costs $0 with Supabase, $175 with Auth0, $137 with AWS Cognito. At 100K users, Supabase costs $25/month while Auth0 hits $700+. The math gets stupid fast.

When Supabase Auth Makes Sense (And When It Doesn't)

Choose Supabase Auth when:

• You're already using PostgreSQL (or planning to)
• You want authentication that integrates with your database
• Cost matters and you don't want to pay enterprise prices for standard features
• You need the flexibility of SQL and open source
• Row Level Security appeals to you (it should)

Look elsewhere when:

• You're deep in a different ecosystem (Firebase, AWS, etc.) and integration matters more than features
• You need identity federation across hundreds of enterprise systems
• You're building a multi-tenant SaaS with complex B2B requirements (though RLS handles most of this)
• You absolutely cannot use PostgreSQL for some reason

Bottom line: if you're using PostgreSQL, Supabase Auth is a no-brainer. If you're deep in the AWS ecosystem with DynamoDB, stick with Cognito. If you're using Firebase, the migration pain might not be worth it unless Auth0's pricing is killing you.

Auth doesn't have to suck or cost a fortune. Supabase proves you can have decent security, reasonable prices, and APIs that don't make you want to throw your laptop out the window.

Setup takes 15 minutes. Production debugging takes 15 hours. Here's what breaks and how to fix it, learned from spending too many nights wondering why auth worked locally but failed in prod.

The 15-Minute Setup (That Actually Works)

Step 1: Project Creation

## Create project via CLI (faster than dashboard clicking)
npx supabase projects create your-app-name
npx supabase init
npx supabase start

If supabase start shits the bed with "Docker not found", install Docker Desktop and restart your terminal. If you get port conflicts (usually 5432 for PostgreSQL), either kill whatever's using that port or add --ignore-health-check to skip the checks.

Step 2: Client Configuration

// lib/supabase.ts - The configuration that won't bite you later
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient(supabaseUrl, supabaseKey, {
  auth: {
    persistSession: true,
    autoRefreshToken: true,
    detectSessionInUrl: true
  }
})

Step 3: Basic Authentication Flow

// Sign up with email/password
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'securePassword123',
  options: {
    emailRedirectTo: 'https://yourapp.com/auth/callback'
  }
})

// Sign in with social provider
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: 'https://yourapp.com/auth/callback'
  }
})

// Listen for auth changes
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN') {
    console.log('User signed in:', session?.user)
  }
  if (event === 'SIGNED_OUT') {
    console.log('User signed out')
  }
})

This works for 90% of use cases. The other 10% will ruin your weekend. Safari blocks third-party cookies and breaks embedded auth. CORS fails because you forgot to add your production domain. JWT refresh gets stuck in infinite loops and crashes your app.

Row Level Security Setup (The Part That Matters)

RLS policies are where Supabase Auth gets powerful and where you'll spend 3 hours debugging why users can't see their own data. Here are policies that actually work:

-- Enable RLS on your tables (required)
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Basic user isolation (bulletproof)
CREATE POLICY \"users_own_profiles\" ON profiles
  FOR ALL USING (auth.uid() = user_id);

-- Team-based access (handles the 80% case)
CREATE POLICY \"team_access\" ON posts
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM team_members tm
      JOIN profiles p ON p.user_id = tm.user_id
      WHERE p.user_id = auth.uid()
      AND tm.team_id = posts.team_id
      AND tm.role IN ('admin', 'editor', 'viewer')
    )
  );

-- Public read, authenticated write (common pattern)
CREATE POLICY \"public_read\" ON posts FOR SELECT USING (published = true);
CREATE POLICY \"authenticated_write\" ON posts FOR INSERT 
  WITH CHECK (auth.role() = 'authenticated');

The gotcha that costs you sleep

RLS policies are allowlists. If no policy matches, you get empty results with no error message. I deployed a policy that checked user_id = auth.uid() but the column was actually owner_id. Users couldn't see their own data for 2 hours while I figured out why.

-- Test your policies with real user context
SELECT set_config('request.jwt.claims', 
  '{\"sub\":\"real-user-uuid\",\"role\":\"authenticated\"}', true);
SELECT * FROM posts; -- This should only return allowed data

Social Login Configuration (The Tedious Part)

Each social provider requires OAuth app setup. Here's the configuration that actually works, plus the errors you'll hit:

Google OAuth Setup

1. Google Cloud Console → APIs & Services → Credentials
2. Create OAuth 2.0 Client ID for Web Application
3. Authorized redirect URIs: https://your-project.supabase.co/auth/v1/callback
4. Copy Client ID and Secret to Supabase Dashboard

GitHub OAuth Setup

1. GitHub Settings → Developer settings → OAuth Apps
2. Authorization callback URL: https://your-project.supabase.co/auth/v1/callback
3. Copy Client ID and Client Secret to Supabase

OAuth gotchas that will waste your afternoon

• Redirect URL case sensitivity → works in dev (localhost) but fails in prod (Localhost vs localhost)
• GitHub OAuth apps need approval for orgs → users can't sign in with work accounts
• Google OAuth requires verified domains for production → localhost works, your domain doesn't
• Forgot to enable provider in Supabase → auth flow completes but silently fails with no user session

The redirect URL pattern

https://[project-ref].supabase.co/auth/v1/callback

Production redirect handling

// pages/auth/callback.tsx - Handle OAuth redirects properly
import { useEffect } from 'react'
import { useRouter } from 'next/router'
import { supabase } from '@/lib/supabase'

export default function AuthCallback() {
  const router = useRouter()

  useEffect(() => {
    const handleAuthCallback = async () => {
      const { data, error } = await supabase.auth.getSession()
      
      if (error) {
        console.error('Auth error:', error)
        router.push('/login?error=auth_failed')
        return
      }

      if (data.session) {
        router.push('/dashboard')
      } else {
        router.push('/login')
      }
    }

    handleAuthCallback()
  }, [router])

  return <div>Authenticating...</div>
}

Email Configuration (Critical for Production)

Supabase's default email service works for development but breaks in production. Configure custom SMTP from day one or watch user registrations silently fail:

SMTP Configuration (Supabase Dashboard)

• Host: smtp.gmail.com (or your provider)
• Port: 587 (TLS) or 465 (SSL)
• Username: your-app@gmail.com
• Password: App-specific password (not your Gmail password)

Email Templates

Customize the default templates because they look terrible - use Supabase's template editor to modify confirmation emails and password reset flows. You'll want to match your brand and improve the default styling.

Email shit that breaks in production

• Gmail SMTP hits limits after 100 emails/day → your user signups silently stop working
• Confirmation emails land in spam → users blame your app for "not sending emails"
• Rate limiting during bulk imports → 100 user invites sent, 3 delivered
• No delivery tracking → emails fail for a week before anyone notices

Multi-Factor Authentication Setup

MFA implementation in Supabase Auth is straightforward but requires careful UX design:

// Enable MFA for a user
const { data, error } = await supabase.auth.mfa.enroll({
  factorType: 'totp',
  friendlyName: 'Your App Name'
})

// The QR code for authenticator apps
const qrCode = data.totp.qr_code
const secret = data.totp.secret

// Verify MFA setup
const { data, error } = await supabase.auth.mfa.verify({
  factorId: data.id,
  challengeId: data.totp.challenge_id,
  code: '123456' // User enters code from authenticator
})

// Challenge MFA during sign-in
const { data, error } = await supabase.auth.mfa.challenge({
  factorId: 'factor-uuid'
})

MFA UX patterns that work

• Optional during onboarding, required for sensitive actions
• Clear backup code generation and storage
• Account recovery flow for lost authenticator devices
• Progressive enhancement - don't force MFA on all users immediately

Production Monitoring and Debugging

Authentication failures are silent killers. Set up monitoring that actually helps:

Essential metrics to track

-- Failed login attempts (potential attacks)
SELECT COUNT(*), user_agent, ip_address
FROM auth.audit_log_entries
WHERE action = 'login' AND result = 'failure'
AND created_at > now() - interval '1 hour'
GROUP BY user_agent, ip_address
HAVING COUNT(*) > 10;

-- User registration trends
SELECT DATE(created_at) as date, COUNT(*) as signups
FROM auth.users
WHERE created_at > now() - interval '30 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- Authentication method distribution
SELECT 
  CASE 
    WHEN email IS NOT NULL THEN 'email'
    ELSE 'social'
  END as auth_method,
  COUNT(*) as users
FROM auth.users
GROUP BY auth_method;

Production issues that will ruin your day

1. JWT refresh loops: Token refresh fails → app gets stuck in infinite redirect loop
2. Silent email failures: SMTP provider blocks you → users can't sign up, no error logs
3. Safari cookie blocking: Third-party cookies disabled → embedded auth completely broken
4. CORS domain mismatch: Works on app.yoursite.com, fails on yoursite.com
5. Rate limit hell: 5 failed login attempts → user locked out for 24 hours

Error handling that doesn't suck

const handleAuthError = (error: AuthError) => {
  switch (error.message) {
    case 'Invalid login credentials':
      return 'Email or password is incorrect'
    case 'Email not confirmed':
      return 'Please check your email and click the confirmation link'
    case 'Signup not allowed for this instance':
      return 'Registration is currently disabled'
    default:
      console.error('Auth error:', error)
      return 'An error occurred. Please try again.'
  }
}

The difference between demo auth and production auth is error handling. Demos work perfectly. Production has users with invalid email formats, broken OAuth flows, expired domains, and edge cases you never considered. Budget 2 days for basic setup, 2 weeks for production edge cases.