无法使用行级安全性更新表行

我正在尝试使用行级安全策略更新表中的行，但不断收到错误new row violates row-level security policy for table "my_table".

以下是我设置 RLS 策略的方法：

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

我尝试运行的查询是：

update my_table set deleted = true where id = 1;

我需要对其中的行执行“软删除”my_table以这种方式 - 通过切换deleted flag.

我在这里做错了什么？我如何使此类查询有效？

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

On the screenshot below are the privileges of current_user:

我当前用户的授权是grant all on schema public to my_user;

Postgres 应用了我的表选择策略 on the 更新行（已删除=假）。出于我不知道的原因。

作为一种解决方法，我建议建立一个宽限期，其中我的表选择策略仍然返回 true：

• 代替delete have a 删除时间列并在删除时将当前时间戳存储在其中（即删除发生时的时间戳）
• 在 SELECT 策略中检查是否删除时间为NULL或deleted_at与now之间的时间小于1秒

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