Supabase Security Audit of an AI-Built Client Portal

Context My own client portal (Next.js 16, Supabase, Stripe, Resend) is live in production: clients log in, see their projects, pay invoices, and download files only after payment. It was built with Claude Code. Row Level Security was in place and had already passed 10 manual checks, including cross-account ID-swap tests and expired signed URLs. The code looked safe.

What I did

Inventoried every table, policy, function and trigger from the migrations and app code.

Ran six read-only SQL queries directly against the production database and compared the results with the migrations, instead of trusting the code alone.

What I found • Leftover default privileges. The authenticated role held TRUNCATE, TRIGGER, REFERENCES and MAINTAIN on three tables. TRUNCATE bypasses RLS. It wasn't reachable through the API, but it broke least privilege, and Supabase's default privileges would re-grant it on every new table. • Drift between production and source control. A SECURITY DEFINER function existed in production but not in the migrations. It had been created by a dashboard setting and was executable by anonymous users. • One layer guarding admin access. Only a single RLS policy stopped a client from changing their own role to admin.

The fix • Mapped exactly which operations the app performs, revoked everything from anon and authenticated, and re-granted only those, including column-level INSERT/UPDATE. 28 privilege rows → 11. • Revoked execute on the undocumented function while keeping its feature working. • Added a trigger that blocks role changes from client sessions. Escalation is now blocked by privileges, RLS and a trigger. • Wrote rollback SQL before touching production, applied the migration in a single transaction, then re-verified with read-only queries and tested every user flow as both client and admin.

What this shows about AI-built apps AI tools write working RLS quickly, but they don't check what the platform adds by default or what's been changed through the dashboard. In the same week, an AI agent tried to add 12 exclusions to a package-manager safety setting just to force an upgrade. I rolled it back. I use AI to build fast, and a human review of the live system to make it safe.

Stack: Supabase (Postgres, RLS, Storage) · Next.js · Stripe · SQL · Claude Code