Re: question on row level security

On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon <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.