Re: Explanation of pg_authid.rolpassword

On Mon, Sep 13, 2010 at 12:09 AM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
> 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?

OK, committed with a bit more wordsmithing. See what you think.

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

Yeah, I can't imagine why we're not storing a flag out-of-line.

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