Is it possible to update rows protected by Row Level Security without being superuser?

Question

PostgreSQL

I'm trying to update values ​​in a table whose rows are protected by row level security policies (do soft delete).
However, when I update I get an error:

new row violates row-level security policy for table "my_table".

Essentially, I need to do row deletion (soft delete) by updating the deleted. And at the same time, I need current_user(who is also the owner of the table, but not the superuser) not to see the rows that have the flag deletedset to true.

Here is how I set the row level security:

alter table my_table enable row level security;
alter table my_table force row level security;

create policy select_policy on my_table for select to public using (deleted is false);
create policy insert_policy on my_table for insert to public with check (true);
create policy delete_policy on my_table for delete to public using (true);
create policy update_policy on my_table for update to public using (true) with check (true);

And this is how I try to update the line: __________ Is it possible to implement this at all? If so, what am I doing wrong and what needs to be fixed? How to reproduce: You need to log into the database not as superuser .
update my_table set deleted = true where id = 1;

create table if not exists my_table (
    "name" varchar(40),
    deleted boolean default false
);

insert into my_table (name) values ('John'), ('Alice'), ('Bob');

alter table my_table enable row level security;
alter table my_table force row level security;

drop policy if exists my_table_select_policy on my_table;
drop policy if exists my_table_insert_policy on my_table;
drop policy if exists my_table_delete_policy on my_table;
drop policy if exists my_table_update_policy on my_table;

create policy my_table_select_policy on my_table for select to public using (deleted is false);
create policy my_table_insert_policy on my_table for insert to public with check (true);
create policy my_table_delete_policy on my_table for delete to public using (true);
create policy my_table_update_policy on my_table for update to public using (true);

update my_table set deleted = true where name = 'John'; -- кидает ошибку

Answer 1

Well, it doesn’t work for you specifically because UPDATE with WHERE also looks at the policy for SELECT in relation to the updated row as well (it’s unclear in the documentation, but it’s mentioned ).
In general, in my opinion, you have chosen the wrong tool. View + triggers will be clearer and more reliable.