03-supabase-database-security

name	03-supabase-database-security
description	Use when auditing Supabase/PostgreSQL security and reliability. Checks RLS, policies, anon vs service_role keys, tenant filters, sensitive data, backups, migrations, indexes, and connection pooling.

Supabase and Database Security

Mission

Treat the database as the vault. Supabase is powerful, but its auto-generated APIs and public anon key are only safe when Row Level Security and policies are correct. The goal is to prevent total data exposure, cross-tenant leakage, data loss, and serverless connection failures.

Use when

• The project uses Supabase, PostgreSQL, Prisma, Drizzle, SQLAlchemy, Neon, or serverless databases.
• The user asks about RLS, service role keys, anon keys, backups, migrations, slow queries, pooling, or tenant isolation.

Workflow

1. Inventory database access

rg -n 'createClient|supabase|service_role|anon|DATABASE_URL|postgres|prisma|drizzle|sqlalchemy|from\(|select\(|insert\(|update\(|delete\(' app pages src server db prisma supabase 2>/dev/null
find . -maxdepth 4 -type f \( -path '*migration*' -o -path '*supabase*' -o -path '*prisma*' -o -name '*.sql' \) 2>/dev/null | sort

Map every place that can read/write data.

2. Check key usage

Rules:

• anon_key may be public, but only if RLS policies are correct.
• service_role_key is root-like and must only exist server-side.
• service_role_key bypasses RLS; code using it must manually check permissions.

Search:

rg -n --hidden --glob '!node_modules' --glob '!.git' 'SERVICE_ROLE|service_role|SUPABASE_SERVICE|NEXT_PUBLIC_SUPABASE_SERVICE|anon|SUPABASE_ANON|createClient\(' .

P0 if service role appears in browser code or public env variables.

3. Verify RLS and policies

If SQL access is available, run:

select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
order by tablename;

select schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, policyname;

Required:

• RLS enabled on every table containing user/customer/tenant data.
• SELECT/INSERT/UPDATE/DELETE policies exist as needed.
• Policies filter by auth.uid() = user_id or tenant membership.
• Avoid broad authenticated policies that expose all rows.

4. Verify query-level tenant/user filtering

Even with RLS, server-side code should include explicit filters:

rg -n 'from\(["\''][a-zA-Z0-9_]+["\'']\)|select\(|eq\(|where:|findMany|findUnique|updateMany|deleteMany|organizationId|tenantId|user_id|userId' app pages src server 2>/dev/null

Red flags:

• select('*') on user data without .eq('user_id', ...) or tenant filter.
• Server Actions using service role without manual permission check.
• Admin dashboards reading all records without role verification.

5. Check backups and migration safety

Search migrations:

rg -n 'DROP TABLE|DROP COLUMN|TRUNCATE|DELETE FROM|ALTER TABLE|migrate|migration' prisma supabase db migrations . 2>/dev/null

Required:

• Backup before destructive changes.
• Migration tested in staging before production.
• Destructive changes performed in phases: add -> migrate -> verify -> drop.
• Restore process tested, not assumed.

6. Check indexes and connection pooling

Required indexes:

• user_id, organization_id/tenant_id, created_at, status fields, email, foreign keys used in filters.

For serverless apps using Supabase/Postgres:

• application connection string should use pooler port 6543 where applicable;
• direct port 5432 should be reserved for migrations/admin tasks.

Search:

rg -n '5432|6543|pool|DATABASE_URL|DIRECT_URL|connectionString' .env* package.json prisma supabase src app 2>/dev/null

7. Sensitive data review

Search schema and code for sensitive fields:

rg -n 'cpf|ssn|passport|document|credit|card|password|birth|address|phone|medical|health|gender|biometric|token' .

Rules:

• Never store raw credit card data.
• Prefer not storing CPF/document IDs unless essential.
• Passwords must be hashed by a proper auth provider or strong password hashing.
• Logs must not contain sensitive values.

Output contract

# Database Security Report

## Tables and data classes

## RLS/policy review

## Key usage review

## Query isolation review

## Backup/migration review

## Performance and pooling review

## Findings
| Severity | Evidence | Risk | Fix | Verification |
|---|---|---|---|---|

Stop criteria

Stop and report before any destructive migration, service role rotation, production RLS change, or operation that could lock out users or delete data.