PostgreSQL - Soft delete problem with RLS UPDATE and SELECT Policies

I know whats the problem is (below) and have a workaround, but i would like to get rid of it.

I would like to implement soft delete for my_table.

The Problem is that if i run UPDATE "my_table" set delete_at = now() where id = ...
i get new row violates row-level security policy for table.

The insert and delete (real delete) is working fine, so i skip this here.

-- create table
CREATE TABLE "my_table" (
  "id" SERIAL PRIMARY KEY
  "user_id" int not null references "user"(id) on delete cascade,
  deleted_at timestamp with time zone
);

-- enable row level security
ALTER TABLE "my_table" ENABLE ROW LEVEL SECURITY;

-- allow read own data and everything for admin
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()
    and (
      deleted_at is null
    )
  )
);

-- update, e.g. set delete_at date
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()
  )
);

I found that this happens because of my SELECT Policy which filters by deleted_at.

After changing the SELECT Policy to the following workaround (delete_at is null OR "within the last second"), it works.

-- allow read own data and everything for admin
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()
    -- workaround: https://stackoverflow.com/a/74306008
    and (
      deleted_at is null OR ABS(EXTRACT(EPOCH FROM (now() - deleted_at))) < 1
    )
  )
);

But why, and how to get rid of this workaround?

Why is this happening?

As per Table 287 (Policies Applied by Command Type), when you execute an UPDATE command, if you have WHERE or RETURNING in your query then SELECT policies are also applied to both existing and new rows. With RLS, you have your SELECT/USING policies evaluate to TRUE for any conditional UPDATE to happen.

In simpler terms, in order to update a specific user_id, you have to be able to read the row with that user_id. Otherwise, it would be 0 rows to update.

Your update query features where user_id = . More than that, policies are considered part of the query as well. So, as long as you have a reference to a field within your CHECK policies, UPDATE queries will always trigger SELECT/USING policies.

How to get rid of this workaround?

Semantically speaking, implementing soft delete introduces another logical layer that splits row state into two deleted states. This is the root for this messy interaction, and the best way of dealing with it would be to not use soft delete fields within RLS.

However, if you still want to do this, and don't like having that < 1s condition in your policies — which makes sense, those would fail for updates that are taking longer than 1s to run — you can still achieve what you want, yet have somewhat clean policies.

The solution would be to create a specific soft delete function, that will switch current role to app_admin, soft delete the record, and then set role back to app_user. The function will have to check that target user_id matches current user_id, of course.

Something along these lines will do the trick, although it is highly not recommended

create or replace function soft_delete_user(del_id int)
  returns void as
$$
begin
  if (del_id = get_current_user_id()) then
    set role = 'app_admin';
    update my_table set deleted_at = now() where user_id = del_id;
  else
    raise exception 'mismatched IDs';
  end if;
  set role = 'app_user';
end;

$$ language 'plpgsql';

Got the same issue recently and read multiple articles promoting an "archive table" strategy that works better anyway, for the following reasons:

• no need to have partial (unique) indexes
• native handling of foreign keys (on delete cascade/set null)
• easier to restore using insert into active_table select * from archive_table where id = ? than juggling with RLS and roles
• archive table can have RLS too
• efficient implementation using an after-delete for each statement trigger and their transition table

All in all, the effort you have to provide with a deleted_at column might be higher than with an archive table.