Re: [9.5] question about row level security - Mailing list pgsql-general

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@postgresql.org [mailto:pgsql-general-

> owner@postgresql.org] On Behalf Of Andreas Kretschmer

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

> To: pgsql-general@postgresql.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 (pgsql-general@postgresql.org)

> To make changes to your subscription:

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