Re: Proposal for encrypting pg_shadow passwords

Attached please find:

the original proposal to encrypt pg_shadow
a diff of the current CVS
two new files (backend/libpq/md5.c and include/libpq/md5.h)
which implement MD5 encryption (from Vince with cleanups)

I have increased the protocol version from 2.0 -> 2.1. I use MD5 for
all client encryption if the client supports it. I know we have
portability problems with libc's crypt() this will fix that right away.
Of course older clients and servers will still talk using libc's
crypt().

It turns out that ODBC doesn't even do crypt authentication so I have
not added MD5 code there yet. That will be another project because I
have to add salt handling and stuff. Perhaps someone with more ODBC
experience can do it. If they add crypt(), I can add MD5. I see code
in connection.c:

case AUTH_REQ_CRYPT:
case AUTH_REQ_MD5:
self->errormsg = "Password crypt authentication not supported";
self->errornumber = CONN_AUTH_TYPE_UNSUPPORTED;
return 0;

I added enough so MD5 will fail just like crypt. Woo-hoo.

JDBC needs to be done, but I am told MD5 is native to Java.

I have made no changes to pg_dump because it will reload the password
just as it was dumped, e.g. if they were encrypted, they will be loaded
encrypted. If plaintext, they will load plaintext, unless they have
changed postgresql.conf to true, in which case they will be encrypted on
load if they weren't already encrypted. I didn't see any value to
adding ENCRYPTED/UNENCRYPTED to pg_dump. Those flags are only useful
for overriding postgresql.conf, but in this case the encryption has
already happened.

I think there is a problem with secondary password files. Because the
client uses MD5 for encryption, it can't be used to check against
crypt() based secondary passwords. If those passwords where MD5, it
would be OK. The problem here is that the client doesn't check to see
if it is going to use the secondary password or the pg_shadow password
until _after_ it gets the password from the client. I could reorganize
the code but I want to get people's opinion first. In fact, if we have
portability problems with libc crypt(), should we be using crypt()-based
secondary passwords at all? I know there was a report just this week
about that. Perhaps it would be better to remove the entire secondary
password mechanism and allow a list of valid username to appear in
pg_hba.conf in the AUTH_ARGUMENT column. I think listing usernames that
can connect to the database is the most valuable thing about secondary
password files anyway. I know you can copy /etc/shadow and use that but
the crypt() problem seems insurmountable and I know others aren't
excited about that feature.

Oh, well, that's about it. Seems to work pretty well.

> 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

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