Proposal for encrypting pg_shadow passwords

Here is my proposal to fix the security problem of storing cleartext
passwords in pg_shadow. The solution is somewhat complex because we
have to allow 7.2 servers to communicate with 7.1 clients, at least for
a short while.

Here is a summary of what we currently do and proposed solutions.

PG_HBA.CONF
-----------
pg_hba.conf has three authentication options of interest to this
discussion:

trust: no authentication required

password: plaintext password is sent over network from client
to server

crypt: random salt is sent to client; client encrypts using that salt
and returns encrypted password to server. Server encrypts pg_shadow
password with same random salt and compares. This is why current
pg_shadow password is cleartext. (Call this "crypt authentication".)

DOUBLE ENCRYPTION
-----------------
The solution for encrypting pg_shadow passwords is to encrypt using a
salt when stored in pg_shadow, and to generate a random salt for each
authentication request. Send _both_ salts to the client, let the client
double encrypt using the pg_shadow salt first, then the random salt, and
send it back. The server encrypt using only the random salt and
compares.

As soon as we encrypt pg_shadow passwords, we can't communicate with
pre-7.2 clients using crypt-authentication. Actually, we could, but we
would have to send the same pg_shadow salt every time, which is insecure
because someone snooping the wire could just play back the same reply so
it is better to just fail such authentications.

USER INTERFACE
--------------
So, my idea is to add an option to CREATE/ALTER USER:

CREATE USER WITH ENCRYPTED PASSWORD 'fred';
CREATE USER WITH UNENCRYPTED PASSWORD 'fred';
ALTER USER WITH ENCRYPTED PASSWORD 'fred';
ALTER USER WITH UNENCRYPTED PASSWORD 'fred';

Keep in mind ENCRYPTED/UNENCRYPTED controls how it is stored in
pg_shadow, not wither "fred" is a cleartext or preencrypted password.
We plan to prefix md5 passwords with "md5" to handle this issue. (Md5
passwords are also 35-characters in length.)

Also add a new GUC config option:

SET password_encrypted_default TO 'OFF';

It would ship as OFF in 7.2 and can be removed in a later release. Once
all clients are upgraded to 7.2, you can change the default to ON and do
ALTER USER WITH PASSWORD 'fred' to encrypt the pg_shadow passwords. The
passwords are in cleartext in pg_shadow so it is easy to do.

MD5
---
I assume we will use MD5 for encryption of pg_shadow passwords. The
letters "md5" will appear at the start of the password string and it
will be exactly 35 characters. Vince sent me the code. We will need to
add MD5 capability to libpq, ODBC, and JDBC. (I hope JDBC will not be a
problem.) When using CREATE/ALTER user, the system will automatically
consider a 35-character string that starts with "md5" to be a
pre-md5-encrypted password, while anything else will be md5 encrypted.

SECONDARY PASSWORD FILES
------------------------
To add complexity to this, we also support secondary password files.
(See pg_hba.conf.sample and pg_password manual in CVS for updated
descriptions.) These password files allow encrypted passwords in the
same format as they appear in traditional /etc/passwd. (Call this
crypt-style passwords.) I realize most BSD's use MD5 in /etc/shadow
now.

Right now we can use passwords from the file only if we use
password-authentication. We can't use crypt-authentication because the
passwords already have a salt and we don't want to sent the same salt
every time. One nice feature of secondary passwords is you can copy
/etc/passwd or /etc/shadow and use that as your secondary password file
for PostgreSQL. I don't know how many people use that but it is nice
feature. Remember the secondary password files sit in /data which is
readable only by the PostgreSQL install user.

DOUBLE-CRYPT ENCRYPTION
-----------------------
So, we are going to add a new double-MD5 encryption protocol to allow
pg_shadow passwords to be encrypted. Do we also add a
double-crypt-style-password protocol to allow crypt-authentication with
secondary password files that use crypt-style passwords or just require
the secondary password files to use MD5?

Comments?

--
Bruce Momjian | http://candle.pha.pa.us
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