On 10/2/07, John Locke <..hidden..
> wrote:
Chris Travers wrote:
> In going to native DB accounts, one of the difficulties we have to
> resolve is how to effectively authenticate serial requests. The major
> problem has to do with how the password to the database is stored. I
> am going to suggest that we move to using HTTP authentication as the
> primary mechanism of authentication and automate this from the login
> screen where possible using _javascript_. A secondary method could be
> offered where the passwords are stored in the db, but this has more
> serious security concerns associated and therefore I would suggest
> that we do not go that route.
This is an extremely difficult problem I've wrestled with a few times,
and have not come up with an ideal solution.
First off, how can you do http auth using _javascript_?
One option is to grab the username and password from the form, make use XMLHttpRequestObject to make a request with that auth information and then redirect to the next page. The big issue here is that some browsers may not support this (I know some versions of Opera and Safari are known to have some issues with auth credentials and this method). Of course, if it doesn't work you get a popup asking for credentials. At least we could filter out known problem browsers and just ask for the auth information directly.
For some deployments, we may want to turn off the login screen entirely (for example, if we want to use Kerberos authentication).
Is that even
possible? If so, we might be able to use Digest authentication to handle
hashing the password--haven't used that much to know its ins and outs.
Digest won;t work for a simple reason. If you are passing authentication on to a third party, it needs to be translated in a form which contains all knowledge needed to get authorization from that third party. This means that if something expects a password, that password needs to be transmitted. Hashes and digests are therefore out. This is a reason why you cannot crypt or hash a password when connecting to PostgreSQL when it is using PAM or LDAP authentication.
If not, well, http auth handles the browser to server session handling
nicely, at the expense of a worse user experience.
Well, it doesn't handle everything. There is a question as to what we do when a session expires. One option is to check to see if the auth used a raw http request directly last time and if so, simply send a 401. If it used the login screen/JS, we use a similar measure this time.
In short we go from login/session to login/session/password used on every request.
>
> The major issue with storing the information in the session object is
> that a database superuser could review all passwords of all currently
> logged in users. I don't think that this is acceptable as it both
> allows a set of trusted individuals to bypass security of the db and
> also undermines basic security mechanisms of PostgreSQL as a whole
> (which we rely on). If anyone has better ideas, I am open to them.
> However, this will also put us within striking distance of transparent
> single signon support (for things like Kerberos).
>
But wouldn't a database superuser be able to do anything they want with
the data?
No. Normally the user passwords are stored via an MD5 hash, salted with the username. Hence the superuser could see the hash, run cracking software on it, etc. but could not generally see the plain text password. If we store in the session, a superuser can bypass the hash protections that exist in the PostgreSQL system catalogs, which I think is a bad thing :-).
I don't see a better alternative here. I think you have to store the
password in a session, if you need it to authenticate to the next tier.
The decision is, where? Seems like these are the options:
1. Browser cookie that's sent each request
2. HTTP Auth that's also sent each request
3. Persistent session on the server, with a session id passed from the
browser:
3a. Store session in some sort of file
3b. Store session in temporary table/session table
4. Use some 3rd party auth system to authenticate a user request (still
using #1-3), and proxy the authentication to Postgres.
Kerberos/LDAP/Active Directory/whatever.
I don't like #1, since these get stored on the client machines and may
be sniffed out through all sorts of methods.
#2 is the easiest (in my experience) to implement, but very difficult to
have an effective "log out", and it has the worst user experience.
I think there are ways to both handle the user experience for at least the most important browsers and at the same time, deal with logout issue the notice to the web browser that it is logged out.
#3 needs to store the db password in clear text somewhere on the system.
But aside from people logging into the system directly (shell or db
connection), it should be possible to protect this data from access
through the normal interfaces (and shut down all the other interfaces).
Also, it's relatively simple to kill sessions, enforce logout behavior,
detect IP address changes of the client, etc.
In the past, I have stored the password separate from the login in clear text session files. File permission becomes a big issue, and in those applications, there is no way to know who is currently logged into the system. LedgerSMB is different in that we also store the login in the db session, meaning that now one can know this. In either case, however, the administrator can determine a few passwords quickly and could then attack user accounts.
The danger in attacking user accounts is that it allows an administrator to effectively impersonate another user for an extended time without getting caught (of course, tampering with the pg_hba.conf could do this as well but at least there you have the chance of determining what happened, and the more you force someone to cover tracks in an ongoing attack, the more likely it is that there will be a signature left behind). In short, one of the principles I use would be compromised in this regard, namely that when faced with two options which pose similar security risks, choose the one which creates the largest signature on the system.
#4 seems like it adds another layer of complexity to the whole system,
and I've never dug through this to even know where to start.
Also, while #4 is great (and indeed a requirement) for some environments, it sucks for others. I don't think we want to force people to run a KDC just to use the software. At the same time, I think we need to (long-term) support this for those who want to do so.
One of the really nice things about PostgreSQL user accounts is that we *can* support these transparently when necessary with very little additional effort using HTTP auth methods.
So some version of #3 seems like the best option, unless somebody has
some trick for doing this that's completely escaped my notice...
Somebody is going to have the skeleton key. How do you protect data from
root? I think the answer is, you can't. The best you can do is provide
audit trails, logs that can provide evidence of tampering. And then
restrict direct access to the machine and database to the smallest
number of people possible. If someone has superuser access, what's to
prevent them from changing the code?
> The big disadvantage is that some browsers may handle authentication
> differently and we will have to address this.
Not sure what you mean here, unless you're talking digest versus basic auth?
There are known issues on auth credentials on XMLHttpRequest objects in some browsers, which may reduce our ability to automate it on _javascript_. In particular, I don't know which versions of Safari and Opera may run into problems with the browser not properly handling the credentials.