Re: question on row level security

On 30/12/2015 17:19, David G. Johnston wrote:
> On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)com
> <mailto:tdudgeon(dot)ml(at)gmail(dot)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