Re: Explanation of pg_authid.rolpassword

On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> I think the docs could do a better job of explaining how passwords are
> stored in the rolpassword column of pg_authid. I've seen a few threads
> where there's some confusion about how md5 hashed passwords are
> stored, and it would be handy to document this somewhere. The existing
> doc page for pg_authid simply says "Password (possibly encrypted);
> null if none".
>
> My SGML-fu is weak, but how about this explanation beneath the table
> of pg_authid columns (in catalogs.sgml):
> ---
> The "rolpassword" column holds one of the following:
>  * NULL, when no password exists for the role
>  * The role's password in plaintext. A password will be stored in
> plaintext when the UNENCRYPTED option is used with the CREATE ROLE
> command, or if the password_encryption GUC is set to 'off'.
>  * The string "md5", followed by a 32-character hexadecimal md5 hash.
> This md5 hash will be computed on the rolename appended to the
> password. For example, if role 'joe' has password 'xyzzy', the
> encrypted password will be stored as
> 'md5b5f5ba1a423792b526f799ae4eb3d59e', since
> 'b5f5ba1a423792b526f799ae4eb3d59e' is the md5 hash of 'xyzzyjoe'.

This seems a bit long-winded to me.  How about just changing the
column description to something like this:

Either the user's unencrypted password (if the UNENCRYPTED option was
used when creating the role or if password_encryption is off), or the
string 'md5' followed by a 32-character hexadecimal md5 hash of the
user's password.  NULL if no password.

> And perhaps a reference from the section on pg_shadow.passwd pointing
> to this description, as well?

I think we could clone the explanation here.  Adding a cross-reference
to the pg_authid documentation seems like a good idea, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company