On Fri, Sep 5, 2014 at 12:21 AM, Laurence Rowe <l@lrowe.co.uk> wrote:
Here my_auth_function would validate the ticket and would need to be able to do two things not currently possible with a SECURITY DEFINER function:
1. Call SET SESSION AUTHORIZATION / SET ROLE to become a user the connection user is not otherwise allowed to become.
2. Dynamically set which roles are 'inherited' by the user it is becoming.
Laurence
I've been testing a similar setup using security barrier views for RLS.
It would be a stretch to call my initialization function an "authentication" function. My application server actually does the authentication using client certificate validation. My database initialization function is primarily for authorization (i.e. it determines what the user should be able to see at a fine-grained level, and it performs the work to grant the right accesses).
Anyway, I also encountered the two issues you mention, and I designed my initialization function to get around them:
1.) My initialization function dynamically creates a role for the user. It returns the role name (as the special "name" text type). The application checks the returned row, and it does the SET ROLE. (And later does a RESET ROLE, since we are using connection pooling.)
2.) My initialization function dynamically grants roles to the user accounts as needed.
Good authentication between the application server and the database server is extra important in my case, since the application is trusted to do the authentication and it is granted access to security definer functions that dynamically create and grant roles.
I just thought I'd share. As a DBA, it's interesting to read ideas about how to push more of this work to the database server.