Postgres error 42501, message new row violates row-level security policy for table "tasks". Reads are perfect - the user sees exactly their org's tasks. Inserts from a server action fail every time.
The policy:
create policy tasks_org on tasks
for all to authenticated
using ( org_id = (auth.jwt() ->> 'org_id')::uuid );
I thought for all covered insert. What am I misreading?
USING is not consulted on INSERT. It cannot be - there is no existing row to test. INSERT is governed by WITH CHECK, and if you do not write one for a policy that has a USING, there is nothing to permit the new row, so it is denied.
create policy tasks_org on tasks
for all to authenticated
using ( org_id = (select auth.jwt() ->> 'org_id')::uuid )
with check ( org_id = (select auth.jwt() ->> 'org_id')::uuid );
The mental model that stuck for me: USING filters rows you are allowed to see or touch, WITH CHECK validates rows you are trying to leave behind. UPDATE needs both, because it does both.
That was it. Added with check and the insert went through immediately. The wording of the error had me convinced the row's org_id was wrong.
The error message is the same for both causes, which is why this question gets asked weekly. If with check does not fix it, print the row you are about to insert - nine times out of ten the column is null.
The second most common cause, once with check is in place: the client never sets org_id and the column defaults to null, so the check compares null to a uuid and gets null, which is not true.
Stop sending it from the client entirely and give the column a default:
alter table tasks
alter column org_id set default ((auth.jwt() ->> 'org_id')::uuid);
Now the client cannot get it wrong or lie about it, and your insert payload gets smaller.
Column defaults that read the JWT are one of the few places I will accept magic, because the alternative is trusting 40 call sites to set the tenant column and every one of them is a potential cross-tenant write.
Doing this next. We currently pass org_id from the client on every insert, which in hindsight is a strange thing to let the client decide.
Worth confirming grants separately. Policies filter, grants permit. grant insert on tasks to authenticated has to exist or you get a different but similarly unhelpful error, and if you have been creating tables by hand rather than through a migration tool it is easy to have select granted and insert not.
"new row violates row-level security policy" has never once meant what I assumed on first read. It is the NoMethodError on nil of Postgres.
@wren_oyelaran · last wk.
For debugging this class of thing without a browser in the loop, reproduce it in psql inside a transaction you roll back:
Ten seconds per iteration instead of a deploy. This is also the shape of the test you should be writing anyway.
Reply
Report