Encrypting pg_shadow passwords
People have complained that we store passwords unencrypted in pg_shadow.
Long ago we agreed to a solution and I am going to try to implement that
next.
What we do now with crypt authentication is that the postmaster reads the
plain-text password out of pg_shadow and encrypts it with a random salt.
That random salt is sent to the client, and the client encrypts with the
supplied salt and sends it to the postmaster. If they match, the client
is authenticated.
The solution for encrypting passwords stored in pg_shadow was to encrypt
them when they are stored in pg_shadow. When a client wants to connect,
the pre-encrypted password is encrypted again with a random salt. The
pg_shadow salt and random salt are sent to the client where the client
performs to encryptions --- one with the pg_shadow salt and one with the
random salt, and sends them back to the postmaster.
It should be pretty easy to do because the encryption code is already
there.
The problem is for older clients. Do I need to create a new encryption
type for this double-encryption? Seems we do.
The bigger problem is how usernames encrypted in pg_shadow can be used
to perform the old 'crypt' authentication. We could sent the pg_shadow
salt to the client each time, but that leaves snoopers able to replay
the dialog to gain authentication because the salt isn't random anymore.
Migrating old sites to encrypted pg_shadow passwords should be easy if a
trigger on pg_shadow will look for unencrypted INSERTs and encrypt them.
This is unrelated to moving to MD5 encryption, which is another item on
our list.
Comments? Seems like lots of old crypt-using client binaries will break
because as soon as someone is encrypted in pg_shadow, we can't use
crypt.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026The solution for encrypting passwords stored in pg_shadow was to encrypt
them when they are stored in pg_shadow. When a client wants to connect,
the pre-encrypted password is encrypted again with a random salt. The
pg_shadow salt and random salt are sent to the client where the client
performs to encryptions --- one with the pg_shadow salt and one with the
random salt, and sends them back to the postmaster.
Once we encrypt in pg_shadow we will be able to use secondary passwords
with 'crypt' or whatever we call the new authentication protocol. Prior
to this we couldn't because secondary password files contain encrypted
passwords.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
The problem is for older clients. Do I need to create a new encryption
type for this double-encryption? Seems we do.
Hmm ... AFAIR that old discussion, backwards compatibility was not
thought about at all :-(
The bigger problem is how usernames encrypted in pg_shadow can be used
to perform the old 'crypt' authentication. We could sent the pg_shadow
salt to the client each time, but that leaves snoopers able to replay
the dialog to gain authentication because the salt isn't random anymore.
Clearly not a good idea.
Migrating old sites to encrypted pg_shadow passwords should be easy if a
trigger on pg_shadow will look for unencrypted INSERTs and encrypt them.
If encrypting pg_shadow will break the old-style crypt method, then I
think forcing a conversion via a trigger is unacceptable. It will have
to be a DBA choice (at configure time, or possibly initdb?) whether to
use encryption or not in pg_shadow; accordingly, either crypt or "new
crypt" auth method will be supported by the server, not both. But
client libraries could be built to support both auth methods.
This is unrelated to moving to MD5 encryption, which is another item on
our list.
It may be unrelated in theory, but in practice we should do both at
the same time to minimize the number of client-library incompatibility
issues that arise. I'd suggest that old-style crypt auth continue to
use the crypt() call forever, while the new-style should be based on
MD5 not crypt() from the get-go.
In a release or three we could discontinue support for old-style crypt,
but I think we must allow a transition period for people to update their
clients.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
The problem is for older clients. Do I need to create a new encryption
type for this double-encryption? Seems we do.Hmm ... AFAIR that old discussion, backwards compatibility was not
thought about at all :-(
Yes, clearly.
The bigger problem is how usernames encrypted in pg_shadow can be used
to perform the old 'crypt' authentication. We could sent the pg_shadow
salt to the client each time, but that leaves snoopers able to replay
the dialog to gain authentication because the salt isn't random anymore.Clearly not a good idea.
Yep, big problem because they think they are safe but they are not.
Better to just reject it.
Migrating old sites to encrypted pg_shadow passwords should be easy if a
trigger on pg_shadow will look for unencrypted INSERTs and encrypt them.If encrypting pg_shadow will break the old-style crypt method, then I
think forcing a conversion via a trigger is unacceptable. It will have
to be a DBA choice (at configure time, or possibly initdb?) whether to
use encryption or not in pg_shadow; accordingly, either crypt or "new
crypt" auth method will be supported by the server, not both. But
client libraries could be built to support both auth methods.
I hate to add initdb options because it may be confusing. I wonder if
we should have a script that encrypts the pg_shadow entries that can be
run when the administrator knows that there are no old clients left
around. That way it can be run _after_ initdb.
This is unrelated to moving to MD5 encryption, which is another item on
our list.It may be unrelated in theory, but in practice we should do both at
the same time to minimize the number of client-library incompatibility
issues that arise. I'd suggest that old-style crypt auth continue to
use the crypt() call forever, while the new-style should be based on
MD5 not crypt() from the get-go.In a release or three we could discontinue support for old-style crypt,
but I think we must allow a transition period for people to update their
clients.
Yes, MD5 is something that probably should be done at the same time to
minimize disruption.
Another idea is to ship 7.2 with double-salt available to clients but
not enabled in pg_shadow then enable it in 7.3.
I think the script idea may be best but it will have to be saved
somewhere so once you run it all future password changes are encrypted
in pg_shadow.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
I think the script idea may be best but it will have to be saved
somewhere so once you run it all future password changes are encrypted
in pg_shadow.
More to the point, how does the postmaster know that it's now dealing
with encrypted passwords and must use the double-salt auth method?
Seems to me that this is not a simple matter of changing the data in one
column of pg_shadow.
The thing I like about a configure option is that when it's in place you
know it's in place. No question of whether some rows of pg_shadow
managed to escape being updated, or any silliness like that. Your point
about "they think they are safe but they are not" seems relevant here.
regards, tom lane
This is unrelated to moving to MD5 encryption, which is another item on
our list.It may be unrelated in theory, but in practice we should do both at
the same time to minimize the number of client-library incompatibility
issues that arise. I'd suggest that old-style crypt auth continue to
use the crypt() call forever, while the new-style should be based on
MD5 not crypt() from the get-go.
I don't know if this was discussed earlier, but for client authentication I
think SHA-1 is preferred over MD5 because of weaknesses discovered in MD5.
Also an HMAC using SHA-1 is preferred for authentication over just simply
using a hash of the password. Ideally the server sends a random session
token to the client, the client creates an HMAC (using SHA-1) from the token
and their password locally, and sends that back to the server. The server
then calculates the HMAC itself using the client's (server stored) password
and the session token. If they match, the client is authenticated. If we
want to store a SHA-1 hash instead of the plain-text password, just do SHA-1
over the plain-text password on the client side before using it as the HMAC
key.
The downside of storing the password as a SHA-1 hash versus symmetrically
encrypting (or leaving as plain-text) it is that there's no way to recover
the password from the hash if the client forgets it. Of course, the
superuser can just reset it to a known value.
-- Joe
On Fri, 15 Jun 2001, Bruce Momjian wrote:
Migrating old sites to encrypted pg_shadow passwords should be easy if a
trigger on pg_shadow will look for unencrypted INSERTs and encrypt them.If encrypting pg_shadow will break the old-style crypt method, then I
think forcing a conversion via a trigger is unacceptable. It will have
to be a DBA choice (at configure time, or possibly initdb?) whether to
use encryption or not in pg_shadow; accordingly, either crypt or "new
crypt" auth method will be supported by the server, not both. But
client libraries could be built to support both auth methods.I hate to add initdb options because it may be confusing. I wonder if
we should have a script that encrypts the pg_shadow entries that can be
run when the administrator knows that there are no old clients left
around. That way it can be run _after_ initdb.
Which clients actually read pg_shadow? I always thought that only the
postmaster read it.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
On Fri, 15 Jun 2001, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
I think the script idea may be best but it will have to be saved
somewhere so once you run it all future password changes are encrypted
in pg_shadow.More to the point, how does the postmaster know that it's now dealing
with encrypted passwords and must use the double-salt auth method?
Seems to me that this is not a simple matter of changing the data in one
column of pg_shadow.
The first three characters are md5 in the code I sent Bruce. The
auth stuff starts looking at the 4th character of the password column
in pg_shadow.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Bruce Momjian writes:
People have complained that we store passwords unencrypted in pg_shadow.
Long ago we agreed to a solution and I am going to try to implement that
next.
Whatever you do, please wait till I've finished the "authenticate after
fork" change. (this weekend?)
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Fri, 15 Jun 2001, Peter Eisentraut wrote:
Bruce Momjian writes:
People have complained that we store passwords unencrypted in pg_shadow.
Long ago we agreed to a solution and I am going to try to implement that
next.Whatever you do, please wait till I've finished the "authenticate after
fork" change. (this weekend?)
If you are going to do this this weekend, should I just wait with the PAM
patch until then? (Patch against the new code)
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
Can someone point me in the direction of any documentation related to
client/backend protocol. If it's use the source, that's ok too.
Dave
Dave Cramer writes:
Can someone point me in the direction of any documentation related to
client/backend protocol. If it's use the source, that's ok too.
http://www.ca.postgresql.org/devel-corner/docs/postgres/protocol.html
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Vince Vielhaber <vev@michvhf.com> writes:
More to the point, how does the postmaster know that it's now dealing
with encrypted passwords and must use the double-salt auth method?
The first three characters are md5 in the code I sent Bruce.
Uh ... so if I use a password that starts with "md5", it breaks?
Seems like adding an additional column to pg_shadow would be a better
technique.
regards, tom lane
On Fri, 15 Jun 2001, Tom Lane wrote:
Vince Vielhaber <vev@michvhf.com> writes:
More to the point, how does the postmaster know that it's now dealing
with encrypted passwords and must use the double-salt auth method?The first three characters are md5 in the code I sent Bruce.
Uh ... so if I use a password that starts with "md5", it breaks?
yup.
Seems like adding an additional column to pg_shadow would be a better
technique.
I agree but that was frowned upon for reasons I don't recall now.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
On Fri, 15 Jun 2001, Vince Vielhaber wrote:
On Fri, 15 Jun 2001, Tom Lane wrote:
Vince Vielhaber <vev@michvhf.com> writes:
More to the point, how does the postmaster know that it's now dealing
with encrypted passwords and must use the double-salt auth method?The first three characters are md5 in the code I sent Bruce.
Uh ... so if I use a password that starts with "md5", it breaks?
yup.
Actually let me elaborate. If you use a password that starts with
md5 AND is 35 characters in length.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Peter Eisentraut <peter_e@gmx.net> writes:
Whatever you do, please wait till I've finished the "authenticate after
fork" change. (this weekend?)
Oh, are you doing that? I thought you weren't convinced it was a good
idea ...
regards, tom lane
In a release or three we could discontinue support for old-style crypt,
but I think we must allow a transition period for people to update their
clients.Yes, MD5 is something that probably should be done at the same time to
minimize disruption.
while we are on the subject of auth and password and crypt, i noticed some
time ago, that there was an inconsistency in the way the auth passwd/crypt
stuff worked.
we have:
host dbname x.x.x.x x.x.x.x password somefile
this method takes a clear-text password from the client, encrypts it
and compares it against the matching second field of "somefile"
ie. somefile is a traditional /etc/passwd style file
i like to think of this as "telnet" authentication, user/passwd in clear text.
server stores pre-encrypted passwords.
and i use it for access from my php scripts, thus avoiding the necessity of
giving "webuser" access to my tables, and setting up some kinda secondary
authentication table.
the docs in pg_hba.conf lead you to believe that if you leave off "somefile",
then it does the same thing, but compares against pg_shadow.
however, and i don't know that this was intentional, but if you leave
"somefile" off, it compares the plain-text user password against the raw
value of the pg_shadow passwd field.
i wanted a behaviour as above, encrypt the clear text, and compare against
the stored pre-encrypted password in pg_shadow.
given that there are many installations which may be using things as they
are, i have created a set of patches which do:
host dbname x.x.x.x x.x.x.x password pg_shadow
(pg_shadow is a "reserved word, similar to the way "sameuser" is used with
ident)
this method takes a clear-text password from the client, encrypts it
and compares it against the password stored in pg_shadow.
this method should not conflict with anyone, except those who actually
want to use a /usr/local/pgsql/data/pg_shadow as their passwd file.
(i seem to recall previous versions actually stored the user data in
that specific file, although it was not in /etc/passwd format)
in my opinion, this method allows you to have pgusers which are wholy
seperate from /etc/passwd users. and allows you to manage them entirely
within postgresql.
you can have a front end which does a
CREATE USER joe WITH PASSWORD 'crypto-gunge';
[ patch attached ]
--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]
Tom Lane writes:
Whatever you do, please wait till I've finished the "authenticate after
fork" change. (this weekend?)Oh, are you doing that? I thought you weren't convinced it was a good
idea ...
I do think it's a win, but the point about the connection limits just
needed to be addressed, which it has. The code changes turned out to be
easier than I expected them to be, so I might as well finish it now.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
[ oops, the patch is actually attached this time ]
In a release or three we could discontinue support for old-style crypt,
but I think we must allow a transition period for people to update their
clients.Yes, MD5 is something that probably should be done at the same time to
minimize disruption.
while we are on the subject of auth and password and crypt, i noticed some
time ago, that there was an inconsistency in the way the auth passwd/crypt
stuff worked.
we have:
host dbname x.x.x.x x.x.x.x password somefile
this method takes a clear-text password from the client, encrypts it
and compares it against the matching second field of "somefile"
ie. somefile is a traditional /etc/passwd style file
i like to think of this as "telnet" authentication, user/passwd in clear text.
server stores pre-encrypted passwords.
and i use it for access from my php scripts, thus avoiding the necessity of
giving "webuser" access to my tables, and setting up some kinda secondary
authentication table.
the docs in pg_hba.conf lead you to believe that if you leave off "somefile",
then it does the same thing, but compares against pg_shadow.
however, and i don't know that this was intentional, but if you leave
"somefile" off, it compares the plain-text user password against the raw
value of the pg_shadow passwd field.
i wanted a behaviour as above, encrypt the clear text, and compare against
the stored pre-encrypted password in pg_shadow.
given that there are many installations which may be using things as they
are, i have created a set of patches which do:
host dbname x.x.x.x x.x.x.x password pg_shadow
(pg_shadow is a "reserved word, similar to the way "sameuser" is used with
ident)
this method takes a clear-text password from the client, encrypts it
and compares it against the password stored in pg_shadow.
this method should not conflict with anyone, except those who actually
want to use a /usr/local/pgsql/data/pg_shadow as their passwd file.
(i seem to recall previous versions actually stored the user data in
that specific file, although it was not in /etc/passwd format)
in my opinion, this method allows you to have pgusers which are wholy
seperate from /etc/passwd users. and allows you to manage them entirely
within postgresql.
you can have a front end which does a
CREATE USER joe WITH PASSWORD 'crypto-gunge';
[ patch attached ]
--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]
Attachments:
auth.patchtext/plain; charset=us-asciiDownload+44-44
On Fri, Jun 15, 2001 at 07:57:37AM -0700, Joe Conway wrote:
This is unrelated to moving to MD5 encryption, which is another item on
our list.It may be unrelated in theory, but in practice we should do both at
the same time to minimize the number of client-library incompatibility
issues that arise. I'd suggest that old-style crypt auth continue to
use the crypt() call forever, while the new-style should be based on
MD5 not crypt() from the get-go.I don't know if this was discussed earlier, but for client
authentication I think SHA-1 is preferred over MD5 because of
weaknesses discovered in MD5. Also an HMAC using SHA-1 is preferred
for authentication over just simply using a hash of the password.Ideally the server sends a random session token to the client, the
client creates an HMAC (using SHA-1) from the token and their password
locally, and sends that back to the server. The server then calculates
the HMAC itself using the client's (server stored) password and the
session token. If they match, the client is authenticated. If we want
to store a SHA-1 hash instead of the plain-text password, just do
SHA-1 over the plain-text password on the client side before using it
as the HMAC key.
Well said. (I don't recall what HMAC stands for; maybe others don't also.)
A diagram of the above, if you'll forgive the ASCII art, is:
password random challenge (from server)
| |
(SHA) |
| |
PW hash (stored in server) |
| |
+--------------> + <------------+
|
(SHA)
|
(compare)
The downside of storing the password as a SHA-1 hash versus symmetrically
encrypting (or leaving as plain-text) it is that there's no way to recover
the password from the hash if the client forgets it. Of course, the
superuser can just reset it to a known value.
I see no value in supporting password recovery, and good reasons not to
support it. Using MD5 or SHA implies we should talk about "password
hashing" rather than "password encryption".
Nathan Myers
ncm@zembu.com
