Re: A mechanism securing web applications in DBMS

> > 2.1 The authentication problem
> > We address the authentication problem by requiring developers to
> > define an authentication function in the DBMS. This function is
> > invoked whenever an application-level user logs in. An authentication
> > function contains the authentication logic in the server-side code.
> > Besides, it also caches some attributes of the current
> > application-level user, which will be checked by the fine-grained
> > access control mechanism.
>
> This is one approach which can be used, though PG has a reasonably
> complex authentication system which applications can leverage (consider
> Kerberos proxying and client-side certificates), rather than having the
> application independently develop an authentication system. Still, if
> the application is to be responsible for the authentication, then a
> simple 'SET ROLE' can be done in PG to switch to the context of an
> individual user.
>
[...]

> > 3.1 Authentication Function
> >
> > The major challenge of this part is how to cache users’ authentication
> > information. In our prototype implementation, an authentication table
> > is actually a temporary table. But it makes our GRANT-WHERE statements
> > also temporary since they refer to the authentication table. Since
> > every session has its own authentication table and all the
> > authentication tables share the same schema, the global temporary
> > table [2] would be a perfect fit for caching users’ authentication
> > information. Also, it will not make GRANT-WHERE statements temporary
> > anymore.
>
> This certainly deserves further discussion. What is the reason that a
> cacheing mechanism into a temporary table is necessary..? Are these
> session identifiers to keep track of already-authenticated users? How
> does that play with a connection pooler (which is a very real
> consideration when thinking about anything web-scale).
>

Absolutely, support for connection pooling is vital for performant web
applications. The problem I see with Kerberos and client side certificates
is that they are only authenticated on the connection level. It would be
helpful if some mechanism existed by which an application could pass a
signed ticket to the database so that the web application user does not
need to be granted privileges to become any user. While Kerberos offers
ultimate flexibility it is rather intimidating. Supporting something a
little simpler would be very helpful.

The scenario I would like to see supported is the following.

1. Web authentication is centralized with users seeing a single log-in
screen. The log-in server issues signed tickets, setting it as a cookie. A
ticket asserts the identity of the user along with a number of additional
principals /groups / roles which are currently authorized (e.g. two-factor
authentication might be required to be issued a delete-permanently
principal.)

2. Web application servers verify a user's ticket by checking the signature
against the login server's public key or in simpler setups with a shared
secret. The ticket has a timeout (or maybe each assertion has a timeout)
and a web application checks it is still valid.

3. In making a database request, the web application passes the ticket down
to the database. Only with this ticket is it able to identify with this
user's id, and only with the group / role / principal assertions is it
granted the additional user roles.

For Apache, mod_auth_tkt and mod_auth_pubtkt are perhaps the simplest
implementations of this concept (Pubcookie/cosign/webauth are more
complex.) As verifying a ticket is cheap, complications around caching can
usually be avoided.

For Postgres, I could imagine the web application interacting with it like
so during the request:

BEGIN;
SELECT
my_auth_function('userid=foo;groups=bar1,bar2;timeout=123456789;signature=abcd1234abcd1234abcd1234abcd1234');
...
COMMIT;

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