agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: Joe Conway <mail@joeconway.com>
To: pgsql-sql@postgresql.org
Subject: Re: question on row level security
Date: Wed, 30 Dec 2015 17:37:54 +0000
Message-ID: <56841672.4090201@gmail.com> (raw)
In-Reply-To: <56841541.6080409@joeconway.com>
References: <56840D1A.8030203@gmail.com>
	<56841541.6080409@joeconway.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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


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

view thread (8+ messages)  latest in thread

Message-ID: <56841672.4090201@gmail.com>
Permalink:  ../56841672.4090201@gmail.com/
Also on:    postgresql.org/message-id/56841672.4090201@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tdudgeon.ml@gmail.com, mail@joeconway.com
  Subject: Re: question on row level security
  In-Reply-To: <56841672.4090201@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox