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

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think the only problem is moving dumps from on machine to another. 
> The crypt version may not exist or be different on different machines.

This is something I was going to bring up, but I see Bruce already did.
How will dump/restore and upgrades cope with crypted passwords?

If we standardize on MD5 encryption then there shouldn't be a cross-
platform problem with incompatible hashes, but we've still got troubles.

Currently, pg_dumpall handles saving and loading of pg_shadow as a
simple table COPY operation.  That'd work OK once you have the passwords
stored in MD5-encrypted form, but it will surely not work for upgrading
from 7.0 to 7.1 (assume 7.1 is when we'll have this stuff ready).

I've never cared for dumping pg_shadow that way anyway, since it makes
cross-version changes in the format of pg_shadow nearly impossible;
this password storage issue is just one case of a larger problem.  What
pg_dumpall should really be doing is emitting CREATE USER commands to
reload the contents of pg_shadow.

To do it that way, we'd need two variants of CREATE USER:

CREATE USER ... WITH PASSWORD 'foo'

(the existing syntax).  'foo' is cleartext and it gets hashed on its
way into the table.  The hashing step includes choosing a random salt.

CREATE USER ... WITH ENCRYPTED PASSWORD 'bar'

Here 'bar' is the already-encrypted password form (with salt value
embedded); it'd be dropped into pg_shadow unchanged, although the
command ought to do whatever it can to check the validity of the
encryption format.

pg_dumpall would generate this second form, inserting the crypted
password it had read from the pg_shadow table being dumped.

(Probably, there should be an ALTER USER SET ENCRYPTED PASSWORD as
well, for transferring already-crypted passwords, but that's not
essential for the purpose at hand.)

That solves our problem going forward, but we're still stuck for
how to get 7.0 password data into 7.1.  One possible avenue is to make
sure that it is possible to distinguish whether an existing database
contains crypted or cleartext passwords (maybe this comes for free,
or maybe we have to change the name of the pg_shadow password column
or some such).  Then pg_dumpall could be made to dump out either
WITH PASSWORD 'foo' or WITH ENCRYPTED PASSWORD 'foo' depending on
whether it sees that it is reading cleartext or crypted passwords
from the source database.  Then we tell people that they have to
use 7.1's pg_dumpall to dump a 7.0 database in preparation for
updating to 7.1, or else expect to have to reset all their passwords.

Is there a better way?
        regards, tom lane