Subject: Re: question on row level security
To: Joe Conway <mail@joeconway.com>, pgsql-sql@postgresql.org
References: <56840D1A.8030203@gmail.com> <56841541.6080409@joeconway.com>
From: Tim Dudgeon <tdudgeon.ml@gmail.com>
Message-ID: <56841672.4090201@gmail.com>
Date: Wed, 30 Dec 2015 17:37:54 +0000
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:38.0)
 Gecko/20100101 Thunderbird/38.5.0
MIME-Version: 1.0
In-Reply-To: <56841541.6080409@joeconway.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql