Re: Explanation of pg_authid.rolpassword

On Sun, Sep 12, 2010 at 9:35 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Sep 12, 2010 at 9:05 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
>> On Sun, Sep 12, 2010 at 8:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> Oh, I see.  But I still don't think we really need to provide specific
>>> examples of what you get when you MD5 particular values... except for
>>> people who can run the MD5 algorithm in reverse in their head, that
>>> doesn't seem like it's adding anything.  Second try:
>>>
>>> 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.  The md5
>>> hash will be of the user's password concatenated to their username
>>> (e.g. if user joe has password xyzzy, PostgreSQL will store the md5
>>> hash of xyzzyjoe).  If the user has no password, this column will be
>>> NULL.
>>
>> This version is fine by me.
>
> Upon reading the code I find I'm not entirely happy with it, because
> the parenthesized condition is not quite accurate.  It's already
> explained (more accurately) in the documentation for CREATE ROLE, so
> we ought to try not to duplicate it here.

Yeah, I agree that it's best to leave the complexity of whether a
password is stored in encrypted form or not to the CREATE ROLE page.

> Password (possibly encrypted); NULL if none.  If the user's password
> was encrypted by CREATE ROLE, this column will contain the string
> 'md5' followed by a 32-character hexadecimal md5 hash.  The md5 hash
> will be of the user's password concatenated to their username (for
> example, if user joe has password xyzzy, PostgreSQL will store the md5
> hash of xyzzyjoe).
>
> ...with the words "CREATE ROLE" as a link to that page.

Very minor quibble about "If the user's password was encrypted by
CREATE ROLE..." - note that a user may manually enter in an encrypted
password:

CREATE ROLE joe WITH LOGIN PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e';
or:
ALTER ROLE joe WITH PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e';

so that it's not really the CREATE ROLE command doing the encrypting.
How about "If the user's password is stored in encrypted form..."
instead?

Just for fun: I noticed that you can actually perform:
ALTER ROLE joe WITH PASSWORD 'md5ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';
(or CREATE ROLE with a similar bogus password), and that user won't be
able to log in using a password.

Josh