Postgres FORCE RLS: `new row violates row-level security policy` even with `WITH CHECK (true)` on UPDATE?

I am running into a Row-Level Security (RLS) issue in my PostgreSQL database. I am implementing a "soft delete" feature where I update a deleted column to true.

I have isolated the issue to a purely SQL reproduction in pgAdmin, eliminating the application layer, but the error persists.

The Setup

Role: I have a dedicated database role rls that owns the tables.
Strictness: The role has NOBYPASSRLS.
FORCE RLS: Tables have FORCE ROW LEVEL SECURITY enabled (so the owner is subject to RLS).
Goal: Allow the owner to "soft delete" (UPDATE) any row, while hiding deleted rows from SELECTs.

The Problem

When I try to soft-delete a row:

UPDATE posts SET deleted = true WHERE id = 12410;

I get this error:

ERROR: new row violates row-level security policy for table "posts" SQL state: 42501

My Configuration (Minimal Reproduction)

I have stripped the table down to the basics. No triggers are active.

Enable RLS:

ALTER TABLE "posts" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "posts" FORCE ROW LEVEL SECURITY;

Policies: I split the policies to be as permissive as possible for the UPDATE operation.

-- READ: Hide deleted rows
CREATE POLICY posts_read ON "posts" FOR SELECT
  USING (deleted IS NULL OR deleted = false);

-- INSERT: Allow anything
CREATE POLICY posts_insert ON "posts" FOR INSERT
  WITH CHECK (true);

-- UPDATE: THE PROBLEM AREA
-- I want to allow updating ANY row (USING true)
-- and writing ANY state (WITH CHECK true)
CREATE POLICY posts_update ON "posts" FOR UPDATE
  USING (true)
  WITH CHECK (true);

Verification: Checking pg_policies confirms the policy is loaded correctly:
- tablename: posts
- policyname: posts_update
- permissive: PERMISSIVE
- cmd: UPDATE
- qual (USING): true
- with_check: true

What I've Tried

Unified Policy: Originally I had USING (deleted=false) WITH CHECK (true), which failed (understandably, as USING limits the scope).
Split Policies: Moved to the explicit FOR UPDATE policy above with USING (true). Still fails.
Checking Triggers: Ran SELECT * FROM pg_trigger for the table. Result: No active triggers.
Checking Foreign Keys: The table does have Foreign Keys to other tables (which also have RLS), but I am purely updating a local boolean column, so I wouldn't expect referential integrity checks to fire unless I was changing the FK column.
Manual Test: Running the query as the rls owner user in pgAdmin directly.

My Question

If I have an RLS policy for UPDATE defined as USING (true) WITH CHECK (true), is there any scenario where a new row violates row-level security policy error is possible?

Does FORCE RLS interact with Foreign Key validation in a way that masks a referential integrity error as an RLS violation?

Any insights on how to debug this "impossible" policy failure would be appreciated.

Solution

If you execute an UPDATE with a WHERE clause, the new row version must satisfy the FOR SELECT policy. That's why you are getting an error.

I don't know if row-level security is the proper solution for your problem, but perhaps you can do the following:

Add a FOR DELETE policy like

CREATE POLICY posts_delete ON posts
   FOR DELETE USING (deleted IS NOT TRUE);

Write a SECURITY DEFINER trigger function that is owned by a user that is exempt from row-level security, updates the row (setting deleted = TRUE) and returns NULL:

CREATE FUNCTION soft_delete() RETURNS trigger
   LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = pg_catalog AS
$$BEGIN
   UPDATE public.posts
      SET deleted = TRUE
      WHERE posts.id = OLD.id;

   /* don't actually delete */
   RETURN NULL;
END;$$;

Create an row-level ON DELETE trigger on the table using the trigger function from the previous point:
```
CREATE TRIGGER soft_delete BEFORE DELETE ON posts
   FOR EACH ROW EXECUTE FUNCTION soft_delete();
```

Then the application can just DELETE rows from the table, and instead you get the soft delete you want.