Unable to update row of table with Row Level Security

Question

I'm trying to update a row in a table with row level security policies, but keep getting the error new row violates row-level security policy for table "my_table".

Here's how I set up RLS policies:

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);

The query I'm trying to run is:

update my_table set deleted = true where id = 1;

I need to perform a "soft-delete" of the rows in my_table in this way - by switching the deleted flag.

---

What am I doing wrong here? How do I make such queries work?

---

Update #1

Steps to Reproduce:

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'; -- throws error

On the screenshot below are the privileges of current_user:

My current user's grant is grant all on schema public to my_user;

Answer 1

Postgres applies the my_table_select_policy on the updated row (having deleted = false). For a reason unknown to me.

As a workaround I would suggest to build in a grace period where the my_table_select_policy still returns true:

• instead of delete have a deleted_at column and on deletion store the current timestamp in it (i.e. timestamp when the deletion happened)
• in the SELECT policy check if deleted_at is NULL or the time between deleted_at and now is less than 1 second

  USING (
        my_table.deleted_at IS NULL
        OR
        ABS(EXTRACT(EPOCH FROM (now() - my_table.deleted_at))) < 1
  )