I spent a day auditing a notes app that looked fine in the browser and was wide open at the API. It was a Next.js + Supabase project put together quickly: sign-up, a list of notes, edit by URL. Click around as a normal user and nothing seems wrong. Hit the REST endpoint with the public anon key and you get every note in the table.

The lesson I keep relearning, and the one I want this post to nail down: the security boundary is the database, not the page that happens to query it.

What “secure looking” usually means

The original page did something like this:

const { data } = await supabase.from('notes').select('*').eq('user_id', session.user.id);

That filter is correct for the happy path. It is not a security control. Anyone who can reach the PostgREST endpoint can omit the user_id filter, or set it to someone else’s id. The browser’s JavaScript is not in the attack path; the attacker talks to the API directly with the same public key your site already ships.

The same story showed up in three other places:

• Note pages loaded by id alone (/notes/41), with no ownership check.
• Inserts that sent user_id from the client, so a forged body could create a note under another account.
• A NEXT_PUBLIC_ service-role key used for “delete my account,” which Next.js helpfully copied into the client bundle. That key bypasses every policy you might add later.

Middleware that redirects signed-out visitors away from /notes does not fix any of this. Delete the middleware and the data must still be safe. Keep it for UX; do not lean on it for isolation.

Put ownership in Postgres

The fix that actually held was a migration, not more TypeScript. Roughly:

1. Enable Row Level Security on the table.
2. Revoke everything from anon. Signed-out visitors need nothing on this table.
3. Grant only select, insert, update, delete to authenticated. No truncate, no extras.
4. One owner-only policy per operation, each filtering on auth.uid().
5. Stop trusting the client for user_id. Default it to auth.uid(), make it NOT NULL, and foreign-key it to auth.users with ON DELETE CASCADE.

alter table public.notes enable row level security;

revoke all on public.notes from anon, authenticated;
grant select, insert, update, delete on public.notes to authenticated;

create policy "Users read their own notes"
  on public.notes for select to authenticated
  using ((select auth.uid()) = user_id);

create policy "Users create notes for themselves"
  on public.notes for insert to authenticated
  with check ((select auth.uid()) = user_id);

create policy "Users update their own notes"
  on public.notes for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy "Users delete their own notes"
  on public.notes for delete to authenticated
  using ((select auth.uid()) = user_id);

alter table public.notes
  alter column user_id set default auth.uid(),
  alter column user_id set not null,
  add constraint notes_user_id_fkey
    foreign key (user_id) references auth.users (id) on delete cascade;

A few details that matter more than they look:

• (select auth.uid()) once per statement, not auth.uid() bare in the policy. Postgres can treat the subquery as stable for the statement instead of re-evaluating per row.
• Update needs both using and with check. using picks which rows you may touch; with check stops you from handing a row to another user by changing user_id.
• The app no longer sends user_id. If the column has a default and the insert policy rejects any other value, the client has nothing useful to forge.
• Orphan rows get moved, not deleted. Rows with a null or dead owner went to a locked notes_orphaned table (RLS on, no policies) so a cleanup pass does not quietly destroy data you might still want to inspect.

Prove it without the UI

A green “Bob cannot open Alice’s note” click-test is nice. It is not enough. The bug that started this was an API call that never loaded a page:

# Public anon key only. No session.
curl "$SUPABASE_URL/rest/v1/notes?select=*" \
  -H "apikey: $ANON_KEY"

Before the migration that returned every row. After, it returns nothing useful for anon, and with Bob’s token it returns only Bob’s rows. I also assert the inverse from a second authenticated client: Bob’s token cannot read, update, or delete Alice’s note by id.

The app layer still helps. Pages that used to render someone else’s note now ask for a row the policy will not return, so they show “Note not found” — the same copy as a missing id. That avoids confirming that an id exists. Middleware still redirects signed-out visitors to login. None of that replaces the policies; the API-level tests keep working if you delete the middleware tomorrow.

Keep the service-role key off the wire

Supabase gives you two keys for a reason. The anon key is meant for browsers. The service role key skips RLS and can manage users. Prefixing it NEXT_PUBLIC_ is how it ends up in .next/static and, from there, in every visitor’s DevTools.

The rule I use now:

• Rename it to something without a public prefix (SUPABASE_SERVICE_ROLE_KEY).
• Import it only from a module guarded by server-only, so a client import fails the build.
• Use it for one job (here, deleting the caller’s own account from a Server Action that reads the id from the verified session), and nothing else.
• Rotate it if it was ever public. Moving the variable does not un-leak the old value.

Tradeoffs I accepted

• “Not found” instead of “forbidden” hides whether an id exists. Fine for private notes; wrong if you need to show “this exists but you cannot see it.”
• One policy per verb is more SQL than a single catch-all policy. I prefer it because each rule is readable and changeable without rewriting the others.
• Email confirmation on the free tier forced the default mailer and a PKCE code exchange. That is a product constraint, not a security one, but it shaped the auth callback. Custom SMTP would unlock token-hash links that work across browsers.
• Playwright against a local Supabase stack is slower than unit tests. It is the only way I trust that the policies, the grants, and the client wiring agree.

The checklist I want next time

1. Assume every .eq('user_id', …) in app code is optional until a policy enforces it.
2. RLS on before the table is useful. New Supabase tables often start with broad grants to anon and authenticated; revoke before you ship.
3. Ownership columns default from auth.uid() and are not writable from the client.
4. Middleware and Server Components are UX and shape, not the boundary.
5. Test with a raw API call using only the anon key and a second user’s token.
6. Never put the service-role key in anything the browser can download, and rotate if you already did.

None of this is specific to notes. It is the same shape for todos, documents, or any per-user row in Postgres behind a public API. The UI can be wrong or missing. The policies still have to hold.