Thread
-
encrypted passwords
Neil Conway <nconway@klamath.dyndns.org> — 2002-08-14T20:32:26Z
A couple questions regarding encrypted passwords: (1) There was talk of changing the default value of the 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't happened yet. Should this be done? (2) What is the reasoning behind the current storage format of MD5-encrypted passwords? At the moment, we "determine" that a password is stored pre-hashed in pg_shadow by checking if it begins with "md5" and is 35 characters long (the isMD5() macro in libpq/crypt.h). This seems problematic, for a couple reasons: (a) it needlessly overloads the password field: that field should store the password or the digest itself, not meta-data about the authentication process. (b) it makes it difficult to determine if the password is *actually* encrypted, or whether the user just happened to specify an (unencrypted) password of that form. (c) it limits us to using the MD5 algorithm. MD5 is not looking as invincible as it once did, and having the capability to support SHA1 or another algorithm without too much pain would be nice. (3) (Related to 2b above) Shouldn't we reject an attempt by the user to specify an un-encrypted password that matches the isMD5() test? For example: nconway=# create user foo encrypted password 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; CREATE USER nconway=# create user foo2 encrypted password 'somethingelse'; CREATE USER nconway=# select usename, passwd from pg_shadow where usename like 'foo%'; usename | passwd ---------+------------------------------------- foo | md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx foo2 | md51b80a20a1b6cd86eb369f01009b739d3 (The first password is stored "as-is", the second is hashed before being stored.) I don't see a need for the ability to specify pre-hashed passwords, and it makes the whole process of determining the type of password being used more complicated. (4) The naming standard for system catalogs would dictate that the 'passwd' field of pg_shadow actually be named 'usepasswd' or something similar, wouldn't it? The same applies to the 'valuntil field. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC -
Re: encrypted passwords
Rod Taylor <rbt@zort.ca> — 2002-08-14T21:05:48Z
On Wed, 2002-08-14 at 16:32, Neil Conway wrote: > A couple questions regarding encrypted passwords: > > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Since ODBC is capable of using the encryption and I presume JDBC also is, what reason is there for not enforcing it's use?
-
Re: encrypted passwords
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T22:18:04Z
Neil Conway wrote: > A couple questions regarding encrypted passwords: > > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Strange. I had updated the docs and postgresql.conf, but not guc.c, where the default it set. Fixed now. > (2) What is the reasoning behind the current storage format of > MD5-encrypted passwords? At the moment, we "determine" that a > password is stored pre-hashed in pg_shadow by checking if it > begins with "md5" and is 35 characters long (the isMD5() macro in > libpq/crypt.h). This seems problematic, for a couple reasons: > > (a) it needlessly overloads the password field: that field > should store the password or the digest itself, not > meta-data about the authentication process. Yep. That is how FreeBSD handles the password string, and I just followed that. > (b) it makes it difficult to determine if the password is > *actually* encrypted, or whether the user just happened to > specify an (unencrypted) password of that form. Yep, good point. > (c) it limits us to using the MD5 algorithm. MD5 is not > looking as invincible as it once did, and having the > capability to support SHA1 or another algorithm without > too much pain would be nice. > > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > to specify an un-encrypted password that matches the isMD5() test? > For example: > > nconway=# create user foo encrypted password > 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; > CREATE USER > nconway=# create user foo2 encrypted password 'somethingelse'; > CREATE USER > nconway=# select usename, passwd from pg_shadow > where usename like 'foo%'; > usename | passwd > ---------+------------------------------------- > foo | md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > foo2 | md51b80a20a1b6cd86eb369f01009b739d3 > > (The first password is stored "as-is", the second is hashed before > being stored.) > > I don't see a need for the ability to specify pre-hashed passwords, > and it makes the whole process of determining the type of password > being used more complicated. Well, pg_dump actually loads in the encrypted passwords in that format, so yea, we do need to allow that. Basically, if you want to split out the encryption type from the encryption string, you will need a new pg_shadow column to handle that, and an update to CREATE USER to pass that flag in for pg_dump to use when reloading. > (4) The naming standard for system catalogs would dictate that the > 'passwd' field of pg_shadow actually be named 'usepasswd' or > something similar, wouldn't it? The same applies to the 'valuntil > field. Yes, not sure what other apps access that, but clearly it is inconsistent. Will it cause hardship to fix that? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: encrypted passwords
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T22:18:44Z
Rod Taylor wrote: > On Wed, 2002-08-14 at 16:32, Neil Conway wrote: > > A couple questions regarding encrypted passwords: > > > > (1) There was talk of changing the default value of the > > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > > happened yet. Should this be done? > > Since ODBC is capable of using the encryption and I presume JDBC also > is, what reason is there for not enforcing it's use? It was delayed until 7.3 so we had 7.2 client apps that understood it so an upgraded would continue to work with older clients. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: encrypted passwords
Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T22:32:35Z
Neil Conway <nconway@klamath.dyndns.org> writes: > A couple questions regarding encrypted passwords: > (1) There was talk of changing the default value of the > 'password_encryption' GUC variable for 7.3; AFAIK, this hasn't > happened yet. Should this be done? Hmm. I thought it *was* done, but it looks like Bruce forgot to change the actual guc.c value? The docs and postgresql.conf.sample claim the default is true... 2002-06-14 21:29 momjian * doc/src/sgml/runtime.sgml, src/backend/utils/misc/postgresql.conf.sample: Make encryption of stored passwords the default, as discussed months ago. Seem to be one file short on that commit ... > (2) What is the reasoning behind the current storage format of > MD5-encrypted passwords? The reasoning for the apparent leakage between encrypted and unencrypted formats is it allows pg_dumpall to reload an already-encrypted password, or an admin to copy-and-paste an encrypted password without knowing exactly what the password is. See the archives when this mechanism was being designed (about a year ago I think), if you want the full story. > (b) it makes it difficult to determine if the password is > *actually* encrypted, or whether the user just happened to > specify an (unencrypted) password of that form. By definition, if it looks like that then it's encrypted. I really doubt anyone will want to use a 35-character plaintext password... the apparent conflict is not going to happen in practice AFAICS. > (c) it limits us to using the MD5 algorithm. Nonsense. If we want another method, we just use another prefix. > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > to specify an un-encrypted password that matches the isMD5() test? No, see above. There are actually three cases here: entering a previously encrypted password (in which case do nothing to it regardless of the "encrypted" option), entering an uncrypted password with the "encrypted" option (apply MD5 transform), or entering an uncrypted password with the "unencrypted" option (do nothing). I suppose we could have instead invented an ALREADY_CRYPTED option instead, but we didn't, for reasons I don't recall at the moment; but I think it had something to do with making life easier for pg_dumpall. > (4) The naming standard for system catalogs would dictate that the > 'passwd' field of pg_shadow actually be named 'usepasswd' or > something similar, wouldn't it? The same applies to the 'valuntil > field. Yeah, they are both ancient mistakes. It's not worth trying to fix now however; we'd just break client queries. regards, tom lane
-
Re: encrypted passwords
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T22:38:44Z
Tom Lane wrote: > Hmm. I thought it *was* done, but it looks like Bruce forgot to change > the actual guc.c value? The docs and postgresql.conf.sample claim the > default is true... > > 2002-06-14 21:29 momjian > > * doc/src/sgml/runtime.sgml, > src/backend/utils/misc/postgresql.conf.sample: Make encryption of > stored passwords the default, as discussed months ago. > > Seem to be one file short on that commit ... Fixed. > > (3) (Related to 2b above) Shouldn't we reject an attempt by the user > > to specify an un-encrypted password that matches the isMD5() test? > > No, see above. There are actually three cases here: entering a > previously encrypted password (in which case do nothing to it regardless > of the "encrypted" option), entering an uncrypted password with the > "encrypted" option (apply MD5 transform), or entering an uncrypted > password with the "unencrypted" option (do nothing). > > I suppose we could have instead invented an ALREADY_CRYPTED option > instead, but we didn't, for reasons I don't recall at the moment; > but I think it had something to do with making life easier for > pg_dumpall. I think there wasn't a reason to make the distinction because it could be detected automatically, and an admin copying a password from somewhere else could easily accidentally double-encrypt the password, which then wouldn't work. It also allowed auto-migration to encrypted passwords from an old dump file. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: encrypted passwords
Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T23:01:14Z
Bruce Momjian <pgman@candle.pha.pa.us> writes: > It also allowed auto-migration to encrypted passwords from an old dump > file. Ah, right, that was it: we wanted to be able to have a pg_dumpall script containing a mix of crypted and noncrypted passwords in CREATE USER commands be loaded either as-is, or have all the passwords forced to crypted form, depending on the setting of password_encryption. So we didn't really want the CREATE USER commands in the script to say exactly what to do. Therefore, in the design as released the CREATE USER commands emitted by pg_dumpall don't actually say either ENCRYPTED or UNENCRYPTED. We didn't see a need for ALREADY_CRYPTED either, figuring that it would actually be more reliable to deduce that by looking at the data than by having a separate flag for it. regards, tom lane