PostgreSQL软删除与RLS更新、查询策略冲突问题问询

报错原因 #

PostgreSQL开启行级安全（RLS）后，执行UPDATE操作时，除了验证UPDATE策略的USING和WITH CHECK条件，默认还会强制检查更新后的行必须对执行操作的用户可见（即符合该表SELECT策略的USING规则）。你的软删除操作会将deleted_at设为当前时间，更新后的行不符合SELECT策略中deleted_at is null的要求，因此触发new row violates row-level security policy错误。

临时方案通过让刚更新的1秒内的行暂时满足SELECT策略绕过检查，但这并非合理的长期解决方案。

合理解决办法 #

方案1：关闭UPDATE策略的可见性强制检查（推荐） #

PostgreSQL 12及以上版本支持WITH CHECK OPTION NOT ENFORCED，添加该选项后，会跳过更新后的行可见性验证，允许普通用户更新自己的行（即使更新后行对自身不可见），完全匹配软删除的业务逻辑。

修改后的UPDATE策略代码：

CREATE POLICY my_table_update_policy ON "my_table" FOR UPDATE
USING (true)
WITH CHECK (
  current_setting('role') = 'app_admin'
  OR
  (
    current_setting('role') = 'app_user'
    AND user_id = get_current_user_id()
  )
)
WITH CHECK OPTION NOT ENFORCED;

方案2：调整SELECT策略，允许用户查看自己的已删除行（按需选择） #

如果业务允许用户查看自己标记为删除的行，可以修改SELECT策略，让普通用户能访问自己的所有行（无论是否删除），后续在业务查询时再手动过滤未删除数据：

修改后的SELECT策略代码：

CREATE POLICY my_table_read_policy ON "my_table" FOR SELECT
USING (
  current_setting('role') = 'app_admin'
  OR
  (
    current_setting('role') = 'app_user'
    AND user_id = get_current_user_id()
  )
);

需要展示未删除数据时，在查询语句中添加WHERE deleted_at IS NULL即可。

内容的提问来源于stack exchange，提问作者dknaack