One of my apps had a textbook-correct RLS policy on its employees table. Every query was scoped to the caller's organization, exactly as designed. And any member of that organization could read every colleague's PIN hash and base salary.

Nothing about the policy was wrong. That's the point.

I build Supabase apps solo — an ERP, an e-invoicing product, a couple of SaaS side projects — with Claude Code doing a lot of the typing. Every one of them had RLS enabled on every table from day one, which is where most security checklists (and most vibe-coded apps) stop. Then I started impersonating my own users in raw SQL, and I found four real holes across apps I would have told you were locked down.

None of them were RLS bugs. All of them live in the layer under RLS that most tutorials barely mention: Postgres grants.

The mental model that was missing

Postgres access control is (at least) three layers, and they answer different questions:

Grants — can this role touch this table, this column, this function at all?
RLS policies — of the rows in a table the role can touch, which ones can it see or change?
security definer functions — deliberate doors through both layers, running as the function's owner.

Here's the part that bites: Supabase's defaults are generous at layer 1. Historically, every new table got broad privileges for the authenticated role, and every new function gets EXECUTE granted to anon and authenticated — automatically, via default privileges. (Supabase has been rolling out explicit-grants-required for new tables through 2026, which is a real improvement — but function defaults remain, and every existing project keeps the old behavior for now.)

So the mental model "everything is denied until I open it" is wrong. It's closer to "everything is open at the grant layer, and RLS is the only thing standing in front of it." Once you internalize that, the four holes below are obvious. I still had to find each one the hard way.

Hole 1: RLS filters rows. It never hides columns.

The PIN-hash leak. The policy said: members see their organization's employees. Correct. But a policy is a row filter — every column of every visible row comes along for the ride. pin_hash, base_salary, everything.

There is no such thing as a column-level RLS policy. Column visibility is a grant-layer job:

-- Hide sensitive columns from the client roles
revoke select on public.employees from authenticated;
grant select (id, org_id, full_name, role, created_at)
  on public.employees to authenticated;

Two traps inside the fix:

You can't subtract a column. revoke select (pin_hash) on employees from authenticated does nothing useful if the role holds a table-level SELECT — column revokes don't override table grants. You have to revoke the whole table, then grant back the allowed column list. Additive only.
returning * will start erroring. supabase-js's .insert(...).select() and .update(...).select() with no arguments compile to RETURNING *, which now touches ungranted columns. Pass explicit columns to .select() on writes, or those code paths break.

If a column must never reach a browser — password hashes, salary, tokens — either grant around it like above, or move it to a separate table only service_role can read. RLS alone will never do it.

Hole 2: Revoking from `public` doesn't revoke from your users

I have a security definer function that allocates official, gapless invoice numbers — the kind a tax authority expects. It's meant to be called exactly once per invoice, by the server, at issue time. The migration dutifully revoked it from public.

Any logged-in user could still call it. From the browser console. select allocate_invoice_number(...) — and burn official sequence numbers, as many as they liked.

Why: in Supabase, new functions get EXECUTE granted directly to anon and authenticated through default privileges. Revoking from public removes the generic fallback grant but doesn't touch those direct grants. The fix is boring — revoke by name:

revoke all on function allocate_invoice_number(uuid)
  from public, anon, authenticated;
grant execute on function allocate_invoice_number(uuid) to service_role;

This one radicalized me, because security definer functions have no RLS of their own — they run as their owner (usually postgres), through every policy. The explicit role revoke is the only lock on that door. Every migration I write now ends server-only functions with that exact two-liner, and user-callable RPCs get the same treatment plus a deliberate grant execute ... to authenticated — after an ownership check inside the function body.

Hole 3: Clients can write tables you think are server-only

Every real app grows tables the browser should read but never write: event logs, counters, job queues, audit trails. Mine has an invoice_events table — the append-only history a compliance auditor would look at.

On a default-privileges project, authenticated holds INSERT, UPDATE, and DELETE on that table the moment it's created. Your API routes politely go through the proper code path; a curious user with the anon key and their own JWT doesn't have to. RLS can help — if you remembered restrictive write policies — but the grant layer shouldn't be open in the first place:

-- Clients may read (RLS scopes the rows); only the server may write
revoke insert, update, delete on public.invoice_events
  from anon, authenticated;

The pattern that fell out of my audits, for anything stateful and important:

Reads: grant SELECT to clients, let RLS scope rows.
User-triggered mutations: a security definer RPC, granted to authenticated, that ownership-checks before acting:

create function send_invoice(p_invoice_id uuid) returns void
language plpgsql security definer set search_path = public as $$
begin
  if not exists (
    select 1 from invoices i
    where i.id = p_invoice_id
      and i.business_id = current_business_id()  -- caller's tenant, from the JWT
  ) then
    raise exception 'invoice not found';
  end if;
  -- state transition + event insert happen here, as the owner
end $$;

revoke all on function send_invoice(uuid) from public, anon, authenticated;
grant execute on function send_invoice(uuid) to authenticated;  -- deliberate

Worker mutations (cron, webhooks): same shape, but granted to service_role only.

The table itself stays unwritable by clients no matter what bugs live in the app layer. That's the whole idea: the database enforces the invariant, so the TypeScript doesn't have to be perfect.

Hole 4: "It returned an empty array" proves almost nothing

The way everyone tests their security: query a protected table with the anon key, get [], declare victory.

Here's what [] actually means: the role holds a SELECT grant on that table, and RLS filtered the rows. If the grant were missing you'd get a 42501 permission denied error instead. So your reassuring empty array is really saying: the grant layer is wide open; the only thing between anon and this data is your policy logic. Delete a policy in some future migration, or write one with a bad using clause, and there's no second lock waiting behind it.

(Bonus illusion: a deny-all table returns [] for the publishable key and for the service-role key. A quick curl test can't even tell you which key you're holding.)

Stop asking the API and ask the catalog — it answers exactly and instantly, with no row data involved:

select has_table_privilege('anon', 'public.invoices', 'select');
select has_column_privilege('authenticated', 'public.employees', 'pin_hash', 'select');
select proname
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
  and has_function_privilege('authenticated', p.oid, 'execute');

That last query — "which functions can a logged-in user execute?" — is the one that exposed my invoice-numbering hole. Run it on your project. The list is longer than you think.

The 30-second audit that found all of this

You don't need a pentest or even a second account. Postgres will happily become one of your users inside a transaction you then throw away:

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<some-real-user-uuid>","role":"authenticated"}';

-- Now every query runs exactly as that user, through RLS and grants:
select * from employees;                      -- which rows AND columns come back?
select * from invoice_events;                  -- can I read someone else's history?
insert into invoice_events (...) values (...); -- can I forge history?
select allocate_invoice_number('<some-id>');   -- can I call server-only functions?

rollback;  -- nothing happened

set local scopes everything to the transaction, and request.jwt.claims is the same GUC PostgREST sets — so auth.uid(), your current_business_id() helper, and every policy behave exactly as in production. Swap the sub for a user from a different tenant and try to read the first tenant's rows. Ten minutes of this, per app, is how every hole in this post was found.

And because it's plain SQL, it freezes into CI: a test file that connects with pg, seeds two users in two tenants, runs the same begin/impersonate/assert/rollback dance, and fails the build if tenant B can ever see tenant A's rows — or call a function they shouldn't. Mine run on every push. RLS regressions are migrations away, always; the test is what makes the lock permanent.

The checklist I actually use now

Every migration that creates something, before it ships:

New table: explicit grant list + enable row level security + policies, written together, every time.
Sensitive column: table-level revoke, column-list grant back — or a service_role-only side table.
New function: ends with revoke all ... from public, anon, authenticated, then a deliberate grant to exactly the role that should call it.
security definer function: ownership check inside, set search_path = public, and remember it has no RLS — the grant is the security.
Server-only tables (events, counters, queues): revoke client writes at the grant layer, mutate through definer RPCs.
Writes in supabase-js: explicit columns in .select() after .insert()/.update() — no returning *.
Before merge: the role-simulation snippet against a real user id, plus the three catalog queries.
The cross-tenant version of that snippet lives in CI and fails loudly.

RLS is the right foundation and you should absolutely have it on every table. But it's one lock on a door that has three. The other two are quieter, they're open by default, and no amount of staring at your policies will show you that — impersonating your own users will.