Failing to update data because of row level security returns [] as response body

[dshukertjr]

Environment

Using Supabase.

Description of issue

Response body of update is just an empty array when failed to perform the update due to row level security.

To Reproduce

Create a sample table:

create table public.sample (
    id uuid not null primary key DEFAULT uuid_generate_v4 (),
    name text not null
);

alter table public.sample enable row level security;

Perform an update

const {data, error} = await postgrestClient.from('sample').update({id: ''})

And the error value is []. Under the hood, the raw response body seems to be [].

Expected behavior

Get an error response message saying something like
new row violates row-level security policy for table \"sample\"

[wolfgangwalther]

This is just how RLS works. There is no error and it makes sense. RLS limits the number of rows a user can "see" on the table. In your example, you are not seeing any rows - so no rows are updated, there is no error and the result is empty. All correct.

There can be a difference between the rows you are seeing and the rows you are allowed to update. This is the difference between USING and WITH CHECK. Have a look here at Table 272.

---

On a side note: If you want to do an update where you expect to change exactly one row, not more and not less, you can request a single row via Accept header - see the docs. This would fail in your example, because 0 rows is not "exactly 1 row". Not sure how to do that with the supabase client.

[dshukertjr]

@steve-chavez @wolfgangwalther
Also, here is another thing that I noticed. I apologize if this is just a regular expected behavior.

With the above example, if I change the update() to an insert() it gives me detailed error response saying new row violates row-level security policy for table \"sample\". I was not sure the difference between insert() and update() in this case.

Response of insert()

POST https://uolvtdzkvkolsynuhmzh.supabase.co/rest/v1/sample 403
 {hint: null, details: null, code: "42501", message: "new row violates row-level security policy for table "sample""}

Response of update()

PATCH https://uolvtdzkvkolsynuhmzh.supabase.co/rest/v1/sample 404
[]

[steve-chavez]

@dshukertjr Yes, that happens because as stated on the pg policies table, an INSERT policy only has a WITH CHECK clause, and that one elicits an error.

check_expression
An error will be thrown if the expression evaluates to false or null for any of the records inserted or any of the records that result from the update

On the other hand, an UPDATE policy has both USING and WITH CHECK clauses. If you don't define a WITH CHECK, the USING expression will be applied.

using_expression
Any rows for which the expression returns false or null will not be visible to the user (in a SELECT), and will not be available for modification (in an UPDATE or DELETE). Such rows are silently suppressed; no error is reported.

[dshukertjr]

Thanks @steve-chavez ! Didn't know that there can be a case where the error message is not returned when the status code indicates an error!

[wolfgangwalther]

If the SELECT policy already has a restricted USING, then there wouldn't be a problem in doing USING (true) on the UPDATE right? Since the rows will be filtered already.

Nope. The select policy is only relevant for a select. For update it's only used when RETURNING the row!

It would change semantics quite a bit, because you would get errors on updates for rows, that you don't even know about - after all you can't select them

Then if the SELECT policy is restricted, this wouldn't happen?

I tested it - and in fact, we were both wrong. It's much worse: The using (true) will allow you to update all rows (no matter what the select policy says) - as long as the new row satisfies the check clause. So you can basically "steal" rows from other people. Or silently change rows, that you didn't expect to change!

Example:

create table t (c text);
insert into t (c) values ('a'), ('b');
alter table t enable row level security;
create policy s on t for select to public using (c='a');
create policy u on t for update to public using (true) with check(c='a');

create role r;
grant all privileges on table t to r;

select * from t;

set role r;

update t set c='a';

select * from t;

The first select returns one row: the a-row. Ok, let's update all those a-rows, you say?

The second select will now return two rows with a. That means the b-row was changed to a - even though you didn't know about it's existance before. Not even an error is thrown here...

So the using (true) is really something that you should not do.

Edit: And of course that is no big surprise... because that is exactly what is stated in the docs and the table I mentioned. I should have just carefully read it, I guess.

[steve-chavez]

So the using (true) is really something that you should not do.

@wolfgangwalther Absolutely, I've updated my previous comment to make it clear. Thanks a lot for elaborating. Personally I only use the USING clause on UPDATE policies and not the WITH CHECK clause(which uses the same expression as USING if not defined).

Then it can be concluded that is not possible to somehow trick pg to show an error when updating inaccessible rows, silent ignore is the only possible outcome.

[jonkurtis]

Would the update throw an error for a policy violation if the policy has a USING and a WITH CHECK? Does the addition of a WITH CHECK produce the error on Update?

[jonkurtis]

Scratch that. I guess WITH CHECK doesn't run if the USING clause is violated. So the best solution is probably to just check if the updated row(s) are in the returned array or not? And if not, we can assume the update failed.

[GaryAustin1]

I was going to post that on your tweet... I think that is best. But a trigger can also raise errors.
Not really a PostgREST thing, but a Postgres thing..