Hello I am not so sure if this is feasible with policies only. Personally I would separate private data from the rest assuming that that part is anyway information that also the boss is not supposed to look at. A separation would make the setup of policies easy. Using the structure that you described I could implement the use case, but it is neither elegant nor particularly nice and I am not sure If there are not some flaws somewhere. What I did is use a function that returns for a name the content of col_privat and compare it with the current value in the with check clause. Well here it goes, but again. The idea to separate private and business is much better... ;-) create table public.staff ( id integer, name text, boss text, col1 integer, col_privat text ); create role boss login password 'xxx'; create role joe login password 'xxx'; create role sue login password 'xxx'; insert into staff values (1,'boss','boss',10,'boss privat'), (2,'joe','boss',20,'joe privat'), (3,'sue','boss',30,'sue privat'); grant select, update on staff to boss; -- Assuming that boss can change basicall everything. grant select, update (col_privat) on staff to sue, joe; -- assuming that normal user should not change other fields. alter table staff enable row level security; -- Let people see their own entries CREATE POLICY all_users ON staff FOR SELECT TO sue, joe USING (name = SESSION_USER); -- Let boss see all entries CREATE POLICY for_boss ON staff FOR SELECT TO boss USING (true); -- Let people and boss only change their own record CREATE POLICY change_own_private ON staff FOR UPDATE TO boss, sue, joe USING (name = SESSION_USER); -- Create a function to check the content of col_privat CREATE OR REPLACE FUNCTION public.get_col_privat(p_name text) RETURNS text AS $$ BEGIN RETURN (SELECT col_privat FROM public.staff WHERE name = $1); END; $$ LANGUAGE plpgsql; GRANT EXECUTE ON FUNCTION get_col_privat(text) TO boss; -- Allow boss to see modify all entries where he is the boss -- but if the record does not belong to him and the content of col_privat is different from the original then do not allow to create the new row. CREATE POLICY change_all_but_privat ON staff FOR UPDATE TO boss USING (boss = SESSION_USER) WITH CHECK (name <> SESSION_USER AND col_privat = (SELECT get_col_privat(name))); Testing (notice which user is doing what on the left): sue=> SELECT * FROM staff; id | name | boss | col1 | col_privat ----+------+------+------+------------ 3 | sue | boss | 30 | sue privat (1 row) sue=> UPDATE staff SET col_privat = 'sue changed' WHERE name = 'sue'; UPDATE 1 sue=> UPDATE staff SET col_privat = 'sue changed' WHERE name = 'joe'; UPDATE 0 boss=> SELECT * FROM staff; id | name | boss | col1 | col_privat ----+------+------+------+------------- 1 | boss | boss | 10 | boss privat 2 | joe | boss | 20 | joe privat 3 | sue | boss | 30 | sue changed (3 rows) Boss sees everything. boss=> UPDATE staff SET col1 = 250 WHERE name = 'sue'; UPDATE 1 boss=> UPDATE staff SET col_privat = 'boss changed' WHERE name = 'sue'; ERROR: new row violates row level security policy for "staff" Boss can change other fields but not col_privat. boss=> UPDATE staff SET col_privat = 'boss changed' WHERE name = 'boss'; UPDATE 1 But he can change his own col_privat. boss=> SELECT * FROM staff; id | name | boss | col1 | col_privat ----+------+------+------+-------------- 2 | joe | boss | 20 | joe privat 3 | sue | boss | 250 | sue changed 1 | boss | boss | 10 | boss changed Hope this helps. Bye Charles > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Andreas Kretschmer > Sent: Mittwoch, 15. Juli 2015 09:55 > To: pgsql-general@xxxxxxxxxxxxxx > Subject: [9.5] question about row level security > > Hello @ll, > > how can i define a policy to prevent update a single field but enable update > other fields in a row? > > For instance, a staff member table. the boss should be able to change all > fields but not a specific field in all rows, but should be able to update this field > for his own row. > > id | name | boss |col1 | col_privat > -------------------------------- > 1 | boss | boss | 10 | boss privat > 2 | joe | boss | 20 | joe privat > 3 | sue | boss | 30 | sue privat > > boss should be able to edit all except col_privat for id=2 and 3, but he should > be able update this column for id=1. > Therefore i can't revoce the update-priv for this column. > > create policy enable_boss on <table> for update using > (boss=current_user) with check (???) > > Is there a way to access old.* and new.* like within a trigger? > (something like with check(old.col_privat=new.col_privat)) > > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: |
