RLS wasn’t enough: what I learned locking down a Supabase app

I’m a solo founder building Ember (Next.js + Supabase) with AI coding tools. Last week I did a security pass on my database. Here’s what I found:

1. Row-level security checks which rows, not which columns.
My users table let a logged-in user update their own row. That sounds fine, until you notice “their own row” includes the column that stores their plan. Fix: revoke table-wide UPDATE and grant it only on the columns the app really needs (streaks, achievements).

2. Policies combine with OR.
An old open policy beats a new strict one. Audit all policies per table and drop the permissive ones.

3. Public pages need a narrow door.
Shared roast links must be readable by logged-out visitors, but the table is owner-only. Solution: a SECURITY DEFINER function that looks up by a random share code and returns only public fields, with a fixed search_path and EXECUTE granted to anon and authenticated.

4. Test as the real roles.
I run tests inside a single DO block that sets role anon or authenticated, checks results, and then raises an exception so everything rolls back. No test data left behind.

Biggest lesson: “it works in the app” proves nothing about what a logged-in user can do through the API.

Curious how others test their RLS policies. What’s your setup?