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 19026

Thu, 14 Jun 2001 20:16:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Encrypting pg_shadow passwords
> 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.

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

Thu, 14 Jun 2001 20:47:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
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

Fri, 15 Jun 2001 10:03:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Encrypting pg_shadow passwords 
> 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 19026

Fri, 15 Jun 2001 10:11:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
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 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

Fri, 15 Jun 2001 10:34:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Encrypting pg_shadow passwords 
> > 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



Fri, 15 Jun 2001 07:57:37 -0700
"Joe Conway" <joseph.conway@home.com>
Re: Encrypting pg_shadow passwords 
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
==========================================================================




Fri, 15 Jun 2001 11:00:17 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: Encrypting pg_shadow passwords
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
==========================================================================




Fri, 15 Jun 2001 11:28:15 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: Encrypting pg_shadow passwords 
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


Fri, 15 Jun 2001 18:27:02 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Encrypting pg_shadow passwords
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/


Fri, 15 Jun 2001 12:07:56 -0500 (CDT)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: Encrypting pg_shadow passwords
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


Fri, 15 Jun 2001 13:16:10 -0400
"Dave Cramer" <dave@fastcrypt.com>
Protocol Documentation
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


Fri, 15 Jun 2001 19:56:46 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Protocol Documentation
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

Fri, 15 Jun 2001 14:02:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Encrypting pg_shadow passwords 
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
==========================================================================




Fri, 15 Jun 2001 14:29:09 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: Encrypting pg_shadow passwords 
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
==========================================================================




Fri, 15 Jun 2001 14:31:04 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: Encrypting pg_shadow passwords 
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

Fri, 15 Jun 2001 14:33:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Encrypting pg_shadow passwords 
> > 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. ]

Fri, 15 Jun 2001 17:42:22 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
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


Fri, 15 Jun 2001 23:47:21 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Encrypting pg_shadow passwords 
[ 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. ]
Fri, 15 Jun 2001 17:58:57 -0400
Jim Mercer <jim@reptiles.org>
oops Re: Encrypting pg_shadow passwords
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

Fri, 15 Jun 2001 16:16:45 -0700
ncm@zembu.com (Nathan Myers)
Re: Encrypting pg_shadow passwords
> Well said.  (I don't recall what HMAC stands for; maybe others don't
also.)
>

HMAC stands for Hash based Message Authentication Code. Here's a link to the
paper which originally introduced it, for those who are interested:

http://www-cse.ucsd.edu/users/mihir/papers/kmd5.pdf

-- Joe


Fri, 15 Jun 2001 16:44:23 -0700
"Joe Conway" <joseph.conway@home.com>
Re: Encrypting pg_shadow passwords
At 02:02 PM 6/15/01 -0400, 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?
>
>Seems like adding an additional column to pg_shadow would be a better
>technique.

I agree. It helps with migration and other things.

For my apps I have: hashed_password, hashtype, salt. I even had MSG at one
point ;) - MSG=Multiple Salt Grinds (the number of times you do the
hashing), but my fellow developers didn't want that.

So if the hash type is NONE and the salt is '', the hashed password is
actually in plaintext. This is very useful when migrating users or creating
users manually, then when the users next change their password (like NEVER
;) ) it will be using the default hash method. So say you start with
MD5BASE64 you can switch to MD5HEX or SHA1HEX later.

BTW, the passing of one time passwords, and subsequently communicating in
plaintext is a bit too '80s for me. Sure the performance is better, but I
think it should be deprecated. If you need to use encryption then having
_everything_ encrypted is a better idea - SSL etc. Those >1GHz CPUs are
handy ;).

Cheerio,
Link.


Sat, 16 Jun 2001 11:20:30 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Encrypting pg_shadow passwords 
On Sat, 16 Jun 2001, Lincoln Yeoh wrote:

> At 02:02 PM 6/15/01 -0400, 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?
> >
> >Seems like adding an additional column to pg_shadow would be a better
> >technique.
> 
> I agree. It helps with migration and other things.
> 
> For my apps I have: hashed_password, hashtype, salt. I even had MSG at one
> point ;) - MSG=Multiple Salt Grinds (the number of times you do the
> hashing), but my fellow developers didn't want that.

Actually, there's more or less bsdish convention at this (implementation
of multiple ways of hashing a password). It may be a good idea to do
postgres' implementation to conform to this convention. (Net/Free/OpenBSD
all store password in passwd file according to this convention).


Convention is, crypted password looks like $algorithm_id$salt$hash
Where algorithm_id is a small system-dependent number, convention is that
algorithm_id 1 is MD5 and is supported everywhere. 

Benefit of adhering to this is that, assuming postgresql doesn't invent
its own algorithms for hashing, you can exchange password data between
system files and postgresql shadow table, and also, you don't need to
massage data coming from conformant bsd-ish crypt implementations.

-alex


Fri, 15 Jun 2001 23:44:41 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Re: Encrypting pg_shadow passwords 
On Sat, Jun 16, 2001 at 11:20:30AM +0800, Lincoln Yeoh wrote:
> If you need to use encryption then having _everything_ encrypted is a
> better idea - SSL etc. Those >1GHz CPUs are handy ;).

[ yes, i noted the smiley ]

it is rather unfortunate to see the OSS community buying into the tenents
that allowed microsoft to get world domination based on crap quality
software.

"hardware is cheap" is a falsehood.

some people might be suprised at the number of 486's and Pentium 100's that
are still in active use.

in some places, that is leading edge technology, mostly due to economic
realities.

and in a sense, people who have limited resources can be some of the
most satisfied recipients of OSS efforts.

please, lets not have postgresql turn into an OSS behemoth like Mozilla
or OpenOffice.

i am not opposed to more new features, or even adding support for leading
edge hardware.

but, i think it is important to retain as much backward compatibility as
possible.

as well, features should be able to be turned off such that they don't
overly bloat the code.

FreeBSD 4.3 can still be built on a 486.  i'm sure if someone was patient
enought to wait, it could be built on a 386.

this is because it very rarely drops support for something in its code base,
and it makes most features (in the kernel) optional, to limit bloat.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Sat, 16 Jun 2001 00:04:58 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Encrypting pg_shadow passwords
At 12:04 AM 6/16/01 -0400, Jim Mercer wrote:
>On Sat, Jun 16, 2001 at 11:20:30AM +0800, Lincoln Yeoh wrote:
>> If you need to use encryption then having _everything_ encrypted is a
>> better idea - SSL etc. Those >1GHz CPUs are handy ;).
>
>[ yes, i noted the smiley ]
>
>it is rather unfortunate to see the OSS community buying into the tenents
>that allowed microsoft to get world domination based on crap quality
>software.
>
>"hardware is cheap" is a falsehood.

My point is if you really need encryption, then your data should be
encrypted too, otherwise it seems a waste of time or more a "feel good" thing.

I find it hard to recommend a setup where just the authentication portion
is encrypted but all the data is left in plaintext for everyone to see. Why
go to all that trouble to _fool_ yourself, when you can either do it
securely (encrypt everything), or do it quick (no encryption).

I'd personally put "only authentication is encrypted" in the "crossing a
chasm in two leaps" category.

Yoda says it better ;).

Cheerio,
Link.


Sun, 17 Jun 2001 23:05:52 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Re: Encrypting pg_shadow passwords
On Sun, Jun 17, 2001 at 11:05:52PM +0800, Lincoln Yeoh wrote:
> At 12:04 AM 6/16/01 -0400, Jim Mercer wrote:
> >On Sat, Jun 16, 2001 at 11:20:30AM +0800, Lincoln Yeoh wrote:
> >> If you need to use encryption then having _everything_ encrypted is a
> >> better idea - SSL etc. Those >1GHz CPUs are handy ;).
> >
> >[ yes, i noted the smiley ]
> >
> >it is rather unfortunate to see the OSS community buying into the tenents
> >that allowed microsoft to get world domination based on crap quality
> >software.
> >
> >"hardware is cheap" is a falsehood.
> 
> My point is if you really need encryption, then your data should be
> encrypted too, otherwise it seems a waste of time or more a "feel good" thing.

i would agree with that.

i guess my rantwas moreso in reaction to what i was as creeping featurism,
with words aluding to "depreciating" legacy functionality.

maybe not your words, but that was what set me off on this thread.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Sun, 17 Jun 2001 11:28:16 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Re: Encrypting pg_shadow passwords
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

> My point is if you really need encryption, then your data should be
> encrypted too, otherwise it seems a waste of time or more a "feel
> good" thing.

I would disagree.  I think there is a level of security where it's not 
a catastrophe if someone sniffs and reconstructs your traffic, but
it's fairly important that such a person not be able to authenticate
as you.  Most of my personal email (and, I assert, most people's)
falls into this category.  Encrypted challenge/response addresses this 
need quite well. 

Naturally, if you're working at a level where intercepted traffic *is* 
catastrophic, you should be doing end-to-end encryption and all that
good stuff.

-Doug
-- 
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

17 Jun 2001 11:46:05 -0400
Doug McNaught <doug@wireboard.com>
Re: Re: Re: Encrypting pg_shadow passwords
Dominic J. Eidson writes:

> 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)

This is finished, more or less, so both of you can look at
backend/libpq/auth.c, function ClientAuthentication() and hook in whatever
you want, blocking however long you want.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 20 Jun 2001 20:33:06 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Encrypting pg_shadow passwords
At 12:20 AM 26-06-2001 -0400, Bruce Momjian wrote:
>> 
>> at this point in time, i do not see a method of doing that without my mods
>> or using external password files.
>
>We will do double-crypt and everyone will be happy, right?

Wow optimistic :).

>> if the API as above existed, then i would be happy to see "password" go
away
>> (although it should be depreciated to a --enable option, otherwise you are
>> going to ruin a bunch of existing code).
>
>Who is using it?  We can continue to allow it but at some point there is
>no purpose to it unless you have clients that are pre-7.2.  Double-crypt
>removes the use for it, no?

I'm using "password".

If I feel that the wire isn't safe I'll use SSL.

Cheerio,
Link.


Mon, 25 Jun 2001 14:07:53 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Encrypting pg_shadow passwords
At 12:51 AM 26-06-2001 -0400, Jim Mercer wrote:

>my mods are server-side only.
>
>to rewind a bit.
>
>my mods correct this by doing:
>
>with an AUTH_ARGUMENT == "pg_shadow", the process is:
>tmp_pwd = crypt(client->passwd, pg_shadow->passwd)
>if strcmp(tmp_pwd, pg_shadow->passwd) == 0
>   access allowed
>else
>   access not allowed
>
>this is not so much an enhancement, but a correction of what i think the
>original "password" authentication scheme was supposed to allow.
>

Yep it's a correction. pg_shadow shouldn't have been in plaintext in the
first place.

 host all 127.0.0.1 255.255.255.255 password 
should have meant check crypted passwords in pg_shadow.

Given your suggestion, what happens when someone does an ALTER USER ...
WITH PASSWORD ....? 

Might it be too late to do a fix? 

HMAC sounds interesting. What would the impact be on stuff like Pg DBD?

Cheerio,
Link.





Mon, 25 Jun 2001 14:34:51 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Encrypting pg_shadow passwords

I am replying to the original message because it has all the relevant
information.  The problem with 'password' authentication is that the
password goes across the wire in plaintext.  Now, if we start to ship
encrypted passwords across the wire, but use the same salt for every
authentication request, we are really no more secure than if we sent it
in the clear.

If a user specifies 'crypt' in pg_hba.conf, they should be assured that
the password is safe in case someone snoops it.  Encrypting pg_shadow
and comparing that with the same salt every time is not secure from
snooping so we don't allow it.

Am I missing something?

> > > 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. ]
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 

-- 
  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

Mon, 25 Jun 2001 23:27:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
On Mon, Jun 25, 2001 at 11:27:42PM -0400, Bruce Momjian wrote:
> I am replying to the original message because it has all the relevant
> information.  The problem with 'password' authentication is that the
> password goes across the wire in plaintext.  Now, if we start to ship
> encrypted passwords across the wire, but use the same salt for every
> authentication request, we are really no more secure than if we sent it
> in the clear.
>
> If a user specifies 'crypt' in pg_hba.conf, they should be assured that
> the password is safe in case someone snoops it.  Encrypting pg_shadow
> and comparing that with the same salt every time is not secure from
> snooping so we don't allow it.
> 
> Am I missing something?

i don't disagree that sending plaintext across the wire, if possible, it
should be avoided.

however, i look at it this way.

many _existing_ implementations send plaintext across the wire, telnet,
ftp, .htaccess, imap and pop (non-ssl).

i would much rather risk a single plain-text password being snooped on the
wire, rather than having an entire database of plain-text passwords for
someone to scoop.

many people re-use passwords for multiple purposes, thus reducing the bio-core
required to keep track of a bazillion passwords.

in my opinion, storing plain-text passwords in any media is just plain wrong,
and a far greater security risk than having a password sniffed.

in my applications, i have SSL covering the client->app (browser->PHP code),
so the sniffing would need to be on the wire from the app-server -> database
server, which in many cases is the same machine.

my mods don't alter the operation of the server in any respect.

they do, however, allow people the choice of using a traditional
telnetd/binlogin authentication scheme without resorting to external password
files.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Mon, 25 Jun 2001 23:42:27 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
> On Mon, Jun 25, 2001 at 11:27:42PM -0400, Bruce Momjian wrote:
> > I am replying to the original message because it has all the relevant
> > information.  The problem with 'password' authentication is that the
> > password goes across the wire in plaintext.  Now, if we start to ship
> > encrypted passwords across the wire, but use the same salt for every
> > authentication request, we are really no more secure than if we sent it
> > in the clear.
> >
> > If a user specifies 'crypt' in pg_hba.conf, they should be assured that
> > the password is safe in case someone snoops it.  Encrypting pg_shadow
> > and comparing that with the same salt every time is not secure from
> > snooping so we don't allow it.
> > 
> > Am I missing something?
> 
> i don't disagree that sending plaintext across the wire, if possible, it
> should be avoided.
> 
> however, i look at it this way.
> 
> many _existing_ implementations send plaintext across the wire, telnet,
> ftp, .htaccess, imap and pop (non-ssl).
> 
> i would much rather risk a single plain-text password being snooped on the
> wire, rather than having an entire database of plain-text passwords for
> someone to scoop.
> 
> many people re-use passwords for multiple purposes, thus reducing the bio-core
> required to keep track of a bazillion passwords.
> 
> in my opinion, storing plain-text passwords in any media is just plain wrong,
> and a far greater security risk than having a password sniffed.
> 
> in my applications, i have SSL covering the client->app (browser->PHP code),
> so the sniffing would need to be on the wire from the app-server -> database
> server, which in many cases is the same machine.
> 
> my mods don't alter the operation of the server in any respect.
> 
> they do, however, allow people the choice of using a traditional
> telnetd/binlogin authentication scheme without resorting to external password
> files.

OK, I get you now.  Why not ask the client to do a crypt and compare
that to pg_shadow.  It is better than what we have now for 'password'
authentication because it encrypts pg_shadow.

The big problem is that you can't do 'crypt' authentication once you
encrypt pg_shadow, unless we do the double-encription thing, and I think
it is a bigger win for them to use crypt-authentication than to encrypt
pg_shadow.  The wire is clearly less secure than 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 19026

Mon, 25 Jun 2001 23:48:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
> in my applications, i have SSL covering the client->app (browser->PHP code),
> so the sniffing would need to be on the wire from the app-server -> database
> server, which in many cases is the same machine.
> 
> my mods don't alter the operation of the server in any respect.
> 
> they do, however, allow people the choice of using a traditional
> telnetd/binlogin authentication scheme without resorting to external password
> files.

One good point you have is what do we do with 'password' authentication
once we encrypt pg_shadow.  My guess is that we just disallow it.  It is
insecure and was only there for clients that couldn't do crypt.  They
all have that now.  It should just go away.  We kept it around for the
secondary password file but those secondary password files are the same
once pg_shadow is encrypted.

One item of my plan is that you can encrypt individual users.  You don't
have to do them all at once in case you have older clients for some
users but not others.

-- 
  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

Tue, 26 Jun 2001 00:00:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
On Mon, Jun 25, 2001 at 11:48:32PM -0400, Bruce Momjian wrote:
> OK, I get you now.  Why not ask the client to do a crypt and compare
> that to pg_shadow.  It is better than what we have now for 'password'
> authentication because it encrypts pg_shadow.
> 
> The big problem is that you can't do 'crypt' authentication once you
> encrypt pg_shadow, unless we do the double-encription thing, and I think
> it is a bigger win for them to use crypt-authentication than to encrypt
> pg_shadow.

my mods do not require encryption of pg_shadow, unless you want to use
my "password pg_shadow" extension.  it is then the responsibility of the
dbadmin to do "CREATE USER username WITH PASSWORD '$1$xxxxxx';
(i have a unix_crypt(text, text) function i can put in contrib, as well
as samba_lm_crypt(text) and samba_nt_crypt(text) for anyone interested)

the current code (without my mods) requires the dbadmin to either play
the lottery and store all passwords in plain-text, or to manipulate
external password files, which causes all manner of issues with regards
to updating (changing) the passwords in the external files.

> The wire is clearly less secure than pg_shadow.

ah, you've not had a client rooted lately.

the wire is far more secure than many default OS installations.

i will not argue that the double-encryption stuff, and MD5 type stuff is
better.

however, forcing the dbadmin to store plain-text passwords in pg_shadow
is at best unwise.

giving them the option of my mods is a reasonable step towards allowing
them to avoid that one-stop-shopping facility for crackers, without breaking
any existing implementations for those who chose to walk what i consider
an unsafe path.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 00:01:03 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 12:00:35AM -0400, Bruce Momjian wrote:
> One good point you have is what do we do with 'password' authentication
> once we encrypt pg_shadow.  My guess is that we just disallow it.  It is
> insecure and was only there for clients that couldn't do crypt.  They
> all have that now.  It should just go away.  We kept it around for the
> secondary password file but those secondary password files are the same
> once pg_shadow is encrypted.

i would be content if the API allowed me to pass it a plain-text password,
and that was compared against pg_shadow, where the password is stored
encrypted.

at this point in time, i do not see a method of doing that without my mods
or using external password files.

if the API as above existed, then i would be happy to see "password" go away
(although it should be depreciated to a --enable option, otherwise you are
going to ruin a bunch of existing code).

> One item of my plan is that you can encrypt individual users.  You don't
> have to do them all at once in case you have older clients for some
> users but not others.

it would be nice (in my opinion) if you could have multiple (cascade) entries
in pg_hba.conf.

and a flag in pg_shadow to "appoint" a blessed scheme.

ie. if a user identd's ok, and the identd flag is set in pg_shadow, then
it is ok.  otherwise, move on to the next pg_hba.conf entry.

the reasoning for this is that i (and i assume others) have two classes of
access.  some type of authenticated client/user and scripts.

hardcoding passwords in scripts is just wrong.

i sometimes have "localhost" set up on ident, and non-localhost on some
type of passord/crypt type thing.  but i don't want to allow all local users
access via ident.

i recognize that some of this can be done with the ident mapping facility,
but again, that is an external file, and thus presents management issues.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 00:12:46 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
> > The wire is clearly less secure than pg_shadow.
> 
> ah, you've not had a client rooted lately.

I think most people would disagree.

> the wire is far more secure than many default OS installations.

Maybe time for a new OS.  We run on some pretty secure OS's.

> i will not argue that the double-encryption stuff, and MD5 type stuff is
> better.
> 
> however, forcing the dbadmin to store plain-text passwords in pg_shadow
> is at best unwise.
> 
> giving them the option of my mods is a reasonable step towards allowing
> them to avoid that one-stop-shopping facility for crackers, without breaking
> any existing implementations for those who chose to walk what i consider
> an unsafe path.

The big problem is that when we make a change we have to also talk to
old clients to you would have a pretty complex setup to have 'password'
encryption passing the same crypt over the wire all the time.  If not,
why not use 'crypt' authentication.

-- 
  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

Tue, 26 Jun 2001 00:17:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
> On Tue, Jun 26, 2001 at 12:00:35AM -0400, Bruce Momjian wrote:
> > One good point you have is what do we do with 'password' authentication
> > once we encrypt pg_shadow.  My guess is that we just disallow it.  It is
> > insecure and was only there for clients that couldn't do crypt.  They
> > all have that now.  It should just go away.  We kept it around for the
> > secondary password file but those secondary password files are the same
> > once pg_shadow is encrypted.
> 
> i would be content if the API allowed me to pass it a plain-text password,
> and that was compared against pg_shadow, where the password is stored
> encrypted.
> 
> at this point in time, i do not see a method of doing that without my mods
> or using external password files.

We will do double-crypt and everyone will be happy, right?

> 
> if the API as above existed, then i would be happy to see "password" go away
> (although it should be depreciated to a --enable option, otherwise you are
> going to ruin a bunch of existing code).

Who is using it?  We can continue to allow it but at some point there is
no purpose to it unless you have clients that are pre-7.2.  Double-crypt
removes the use for it, no?

> 
> > One item of my plan is that you can encrypt individual users.  You don't
> > have to do them all at once in case you have older clients for some
> > users but not others.
> 
> it would be nice (in my opinion) if you could have multiple (cascade) entries
> in pg_hba.conf.
> 
> and a flag in pg_shadow to "appoint" a blessed scheme.
> 
> ie. if a user identd's ok, and the identd flag is set in pg_shadow, then
> it is ok.  otherwise, move on to the next pg_hba.conf entry.
> 
> the reasoning for this is that i (and i assume others) have two classes of
> access.  some type of authenticated client/user and scripts.
> 
> hardcoding passwords in scripts is just wrong.
> 
> i sometimes have "localhost" set up on ident, and non-localhost on some
> type of passord/crypt type thing.  but i don't want to allow all local users
> access via ident.
> 
> i recognize that some of this can be done with the ident mapping facility,
> but again, that is an external file, and thus presents management issues.

Our authentication system is already too complex.  I would prefer not to
make it more so.  The more complex, the more mistakes admins make.

-- 
  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

Tue, 26 Jun 2001 00:20:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
[ this message is not meant to be completely denigrating to linux.  YMMV ]

On Tue, Jun 26, 2001 at 12:17:03AM -0400, Bruce Momjian wrote:
> > > The wire is clearly less secure than pg_shadow.
> > 
> > ah, you've not had a client rooted lately.
> 
> I think most people would disagree.

depends on the crowd.  i get to de-crack several linux boxes a month.

> > the wire is far more secure than many default OS installations.
> 
> Maybe time for a new OS.  We run on some pretty secure OS's.

i run a fairly tight ship as well.

however, joe blow redhat 6.1 installer who is just following the recipes
and the RPM's wouldn't know a secure OS from a hole in their head.

and Solaris is just insecure by design, lets not talk about Irix.

the design should not assume that the dbadmin has a clue.  in fact, it should
assume they don't have a clue.

i challenge you to post "i think storing plain-text passwords on my system
is ok." to NANOG.  8^)

> The big problem is that when we make a change we have to also talk to
> old clients to you would have a pretty complex setup to have 'password'
> encryption passing the same crypt over the wire all the time.  If not,
> why not use 'crypt' authentication.

i don't understand the objection to my mods.

crypt authentication requires plain-text passwords stored in pg_shadow.

my stand is that this is not a good idea.

my mods in no way break any existing code, and add another variant on the
existing auth schemes.

i think that any evolution of the auth schemes should depreciate the older
methods, but that backwards compatibility needs to be maintained, even
if the code is disabled by default, and needs a --enable to turn it back on.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 00:33:20 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
> > The big problem is that when we make a change we have to also talk to
> > old clients to you would have a pretty complex setup to have 'password'
> > encryption passing the same crypt over the wire all the time.  If not,
> > why not use 'crypt' authentication.
> 
> i don't understand the objection to my mods.
> 
> crypt authentication requires plain-text passwords stored in pg_shadow.
> 
> my stand is that this is not a good idea.
> 
> my mods in no way break any existing code, and add another variant on the
> existing auth schemes.
> 
> i think that any evolution of the auth schemes should depreciate the older
> methods, but that backwards compatibility needs to be maintained, even
> if the code is disabled by default, and needs a --enable to turn it back on.

OK, your mods are going to have to propogate to all clients.  Older
clients can't use this scheme, and once we have double-encryption, what
advantage does this have?

-- 
  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

Tue, 26 Jun 2001 00:36:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 12:20:40AM -0400, Bruce Momjian wrote:
> We will do double-crypt and everyone will be happy, right?
> 
> > if the API as above existed, then i would be happy to see "password" go away
> > (although it should be depreciated to a --enable option, otherwise you are
> > going to ruin a bunch of existing code).
> 
> Who is using it?  We can continue to allow it but at some point there is
> no purpose to it unless you have clients that are pre-7.2.  Double-crypt
> removes the use for it, no?

if the API allows a plain text password, and compares agains a cyrtpo-pg_shadow
i would imagine that would be fine.

at this point i should apologize for possibly arguing out of turn.

if 7.2 has the above, that is cool.

i'm sorta hoping my mods can make it into 7.1.3, if there is one.

> > i recognize that some of this can be done with the ident mapping facility,
> > but again, that is an external file, and thus presents management issues.
> 
> Our authentication system is already too complex.  I would prefer not to
> make it more so.  The more complex, the more mistakes admins make.

understood, but you were asking for comments.  8^)

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 00:38:24 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 12:36:43AM -0400, Bruce Momjian wrote:
> > > The big problem is that when we make a change we have to also talk to
> > > old clients to you would have a pretty complex setup to have 'password'
> > > encryption passing the same crypt over the wire all the time.  If not,
> > > why not use 'crypt' authentication.
> > 
> > i don't understand the objection to my mods.
> > 
> > crypt authentication requires plain-text passwords stored in pg_shadow.
> > 
> > my stand is that this is not a good idea.
> > 
> > my mods in no way break any existing code, and add another variant on the
> > existing auth schemes.
> > 
> > i think that any evolution of the auth schemes should depreciate the older
> > methods, but that backwards compatibility needs to be maintained, even
> > if the code is disabled by default, and needs a --enable to turn it back on.
> 
> OK, your mods are going to have to propogate to all clients.  Older
> clients can't use this scheme,

my mods are server-side only.

to rewind a bit.

the existing implementation of:

host dbname ipaddr netmask password

says:

#   password:   Authentication is done by matching a password supplied
#           in clear by the host. If AUTH_ARGUMENT is specified then
#           the password is compared with the user's entry in that   
#           file (in the $PGDATA directory).  These per-host password 
#       files can be maintained with the pg_passwd(1) utility.
#       If no AUTH_ARGUMENT appears then the password is compared
#       with the user's entry in the pg_shadow table.

this description is a tad misleading.

with an AUTH_ARGUMENT, the process is:
tmp_pwd = crypt(client->passwd, AUTH_ARGUMENT->passwd)
if strcmp(tmp_pwd, AUTH_ARGUMENT->passwd) == 0
   access allowed
else
   access not allowed

without an AUTH_ARGUMENT, the process is:
if strcmp(client->passwd, pg_shadow->passwd) == 0
   access allowed
else
   access not allowed

my mods correct this by doing:

with an AUTH_ARGUMENT == "pg_shadow", the process is:
tmp_pwd = crypt(client->passwd, pg_shadow->passwd)
if strcmp(tmp_pwd, pg_shadow->passwd) == 0
   access allowed
else
   access not allowed

this is not so much an enhancement, but a correction of what i think the
original "password" authentication scheme was supposed to allow.

> and once we have double-encryption, what advantage does this have?

once we have it, cool. as long as the passwords are not stored plain-text.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 00:51:21 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
> On Tue, Jun 26, 2001 at 12:20:40AM -0400, Bruce Momjian wrote:
> > We will do double-crypt and everyone will be happy, right?
> > 
> > > if the API as above existed, then i would be happy to see "password" go away
> > > (although it should be depreciated to a --enable option, otherwise you are
> > > going to ruin a bunch of existing code).
> > 
> > Who is using it?  We can continue to allow it but at some point there is
> > no purpose to it unless you have clients that are pre-7.2.  Double-crypt
> > removes the use for it, no?
> 
> if the API allows a plain text password, and compares agains a cyrtpo-pg_shadow
> i would imagine that would be fine.
> 
> at this point i should apologize for possibly arguing out of turn.
> 
> if 7.2 has the above, that is cool.
> 
> i'm sorta hoping my mods can make it into 7.1.3, if there is one.

Not a chance.  Only major bug fixes in 7.1.X.

-- 
  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

Tue, 26 Jun 2001 00:56:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
On Mon, Jun 25, 2001 at 02:34:51PM +0800, Lincoln Yeoh wrote:
> At 12:51 AM 26-06-2001 -0400, Jim Mercer wrote:
> >this is not so much an enhancement, but a correction of what i think the
> >original "password" authentication scheme was supposed to allow.
> 
> Yep it's a correction. pg_shadow shouldn't have been in plaintext in the
> first place.
> 
>  host all 127.0.0.1 255.255.255.255 password 
> should have meant check crypted passwords in pg_shadow.
> 
> Given your suggestion, what happens when someone does an ALTER USER ...
> WITH PASSWORD ....? 
> 
> Might it be too late to do a fix? 

i didn't want to change things too much.  in the case of ALTER USER, the
code would need to encrypt the password beforehand, either inline, or
using a pgsql-contrib crypt() function.  (i have this if you want it)

the fix is for the authentication behaviour, not the adminitrative interface
(ie. ALTER USER).

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 08:56:28 -0400
Jim Mercer <jim@reptiles.org>
Re: Encrypting pg_shadow passwords
Jim and Bruce wrote:
> [ a lot of stuff ]

What this discussion seems to come down to is whether we should take a
backward step in one area of security (security against wire-sniffing)
to take a forward step in another (not storing plaintext passwords).
It seems largely a matter of local conditions which hazard you consider
greater (though I would note that anyone who is able to examine the
contents of pg_shadow has *already* broken into your database).  Anyway,
I doubt anyone will convince anyone else to change sides on that point.

My take on the matter is that we shouldn't invest any more effort in
crypt-based solutions (here crypt means specifically crypt(3), it's
not a generic term).  The future is double encryption using MD5 ---
or s/MD5/more-modern-hash-algorithm-of-your-choice/, the exact choice
is irrelevant to my point.  We ought to get off our duffs and implement
that, then encourage people to migrate their clients ASAP.  The crypt
code will be supported for awhile longer, but strictly as a
backwards-compatibility measure for old clients.  There's no reason to
spend any additional work on it.

For the same reason I don't see any value in the idea of adding
crypt-based double encryption to clients.  We don't really want to
support that over the long run, so why put effort into it?

			regards, tom lane

Tue, 26 Jun 2001 10:18:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Encrypting pg_shadow passwords 

pgman@candle.pha.pa.us (Bruce Momjian) writes:

: OK, I get you now.  Why not ask the client to do a crypt and compare
: that to pg_shadow.  [...]

You can't trust the client to do the one-way encryption, for then the
encrypted password becomes plaintext-equivalent.  (The SMB protocol
apparently suffers or suffered from a similar flaw.)

- FChE

26 Jun 2001 10:30:43 -0400
fche@redhat.com (Frank Ch. Eigler)
Re: Encrypting pg_shadow passwords
> At 12:51 AM 26-06-2001 -0400, Jim Mercer wrote:
> 
> >my mods are server-side only.
> >
> >to rewind a bit.
> >
> >my mods correct this by doing:
> >
> >with an AUTH_ARGUMENT == "pg_shadow", the process is:
> >tmp_pwd = crypt(client->passwd, pg_shadow->passwd)
> >if strcmp(tmp_pwd, pg_shadow->passwd) == 0
> >   access allowed
> >else
> >   access not allowed
> >
> >this is not so much an enhancement, but a correction of what i think the
> >original "password" authentication scheme was supposed to allow.
> >
> 
> Yep it's a correction. pg_shadow shouldn't have been in plaintext in the
> first place.
> 
>  host all 127.0.0.1 255.255.255.255 password 
> should have meant check crypted passwords in pg_shadow.

The issue is that when we store users in pg_shadow we don't know what
kind of authentication is going to be used in pg_hba.conf, and in the
old days if we stored it encrypted we couldn't use random salt in
'crypt' authentication.

This is the first time I am hearing people are more concerned about
pg_shadow security than the wire security.  I can see cases where people
are on secure networks or are using only local users where having
pg_shadow encrypted is more important than crypt authentication. 
Fortunately the new system will solve both problems.


> Given your suggestion, what happens when someone does an ALTER USER ...
> WITH PASSWORD ....? 

It stores it encrypted in pg_shadow.

> Might it be too late to do a fix? 
> 
> HMAC sounds interesting. What would the impact be on stuff like Pg DBD?

No idea.

-- 
  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

Tue, 26 Jun 2001 11:02:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
> On Mon, Jun 25, 2001 at 02:34:51PM +0800, Lincoln Yeoh wrote:
> > At 12:51 AM 26-06-2001 -0400, Jim Mercer wrote:
> > >this is not so much an enhancement, but a correction of what i think the
> > >original "password" authentication scheme was supposed to allow.
> > 
> > Yep it's a correction. pg_shadow shouldn't have been in plaintext in the
> > first place.
> > 
> >  host all 127.0.0.1 255.255.255.255 password 
> > should have meant check crypted passwords in pg_shadow.
> > 
> > Given your suggestion, what happens when someone does an ALTER USER ...
> > WITH PASSWORD ....? 
> > 
> > Might it be too late to do a fix? 
> 
> i didn't want to change things too much.  in the case of ALTER USER, the
> code would need to encrypt the password beforehand, either inline, or
> using a pgsql-contrib crypt() function.  (i have this if you want it)
> 
> the fix is for the authentication behaviour, not the adminitrative interface
> (ie. ALTER USER).

But the fix disables crypt authentication, at least until we do double
encryption.

-- 
  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

Tue, 26 Jun 2001 11:03:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Encrypting pg_shadow passwords
> My take on the matter is that we shouldn't invest any more effort in
> crypt-based solutions (here crypt means specifically crypt(3), it's
> not a generic term).  The future is double encryption using MD5 ---
> or s/MD5/more-modern-hash-algorithm-of-your-choice/, the exact choice
> is irrelevant to my point.  We ought to get off our duffs and implement
> that, then encourage people to migrate their clients ASAP.  The crypt
> code will be supported for awhile longer, but strictly as a
> backwards-compatibility measure for old clients.  There's no reason to
> spend any additional work on it.
> 
> For the same reason I don't see any value in the idea of adding
> crypt-based double encryption to clients.  We don't really want to
> support that over the long run, so why put effort into it?

The only reason to add double-crypt is so we can continue to use
/etc/passwd entries on systems that use crypt() in /etc/passwd.

-- 
  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

Tue, 26 Jun 2001 11:05:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The only reason to add double-crypt is so we can continue to use
> /etc/passwd entries on systems that use crypt() in /etc/passwd.

In the long run, though, we want to drop crypt(3) usage entirely.
It's just too much of a pain in the neck to depend on the C library's
crypt(), for two reasons:

1. It's not in libc on all systems, leading to constant problems when
linking clients, particularly with shared libraries that have to have
a dependency on another shared library because of this.  (Search the
archives for problems about "can't find crypt".  There are many such
reports.)

2. crypt() isn't guaranteed compatible across platforms, meaning that
your clients may be unable to log in anyway.  See for example
http://fts.postgresql.org/db/mw/msg.html?mid=57516

Using our own MD5 (or whatever) code will avoid these problems.

			regards, tom lane

Tue, 26 Jun 2001 11:27:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Encrypting pg_shadow passwords 
> In the long run, though, we want to drop crypt(3) usage entirely.
> It's just too much of a pain in the neck to depend on the C library's
> crypt(), for two reasons:
> 
> 1. It's not in libc on all systems, leading to constant problems when
> linking clients, particularly with shared libraries that have to have
> a dependency on another shared library because of this.  (Search the
> archives for problems about "can't find crypt".  There are many such
> reports.)
> 
> 2. crypt() isn't guaranteed compatible across platforms, meaning that
> your clients may be unable to log in anyway.  See for example
> http://fts.postgresql.org/db/mw/msg.html?mid=57516
> 
> Using our own MD5 (or whatever) code will avoid these problems.

Agreed.  If people say they want to keep crypt for /etc/passwd, we can. 
If they don't say they want it, we can go with only MD5.

-- 
  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

Tue, 26 Jun 2001 11:31:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> > My take on the matter is that we shouldn't invest any more effort in
> > crypt-based solutions (here crypt means specifically crypt(3), it's
> > not a generic term).  The future is double encryption using MD5 ---
> > or s/MD5/more-modern-hash-algorithm-of-your-choice/, the exact choice
> > is irrelevant to my point.  We ought to get off our duffs and implement
> > that, then encourage people to migrate their clients ASAP.  The crypt
> > code will be supported for awhile longer, but strictly as a
> > backwards-compatibility measure for old clients.  There's no reason to
> > spend any additional work on it.
> > 
> > For the same reason I don't see any value in the idea of adding
> > crypt-based double encryption to clients.  We don't really want to
> > support that over the long run, so why put effort into it?
> 
> The only reason to add double-crypt is so we can continue to use
> /etc/passwd entries on systems that use crypt() in /etc/passwd.

Haven't many systems (at least Linux and FreeBSD) switched from this
to other algorithms as default, like MD5? (and usually found in /etc/shadow)

-- 
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

26 Jun 2001 12:26:28 -0400
teg@redhat.com (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=)
Re: Re: Encrypting pg_shadow passwords
> > > For the same reason I don't see any value in the idea of adding
> > > crypt-based double encryption to clients.  We don't really want to
> > > support that over the long run, so why put effort into it?
> > 
> > The only reason to add double-crypt is so we can continue to use
> > /etc/passwd entries on systems that use crypt() in /etc/passwd.
> 
> Haven't many systems (at least Linux and FreeBSD) switched from this
> to other algorithms as default, like MD5? (and usually found in /etc/shadow)

Yes, most BSD's are MD5.  I wasn't sure about Linux.  If it is md5 by
default that would remove many sites from using crypt in secondary
password files already.

-- 
  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

Tue, 26 Jun 2001 12:30:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> > > > For the same reason I don't see any value in the idea of adding
> > > > crypt-based double encryption to clients.  We don't really want to
> > > > support that over the long run, so why put effort into it?
> > > 
> > > The only reason to add double-crypt is so we can continue to use
> > > /etc/passwd entries on systems that use crypt() in /etc/passwd.
> > 
> > Haven't many systems (at least Linux and FreeBSD) switched from this
> > to other algorithms as default, like MD5? (and usually found in /etc/shadow)
> 
> Yes, most BSD's are MD5.  I wasn't sure about Linux. 

Most recent (3-4 years and newer) use PAM, which can use MD5 as an
underlying module.

-- 
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

26 Jun 2001 12:33:00 -0400
teg@redhat.com (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=)
Re: Re: Encrypting pg_shadow passwords
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > > > > For the same reason I don't see any value in the idea of adding
> > > > > crypt-based double encryption to clients.  We don't really want to
> > > > > support that over the long run, so why put effort into it?
> > > > 
> > > > The only reason to add double-crypt is so we can continue to use
> > > > /etc/passwd entries on systems that use crypt() in /etc/passwd.
> > > 
> > > Haven't many systems (at least Linux and FreeBSD) switched from this
> > > to other algorithms as default, like MD5? (and usually found in /etc/shadow)
> > 
> > Yes, most BSD's are MD5.  I wasn't sure about Linux. 
> 
> Most recent (3-4 years and newer) use PAM, which can use MD5 as an
> underlying module.

But what is the default?  crypt or md5?

-- 
  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

Tue, 26 Jun 2001 12:33:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 
> > > > > > For the same reason I don't see any value in the idea of adding
> > > > > > crypt-based double encryption to clients.  We don't really want to
> > > > > > support that over the long run, so why put effort into it?
> > > > > 
> > > > > The only reason to add double-crypt is so we can continue to use
> > > > > /etc/passwd entries on systems that use crypt() in /etc/passwd.
> > > > 
> > > > Haven't many systems (at least Linux and FreeBSD) switched from this
> > > > to other algorithms as default, like MD5? (and usually found in /etc/shadow)
> > > 
> > > Yes, most BSD's are MD5.  I wasn't sure about Linux. 
> > 
> > Most recent (3-4 years and newer) use PAM, which can use MD5 as an
> > underlying module.
> 
> But what is the default?  crypt or md5?

Varies. In Red Hat Linux, it's been user configurable during install
for a couple of years now - it's been default to on for most of that
time, AFAIR.
-- 
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

26 Jun 2001 12:43:08 -0400
teg@redhat.com (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=)
Re: Re: Encrypting pg_shadow passwords
Bruce Momjian writes:

> The only reason to add double-crypt is so we can continue to use
> /etc/passwd entries on systems that use crypt() in /etc/passwd.

On the sites that are most likely to utilize that (because they have a lot
of users) it won't work (because they use NIS).  There are better ways to
do that (e.g., PAM).

Also, see http://httpd.apache.org/docs/misc/FAQ.html#passwdauth

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Tue, 26 Jun 2001 19:28:25 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 11:03:38AM -0400, Bruce Momjian wrote:
> > the fix is for the authentication behaviour, not the adminitrative interface
> > (ie. ALTER USER).
> 
> But the fix disables crypt authentication, at least until we do double
> encryption.

only if the dbadmin decides to store crypt'd passwords in pg_shadow.

the code only alters the behaviour of the way the client and server
passwords are compared, if, and only if, the auth type is "password pg_shadow".

the current code does not allow a method for the client to pass clear-text
password, and have it compared to an encrypted pg_shadow.

i consider this broken (especially given the intention of using
"password /some/file").

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 14:49:13 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 11:05:55AM -0400, Bruce Momjian wrote:
> The only reason to add double-crypt is so we can continue to use
> /etc/passwd entries on systems that use crypt() in /etc/passwd.

as someone else noted, freebsd's crypt() function does multiple types
types of crypto (DES, MD5, etc) based on a tag in the salt.

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 14:50:23 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 12:30:11PM -0400, Bruce Momjian wrote:
> > Haven't many systems (at least Linux and FreeBSD) switched from this
> > to other algorithms as default, like MD5? (and usually found in /etc/shadow)
> 
> Yes, most BSD's are MD5.  I wasn't sure about Linux.  If it is md5 by
> default that would remove many sites from using crypt in secondary
> password files already.

while freebsd is now defaulting to MD5, the core function is still crypt().

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 14:52:56 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Encrypting pg_shadow passwords

I am not sure this fits in to this discussion (I guess I think
it does, since I am posting this message...)

We talk about how it is not good to be storing plain text
passwords, but I don't know what people are doing about 
clients which are expected to connect without input from
an authorized user (ie. web scripts, or other public
applications with access to the database)

I have been:
creating users with minimum possible privileges, and
storing password in file with minimum possible privileges


What other options are there?

Tue, 26 Jun 2001 21:11:00 +0000 (UTC)
missive@frontiernet.net (Lee Harr)
Re: Encrypting pg_shadow passwords
> Bruce Momjian writes:
> 
> > The only reason to add double-crypt is so we can continue to use
> > /etc/passwd entries on systems that use crypt() in /etc/passwd.
> 
> On the sites that are most likely to utilize that (because they have a lot
> of users) it won't work (because they use NIS).  There are better ways to
> do that (e.g., PAM).
> 
> Also, see http://httpd.apache.org/docs/misc/FAQ.html#passwdauth

Thanks.  That was a nice description.  Seems no one is worried about
losing /etc/passwd capability so I will not worry about doing
double-crypt and concentrate on md5.  I just didn't want to remove
functionality before warning people.

-- 
  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

Tue, 26 Jun 2001 19:17:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 10:18:37AM -0400, Tom Lane wrote:
> though I would note that anyone who is able to examine the
> contents of pg_shadow has *already* broken into your database

note: the dbadmin ay not bethe system administrator, but the dbadmin,
by default (with plaintext) can scoop an entirelist of "useful" passwords,
since many users (like it or not) use the same/similar passwords for
multiple accounts.

> There's no reason to spend any additional work on it.

hmmm.  what is the expected date of rollout of the new code with a backwards
compatible API (i don't mind recompiling), which has encrypted passwords
in pg_shadow?

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[ Now with more and longer words for your reading enjoyment. ]

Tue, 26 Jun 2001 22:16:00 -0400
Jim Mercer <jim@reptiles.org>
Re: Re: Encrypting pg_shadow passwords
> > There's no reason to spend any additional work on it.
> 
> hmmm.  what is the expected date of rollout of the new code with a backwards
> compatible API (i don't mind recompiling), which has encrypted passwords
> in pg_shadow?

It will be in 7.2, whenever that is.  It will not talk to pre-7.2
clients once you encrypt pg_shadow entries.

-- 
  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

Tue, 26 Jun 2001 22:34:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords

pgman wrote:

: OK, I get you now.  Why not ask the client to do a crypt and compare
: that to pg_shadow.  [...]

You can't trust the client to do the one-way encryption, for then the
encrypted password becomes plaintext-equivalent - it defeats the
purpose.  (The SMB protocol apparently suffers or suffered from a
similar flaw.)


tgl wrote:

: What this discussion seems to come down to is whether we should take a
: backward step in one area of security (security against wire-sniffing)
: to take a forward step in another (not storing plaintext passwords).
: [...]

It seems to me that the two issues are orthogonal.  Authentication and
confidentiality are not mutually dependent or reinforcing, and thus
generally need separate mechanisms.


- FChE

27 Jun 2001 09:58:08 -0400
fche@redhat.com (Frank Ch. Eigler)
Re: Encrypting pg_shadow passwords
fche@redhat.com (Frank Ch. Eigler) writes:
> tgl wrote:
> : What this discussion seems to come down to is whether we should take a
> : backward step in one area of security (security against wire-sniffing)
> : to take a forward step in another (not storing plaintext passwords).

> It seems to me that the two issues are orthogonal.

In the abstract yes, but not when you have a constraint that you can't
change the protocol or the client-side code.  Remember we are talking
about a backwards-compatibility mode.

			regards, tom lane

Wed, 27 Jun 2001 11:07:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Encrypting pg_shadow passwords 
Hi -

tgl wrote:

: [...]
: > : What this discussion seems to come down to is whether we should take a
: > : backward step in one area of security (security against wire-sniffing)
: > : to take a forward step in another (not storing plaintext passwords).
: 
: > It seems to me that the two issues are orthogonal.
: 
: In the abstract yes, but not when you have a constraint that you can't
: change the protocol or the client-side code.  Remember we are talking
: about a backwards-compatibility mode.

Having scanned over the discussion again, my understanding is that Jim's
proposed changes don't affect backwards compatibility.  As long as user
passwords continue to be passed in plaintext to the server, the server
can store encrypted passwords in the authentication table.

Protecting against wire snooping could properly be left to another
layer, which might indeed require client & server changes (unless
performed by some external system like stunnel).  Wouldn't that be
sufficient, and avoid the need to invent anything special just for
postgresql?

- FChE
Wed, 27 Jun 2001 11:27:07 -0400
"Frank Ch. Eigler" <fche@redhat.com>
Re: Re: Encrypting pg_shadow passwords
"Frank Ch. Eigler" <fche@redhat.com> writes:
> Having scanned over the discussion again, my understanding is that Jim's
> proposed changes don't affect backwards compatibility.  As long as user
> passwords continue to be passed in plaintext to the server, the server
> can store encrypted passwords in the authentication table.

The 'passwd' mode wouldn't be affected, but the 'crypt' mode would be;
it would become less secure than it is now, because the server would be
forced to send the same salt always, and so a captured encrypted
password would be just as useful as a captured plaintext one.  That's
the step backwards.

			regards, tom lane

Wed, 27 Jun 2001 11:34:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Encrypting pg_shadow passwords 
Hi -

tgl wrote:

: The 'passwd' mode wouldn't be affected, but the 'crypt' mode would be;
: it would become less secure than it is now, because the server would be
: forced to send the same salt always, and so a captured encrypted
: password would be just as useful as a captured plaintext one.  That's
: the step backwards.

Oh, I see finally.  You already put a custom little
challenge/response authentication scheme into postgresql,
and want to keep that working.  (May I ask when/why that
went in at all?  Was lower-layer encryption not an option?)

At least, it looks like the choice of authentication protocol is a
server-side decision.  Backward-compatibility for old clients can
be forced by the adminstrator, whether the server switches to
encrypted password storage, and/or to lower-level encryption.

- FChE
Wed, 27 Jun 2001 12:27:08 -0400
"Frank Ch. Eigler" <fche@redhat.com>
Re: Re: Encrypting pg_shadow passwords
"Frank Ch. Eigler" <fche@redhat.com> writes:
> Oh, I see finally.  You already put a custom little
> challenge/response authentication scheme into postgresql,
> and want to keep that working.  (May I ask when/why that
> went in at all?

Long before any of the current generation of developers, AFAIK.

> Was lower-layer encryption not an option?)

What lower layer?  This code predates SSL by a good bit.

In any case, as several people have pointed out, one may well want to
guard one's password more carefully than one guards the entire session
contents.  Running SSL on a session that may transfer many megabytes
is a lot of overhead.

			regards, tom lane

Wed, 27 Jun 2001 12:33:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Encrypting pg_shadow passwords 
Hi -

tgl wrote:
: > Oh, I see finally.  You already put a custom little
: > challenge/response authentication scheme into postgresql,
: [...]
: Long before any of the current generation of developers, AFAIK.

Okay.  (Sorry about misinferring "You" above!)


: In any case, as several people have pointed out, one may well want to
: guard one's password more carefully than one guards the entire session
: contents.  Running SSL on a session that may transfer many megabytes
: is a lot of overhead.

Sure, but that's a separate performance question that shouldn't affect
the logical layering of the mechanisms.  With SSL, for example, methinks
it's possible to renegotiate a connection to turn off encryption after
a certain point.


- FChE
Wed, 27 Jun 2001 12:41:09 -0400
"Frank Ch. Eigler" <fche@redhat.com>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jun 26, 2001 at 11:02:15AM -0400, Bruce Momjian wrote:
> This is the first time I am hearing people are more concerned about
> pg_shadow security than the wire security.  I can see cases where people
> are on secure networks or are using only local users where having
> pg_shadow encrypted is more important than crypt authentication. 
> Fortunately the new system will solve both problems.

The crypt authentication currently used offers _no_ security.  If I can
sniff on the wire, I can hijack the tcp stream, and trick the client
into doing password authentication.

Also, the double crypt authentication offers no advantage over the wire.

You're better off just doing an md5crypt() on the server side, and just
passing the password in the clear.  At least you're not confusing users
into thinking that they're secure.

Of course, SSL *if done correctly with certificate verification* is the
correct fix.  If no certificate verification is done, you fall victim to
a man-in-the-middle attack.

-- 
Michael Samuel <michael@miknet.net>

Wed, 11 Jul 2001 13:24:53 +1000
michael@miknet.net (Michael Samuel)
Re: Re: Encrypting pg_shadow passwords
> On Tue, Jun 26, 2001 at 11:02:15AM -0400, Bruce Momjian wrote:
> > This is the first time I am hearing people are more concerned about
> > pg_shadow security than the wire security.  I can see cases where people
> > are on secure networks or are using only local users where having
> > pg_shadow encrypted is more important than crypt authentication. 
> > Fortunately the new system will solve both problems.
> 
> The crypt authentication currently used offers _no_ security.  If I can
> sniff on the wire, I can hijack the tcp stream, and trick the client
> into doing password authentication.

It is my understanding that sniffing is much easier than hijacking.  If
hijacking is a concern, you have to use SSL.

-- 
  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

Tue, 10 Jul 2001 23:32:00 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
On Tue, Jul 10, 2001 at 11:32:00PM -0400, Bruce Momjian wrote:
> > On Tue, Jun 26, 2001 at 11:02:15AM -0400, Bruce Momjian wrote:
> > > This is the first time I am hearing people are more concerned about
> > > pg_shadow security than the wire security.  I can see cases where people
> > > are on secure networks or are using only local users where having
> > > pg_shadow encrypted is more important than crypt authentication. 
> > > Fortunately the new system will solve both problems.
> > 
> > The crypt authentication currently used offers _no_ security.  If I can
> > sniff on the wire, I can hijack the tcp stream, and trick the client
> > into doing password authentication.
> 
> It is my understanding that sniffing is much easier than hijacking.  If
> hijacking is a concern, you have to use SSL.

That is not true.  The internet happily allows for active attacks.  In
fact, active attacks are easier on the internet than passive ones.

My concern is, that by having something that we proclaim to be secure, we
need for it to really be secure.

An HMAC would be a better alternative to the current crypt scheme, as
it would provide integrity, without the overhead of having privacy.

Of course, HMAC would require the postgres protocol to talk in "packets",
as it can't accept the data as being valid until it verifies the MAC. I'm
not familiar with the protocol yet.

I suggest these authentication options:

* password - The current meaning of password, but with passwords hashed
  using md5crypt() or something. (The usual crypt unneccessarily limits
  passwords to 8 characters)
* HMAC - Wrap all postgres data in an HMAC (I believe this requires an
  plaintext-like password on the server as does crypt and the double
  crypt scheme)
* Public Key (RSA/DSA) - Use public key cryptography to negotiate a
  connection. (When I'm not busy, I may decide to do this myself)

Also, I think we should add to the client API the ability to only accept
certain authentication schemes, to avoid active attacks tricking your
software from sending the HMAC password in cleartext.

-- 
Michael Samuel <michael@miknet.net>

Wed, 11 Jul 2001 19:02:22 +1000
michael@miknet.net (Michael Samuel)
Re: Re: Encrypting pg_shadow passwords
> That is not true.  The internet happily allows for active attacks.  In
> fact, active attacks are easier on the internet than passive ones.
> 
> My concern is, that by having something that we proclaim to be secure, we
> need for it to really be secure.
> 
> An HMAC would be a better alternative to the current crypt scheme, as
> it would provide integrity, without the overhead of having privacy.
> 
> Of course, HMAC would require the postgres protocol to talk in "packets",
> as it can't accept the data as being valid until it verifies the MAC. I'm
> not familiar with the protocol yet.
> 
> I suggest these authentication options:
> 
> * password - The current meaning of password, but with passwords hashed
>   using md5crypt() or something. (The usual crypt unneccessarily limits
>   passwords to 8 characters)

Once I do crypting of pg_shadow/double-crypt for 7.2, we don't need
password anymore.  It is around only for very old clients and for
secondary password files but wWe will not need that workaround with
double-crypt.

> * HMAC - Wrap all postgres data in an HMAC (I believe this requires an
>   plaintext-like password on the server as does crypt and the double
>   crypt scheme)

No, double-crypt has the passwords stored encrypted.

> * Public Key (RSA/DSA) - Use public key cryptography to negotiate a
>   connection. (When I'm not busy, I may decide to do this myself)

SSL?

-- 
  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

Wed, 11 Jul 2001 13:00:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
> Also, I think we should add to the client API the ability to only accept
> certain authentication schemes, to avoid active attacks tricking your
> software from sending the HMAC password in cleartext.

This is an interesting point.  We have kept 'password' authentication
around for secondary password files and for very old clients, but now
see that having it around can be a security problem because you can ask
the client to send you cleartext passwords.

Comments?

-- 
  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

Wed, 11 Jul 2001 13:02:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Encrypting pg_shadow passwords
On Wed, Jul 11, 2001 at 01:24:53PM +1000, Michael Samuel wrote:
> The crypt authentication currently used offers _no_ security. ...
> Of course, SSL *if done correctly with certificate verification* is the
> correct fix.  If no certificate verification is done, you fall victim to
> a man-in-the-middle attack.

It seems worth noting here that you don't have to depend on
SSL authentication; PG can do its own authentication over SSL
and avoid the man-in-the-middle attack that way.  

Of course, PG would have to do its authentication properly, e.g. 
with the HMAC method.  That seems better than depending on SSL 
authentication, because SSL certification seems to be universally
misconfigured.

Nathan Myers
ncm@zembu.com

Wed, 11 Jul 2001 13:48:21 -0700
ncm@zembu.com (Nathan Myers)
Re: Re: Encrypting pg_shadow passwords
On Wed, Jul 11, 2001 at 01:00:42PM -0400, Bruce Momjian wrote:
> > * HMAC - Wrap all postgres data in an HMAC (I believe this requires an
> >   plaintext-like password on the server as does crypt and the double
> >   crypt scheme)
> 
> No, double-crypt has the passwords stored encrypted.

You missed my point.  If I can get hold of the encrypted password in
the database, I can hack up a client library to use the encrypted
password to log in.  Therefore, encrypting the password in pg_shadow
offers no advantage.

> > * Public Key (RSA/DSA) - Use public key cryptography to negotiate a
> >   connection. (When I'm not busy, I may decide to do this myself)
> 
> SSL?

I'd use the OpenSSL libraries to implement it, but we're talking about
public key authentication here, not connection encryption.

-- 
Michael Samuel <michael@miknet.net>

Thu, 12 Jul 2001 16:20:35 +1000
michael@miknet.net (Michael Samuel)
Re: Re: Encrypting pg_shadow passwords