Supabase Security Best Practices: Protect Your Database

-- Enable RLS on all tables
alter table posts enable row level security;
alter table profiles enable row level security;
alter table comments enable row level security;

-- Policy 1: Users can only read their own profile
create policy "Users can view own profile"
  on profiles for select
  using (auth.uid() = id);

-- Policy 2: Users can update their own profile
create policy "Users can update own profile"
  on profiles for update
  using (auth.uid() = id);

-- Policy 3: Public posts are readable by everyone
create policy "Public posts are viewable"
  on posts for select
  using (published = true);

-- Policy 4: Users can only insert posts as themselves
create policy "Users can create posts"
  on posts for insert
  with check (auth.uid() = user_id);

-- Policy 5: Users can only update/delete their own posts
create policy "Users can update own posts"
  on posts for update
  using (auth.uid() = user_id);

create policy "Users can delete own posts"
  on posts for delete
  using (auth.uid() = user_id);

-- Policy 6: Prevent users from modifying others' data
create policy "Prevent unauthorized modifications"
  on comments for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Advanced: Role-based access
create policy "Admins have full access"
  on posts for all
  using (
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role = 'admin'
    )
  );

-- Policy for multi-tenancy
create policy "Tenant isolation"
  on data for all
  using (
    tenant_id = (
      select tenant_id from profiles
      where id = auth.uid()
    )
  );

-- Verify RLS is enabled
select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by tablename;

# .env.example - Template for environment variables
VITE_SUPABASE_URL=your-project-url
VITE_SUPABASE_ANON_KEY=your-anon-key

# NEVER commit .env to version control!
# Add to .gitignore
.env
.env.local
.env.production

# API Key Security Practices

# 1. Use anon key for client-side (safe)
const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL,
  import.meta.env.VITE_SUPABASE_ANON_KEY
)

# 2. NEVER expose service_role key on client
# ❌ WRONG - Service role key on frontend
const supabase = createClient(url, SERVICE_ROLE_KEY) // Dangerous!

# 3. Use service_role only on backend
// backend/server.js
import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY, // Server-side only
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
)

# 4. Rotate keys if compromised
# Go to: Project Settings > API > Reset keys
# Update all applications immediately

# 5. Use environment-specific keys
# .env.development
VITE_SUPABASE_URL=https://dev-project.supabase.co
VITE_SUPABASE_ANON_KEY=dev-anon-key

# .env.production
VITE_SUPABASE_URL=https://prod-project.supabase.co
VITE_SUPABASE_ANON_KEY=prod-anon-key

# 6. Validate environment variables
if (!import.meta.env.VITE_SUPABASE_URL) {
  throw new Error('Missing VITE_SUPABASE_URL')
}

if (!import.meta.env.VITE_SUPABASE_ANON_KEY) {
  throw new Error('Missing VITE_SUPABASE_ANON_KEY')
}

# 7. Use secure headers
// Next.js middleware
export function middleware(request: NextRequest) {
  const response = NextResponse.next()
  
  // Security headers
  response.headers.set('X-Content-Type-Options', 'nosniff')
  response.headers.set('X-Frame-Options', 'DENY')
  response.headers.set('X-XSS-Protection', '1; mode=block')
  response.headers.set('Referrer-Policy', 'strict-origin-when-cross-origin')
  
  return response
}

// ❌ WRONG - Never build raw SQL queries
const userInput = request.body.username
const query = `SELECT * FROM users WHERE username = '${userInput}'`
// Vulnerable to: admin' OR '1'='1

// ✅ CORRECT - Use parameterized queries (Supabase does this automatically)
const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('username', userInput) // Automatically sanitized

// Input validation with Zod
import { z } from 'zod'

const postSchema = z.object({
  title: z.string().min(3).max(200),
  content: z.string().min(10).max(10000),
  published: z.boolean(),
})

async function createPost(data: unknown) {
  try {
    // Validate input
    const validated = postSchema.parse(data)
    
    // Safe to use
    const { data: post, error } = await supabase
      .from('posts')
      .insert(validated)
      .select()
      .single()
    
    return post
  } catch (error) {
    if (error instanceof z.ZodError) {
      throw new Error('Invalid input data')
    }
    throw error
  }
}

// Sanitize HTML content
import DOMPurify from 'isomorphic-dompurify'

function sanitizeContent(html: string): string {
  return DOMPurify.sanitize(html, {
    ALLOWED_TAGS: ['p', 'br', 'strong', 'em', 'u'],
    ALLOWED_ATTR: [],
  })
}

// Validate file uploads
function validateFile(file: File): boolean {
  const allowedTypes = ['image/jpeg', 'image/png', 'image/gif']
  const maxSize = 5 * 1024 * 1024 // 5MB
  
  if (!allowedTypes.includes(file.type)) {
    throw new Error('Invalid file type')
  }
  
  if (file.size > maxSize) {
    throw new Error('File too large')
  }
  
  return true
}

// Database-level constraints
-- SQL schema validation
create table posts (
  id uuid default gen_random_uuid() primary key,
  title text not null check (length(title) between 3 and 200),
  content text not null check (length(content) between 10 and 10000),
  email text check (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
  age int check (age >= 18 and age <= 120),
  status text check (status in ('draft', 'published', 'archived')),
  created_at timestamp with time zone default now()
);

// 1. Strong password requirements
// Configure in Supabase Dashboard > Authentication > Policies
// Minimum password length: 8 characters
// Require uppercase, lowercase, numbers, special characters

// Client-side validation
function validatePassword(password: string): boolean {
  const minLength = 8
  const hasUppercase = /[A-Z]/.test(password)
  const hasLowercase = /[a-z]/.test(password)
  const hasNumber = /[0-9]/.test(password)
  const hasSpecial = /[!@#$%^&*]/.test(password)
  
  return (
    password.length >= minLength &&
    hasUppercase &&
    hasLowercase &&
    hasNumber &&
    hasSpecial
  )
}

// 2. Email verification
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'SecurePassword123!',
  options: {
    emailRedirectTo: 'https://yourapp.com/verify',
  },
})

// 3. Multi-Factor Authentication (MFA)
// Enable MFA
const { data: { factors } } = await supabase.auth.mfa.enroll({
  factorType: 'totp',
})

// Verify MFA
const { data, error } = await supabase.auth.mfa.verify({
  factorId: factors[0].id,
  code: '123456',
})

// 4. Session management
// Set session timeout in Dashboard > Authentication > Settings
// JWT Expiry: 3600 seconds (1 hour)
// Refresh Token: 7 days

// Detect inactive sessions
let sessionTimeout: NodeJS.Timeout

function resetSessionTimer() {
  clearTimeout(sessionTimeout)
  sessionTimeout = setTimeout(() => {
    supabase.auth.signOut()
    alert('Session expired due to inactivity')
  }, 30 * 60 * 1000) // 30 minutes
}

window.addEventListener('mousemove', resetSessionTimer)
window.addEventListener('keypress', resetSessionTimer)

// 5. Prevent brute force attacks
// Rate limit login attempts
let loginAttempts = 0
let lockoutUntil: Date | null = null

async function login(email: string, password: string) {
  if (lockoutUntil && new Date() < lockoutUntil) {
    throw new Error('Too many attempts. Try again later.')
  }
  
  const { data, error } = await supabase.auth.signInWithPassword({
    email,
    password,
  })
  
  if (error) {
    loginAttempts++
    
    if (loginAttempts >= 5) {
      lockoutUntil = new Date(Date.now() + 15 * 60 * 1000) // 15 min
      throw new Error('Account locked. Try again in 15 minutes.')
    }
    
    throw error
  }
  
  loginAttempts = 0
  return data
}

// 6. Secure password reset
const { error } = await supabase.auth.resetPasswordForEmail(
  '[email protected]',
  {
    redirectTo: 'https://yourapp.com/reset-password',
  }
)

// 7. OAuth security
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: {
    redirectTo: 'https://yourapp.com/auth/callback',
    scopes: 'email profile', // Minimal required scopes
  },
})

// Edge Function rate limiting
// supabase/functions/api/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const rateLimitMap = new Map<string, { count: number; resetAt: number }>()

function checkRateLimit(ip: string, limit = 100, windowMs = 60000): boolean {
  const now = Date.now()
  const record = rateLimitMap.get(ip)
  
  if (!record || now > record.resetAt) {
    rateLimitMap.set(ip, { count: 1, resetAt: now + windowMs })
    return true
  }
  
  if (record.count >= limit) {
    return false
  }
  
  record.count++
  return true
}

serve(async (req) => {
  const ip = req.headers.get('x-forwarded-for') || 'unknown'
  
  if (!checkRateLimit(ip, 100, 60000)) {
    return new Response(
      JSON.stringify({ error: 'Too many requests' }),
      {
        status: 429,
        headers: {
          'Content-Type': 'application/json',
          'Retry-After': '60',
        },
      }
    )
  }
  
  // Process request
  return new Response(JSON.stringify({ success: true }))
})

// Client-side rate limiting
class RateLimiter {
  private requests: number[] = []
  
  constructor(
    private maxRequests: number,
    private windowMs: number
  ) {}
  
  async throttle<T>(fn: () => Promise<T>): Promise<T> {
    const now = Date.now()
    this.requests = this.requests.filter(time => now - time < this.windowMs)
    
    if (this.requests.length >= this.maxRequests) {
      const oldestRequest = this.requests[0]
      const waitTime = this.windowMs - (now - oldestRequest)
      await new Promise(resolve => setTimeout(resolve, waitTime))
    }
    
    this.requests.push(now)
    return fn()
  }
}

const limiter = new RateLimiter(10, 60000) // 10 requests per minute

// Usage
const data = await limiter.throttle(() =>
  supabase.from('posts').select('*')
)

// Database query rate limiting
-- Enable pg_cron for cleanup
create extension if not exists pg_cron;

-- Rate limit tracking table
create table request_logs (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id),
  endpoint text not null,
  created_at timestamp with time zone default now()
);

-- Function to check rate limit
create or replace function check_rate_limit(
  p_user_id uuid,
  p_endpoint text,
  p_max_requests int,
  p_window_seconds int
)
returns boolean as $$
declare
  request_count int;
begin
  select count(*)
  into request_count
  from request_logs
  where user_id = p_user_id
    and endpoint = p_endpoint
    and created_at > now() - (p_window_seconds || ' seconds')::interval;
  
  if request_count >= p_max_requests then
    return false;
  end if;
  
  insert into request_logs (user_id, endpoint)
  values (p_user_id, p_endpoint);
  
  return true;
end;
$$ language plpgsql;

-- Clean up old logs daily
select cron.schedule(
  'cleanup-request-logs',
  '0 0 * * *',
  $$delete from request_logs where created_at < now() - interval '7 days'$$
);

-- 1. Enable pgcrypto extension
create extension if not exists pgcrypto;

-- 2. Encrypt sensitive columns
create table user_secrets (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id),
  ssn text, -- Encrypted
  credit_card text, -- Encrypted
  created_at timestamp with time zone default now()
);

-- Function to encrypt data
create or replace function encrypt_data(data text, key text)
returns text as $$
begin
  return encode(
    pgp_sym_encrypt(data, key),
    'base64'
  );
end;
$$ language plpgsql;

-- Function to decrypt data
create or replace function decrypt_data(encrypted text, key text)
returns text as $$
begin
  return pgp_sym_decrypt(
    decode(encrypted, 'base64'),
    key
  );
end;
$$ language plpgsql;

-- Usage in application
// TypeScript client-side encryption
import CryptoJS from 'crypto-js'

const ENCRYPTION_KEY = process.env.ENCRYPTION_KEY!

function encryptData(data: string): string {
  return CryptoJS.AES.encrypt(data, ENCRYPTION_KEY).toString()
}

function decryptData(encrypted: string): string {
  const bytes = CryptoJS.AES.decrypt(encrypted, ENCRYPTION_KEY)
  return bytes.toString(CryptoJS.enc.Utf8)
}

// Save encrypted data
const encryptedSSN = encryptData('123-45-6789')
await supabase.from('user_secrets').insert({
  user_id: userId,
  ssn: encryptedSSN,
})

// Read and decrypt
const { data } = await supabase
  .from('user_secrets')
  .select('ssn')
  .eq('user_id', userId)
  .single()

const decryptedSSN = decryptData(data.ssn)

// 3. Hash passwords (Supabase does this automatically)
// Never store plain text passwords

-- 4. Anonymize PII for analytics
create table analytics_events (
  id uuid default gen_random_uuid() primary key,
  user_hash text, -- Hashed user ID
  event_type text,
  created_at timestamp with time zone default now()
);

-- Hash user IDs
insert into analytics_events (user_hash, event_type)
values (
  encode(digest(auth.uid()::text, 'sha256'), 'hex'),
  'page_view'
);

-- 5. Secure file storage
const { data, error } = await supabase.storage
  .from('private-documents')
  .upload(`${userId}/${filename}`, file, {
    cacheControl: '3600',
    upsert: false,
  })

-- Storage RLS policies
create policy "Users can only access own files"
  on storage.objects for select
  using (auth.uid()::text = (storage.foldername(name))[1]);