Supabase Verification Guide

Status: Complete

Overview

This guide provides comprehensive testing and verification procedures to ensure your Supabase setup is working correctly, secure, and optimized for production use.

Quick Health Check

1. Service Status Verification

# For Local Development
docker-compose ps

# Expected output:
# NAME                 STATUS    PORTS
# supabase-db         running   0.0.0.0:54322->5432/tcp
# supabase-kong       running   0.0.0.0:54321->8000/tcp
# supabase-auth       running   0.0.0.0:54326->9999/tcp
# supabase-rest       running   0.0.0.0:54325->3000/tcp
# supabase-studio     running   0.0.0.0:54323->3000/tcp

2. API Gateway Test

# Test Kong is responding
curl -i http://localhost:54321/auth/v1/health

# Expected response:
# HTTP/1.1 200 OK
# {"healthy":true}

3. Database Connection

# Test PostgreSQL connection
docker exec supabase-db psql -U postgres -c "SELECT version();"

# Or with connection string
psql "postgresql://postgres:postgres@localhost:54322/postgres" -c "SELECT 1;"

Authentication Testing

1. User Registration

// test-auth.js
const { createClient } = require('@supabase/supabase-js')

const supabase = createClient(
  'http://localhost:54321',
  'your-anon-key'
)

async function testSignUp() {
  const { data, error } = await supabase.auth.signUp({
    email: 'test@example.com',
    password: 'TestPassword123!'
  })
  
  if (error) {
    console.error('❌ Sign up failed:', error.message)
    return false
  }
  
  console.log('✅ Sign up successful:', data.user?.email)
  return true
}

async function testSignIn() {
  const { data, error } = await supabase.auth.signInWithPassword({
    email: 'test@example.com',
    password: 'TestPassword123!'
  })
  
  if (error) {
    console.error('❌ Sign in failed:', error.message)
    return false
  }
  
  console.log('✅ Sign in successful:', data.session?.access_token ? 'Token received' : 'No token')
  return true
}

// Run tests
testSignUp().then(() => testSignIn())

2. Session Management

async function testSession() {
  // Get current session
  const { data: { session } } = await supabase.auth.getSession()
  console.log('Current session:', session ? 'Active' : 'None')
  
  // Refresh session
  const { data: { session: refreshed } } = await supabase.auth.refreshSession()
  console.log('Refreshed session:', refreshed ? 'Success' : 'Failed')
  
  // Sign out
  const { error } = await supabase.auth.signOut()
  console.log('Sign out:', error ? `Failed: ${error.message}` : 'Success')
}

3. OAuth Provider Test (if configured)

async function testOAuth() {
  const { data, error } = await supabase.auth.signInWithOAuth({
    provider: 'google',
    options: {
      redirectTo: 'http://localhost:3000/auth/callback'
    }
  })
  
  if (error) {
    console.error('❌ OAuth failed:', error.message)
  } else {
    console.log('✅ OAuth URL:', data.url)
  }
}

Database Testing

1. Table Creation and RLS

-- Create test table
CREATE TABLE test_items (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT NOT NULL,
    owner_id UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE test_items ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY "Users can manage own items" ON test_items
    FOR ALL USING (auth.uid() = owner_id);

-- Test RLS is working
-- This should return empty when not authenticated
SELECT * FROM test_items;

2. CRUD Operations

async function testCRUD() {
  const tableName = 'test_items'
  
  // CREATE
  const { data: created, error: createError } = await supabase
    .from(tableName)
    .insert({ name: 'Test Item' })
    .select()
    .single()
  
  console.log('Create:', createError ? `❌ ${createError.message}` : `✅ ID: ${created.id}`)
  
  if (!created) return
  
  // READ
  const { data: items, error: readError } = await supabase
    .from(tableName)
    .select('*')
  
  console.log('Read:', readError ? `❌ ${readError.message}` : `✅ Found ${items.length} items`)
  
  // UPDATE
  const { error: updateError } = await supabase
    .from(tableName)
    .update({ name: 'Updated Item' })
    .eq('id', created.id)
  
  console.log('Update:', updateError ? `❌ ${updateError.message}` : '✅ Success')
  
  // DELETE
  const { error: deleteError } = await supabase
    .from(tableName)
    .delete()
    .eq('id', created.id)
  
  console.log('Delete:', deleteError ? `❌ ${deleteError.message}` : '✅ Success')
}

3. RLS Policy Testing

async function testRLS() {
  // Test with anon key (should fail)
  const anonClient = createClient(url, anonKey)
  const { data: anonData, error: anonError } = await anonClient
    .from('protected_table')
    .select('*')
  
  console.log('Anon access:', anonError ? '✅ Blocked (expected)' : '❌ Not blocked')
  
  // Test with authenticated user
  await supabase.auth.signInWithPassword({
    email: 'user@example.com',
    password: 'password'
  })
  
  const { data: authData, error: authError } = await supabase
    .from('protected_table')
    .select('*')
  
  console.log('Auth access:', authError ? `❌ ${authError.message}` : '✅ Allowed')
  
  // Test with service role (bypasses RLS)
  const serviceClient = createClient(url, serviceRoleKey)
  const { data: serviceData, error: serviceError } = await serviceClient
    .from('protected_table')
    .select('*')
  
  console.log('Service role:', serviceError ? `❌ ${serviceError.message}` : '✅ Full access')
}

Real-time Testing

1. Channel Subscription

async function testRealtime() {
  // Subscribe to changes
  const channel = supabase
    .channel('test-channel')
    .on('postgres_changes', {
      event: '*',
      schema: 'public',
      table: 'test_items'
    }, (payload) => {
      console.log('Change received:', payload)
    })
    .subscribe((status) => {
      console.log('Subscription status:', status)
    })
  
  // Test insert (should trigger event)
  await supabase.from('test_items').insert({ name: 'Realtime Test' })
  
  // Clean up
  setTimeout(() => {
    supabase.removeChannel(channel)
    console.log('Channel removed')
  }, 5000)
}

2. Presence Testing

async function testPresence() {
  const channel = supabase.channel('presence-test')
  
  channel
    .on('presence', { event: 'sync' }, () => {
      const state = channel.presenceState()
      console.log('Presence state:', state)
    })
    .on('presence', { event: 'join' }, ({ key, newPresences }) => {
      console.log('User joined:', key)
    })
    .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
      console.log('User left:', key)
    })
    .subscribe(async (status) => {
      if (status === 'SUBSCRIBED') {
        await channel.track({
          user_id: 'user-1',
          online_at: new Date().toISOString()
        })
      }
    })
}

Storage Testing

1. Bucket Operations

async function testStorage() {
  const bucketName = 'test-bucket'
  
  // Create bucket (admin only)
  const { data: bucket, error: bucketError } = await supabase.storage
    .createBucket(bucketName, { public: true })
  
  console.log('Create bucket:', bucketError ? `❌ ${bucketError.message}` : '✅ Created')
  
  // List buckets
  const { data: buckets, error: listError } = await supabase.storage
    .listBuckets()
  
  console.log('List buckets:', listError ? `❌ ${listError.message}` : `✅ Found ${buckets.length}`)
}

2. File Operations

async function testFileOperations() {
  const bucket = 'avatars'
  const file = new File(['test content'], 'test.txt', { type: 'text/plain' })
  
  // Upload file
  const { data: upload, error: uploadError } = await supabase.storage
    .from(bucket)
    .upload('test/test.txt', file)
  
  console.log('Upload:', uploadError ? `❌ ${uploadError.message}` : `✅ ${upload.path}`)
  
  // Get public URL
  const { data: urlData } = supabase.storage
    .from(bucket)
    .getPublicUrl('test/test.txt')
  
  console.log('Public URL:', urlData.publicUrl)
  
  // Download file
  const { data: download, error: downloadError } = await supabase.storage
    .from(bucket)
    .download('test/test.txt')
  
  console.log('Download:', downloadError ? `❌ ${downloadError.message}` : '✅ Success')
  
  // Delete file
  const { error: deleteError } = await supabase.storage
    .from(bucket)
    .remove(['test/test.txt'])
  
  console.log('Delete:', deleteError ? `❌ ${deleteError.message}` : '✅ Success')
}

Performance Testing

1. Query Performance

-- Enable query timing
\timing on

-- Test query performance
EXPLAIN ANALYZE
SELECT * FROM large_table
WHERE organization_id = 'uuid-here'
ORDER BY created_at DESC
LIMIT 100;

-- Check indexes are being used
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

2. Connection Pool Testing

async function testConnectionPool() {
  const promises = []
  const concurrentRequests = 50
  
  console.time('Connection pool test')
  
  for (let i = 0; i < concurrentRequests; i++) {
    promises.push(
      supabase.from('test_table').select('*').limit(1)
    )
  }
  
  const results = await Promise.allSettled(promises)
  const successful = results.filter(r => r.status === 'fulfilled').length
  const failed = results.filter(r => r.status === 'rejected').length
  
  console.timeEnd('Connection pool test')
  console.log(`Results: ${successful} successful, ${failed} failed`)
}

3. Load Testing

# Install k6
brew install k6

# Create load test script
cat > load-test.js << 'EOF'
import http from 'k6/http';
import { check } from 'k6';

export let options = {
  stages: [
    { duration: '30s', target: 20 },
    { duration: '1m', target: 20 },
    { duration: '30s', target: 0 },
  ],
};

export default function () {
  let response = http.get('http://localhost:54321/rest/v1/test_table', {
    headers: {
      'apikey': 'your-anon-key',
      'Authorization': 'Bearer your-anon-key',
    },
  });
  
  check(response, {
    'status is 200': (r) => r.status === 200,
    'response time < 500ms': (r) => r.timings.duration < 500,
  });
}
EOF

# Run load test
k6 run load-test.js

Security Testing

1. RLS Policy Verification

async function verifyRLSPolicies() {
  const tests = [
    {
      name: 'User cannot see other org data',
      table: 'projects',
      shouldFail: true,
      filter: { organization_id: 'other-org-id' }
    },
    {
      name: 'User can see own org data',
      table: 'projects',
      shouldFail: false,
      filter: { organization_id: 'user-org-id' }
    },
    {
      name: 'Anon cannot access protected table',
      table: 'sensitive_data',
      shouldFail: true,
      useAnonKey: true
    }
  ]
  
  for (const test of tests) {
    const client = test.useAnonKey ? anonClient : supabase
    const { data, error } = await client
      .from(test.table)
      .select('*')
      .match(test.filter || {})
    
    const passed = test.shouldFail ? !!error : !error
    console.log(`${passed ? '✅' : '❌'} ${test.name}`)
  }
}

2. SQL Injection Testing

async function testSQLInjection() {
  const maliciousInputs = [
    "'; DROP TABLE users; --",
    "1' OR '1'='1",
    "admin'--",
    "1; UPDATE users SET role='admin'",
  ]
  
  for (const input of maliciousInputs) {
    try {
      const { data, error } = await supabase
        .from('test_table')
        .select('*')
        .eq('name', input)
      
      console.log(`✅ Protected against: ${input.substring(0, 20)}...`)
    } catch (e) {
      console.log(`⚠️  Potential issue with: ${input.substring(0, 20)}...`)
    }
  }
}

3. API Key Security

# Verify keys are not exposed
echo "Checking for exposed keys..."

# Check environment variables
env | grep -E "KEY|SECRET|PASSWORD" | wc -l

# Check source code
grep -r "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9" --exclude-dir=node_modules .

# Check git history
git log -p | grep -E "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9"

Monitoring Setup

1. Health Check Endpoint

// api/health/route.js
export async function GET() {
  const checks = {
    database: false,
    auth: false,
    storage: false,
    realtime: false
  }
  
  try {
    // Check database
    const { error: dbError } = await supabase.from('health_check').select('1')
    checks.database = !dbError
    
    // Check auth
    const { error: authError } = await supabase.auth.getSession()
    checks.auth = !authError
    
    // Check storage
    const { error: storageError } = await supabase.storage.listBuckets()
    checks.storage = !storageError
    
    // Overall status
    const healthy = Object.values(checks).every(v => v)
    
    return Response.json({
      status: healthy ? 'healthy' : 'degraded',
      checks,
      timestamp: new Date().toISOString()
    }, {
      status: healthy ? 200 : 503
    })
  } catch (error) {
    return Response.json({
      status: 'error',
      error: error.message
    }, { status: 500 })
  }
}

2. Automated Testing Script

#!/bin/bash
# test-supabase.sh

echo "🔍 Starting Supabase verification..."

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
NC='\033[0m'

# Test functions
test_service() {
    local name=$1
    local url=$2
    
    if curl -s -o /dev/null -w "%{http_code}" "$url" | grep -q "200\|401"; then
        echo -e "${GREEN}✅ $name is running${NC}"
        return 0
    else
        echo -e "${RED}❌ $name is not responding${NC}"
        return 1
    fi
}

# Run tests
test_service "Kong Gateway" "http://localhost:54321/auth/v1/health"
test_service "PostgREST" "http://localhost:54325/"
test_service "GoTrue Auth" "http://localhost:54326/health"
test_service "Studio" "http://localhost:54323"

# Database test
if docker exec supabase-db pg_isready -U postgres > /dev/null 2>&1; then
    echo -e "${GREEN}✅ PostgreSQL is ready${NC}"
else
    echo -e "${RED}❌ PostgreSQL is not ready${NC}"
fi

echo "✨ Verification complete!"

Troubleshooting Common Issues

Issue: "relation does not exist"

-- Check if table exists
SELECT * FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name = 'your_table';

-- Check current schema
SHOW search_path;

-- Set search path
SET search_path TO public, auth, storage;

Issue: "permission denied for schema public"

-- Grant permissions
GRANT ALL ON SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO postgres, anon, authenticated, service_role;

Issue: RLS policies blocking access

// Temporarily bypass RLS for debugging
const adminClient = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  }
)

// Test with admin client
const { data, error } = await adminClient
  .from('protected_table')
  .select('*')

console.log('Admin access:', { data, error })

Production Readiness Checklist

Essential Checks

All services responding to health checks
Authentication flow working (signup, signin, signout)
RLS policies tested and verified
Database migrations applied successfully
Storage buckets configured with proper policies
Real-time subscriptions working
API rate limiting configured
SSL/TLS enabled (production)
Backup strategy in place
Monitoring and alerting configured

Performance Checks

Database indexes created
Query performance acceptable (<100ms for common queries)
Connection pooling configured
Cache headers set appropriately
CDN configured for static assets

Security Checks

All tables have RLS enabled
Service role key not exposed
CORS configured correctly
SQL injection protection verified
API keys rotated from defaults
Audit logging enabled

Continuous Verification

Daily Checks

Service health status
Error rate monitoring
Performance metrics

Weekly Checks

Security audit
Backup verification
Usage analysis

Monthly Checks

Full system test
Disaster recovery drill
Performance optimization review

Next Steps

Automate verification with CI/CD
Set up monitoring dashboards
Create runbooks for common issues
Document custom policies and configurations
Plan regular security audits