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".