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
