agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: question on row level security
Date: Wed, 30 Dec 2015 17:28:13 +0000
Message-ID: <5684142D.9070701@gmail.com> (raw)
In-Reply-To: <CAKFQuwaqtnVAQaVK1btsftjft39QpsF0oY=QoAFGSJFo2ozRaQ@mail.gmail.com>
References: <56840D1A.8030203@gmail.com>
<CAKFQuwaqtnVAQaVK1btsftjft39QpsF0oY=QoAFGSJFo2ozRaQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (8+ messages) latest in thread
Message-ID: <5684142D.9070701@gmail.com>
Permalink: ../5684142D.9070701@gmail.com/
Also on: postgresql.org/message-id/5684142D.9070701@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, david.g.johnston@gmail.com
Subject: Re: question on row level security
In-Reply-To: <5684142D.9070701@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