Explanation of pg_authid.rolpassword

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

On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)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

On Sun, Sep 12, 2010 at 8:36 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
> 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.

I agree my explanation might have been a tad lengthy. But the md5 hash
is of password plus username, not just password. This does seem to
cause confusion; maybe we could leave the example password hashing in
as a footnote on that page?

Josh

On Sun, Sep 12, 2010 at 8:50 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
> On Sun, Sep 12, 2010 at 8:36 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
>> 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.
>
> I agree my explanation might have been a tad lengthy. But the md5 hash
> is of password plus username, not just password. This does seem to
> cause confusion; maybe we could leave the example password hashing in
> as a footnote on that page?

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.

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

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

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.

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.

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

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

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

On Mon, Sep 13, 2010 at 1:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> OK, committed with a bit more wordsmithing.  See what you think.

Looks good. Thanks for the help and patience :-)
Josh

On Mon, Sep 13, 2010 at 2:20 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com> wrote:
> On Mon, Sep 13, 2010 at 1:18 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> OK, committed with a bit more wordsmithing.  See what you think.
>
> Looks good. Thanks for the help and patience :-)

Hey, my pleasure. Thank you for sticking with it also.

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