agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedquestion on row level security
8+ messages / 5 participants
[nested] [flat]
* question on row level security
@ 2015-12-30 16:58 Tim Dudgeon <tdudgeon.ml@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Tim Dudgeon @ 2015-12-30 16:58 UTC (permalink / raw)
To: pgsql-sql
The new row level security feature in 9.5 looks great.
I guess its designed around the need to restrict access based on the
current database user (current_user) where this maps to a database user.
But most applications now access the database using an application user
and manages data for the applications multiple users (probably with each
user being a row in a USERS table somewhere).
Is there any way to "inject" the application user so that this can be
used in a RLS check?
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?
Tim
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:19 David G. Johnston <david.g.johnston@gmail.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-12-30 17:19 UTC (permalink / raw)
To: Tim Dudgeon <tdudgeon.ml@gmail.com>; +Cc: pgsql-sql
On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon <tdudgeon.ml@gmail.com> wrote:
> The new row level security feature in 9.5 looks great.
> I guess its designed around the need to restrict access based on the
> current database user (current_user) where this maps to a database user.
> But most applications now access the database using an application user
> and manages data for the applications multiple users (probably with each
> user being a row in a USERS table somewhere).
> Is there any way to "inject" the application user so that this can be used
> in a RLS check?
> 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
> ?
>
Does this address your concerns?
"""
The session_user is normally the user who initiated the current database
connection; but superusers can change this setting with SET SESSION
AUTHORIZATION. The current_user is the user identifier that is applicable
for permission checking. Normally it is equal to the session user, but it
can be changed with SET ROLE. It also changes during the execution of
functions with the attribute SECURITY DEFINER. In Unix parlance, the
session user is the "real user" and the current user is the "effective
user".
"""
http://www.postgresql.org/docs/9.5/static/functions-info.html
RLS uses "current_user" when performing checks.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:28 Tim Dudgeon <tdudgeon.ml@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tim Dudgeon @ 2015-12-30 17:28 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
On 30/12/2015 17:19, David G. Johnston wrote:
> On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon <tdudgeon.ml@gmail.com
> <mailto:tdudgeon.ml@gmail.com>>wrote:
>
> The new row level security feature in 9.5 looks great.
> I guess its designed around the need to restrict access based on
> the current database user (current_user) where this maps to a
> database user.
> But most applications now access the database using an application
> user and manages data for the applications multiple users
> (probably with each user being a row in a USERS table somewhere).
> Is there any way to "inject" the application user so that this can
> be used in a RLS check?
> 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
> ?
>
>
> Does this address your concerns?
>
> """
> The session_user is normally the user who initiated the current
> database connection; but superusers can change this setting with SET
> SESSION AUTHORIZATION. The current_user is the user identifier that is
> applicable for permission checking. Normally it is equal to the
> session user, but it can be changed with SET ROLE. It also changes
> during the execution of functions with the attribute SECURITY DEFINER.
> In Unix parlance, the session user is the "real user" and the current
> user is the "effective user".
> """
>
> http://www.postgresql.org/docs/9.5/static/functions-info.html
>
> RLS uses "current_user" when performing checks.
>
> David J.
>
It might, but does it mean that that user (the app_user in my original
question) still has to be a regular database user (e.g. one who has a
database account and can connect to the database)? This is what I want
to avoid.
Tim
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:28 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2015-12-30 17:28 UTC (permalink / raw)
To: Tim Dudgeon <tdudgeon.ml@gmail.com>; pgsql-sql
On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
> The new row level security feature in 9.5 looks great.
> I guess its designed around the need to restrict access based on the
> current database user (current_user) where this maps to a database user.
> But most applications now access the database using an application user
> and manages data for the applications multiple users (probably with each
> user being a row in a USERS table somewhere).
> Is there any way to "inject" the application user so that this can be
> used in a RLS check?
> 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?
User name maps?:
http://www.postgresql.org/docs/9.5/interactive/auth-username-maps.html
This still results in an external user becoming a database user. From
there you can set up users as members of larger roles, i.e accounting,
hr, etc to manage access, or not.
>
> Tim
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:32 Joe Conway <mail@joeconway.com>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
2 siblings, 2 replies; 8+ messages in thread
From: Joe Conway @ 2015-12-30 17:32 UTC (permalink / raw)
To: Tim Dudgeon <tdudgeon.ml@gmail.com>; pgsql-sql
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
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Attachments:
[application/pgp-signature] signature.asc (835B, ../../56841541.6080409@joeconway.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:37 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Tim Dudgeon <tdudgeon.ml@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Karsten Hilbert @ 2015-12-30 17:37 UTC (permalink / raw)
To: pgsql-sql
On Wed, Dec 30, 2015 at 05:28:13PM +0000, Tim Dudgeon wrote:
> > The new row level security feature in 9.5 looks great.
> > I guess its designed around the need to restrict access based on
> > the current database user (current_user) where this maps to a
> > database user.
> > But most applications now access the database using an application
> > user and manages data for the applications multiple users
> > (probably with each user being a row in a USERS table somewhere).
> > Is there any way to "inject" the application user so that this can
> > be used in a RLS check?
> > 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
You could store a session cookie (say, the app_user) into a
table and have the RLS policy refer to that, no ?
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:37 Tim Dudgeon <tdudgeon.ml@gmail.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tim Dudgeon @ 2015-12-30 17:37 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on row level security
@ 2015-12-30 17:42 David G. Johnston <david.g.johnston@gmail.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2015-12-30 17:42 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Tim Dudgeon <tdudgeon.ml@gmail.com>; pgsql-sql
On Wed, Dec 30, 2015 at 10:32 AM, Joe Conway <mail@joeconway.com> 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
>
>
I think an example like this, emphasizing the use of something other than
current_user, should be considered for the documentation @
http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-12-30 17:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-30 16:58 question on row level security Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-12-30 17:19 ` David G. Johnston <david.g.johnston@gmail.com>
2015-12-30 17:28 ` Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-12-30 17:37 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2015-12-30 17:28 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-12-30 17:32 ` Joe Conway <mail@joeconway.com>
2015-12-30 17:37 ` Tim Dudgeon <tdudgeon.ml@gmail.com>
2015-12-30 17:42 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox