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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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.

Hey, pg_dumpall is only a shell script. It does what it can. :-)

>
> 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?

If we add this WITH PASSWORD 'foo' to 7.0.X, then 7.1 can read that
format, know it is in cleartext, hash it, and load it in. 7.1 can dump
its table out as WITH ENCRYPTED PASSWORD 'foo'.

With 6.5.X and earlier, we can just tell people they have to manually
update them. We can emit a warning if the pg_shadow password field does
contain an md5 format password.

Another idea is to add code to 7.1 to convert non-md5 shadow password
fields to md5 format. Since we already have special handling to do
pg_pwd, we could do it there. Seems like a plan. MD5 format is all
hex digits of a specific length. No way to get that confused with a
real password.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026