Thread: encrypted passwords
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"
thata 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
justhappened 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
havingthe 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
theisMD5() 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 | md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxfoo2 |
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
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?
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, Pennsylvania19073
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, Pennsylvania19073
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 ofstored 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
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, Pennsylvania19073
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