3

From the Postgres CREATE POLICY documentation:

UPDATE policies accept both a USING expression and a WITH CHECK expression. The USING expression determines which records the UPDATE command will see to operate against, while the WITH CHECK expression defines which modified rows are allowed to be stored back into the relation.

See the following queries to reproduce an error.

-- model + data
create schema sandbox;
create table sandbox.person (
    name text primary key,
    deleted_at timestamp without time zone
);
insert into sandbox.person (name) values ('Bob'),('Alice');

-- user and permissions
create role tester;
grant usage on schema sandbox to tester;
grant select, insert, update, delete on table sandbox.person to tester;

-- RLS
alter table sandbox.person enable row level security;

create policy test_policy on sandbox.person
for all
to tester
using (deleted_at is null)
with check (true);

-- testing the policy
set role tester;

update sandbox.person set deleted_at = now()
where name = 'Alice';

--> ERROR:  new row violates row-level security policy for table "person"


-- cleanup
reset role; 
drop schema if exists sandbox cascade;
drop role if exists tester;

The error seems to be caused by the "using"-clause that is checked against the NEW row, but the documentation says that when a "with check"-clause is provided, the NEW row will be checked against the "with check"-clause instead of the "using"-clause. But according to the error I must be misinterpreting or overlooked a condition that invalidates the statement in the docs in this scenario.

Can you explain what I am missing and how the policy should look like? The end goal is to hide the records marked as deleted, and allow a user to mark them as such.

I reproduced my problem in Postgres 11 and 15.

Update

I've tried separating the policies into 1 for select and 1 for the update.


create policy test_policy_for_update on sandbox.person
for update
to tester
with check (true);

create policy test_policy_for_select  on sandbox.person
for select
to tester
using (deleted_at is null);

This prevents the error in the update-statement but results in zero records updated.

Update 2

I now believe that the next best way to solves this is to create a separate update function with security definer to bypass RLS as proposed in this answer:

Postgres Row Level Security doesn't allow update on restrictive select policy

That proposal is due to the returning * added by postgraphile, but even without that, the separate update function solution is still needed.

Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89

1 Answers1

0

You made your policy applying to all operations, including SELECT operations. The documentation states that even for an UPDATE statement there are cases where the USING clause of the SELECT policy is checked for the new row. This seems to be happening here.

SebDieBln
  • 3,303
  • 1
  • 7
  • 21
  • You're referring to the footnote of Table 287. Policies Applied by Command Type... Unfortunetaly that isn't very clear to me because it seems to contradict the other part of the docs I highlighted. What should my policy look like instead? – Christiaan Westerbeek Dec 20 '22 at 11:38
  • I've now tried splitting the policies in 2. Still not working. This is a simple use case right? Do you have time to propose another solution. Maybe work with views or triggers. Seems convoluted for this simple use case, but can't think of another way right now either. – Christiaan Westerbeek Dec 20 '22 at 12:10
  • @ChristiaanWesterbeek Can you confirm it is the `SELECT` policy? Does it work if you allow all `SELECT`s? – SebDieBln Dec 20 '22 at 17:29
  • 1
    "You're referring to the footnote of Table 287." Yes, exactly. But I admit it is quite counterintuitive for a simple `WHERE` clause to enforce the `SELECT` policy also for the new data, because the `WHERE` only references the old data. – SebDieBln Dec 20 '22 at 17:34
  • To answer your question. I suppose you mean in my update with 2 policies. It's interesting to see if I change the `test_policy_for_select` to `using (true)` , then still zero records are updated. I have to add `using (true)` to the `test_policy_for_update` as well for the update to work. I've tried all combinations with policies, but I can't get it to work. Meaning, being able to "hide" a record and not get "hidden" records. – Christiaan Westerbeek Dec 21 '22 at 08:30