From: | Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)com> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: question on row level security |
Date: | 2015-12-30 17:37:54 |
Message-ID: | 56841672.4090201@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 30/12/2015 17:32, Joe Conway wrote:
> On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
>> e.g. conceptually:
>>
>> set app_user 'john';
>> select * from foo;
>>
>> where the select * is restricted by a RLS check that includes 'john' as
>> the app_user.
>> Of course custom SQL could be generated for this, but it would be safer
>> if it could be handled using RLS.
>>
>> Any ways to do this?
> Something like this:
>
> 8<--------------------------
> CREATE USER application;
>
> CREATE TABLE t1 (id int primary key, f1 text, app_user text);
> INSERT INTO t1 VALUES(1,'a','bob');
> INSERT INTO t1 VALUES(2,'b','alice');
> ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
> CREATE POLICY P ON t1 USING (app_user =
> current_setting('app_name.app_user'));
> GRANT SELECT ON t1 TO application;
>
> SET SESSION AUTHORIZATION application;
>
> regression=> SET app_name.app_user = 'bob';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> 1 | a | bob
> (1 row)
>
> regression=> SET app_name.app_user = 'alice';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> 2 | b | alice
> (1 row)
>
> regression=> SET app_name.app_user = 'none';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> (0 rows)
>
> 8<--------------------------
>
> HTH,
>
> Joe
>
Looks like that's what I need.
I'll give it a try.
Thanks
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-12-30 17:42:07 | Re: question on row level security |
Previous Message | Karsten Hilbert | 2015-12-30 17:37:15 | Re: question on row level security |