Update row attribute that is part of row level security policy using_expression

From: Saupe Stefan <stefan(dot)saupe(at)adcubum(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Update row attribute that is part of row level security policy using_expression
Date: 2019-05-03 11:47:06
Message-ID: FF0E2DAE-7DAE-4F02-9449-7492433FBF21@adcubum.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to use RLS to 'hide' or 'deactivate' data at some point that some rows are not visible to the application user anymore.
Let's say user a owns the data and can see all his data.
The application user 'b' can only select,update,delete... 'active' data, but is also able to 'deactivate' currently 'active' rows.

Below is how I tried to accomplish this.

But I'm not able to 'deactivate' rows in the table as application user b.
How can i accomplish this? If possible without having to change the application sql’s that run against the table(s)?

create user a with password 'a';
create user b with password 'b';

\c postgres a;
create table t1(id int,active boolean);
insert into t1 values(1,true);
insert into t1 values(2,false);
create policy mypolicy on t1 for all to b using (active);
alter table t1 enable row level security;
grant all on t1 to b;
select * from t1;
id | active
----+--------
1 | t
2 | f
(2 rows)
--> OK
--Now connect as the application user b
\c postgres b;
select * from t1;
id | active
----+--------
1 | t
(1 row)
--> OK

--now I want to 'deactivate' the active row
update t1 set active=false where id=1;
ERROR: new row violates row-level security policy for table "t1"
--> I want to be able to do this.
My question is:
How can user b read just ‘active’ data AND be able to ‘deactivate’ some active rows?

According to the docs (https://www.postgresql.org/docs/current/sql-createpolicy.html) the reason why the update fails is:
The policy USING expression is applied to Existing & new rows on UPDATES if read access is required to the existing or new row

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Apitz 2019-05-03 11:56:51 Migrating database(s) from Sybase ASE 15.7 to PostgreSQL 10.6 on Linux
Previous Message Pavel Stehule 2019-05-03 10:21:15 Re: Migrating an application with Oracle temporary tables