Thread: 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@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
On Sun, Sep 12, 2010 at 8:36 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy@gmail.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@gmail.com> wrote: > On Sun, Sep 12, 2010 at 8:36 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Thu, Sep 2, 2010 at 11:06 PM, Josh Kupershmidt <schmiddy@gmail.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@gmail.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@gmail.com> wrote: > On Sun, Sep 12, 2010 at 8:57 PM, Robert Haas <robertmhaas@gmail.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@gmail.com> wrote: > On Sun, Sep 12, 2010 at 9:05 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote: >> On Sun, Sep 12, 2010 at 8:57 PM, Robert Haas <robertmhaas@gmail.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@gmail.com> wrote: > On Sun, Sep 12, 2010 at 9:35 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Sun, Sep 12, 2010 at 9:05 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote: >>> On Sun, Sep 12, 2010 at 8:57 PM, Robert Haas <robertmhaas@gmail.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@gmail.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@gmail.com> wrote: > On Mon, Sep 13, 2010 at 1:18 PM, Robert Haas <robertmhaas@gmail.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