Re: You're on SecurityFocus.com for the cleartext passwords.

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Tom Lane wrote:
>> I don't think it's optional to get rid of the cleartext password;
>> kindly recall the original complaint we are trying to address
>> (see subject line of this thread ;-)). So there's little value in
>> storing two columns.

> there is some value in

> A. a smooth transition path via dump/restore

True, but if we set up the translation to occur in a trigger that
checks for already-hashed data, then we've got that licked.

> B. backwards compatibility for those that need it more than security -
> we can still do DES-crypt authentication if we choose to

That is a definite loss, but on the other hand I now realize that the
crypt-based authentication has its own compatibility problems: it may
fail with a client running on another machine already! Not to mention
that some popular client platforms and/or interfaces (think ODBC) never
have supported crypt authentication. So it's questionable that there
are very many people using it in cross-platform situations where
backwards compatibility with old clients is critical. Furthermore,
it's not like the people who do get burnt have no option at all: they
can switch to cleartext password authentication or one of the other
already-supported methods until they can bring their clients up to
speed. On the whole, I think the advantages of moving to MD5 clearly
outweigh this single disadvantage.

>> Also, by having just one password field we can deal with either
>> cleartext or pre-encrypted incoming passwords fairly easily.
>> The trigger either reformats the field, or not; no upstream code
>> needs to worry about whether the password is already encrypted.
>> So we don't need the "WITH ENCRYPTED PASSWORD" variant syntax,
>> which is a good thing IMHO.

> But how will you know if the data in the field is md5 hashed ?

Easily. We will need, say, 32 bits of random salt plus the 128-bit
MD5 hash value. Represent these in a string that consists of, say,
8 hex digits, a '/' sign, and 32 more hex digits; use only uppercase
A-F, not lowercase, as hex digits. Now, are you going to say that
23B3C990/652B8383A48348CDEF57298289A882DD
is plausible as a cleartext password? I don't agree...

>> No, I don't think that's an improvement. Please recall that the
>> original reason for inventing salt was to make sure that it wouldn't be
>> obvious whether the same user was using the same password on multiple
>> machines.

> Ok. My previous impression was that it was in order to make sure that
> two users on the same machine would not find out theat the other is
> using the same passord as she is

It does that too. But remember that the point of not storing cleartext
passwords is to prevent using a password stolen from User A to break
into User A's other accounts, not User B's accounts. So same username,
different machine is definitely a case we should consider.

> Can we really rename users ?

update pg_shadow set usename = 'bar' where usename = 'foo';

An ALTER USER syntax would be handier, but you don't really need it...

regards, tom lane