Re: Known but bad behavior with alter user?

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Joshua D. Drake (jd(at)commandprompt(dot)com) wrote:
> >> template1=3D# alter user foo rename to bar;
> >> NOTICE: MD5 password cleared because of role rename
>
> >> Now we have to reset the password.. which seems an extra
> >> step that shouldn't be required.
>
> > Wouldn't this be because the username is used as the salt for MD5 and so
> > there's no way to update the password because the system doesn't know
> > the original password?
>
> Yeah. This isn't changing unless you have an alternative that's not
> worse (ie, doesn't defeat the purpose of storing an encrypted password).

Well, you could use a random salt and then keep track of that random
salt. Of course, there are issues with this: either the salt has to be
provided to the client in order to use it to generate the MD5 to send to
the server for authorization, or the client has to send the password in
the clear to the server (the normal unix/PAM method) so the server can
use the stored random salt to generate the MD5 to compare to the stored
hashed password.

The first case would change our protocol (I believe) and would provide
the random salt to anyone who asked (which means using a random salt
instead of the username doesn't gain us very much). The second case
would mean the plaintext password would be sent in the clear to the
server meaning anyone eavesdropping, or an admin on the server, would be
able to get at the password. Now, I think an admin could still get the
client to send the password in the clear if s/he changed pg_hba.conf
appropriately, and anyone eavesdropping would be able to get whatever is
required to authenticate in any case, though perhaps wouldn't be able to
reuse that information for other servers (such as if it had been a
plaintext password which was used elsewhere).

Enjoy,

Stephen