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'.
---
And perhaps a reference from the section on pg_shadow.passwd pointing
to this description, as well?
Josh