Re: [9.5] question about row level security

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(at)postgresql(dot)org [mailto:pgsql-general-

> owner(at)postgresql(dot)org] On Behalf Of Andreas Kretschmer

> Sent: Mittwoch, 15. Juli 2015 09:55

> To: pgsql-general(at)postgresql(dot)org

> Subject: [GENERAL] [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 (
<mailto:pgsql-general(at)postgresql(dot)org> pgsql-general(at)postgresql(dot)org)

> To make changes to your subscription:

> <http://www.postgresql.org/mailpref/pgsql-general>
http://www.postgresql.org/mailpref/pgsql-general