Re: A mechanism securing web applications in DBMS

On Fri, Sep 5, 2014 at 12:21 AM, Laurence Rowe <l(at)lrowe(dot)co(dot)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.

Geoff Montee