Thread: Update row attribute that is part of row level security policyusing_expression

On 5/3/19 4:47 AM, Saupe Stefan wrote:
> 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?

The primary issue here is you are using a security policy to try to 
enforce something that is not security related, the visibility of data. 
If a user was locked out of data for security reasons, but had the 
ability to unlock that data on their own it would not be much of a 
security policy. I see two choices:

1) Don't use RLS for this. Just allow the user to toggle active as 
needed. Not sure where the user is viewing the data, but active/inactive 
could be part of the code that allows data through.

2) If you want to use RLS then create a SECURITY DEFINER function that 
runs as the user that does have non-RLS restricted access to the table. 
Have user b use that to change the active status.

> 
> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com