Upsert fails: new row violates row-level security policy

Postgres 15 · Supabase✓ code tested against a real database

TL;DR

upsert() becomes insert ... on conflict do update. For that, Postgres checks your UPDATE and SELECT policies as well as the INSERT one, and it does so even when the row is new. Add an UPDATE policy (with the same owner condition) and a SELECT policy, and grant update.

01 Why insert works and upsert doesn't

By default supabase.from('profiles').upsert(row) is sent as insert ... on conflict (id) do update. A statement that might update needs every policy it could use: INSERT for the new row, UPDATE for the existing one, and SELECT to read the row it conflicts with. If the UPDATE or SELECT policy is missing, Postgres refuses the statement up front with new row violates row-level security policy, even when the row doesn't exist yet.

02 The fix

migration.sql

create policy "owner inserts" on profiles for insert to authenticated
  with check (id = (select auth.uid()));

create policy "owner updates" on profiles for update to authenticated
  using (id = (select auth.uid()));

create policy "owner reads" on profiles for select to authenticated
  using (id = (select auth.uid()));

grant select, insert, update on profiles to authenticated;

Keep all three scoped to the same owner condition. A user still can't upsert a row with someone else's id: the INSERT check refuses it.

03 If you only ever insert

If the row should never be overwritten, don't use upsert(). Use insert() and handle the duplicate-key error (23505), or pass { ignoreDuplicates: true } so the request turns into on conflict do nothing.

04 Prove it

✗ only an INSERT policy, upsert of a new row → new row violates row-level security policy

✓ plain insert with the same policy → ok

✗ INSERT + UPDATE, no SELECT → still refused

✓ INSERT + UPDATE + SELECT → ok; another user's id → refused

◆ FREE · 30 SECONDS · NO SIGNUP

Is your app leaking right now?

Paste your live URL. The scanner reads your page and its JavaScript for a Supabase service_role key, the one leak that skips every policy in this guide.

◆ FREE · MIT · npm + GitHub Action

Catch this before it ships

airlock-rls is a CI gate that fails your build when a table ships exposed or a policy is permissive: the same class of bug, caught in CI against a database with your migrations applied, before it reaches prod.

npx airlock-rls→

Or start from nextjs-supabase-starter : auth + a table with RLS + an isolation test, so you start from a pattern that's already sealed.

FREE PDF

Grab the Supabase RLS cheat sheet

The golden rules, the footguns that leak in prod, correct policy snippets, and the isolation test — on one page.

FAQ

Why does Postgres need SELECT for an upsert?

To detect the conflict it has to look at the existing row, and that read goes through your SELECT policies. Without one, the upsert can't be checked, so it's refused.

Does this also apply to Storage uploads with upsert: true?

Yes. Replacing a file is an upsert on storage.objects, so it needs SELECT and UPDATE policies on top of the INSERT one.