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" 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
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, Pennsylvania 19073Rod 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
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
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 19073Bruce 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