Connection between SELECT policy and PATCH API? PATCH request doesn't work due to SELECT policy

Question

There is this table "customer" and it has "is_deleted" column. I did to use it for soft deletetion.

And I set policy like below so that I don't have to add is_deleted = false condition everytime I select or join customer table.


  CREATE POLICY "can view customer" ON "public"."customer" USING (((is_deleted = false) AND (EXISTS ( SELECT 1 FROM admin_user WHERE (admin_user.uid = uid())))));

But PATCH request which updates "is_deleted" column doesn't work with this policy.

export const softDeleteCustomer = async(id: number) => {
    await supabaseClient.from('customer').update({
      is_deleted: true
    }).eq('id', id);
}

I get an error saying It's violating row-level security.

{
  "code":"42501",
  "details":null,
  "hint":null,
  "message":"new row violates row-level security policy for table \"customer\""
}

So I just removed is_deleted part in policy and it's working. But I don't get it.

What is the problem of it?

What I'm trying to is not to select table but to update table. Why SELECT policy above is preventing updating is_delete column?

Steve Chavez · Accepted Answer · 2023-05-12 01:50:30Z

0

Why SELECT policy above is preventing updating is_delete column?

Your policy is not only affecting SELECT but all commands. You'd need to add FOR SELECT to be scoped for SELECT:

CREATE POLICY "can view customer" ON "public"."customer" FOR SELECT --..

https://www.postgresql.org/docs/current/sql-createpolicy.html#SQL-CREATEPOLICY-SUMMARY

answered May 12, 2023 at 1:50

Steve Chavez

1,25416 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connection between SELECT policy and PATCH API? PATCH request doesn't work due to SELECT policy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related