Supabase upsert new row violates row-level security fix

You call supabase.from('profiles').upsert(...) and get 42501 with new row violates row-level security policy for table "profiles", while the same row goes in fine with insert. Or the message ends in (USING expression), which a plain insert or update never shows. The cause is that an upsert is INSERT ... ON CONFLICT DO UPDATE, and Postgres checks grants and policies for an insert, an update and a select on it, all in one statement.

This guide lists each upsert error, what causes it and the tested fix. Then it covers the policy set that makes upserts work without letting one user overwrite another user's row. The general difference between permission denied and new row violates is in 42501 permission denied vs new row violates, and is not repeated here. File uploads with upload(..., { upsert: true }) in Supabase Storage hit the same rules on storage.objects, covered in Supabase Storage upload new row violates RLS.

Tested on October 26, 2026. We ran PostgreSQL 17.11 behind PostgREST 16.5 (the API layer Supabase uses) with @supabase/supabase-js 2.109.0 and Supabase style anon and authenticated roles, no default grants, and auth.uid() read from the JWT. That came to 49 checks, all passing. Every claim below is either something we reproduced or a quote from the docs linked at the end. VibeLocked is independent and not affiliated with Supabase.

The short version

1. Grants: the role needs INSERT, UPDATE and SELECT on the table. Without UPDATE, even a brand new row fails with permission denied. With column grants, SELECT must cover the conflict column and every column you send.
2. Policies: an upsert needs an INSERT policy, an UPDATE policy and a SELECT policy for the role. Missing any one of them breaks it, even when you never call .select().
3. Errors, not silence: a plain update skips rows that fail USING. An upsert that hits such a row throws new row violates row-level security policy (USING expression).
4. onConflict must name columns with a unique constraint, or you get 42P10.
5. Do not fix it with using (true). In our test that let one user overwrite another user's row with a 200 response.
6. Bulk upserts overwrite missing keys with NULL or the column default.

Why upsert needs more than insert

supabase-js turns .upsert() into a POST with Prefer: resolution=merge-duplicates. In our Postgres statement log, PostgREST ran it as INSERT INTO profiles (id, username) ... ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id, username = EXCLUDED.username. The supabase-js upsert reference puts it this way: it does "the equivalent of .insert() if a row with the corresponding onConflict columns doesn't exist," or, if it does exist, an "alternative action depending on ignoreDuplicates."

PostgreSQL then applies all of this. Every quote below is from the CREATE POLICY docs:

• The INSERT policy runs on every row you send, even ones that will update. "Note that an INSERT with an ON CONFLICT DO NOTHING/UPDATE clause will check the INSERT policies' WITH CHECK expressions for all rows proposed for insertion, regardless of whether or not they end up being inserted."
• The SELECT policies run too, with or without .select(). "If an INSERT has an ON CONFLICT DO UPDATE clause, or an ON CONFLICT DO NOTHING clause with an arbiter index or constraint specification, then SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies."
• On the update path, the existing row and the new row are checked. "The row to be updated is first checked against the USING expressions of any UPDATE policies, and then the new updated row is checked against the WITH CHECK expressions." The SELECT policies also apply to both rows, and "an auxiliary UPDATE is never silently avoided."
• A failing USING is an error here. "unlike a standalone UPDATE command, if the existing row does not pass the USING expressions, an error will be thrown (the UPDATE path will never be silently avoided)."

That last point is why upsert errors look different. A normal .update() on a row you cannot touch returns no error and changes nothing, as the 42501 guide shows. An upsert that lands on that row fails loudly.

Decision table: upsert error to fix

These are the exact messages supabase-js returned in our test. Signed-in requests got HTTP 403 for 42501 errors. PostgREST's error docs map 42501 to 401 for anonymous requests. A successful upsert returned 201 when it inserted and 200 when it updated.

error.message (code)	What caused it in our test	Fix
permission denied for table profiles (42501)	Missing INSERT, UPDATE or SELECT grant. With SELECT and INSERT but no UPDATE, the upsert of a brand new row still failed while a plain insert worked. Also a column SELECT grant that did not cover a column we sent	Grant select, insert, update to the role. With column grants, include the conflict column and every column the upsert sends
new row violates row-level security policy for table "profiles" (42501)	No INSERT policy (even when the row already existed), no SELECT policy (even without .select()), or the row you sent fails the INSERT with check, such as another user's id or a user_id that became NULL in a bulk upsert	Add owner INSERT and SELECT policies. Let user_id default to auth.uid() and send the same keys in every object
new row violates row-level security policy (USING expression) for table "profiles" (42501)	The row existed, so Postgres took the UPDATE path, and the existing row failed the UPDATE using (no UPDATE policy) or a SELECT policy (the row belongs to someone else)	Add an owner UPDATE policy. If the row belongs to another user, RLS is working: fix the key your app sends, do not loosen the policy
there is no unique or exclusion constraint matching the ON CONFLICT specification (42P10, HTTP 400)	onConflict: 'user_id' on a column without a unique constraint	Add a unique constraint or unique index on that column
duplicate key value violates unique constraint "settings_user_id_key" (23505, HTTP 409)	Unique user_id exists, but onConflict was left out, so the upsert used the primary key	Pass onConflict: 'user_id'
null value in column "done" of relation "todos" violates not-null constraint (23502, HTTP 400)	Bulk upsert where one object had no done key, so it became NULL	Send every key in every object, or defaultToNull: false
permission denied for sequence todos_id_seq (42501)	Bulk upsert with defaultToNull: false on a table whose id is generated by default as identity, with no sequence grant	grant usage, select on sequence public.todos_id_seq to authenticated, or send the same keys in every object instead

To list the grants and policies on one table, use the read-only diagnostic query in the 42501 guide.

Grants an upsert needs

The PostgreSQL INSERT docs list the privileges: "You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also required." And: "all forms of ON CONFLICT also require SELECT privilege on any column whose values are read. This includes any column mentioned in conflict_target (including columns referred to by the arbiter constraint), and any column mentioned in an ON CONFLICT DO UPDATE expression."

What that meant in our run, signed in, with correct owner policies on every check:

Grants on profiles	Upsert result
None	403 permission denied for table profiles
select, insert (no update)	403 permission denied, even for a new row. A plain insert of the same row returned 201
insert, update (no select)	403 permission denied
insert, update plus select (id) only	403 permission denied, because the DO UPDATE reads EXCLUDED.username
insert, update plus select (id, username)	201 for a new row, 200 for an existing one. Adding bio to the payload failed again
select, insert, update	Works

From Supabase's October 30, 2026 change, new tables no longer get Data API grants by default, so this is the first thing to check on a new table. The grant itself is one line:

grant select, insert, update on table public.profiles to authenticated;

Grant to authenticated, not anon, unless signed-out users really should write. If some columns must stay read only, such as plan or credits, use column grants, as shown in Supabase column-level security. The upsert then works only while it sends the allowed columns.

Policies an upsert needs

We removed one policy at a time from a correct owner set on profiles (id uuid primary key default auth.uid()). All requests were signed in with full grants:

Policies present	Upsert a new row	Upsert your existing row
SELECT, INSERT, UPDATE (owner)	201, inserted	200, updated
SELECT, INSERT (no UPDATE)	201, inserted	403 ... (USING expression)
SELECT, UPDATE (no INSERT)	403 new row violates	403 new row violates, while a plain update worked
INSERT, UPDATE (no SELECT)	403 new row violates, while a plain insert worked	403 new row violates

The middle two rows catch most people. Without an INSERT policy, even updating a row that already exists fails, because the INSERT with check runs first on the proposed row. Without a SELECT policy, the upsert fails with or without .select(), which surprises anyone who only knows that insert().select() needs read access. Supabase's RLS guide already warns that "To perform an UPDATE operation, a corresponding SELECT policy is required." An upsert needs it on the insert path too.

A correct policy set

This is the set our checks passed with. It works for a profiles table keyed by the user id, and for a todos style table with its own id and a user_id column:

-- Profiles: one row per user, keyed by the user id
alter table public.profiles enable row level security;
grant select, insert, update on table public.profiles to authenticated;

create policy "read own profile" on public.profiles
  for select to authenticated
  using ((select auth.uid()) = id);

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

create policy "update own profile" on public.profiles
  for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

-- Todos: many rows per user, owner in user_id
alter table public.todos enable row level security;
grant select, insert, update on table public.todos to authenticated;

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

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

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

A single for all policy with the same owner check in using and with check also worked for upserts in our test, on a settings table. Separate policies per command are easier to review, and they keep DELETE out unless you add it on purpose. Your profiles can be public: replace the read policy with using (true) for reads only and keep the INSERT and UPDATE owner checks. In our test that still blocked a user from upserting another user's profile.

On the client, let the database fill in ownership. With id defaulting to auth.uid(), supabase.from('profiles').upsert({ username }) with no id updated the caller's own row in our test. For todos, user_id uuid not null default auth.uid() plus the owner policies means a client cannot claim someone else's row by sending their id.

The overwrite risk: what a permissive upsert allows

When an upsert fails, AI coding tools often suggest policies that make the error disappear. These are the ones we tested, with Alice signed in and Bob's rows already in the table:

Policies	Alice's upsert	Result
Owner SELECT, INSERT, UPDATE	{ id: bob_id, username: 'pwned' } on profiles	403 new row violates, Bob unchanged
Public read, owner INSERT and UPDATE	Same	403 new row violates, Bob unchanged
SELECT, INSERT, UPDATE all true	Same, plus a new bio	200, Bob's profile overwritten
Public read, owner INSERT, UPDATE using (true) with owner with check	{ id: 2, user_id: alice_id, title: 'stolen' } on Bob's todo 2	200, Bob's todo now belongs to Alice
Same, but owner-only SELECT policy	Same	403 (USING expression), Bob's todo intact
Owner SELECT, INSERT, UPDATE	Same	403 (USING expression), Bob's todo intact

The fourth row is the subtle one. The INSERT check passes because Alice sends her own user_id. The UPDATE with check passes because the new row has her user_id. Only the UPDATE using decides whose rows can be touched, and true lets her touch Bob's. Owner-only SELECT policies happened to block it as well, because the existing row must pass them. Do not rely on that, since many apps make reads public. Put the owner check in the UPDATE using.

WITH CHECK on the updated row

The UPDATE with check is what stops a user from moving their own row to someone else. With owner policies, Alice upserting her own todo with user_id: bob_id got 403 new row violates and the row stayed hers. We also tried an UPDATE policy with with check (true) and public reads. The upsert was still blocked, because the INSERT with check runs on the proposed row too. A plain .update({ user_id: bob_id }) with the same policies returned 204 and gave the row to Bob. So with check (true) is still a hole, just not through upsert. More on this pattern in RLS on but using (true) and IDOR in Next.js and Supabase.

onConflict needs a unique constraint

PostgREST's upsert docs say that "By default, upsert operates based on the primary key columns," and that on_conflict makes "upsert work on a column(s) that has a UNIQUE constraint." PostgreSQL picks the arbiter from unique indexes that match the conflict target, as described under conflict_target in the INSERT docs.

On a settings table with an identity id primary key and a user_id column:

• onConflict: 'user_id' with no unique constraint returned 400, code 42P10, there is no unique or exclusion constraint matching the ON CONFLICT specification.
• Leaving onConflict out returned 201 and inserted a second settings row for the same user, because the conflict target was the primary key, which the client never sent.
• After alter table public.settings add constraint settings_user_id_key unique (user_id);, onConflict: 'user_id' returned 200 and updated the one row.
• With the constraint in place but onConflict left out, the upsert returned 409, code 23505, duplicate key.

That 409 tells the caller the value already exists, even for a row RLS hides from them. On an email or handle column that works as an existence check. Error details that leak hidden rows shows when it matters and how to remove it.

PostgREST also notes that after you create a table or change its primary key, "you must refresh PostgREST schema cache for upsert to work properly." If an upsert still behaves as if the old key is in place right after a migration, reload the cache with notify pgrst, 'reload schema'; in the SQL editor.

ignoreDuplicates: insert or skip

{ ignoreDuplicates: true } sends resolution=ignore-duplicates, which is ON CONFLICT DO NOTHING. The PostgreSQL docs put it simply: "ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action." In our test:

• On an existing row it returned 201 with no error, .select() returned [], and the row was unchanged. Check the returned rows if your code needs to know whether it inserted.
• It worked with only select, insert granted. No UPDATE grant or policy is needed.
• It still needed the INSERT policy. Alice sending Bob's id got 403 new row violates, Bob untouched.
• It still needed a SELECT policy. PostgREST always names a conflict target (our log showed ON CONFLICT (id) DO NOTHING, the primary key, when no onConflict was passed), and the docs apply SELECT policies when one is given. Without a SELECT policy it failed with new row violates.

Missing columns and defaultToNull

A single object upsert only sets the columns you send. Upserting { id: 1, title: 'renamed' } kept the row's existing notes.

Arrays are different. supabase-js sends the union of keys from all objects as the column list, so a key missing from one object is still written for that row. The supabase-js source describes defaultToNull as: "Make missing fields default to null. Otherwise, use the default value for the column." What we saw:

• Default (defaultToNull: true): [{ id: 1, title: 'bulk 1' }, { id: 3, title: 'bulk 3', notes: 'new notes' }] set row 1's saved notes to NULL.
• New rows with a NOT NULL default: an object without done (not null default false) failed with 23502 null value.
• user_id default auth.uid(): an object without user_id got NULL, which failed the INSERT policy with new row violates row-level security policy. This is a common source of the exact error in this guide.
• defaultToNull: false (Prefer: missing=default): new rows got their defaults (done = false, user_id = auth.uid()). On existing rows a missing key was still overwritten: notes became NULL (its default) and a saved done = true went back to false. The source comment says the option "only applies when inserting new rows, not when merging with existing rows," so do not use it to protect existing values. It also failed with permission denied for sequence todos_id_seq until we granted the sequence.
• Same keys in every object: row 1 kept its done and notes.

Build every object in a bulk upsert with the same keys, or group rows by shape and upsert each group.

Checklist

• authenticated has select, insert, update on the table (column grants cover the conflict column and every column you send)
• Owner SELECT, INSERT and UPDATE policies, each with (select auth.uid())
• UPDATE policy has an owner check in both using and with check
• No policy on the table uses true for INSERT or UPDATE
• onConflict columns have a unique constraint
• Ownership columns default to auth.uid() and the client does not choose another user's id
• Bulk upserts send the same keys in every object
• A test signs in as one user and tries to upsert another user's row, expecting an error

The RLS testing guide shows how to turn that last line into a CI check, and the Supabase RLS checklist covers the rest of the table setup.

Common AI generated mistakes

• "Enable insert and update for all users" with true, to make the upsert error go away. That is the overwrite row above.
• Adding only an INSERT policy, so upserts of new rows work and every second save fails with (USING expression).
• Removing .select() as the fix. The SELECT policy is still required without it.
• onConflict on a column with no unique constraint, or no onConflict at all, creating duplicate rows.
• Sending user_id from the client instead of a database default, or leaving it out of some objects in a bulk upsert.
• Granting all to anon to fix permission denied. See the 42501 guide for why.

Copy-paste prompt for your AI coding tool

Paste this into Cursor, Claude Code, Codex, Lovable, Bolt, or Replit inside your own project. It asks for SQL and file names only. Do not paste database passwords, connection strings, or keys into the chat.

This project uses Supabase. Do not print, log, or ask me for any secret values. 1) Find every .upsert() call in the code and list the table, the onConflict columns, ignoreDuplicates, and the keys each call sends, flagging bulk upserts whose objects do not all have the same keys. 2) For each of those tables, write a read only SQL query that shows the grants for anon and authenticated (has_table_privilege and has_column_privilege) and every policy from pg_policies with its command, roles, using and with check. 3) Confirm each onConflict column list has a unique constraint or unique index. 4) Flag any INSERT, UPDATE or ALL policy that uses true, applies to public or anon without a reason, or has no owner check comparing (select auth.uid()) to the owner column in both using and with check. 5) Flag missing SELECT policies, since upsert needs them even without .select(). 6) Write one migration that grants only select, insert, update to authenticated on those tables, adds owner SELECT, INSERT and UPDATE policies, adds missing unique constraints, and sets owner columns to default auth.uid(). 7) Write tests that sign in as one user and try to upsert another user's row and expect an error. Do not run the migration until I approve it.

More prompts are in the security review prompt guide.

Check what your live app exposes with LeakCheck

LeakCheck scans any live app URL or public GitHub repo, read only, wherever it is hosted. On a live app that uses Supabase, it finds the key your app ships and checks whether that key can list rows from your tables, alongside its other checks for exposed files and leaked keys.

Its limits, honestly: it never sends inserts, updates or upserts, so it cannot prove that one user is unable to overwrite another user's row. It also cannot see your grants or policy definitions. The policy set and the cross-user test above are how you prove that.

Sources

• Supabase JavaScript: upsert()
• supabase-js source: PostgrestQueryBuilder (upsert, defaultToNull, ignoreDuplicates)
• Supabase: Row Level Security
• Supabase: Securing your API (grants)
• PostgREST: Tables and Views (Upsert, On Conflict, missing=default)
• PostgREST: Errors (HTTP status for 42501)
• PostgreSQL: INSERT (ON CONFLICT, privileges)
• PostgreSQL: CREATE POLICY (policies applied by command type)
• PostgreSQL: Row Security Policies
• PostgreSQL: GRANT