Re: libpq: passwords WAS: scripting & psql issues

Hi,

On Thu, Aug 19, 2004 at 10:44:57AM -0400, Tom Lane wrote:
> [ looks at code... ] The actual algorithm is
>
> t = md5hash(cleartext_password || username);
> p = md5hash(t || salt);
> transmit p;
>
> In theory we could make libpq accept the password in the form of t
> rather than cleartext_password, but I pretty much fail to see the point.

Alright, I admit hashing the password in the scenario of a direct connection
to the postgresql server via libpq doesn't make much sense. So here is the
complete (a bit lengthy) story, how this question came into my mind.

Consider the following scenario:

A webserver serving a interface to a postgresql database as cgi. The cgi
is coded using libpq. Access to the database server is restricted to the
webserver (e.g. via pg_hba.conf). Users connect with their browser to the
webserver. The server presents a login page, where users have to type their
postgresql username and password.

How does authentication happen?
User enters password, it gets sent (ssl-protected) to the webserver. The
cgi calls PQconnectdb or PQconnectStart with the plaintext password and
other parameters, checks for success, sends the appropriate message to the
browser and exits. As the http protocol is stateless, the webserver will
normally forget about the user's username and password. So for the user
to be able to do further queries without having to enter username and
password again and again, we have to establish a session. To do this, we
generate a unique session id with a certain expiration time and either
send it as a cookie or embed it in a html form we send back. To be able
to use this session id to connect to the postgresql server via
PQconnectdb/PQconnectStart in the future, we have to maintain a mapping
on the webserver of session ids to username/password combinations. This
mapping has to be readable by the user the cgi runs as. An attacker, who
could gain access to this mapping would have the database passwords of all
the users, which currently have a session open. Encrypting the mapping
would only reduce the risk of a successfull attack marginally, as the
encryption key has to be readable by the cgi as well and could probably
be gotten hold of in the same way the attacker got hold of the mapping.

Now how would this work, if it would be possible to send hashed passwords
from libpq:
user sends username/password, this gets hashed by the cgi, then the hashed
value is sent by libpq. Session id is generated and
stored together with the hashed password in the mapping. Now attacker gets
hold of the mapping. Assuming he does only have access as the user the cgi
is running as, he would not have gained anything (except having compromised
the current sessions, which is less bad than having all passwords in
cleartext), as he only has the hashed passwords (a brute force attack on
the hashed values would be possible, but that is at least additional effort
for the attacker). If he had root, he could install a backdoor allowing
him to use the hashed passwords, but a compromise like this is much easier
detected than a compromise based on spied passwords.

So the key question to me is:
How can I ensure in such an application, that the password is visible in
cleartext only at one place (while the user enters it at his browser)?
Any ideas?
Does access via mod_php solve the problem? If yes, how is the session id
user/password mapping maintained?
Probably kerberos in conjunction with apache and mod_auth_kerberos would
do the job, but I have not yet looked into this.

Regards,
Daniel