Re: encrypted passwords

Neil Conway wrote:
> A couple questions regarding encrypted passwords:
> 
> (1) There was talk of changing the default value of the
>     'password_encryption' GUC variable for 7.3; AFAIK, this hasn't
>     happened yet. Should this be done?

Strange.  I had updated the docs and postgresql.conf, but not guc.c,
where the default it set.  Fixed now.

> (2) What is the reasoning behind the current storage format of
>     MD5-encrypted passwords? At the moment, we "determine" that a
>     password is stored pre-hashed in pg_shadow by checking if it
>     begins with "md5" and is 35 characters long (the isMD5() macro in
>     libpq/crypt.h). This seems problematic, for a couple reasons:
> 
>         (a) it needlessly overloads the password field: that field
>             should store the password or the digest itself, not
>             meta-data about the authentication process.

Yep.  That is how FreeBSD handles the password string, and I just
followed that.

>         (b) it makes it difficult to determine if the password is
>             *actually* encrypted, or whether the user just happened to
>             specify an (unencrypted) password of that form.

Yep, good point.

>         (c) it limits us to using the MD5 algorithm. MD5 is not
>             looking as invincible as it once did, and having the
>             capability to support SHA1 or another algorithm without
>             too much pain would be nice.
> 
> (3) (Related to 2b above) Shouldn't we reject an attempt by the user
>     to specify an un-encrypted password that matches the isMD5() test?
>     For example:
> 
> nconway=# create user foo encrypted password
> 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
> CREATE USER
> nconway=# create user foo2 encrypted password 'somethingelse';
> CREATE USER
> nconway=# select usename, passwd from pg_shadow
>           where usename like 'foo%';
>  usename |               passwd                
> ---------+-------------------------------------
>  foo     | md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>  foo2    | md51b80a20a1b6cd86eb369f01009b739d3
> 
> (The first password is stored "as-is", the second is hashed before
> being stored.)
> 
> I don't see a need for the ability to specify pre-hashed passwords,
> and it makes the whole process of determining the type of password
> being used more complicated.

Well, pg_dump actually loads in the encrypted passwords in that format,
so yea, we do need to allow that.  Basically, if you want to split out
the encryption type from the encryption string, you will need a new
pg_shadow column to handle that, and an update to CREATE USER to pass
that flag in for pg_dump to use when reloading.

> (4) The naming standard for system catalogs would dictate that the
>     'passwd' field of pg_shadow actually be named 'usepasswd' or
>     something similar, wouldn't it? The same applies to the 'valuntil
>     field.

Yes, not sure what other apps access that, but clearly it is
inconsistent.  Will it cause hardship to fix that?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073