Encrypting pg_shadow passwords

People have complained that we store passwords unencrypted in pg_shadow.
Long ago we agreed to a solution and I am going to try to implement that
next.

What we do now with crypt authentication is that the postmaster reads the
plain-text password out of pg_shadow and encrypts it with a random salt.
That random salt is sent to the client, and the client encrypts with the
supplied salt and sends it to the postmaster.  If they match, the client
is authenticated.

The solution for encrypting passwords stored in pg_shadow was to encrypt
them when they are stored in pg_shadow.  When a client wants to connect,
the pre-encrypted password is encrypted again with a random salt.  The
pg_shadow salt and random salt are sent to the client where the client
performs to encryptions --- one with the pg_shadow salt and one with the
random salt, and sends them back to the postmaster.

It should be pretty easy to do because the encryption code is already
there.  

The problem is for older clients.  Do I need to create a new encryption
type for this double-encryption?  Seems we do.

The bigger problem is how usernames encrypted in pg_shadow can be used
to perform the old 'crypt' authentication.  We could sent the pg_shadow
salt to the client each time, but that leaves snoopers able to replay
the dialog to gain authentication because the salt isn't random anymore.

Migrating old sites to encrypted pg_shadow passwords should be easy if a
trigger on pg_shadow will look for unencrypted INSERTs and encrypt them.

This is unrelated to moving to MD5 encryption, which is another item on
our list.

Comments?  Seems like lots of old crypt-using client binaries will break
because as soon as someone is encrypted in pg_shadow, we can't use
crypt.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026