Re: A mechanism securing web applications in DBMS - Mailing list pgsql-hackers
| From | Laurence Rowe |
|---|---|
| Subject | Re: A mechanism securing web applications in DBMS |
| Date | |
| Msg-id | CAOycyLSu24GPrOi-KkxX8wrsNrX6HQ3PFPYLu0982mQenU18+Q@mail.gmail.com Whole thread Raw |
| In response to | Re: A mechanism securing web applications in DBMS (Stephen Frost <sfrost@snowman.net>) |
| Responses |
Re: A mechanism securing web applications in DBMS
|
| List | pgsql-hackers |
> 2.1 The authentication problemThis is one approach which can be used, though PG has a reasonably
> 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.
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 FunctionThis certainly deserves further discussion. What is the reason that a
>
> 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.
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.
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
pgsql-hackers by date: