Help :( - Every authenticated INSERT into two specific tables is rejected with an RLS violation via the Data API, but the identical operation succeeds in a plain SQL session with the exact same JWT claims

[OddOne233]

Every authenticated INSERT into public.weddings or public.vendors (a row the caller legitimately owns, owner_id = their own auth.uid()) is rejected via the Data API with:

42501 - new row violates row-level security policy for table "weddings" (or "vendors")

100% reproducible, via every path tested: our own SECURITY INVOKER RPC wrappers (create_wedding/create_vendor), a raw POST /rest/v1/weddings, and the real production app itself (browser signup via @supabase/supabase-js).

DEFINITIVE PROOF POSTGREST IS SETTING UP THE CORRECT AUTH CONTEXT:

We created this debug function and called it via the exact same client/token, in the exact same request pattern, immediately before the failing insert:

create function public.debug_request_jwt() returns jsonb language sql stable as $$
select jsonb_build_object('current_user', current_user, 'auth_uid', auth.uid(),
'claims', current_setting('request.jwt.claims', true));
$$;

Result: current_user = "authenticated", auth_uid = the test user's real id (byte-for-byte match), claims JSON complete and correct (role: authenticated, sub matching). The very next call, same token, same connection pattern - create_wedding (whose only relevant check is owner_id = auth.uid(), and which builds the row via values (auth.uid(), ...)) still fails with the same 42501.

ADDITIONAL RULED-OUT CAUSES:

pg_policies confirms no RESTRICTIVE policies exist; INSERT policies are exactly owner_id = auth.uid() (checked permissive/roles columns).
The identical insert in the SQL editor, simulating the exact real JWT claims for the same user, succeeds every time.
Full project restart made no difference.
Migrating from legacy anon/service_role keys to the new publishable/secret keys made no difference.
No RETURNING/SELECT-policy issue - confirmed directly that zero rows are created on the failed attempts (not just unreturned).
No type mismatch - owner_id and auth.uid() are both uuid.
Token signature is trusted - GET /auth/v1/user with the exact failing token returns 200 with full user data.
So: role switching, JWT claims, auth.uid() resolution, and token trust are all provably correct at the moment of the request. The failure appears to be inside RLS enforcement for these two tables specifically, at the Postgres/PostgREST layer, in a way we can't inspect further from the client side.

IMPACT: blocks every real couple/vendor signup on our live site right now.

Any currently facing the same issue or solved this? any help will be much appreciated.

[GaryAustin1]

What role is the policy set for with a TO if any?
Try just a WITH CHECK (True).
For a direct table read from the API if you return the row then you also have to meet the Select policy.
Do you have a trigger function on the tables for insert that accesses other tables?

Check the API Gateway log to see if you are sending in the Authenticated user ID or log the auth.jwt() value in your rpc.
Check the Postgres log to see if more detail error on where the RLS error is occurring.

[OddOne233]

Hey, thank you GaryAustin1!
the cause was found on our end (a self-referential RLS policy).

[OddOne233]

Solved — root cause was a self-referential SELECT policy, not auth/role/trigger issues.

Thanks for the diagnostic pointers — to answer them directly, since they ruled out most of the usual suspects and pointed us at the right area:

Role/TO on the policy: default (no TO clause → applies to PUBLIC, gated by WITH CHECK/USING as normal). Not the issue.
WITH CHECK (true) test: tried it — insert still failed. That's what told us the problem wasn't the WITH CHECK expression itself.
Trigger functions on other tables: we did have SECURITY DEFINER triggers seeding related rows after insert, but disabling them made no difference either.
JWT/auth.uid() logging: this was the key diagnostic. We added a temporary SQL function that returned current_user, auth.uid(), and the raw request.jwt.claims GUC, and called it via the same client/token immediately before the failing insert. Everything came back correct — right user, right role, right claims. So the failure wasn't in auth setup at all; it was happening after the INSERT's own WITH CHECK had already passed.
Actual root cause: our INSERT ... RETURNING (the PostgREST default) has to re-check the SELECT policy on the row it just inserted, in order to return it. Our SELECT policy for that table called a helper function that queried the same table it was protecting to check ownership (e.g. select 1 from public.weddings w where w.id = wid and w.owner_id = auth.uid()). For a row inserted moments earlier in the same transaction, that self-referential lookup wasn't visible in time for the RETURNING re-check — so the whole request came back as a generic 42501 RLS-violation error, even though the actual WITH CHECK on the INSERT had already succeeded.

This is why it "worked" everywhere except the real API call:

A bare SQL-editor insert ... values (...); with no RETURNING never triggers this re-check at all.
Sending Prefer: return=minimal (which suppresses RETURNING) made the identical insert via the REST API succeed.
Manually simulating the exact JWT claims in a plain Postgres session (no RETURNING) also succeeded.
Only the real, default REST call — which always does INSERT ... RETURNING * — hit the gap.

Fix: add a second, non-recursive SELECT policy that grants visibility with a plain column check instead of a subquery against the same table, e.g.:

create policy weddings_owner_select on public.weddings
for select using (owner_id = auth.uid());
Since same-command RLS policies are OR'd, this doesn't replace your existing (more complex, collaborator-aware) SELECT policy — it just adds a fast, direct path for "it's literally my own row," which resolves correctly for a same-transaction insert with no self-reference involved.

Takeaway for anyone else hitting "works in SQL Editor, fails through the Data API" with RLS: if your SELECT policy queries the same table it's attached to (directly or through a helper function), suspect the RETURNING re-check first. Test by sending Prefer: return=minimal — if that makes the insert succeed, this is your bug.

[cekuu35]

Classic WITH CHECK (true) without USING on FOR INSERT — the RLS engine has no USING expression for the insert path.

Check:

SELECT policyname, cmd, roles, qual, with_check
FROM pg_policies WHERE tablename IN ('table1', 'table2');
-- Look for: cmd = 'INSERT', qual = NULL, with_check = 'true'

Fix (FOR ALL covers everything):

DROP POLICY IF EXISTS "your_policy" ON table1;
CREATE POLICY "owner_all" ON table1
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
-- Repeat for table2

Why SQL Editor passes: SET ROLE authenticated bypasses the policy command-type check; the Data API doesn't.

Free audit script: https://github.com/cekuu35/supabase-rls-leak-demo

[Concepto505]

Glad you found it. One correction for anyone who lands here from the error message, because the last comment misdiagnoses it and the wrong fix is easy to cargo-cult.

qual = NULL on an INSERT policy is not a bug — it is the only thing it can be. USING filters existing rows. An INSERT has no existing row, so an INSERT policy takes WITH CHECK and nothing else; pg_policies reporting cmd = 'INSERT', qual = NULL, with_check = '...' is a correctly formed policy. Rewriting it as FOR ALL ... USING (...) does not fix an insert problem: the USING clause you just added is ignored on the insert path. What that rewrite actually does is quietly give you a SELECT/UPDATE/DELETE policy you had not thought about.

The mechanism behind your actual root cause is worth stating explicitly, because it is the thing that produces the "works in the SQL editor, fails through the API" asymmetry that had you chasing JWT claims:

Postgres applies SELECT policies to the RETURNING clause. A plain insert into ... values (...) in the SQL editor has no RETURNING, so a broken SELECT policy never gets evaluated and the insert succeeds. The moment the same insert goes through PostgREST and the row is returned — which is what supabase-js does as soon as you chain .select() — the SELECT policy runs, and a self-referential one takes the whole statement down with it. The insert itself was legal both times. Only the read-back differed.

The one-line test that separates the two cases, which is much faster than auditing policies:

// no .select() → Prefer: return=minimal → no RETURNING → SELECT policy never runs
const { error } = await supabase.from('weddings').insert({ ... })

If that succeeds and the .select() version fails, the INSERT policy is fine and the problem is in your SELECT policy — exactly what you found. If both fail, it really is WITH CHECK.

Worth knowing because the debugging instinct on a 42501 is to stare at the INSERT policy, and in this shape the INSERT policy is innocent.

[Concepto505]

Follow-up, because I went looking for the exact wording and found two things worth leaving here for whoever lands on this from the error message.

The behaviour is documented, in CREATE POLICY: "If a data-modifying query has a RETURNING clause, SELECT permissions are required on the relation, and any newly inserted or updated rows from the relation must satisfy the relation's SELECT policies." So an INSERT ... RETURNING is two checks — WITH CHECK inbound, SELECT policy outbound — reported through one error message.

The nastier part, if a BEFORE INSERT trigger is what makes the row satisfy the SELECT policy: there is an open Supabase issue for the ordering (#7289), and a live PostgreSQL bug report, #19015, for a narrower case — a SELECT policy calling a STABLE function can evaluate against a snapshot predating the trigger's change and report a violation. The same insert without RETURNING succeeds, and so does the same insert with the function marked VOLATILE.

Worth knowing because the standard advice is to mark policy helpers STABLE for performance, and in this one shape that advice is what breaks the insert.