Defence-in-depth for data-base backed websites: Connections

As we've seen, connections can on some database/os combinations be scarce resources. If we're keeping one connection for each session, then won't that limit the site to something like 1000 sessions? Well, there is a way around that.

In a typical site, there will be a large number of users logged in, who all have sessions in the application (especially with long session timeouts), but not that many are actually active at the same time. If we could disconnect the connections for uses who are logged in but idle, and then reconnect when the users become active, then we'd get by using only as many connections as we expect to have simultaneously active users. That number is likely to be much lower, and a site that has 1000 users actively clicking on things in a, say, 5-minute period should probably run Oracle on Solaris anyway.

Basically we get a most-recently-used cache the size of the number of connections that our database and OS can provide for us, and the way we use it looks pretty much the same as a connection pool: get a connection, use it, and release it. The difference is that instead of blocking in the get until there is a connection for us, we may be reconnecting to the database (possibly after waiting, in case the active set is full and we're thrashing).

In a traditional setup, the web app of course knows how to log in to the database, since it's always using the same username etc. In the setup I'm proposing, only the users themselves know how to log on to the database. The web app could technically store the passwords, but that's madness from a security perspective. Cleartext passwords are to be discarded as soon as possible. Thus, we have to try some other way to log on to the database.

One solution that's pretty much made for this scenario is Kerberos. When a user logs in to the site, authenticate to the Authentication Service, get a Ticket-Granting Ticket, and store that one. Whenever you need to connect to the database on the behalf of the user, use the user's TGT to get a ticket for the database. Should work in theory, but in practice it can be a nightmare both to set up and to get working in Java. It's possible that this would be smoother in Windows, where I imagine you could put the users in Active Directory and be done with the first part, but whether it will work still depends on the Kerberos APIs and how your database drivers use them.

So, if we're passing on Kerberos, we can go for PAM or just roll our own solution. With PAM we could build a module that will let us use one-time passwords, so that we authenticate with the password via PAM on login, get a cookie, and then use that cookie when reconnecting. On logout, the cookie gets invalidated.

For my prototype, I've skipped even that and gone for pre-salted passwords. What I do is that before I send passwords to the database (including on enrollment), I hash the passwords together with a random per-user salt. That salted password is what the database sees when authenticating the users, and never the cleartext one. The salted passwords are then stored in the sessions of the users, and used when reconnecting. Thus, cleartext passwords are never stored, so an attack that would show the contents of session variables of other users would not immediately give a way passwords that the users potentially could be using for other sites.

Now that the prototype implementation can have very large numbers of simultaneous users, the real load testing and performance comparison of using a view filter versus working directly on the tables can commence.


  1. I wonder why it is that database vendors (and traditional server-based services) still manages to con-fuse the authentication and the authorization system. What you really want to say to the database is "look, if system S says the user is P then it IS P".
    What you, as an application developer, want to use is the database authorization mechanism to protect the stored information and couldn't care less about authenticating connections.
    Kerberos and PAM are solutions but not well supported by tools, so, wouldn't it have been nice if you could pass one set of credentials and two principals to the database authentication system. The database would use the first principal and credentials to authenticate 'system S' and then the database would give you a connection with 'setid' of the other principal.
    Yes, you end up with one application = one password and you still might not be able to pool connections (depending on implementations and if it is possible to 'setid' on the fly).
    Still, that is no less safe than what we use now.

    Which of course begs the question, is your application designed to support OAth? :-)

  2. Yes, as long as the functionality for setting the current effective user is not accessible via SQL commands (as I'm attempting to make the system secure even when an attacker has full control over the SQL), then I think it would be an excellent feature. If it can be done via SQL, then it has to be one-way: after switching to the actual user account, then there must not be any way back. That could be used for some things, but would not benefit my scenario much.

    A related thing I considered was to modify Firebird SQL's protocol to multiplex several database connections in one TCP session. That way, I wouldn't be wasting resources per connection, and would be able to keep one connection per session for the lifetime of the sessions.

    And no, no Open ID or OAuth or anything like that. Keep it stupid, simple! ;-)


Note: only a member of this blog may post a comment.