UPDATE and DELETE requires SELECT permission

[dshukertjr]

I am sorry if this is a known or intended behavior, but I did find an PR that seemed to have fixed it at one point, and was wondering if this is the intended behavior right now or not.
#1393

Environment

• supabase-js: v1.35.6
• PostgreSQL version: Using Supabase cloud project ref nlbsnpoablmsxwkdbmer
• PostgREST version: Using Supabase cloud project ref nlbsnpoablmsxwkdbmer

Description of issue

Performing update() or delete() using the supabase-js client library with returning: 'minimal' fails when there is no row level policy on select.

(Expected behavior vs actual behavior)

Users should be able to perform update or delete without SELECT permission on row level policy.

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

1. Create a table , enable row level security and put some dummy data
2. Create the following policy that allows update and delete

CREATE POLICY "can update" ON "public"."products"
AS PERMISSIVE FOR UPDATE
TO public
USING (true)
WITH CHECK (true);

CREATE POLICY "can delete" ON "public"."products"
AS PERMISSIVE FOR DELETE
TO public
USING (true)

1. Perform update or delete fails

const { data, error } = await supabase
  .from('products')
  .update({ name: 'new name' }, { returning: 'minimal' })
  .eq('id', 1)
  // both data and error are null, but the target row is not deleted
      
const { data, error } = await supabase
  .from('products')
  .delete({ returning: 'minimal' })
 .eq('id', 1)
 console.log(error)
 // Fails with status 404
 // error: {message: ''}

[steve-chavez]

Hm, since you don't have a SELECT policy defined and you do:

  .eq('id', 1)

That means you're not getting any rows to update. Try just selecting the data, you should get zero rows as well.

The solution should be creating a SELECT policy.

---

Doing it at the SQL level should give you the same results:

begin;
set local role anon;
update products set name = 'new name' where id = 1;
UPDATE 0
commit;

[steve-chavez]

UPDATE and DELETE requires SELECT permission

Generally, yes. Unless you're doing full table updates/deletes, i.e. with no filters(which is restricted on Supabase by pg-safeupdate).

INSERT is the only statement that should be standalone - doesn't require other privilege/policy than INSERT(unless you return the inserted data)

[steve-chavez]

https://www.postgresql.org/docs/current/sql-createpolicy.html#id-1.9.3.75.6.3

UPDATE

Typically an UPDATE command also needs to read data from columns in the relation being updated (e.g., in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause). In this case, SELECT rights are also required on the relation being updated, and the appropriate SELECT or ALL policies will be applied in addition to the UPDATE policies. Thus the user must have access to the row(s) being updated through a SELECT or ALL policy in addition to being granted permission to update the row(s) via an UPDATE or ALL policy.

[dshukertjr]

Thank you so much @steve-chavez for the clarification! I knew I was missing something 😂