Hi all,

Why does the password_encryption GUC variable default to false?

AFAICT there shouldn't be any issues with client compatibility -- in
fact, I'd be inclined to rip out all support for storing cleartext
passwords...

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thu, 25 Apr 2002 01:21:16 -0400
Neil Conway <nconway@klamath.dyndns.org>
md5 passwords and pg_shadow
Neil Conway wrote:
> Hi all,
> 
> Why does the password_encryption GUC variable default to false?
> 
> AFAICT there shouldn't be any issues with client compatibility -- in
> fact, I'd be inclined to rip out all support for storing cleartext
> passwords...

It is false so passwords can be handled by pre-7.2 clients.  Once you
encrypt them, you can't use passwords on pre-7.2 clients because they
don't understand the double-md5 hash required.  We will set it to true,
but when are most pre-7.2 clients gone?

-- 
  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, 25 Apr 2002 01:50:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: md5 passwords and pg_shadow
On Thu, 25 Apr 2002 01:50:32 -0400 (EDT)
"Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
> Neil Conway wrote:
> > Hi all,
> > 
> > Why does the password_encryption GUC variable default to false?
> > 
> > AFAICT there shouldn't be any issues with client compatibility -- in
> > fact, I'd be inclined to rip out all support for storing cleartext
> > passwords...
> 
> It is false so passwords can be handled by pre-7.2 clients.  Once you
> encrypt them, you can't use passwords on pre-7.2 clients because they
> don't understand the double-md5 hash required.

IMHO, there are two separate processes going on here:

   (1) password storage in pg_shadow

   (2) password submission over the wire

You want to use a hash like MD5 for #1 so that someone who breaks
into the server can't read all the passwords in pg_shadow. You
want to use a hash for #2 so that someone sniffing the network
won't be able to read passwords. Aren't these two goals
orthagonal? In other words, what does the format in which the
password is stored have to do with the format in which data
is sent over the wire?

How about this scheme:

- store all passwords md5 hashed: never store the cleartext
password.
- if the client is using 'password' authentication, they will
send in the cleartext password: MD5 hash it and compare it
with the store MD5 hash. If they match, authentication
succeeds.
- if the client is using 'md5' authentication, use the
existing double-md5 hash technique

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thu, 25 Apr 2002 12:48:13 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: md5 passwords and pg_shadow
Neil Conway <nconway@klamath.dyndns.org> writes:
> IMHO, there are two separate processes going on here:

The connection you are missing is that hashed password storage is
incompatible with crypt-style password transmission.  If we force
hashed storage then the only password transmission style available
to pre-7.2 clients is cleartext.  It's not at all clear that securing
the on-disk representation is a more important goal than wire security.
(Perhaps it is for some cases, but in other cases it's surely not.)
So the parameter variable is there to let the DBA choose which he's
more worried about.

We should probably change the default setting for 7.3, but I don't
think we'll be able to force hashed storage of passwords in all
installations for awhile longer yet.

			regards, tom lane

Thu, 25 Apr 2002 13:32:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: md5 passwords and pg_shadow 

OK, I remember now.  'Password' is fine for MD5-encrypted pg_shadow
because you are using the password supplied over the wire to compare to
the md5.  (Of couse, no one should be using 'password'.)

It is 'crypt' that is the problem.  You get a random salted crypted
password from the user, and you can't compare that to the MD5.

In the 7.2 setup, the client knows the password and can double-md5 
encrypts and sends it to you.  The double-md5 uses the pg_shadow salt,
and then a random salt.

---------------------------------------------------------------------------

Neil Conway wrote:
> On Thu, 25 Apr 2002 01:50:32 -0400 (EDT)
> "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:
> > Neil Conway wrote:
> > > Hi all,
> > > 
> > > Why does the password_encryption GUC variable default to false?
> > > 
> > > AFAICT there shouldn't be any issues with client compatibility -- in
> > > fact, I'd be inclined to rip out all support for storing cleartext
> > > passwords...
> > 
> > It is false so passwords can be handled by pre-7.2 clients.  Once you
> > encrypt them, you can't use passwords on pre-7.2 clients because they
> > don't understand the double-md5 hash required.
> 
> IMHO, there are two separate processes going on here:
> 
>    (1) password storage in pg_shadow
> 
>    (2) password submission over the wire
> 
> You want to use a hash like MD5 for #1 so that someone who breaks
> into the server can't read all the passwords in pg_shadow. You
> want to use a hash for #2 so that someone sniffing the network
> won't be able to read passwords. Aren't these two goals
> orthagonal? In other words, what does the format in which the
> password is stored have to do with the format in which data
> is sent over the wire?
> 
> How about this scheme:
> 
> - store all passwords md5 hashed: never store the cleartext
> password.
> - if the client is using 'password' authentication, they will
> send in the cleartext password: MD5 hash it and compare it
> with the store MD5 hash. If they match, authentication
> succeeds.
> - if the client is using 'md5' authentication, use the
> existing double-md5 hash technique
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
> 

-- 
  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, 25 Apr 2002 13:37:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: md5 passwords and pg_shadow
Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > IMHO, there are two separate processes going on here:
> 
> The connection you are missing is that hashed password storage is
> incompatible with crypt-style password transmission.  If we force
> hashed storage then the only password transmission style available
> to pre-7.2 clients is cleartext.  It's not at all clear that securing
> the on-disk representation is a more important goal than wire security.
> (Perhaps it is for some cases, but in other cases it's surely not.)
> So the parameter variable is there to let the DBA choose which he's
> more worried about.
> 
> We should probably change the default setting for 7.3, but I don't
> think we'll be able to force hashed storage of passwords in all
> installations for awhile longer yet.

If we change that default in 7.3, pg_dump reload will md5 encrypt the
passwords supplied from 7.2.  Is that OK, and we can require them to set
it to 'false' if they want pre-7.2 crypt compatibility?

If so, I can make the change.

-- 
  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, 25 Apr 2002 13:39:41 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: md5 passwords and pg_shadow
On Thu, 25 Apr 2002 13:32:27 -0400
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > IMHO, there are two separate processes going on here:
> 
> The connection you are missing is that hashed password storage is
> incompatible with crypt-style password transmission.

Ah, ok -- that makes sense.

> If we force
> hashed storage then the only password transmission style available
> to pre-7.2 clients is cleartext.  It's not at all clear that securing
> the on-disk representation is a more important goal than wire security.

I'd agree they are both important.

How many pre-7.2 clients are actually out there? If 'crypt' authentication
is deprecated in 7.2, is there any chance it will be removed in
7.3? If it is, it should be safe to switch to the scheme I mentioned
in my previous email, which is both less complicated, and
"secure-by-default".

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thu, 25 Apr 2002 14:33:46 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: md5 passwords and pg_shadow
Neil Conway <nconway@klamath.dyndns.org> writes:
> How many pre-7.2 clients are actually out there? If 'crypt' authentication
> is deprecated in 7.2, is there any chance it will be removed in
> 7.3? If it is, it should be safe to switch to the scheme I mentioned
> in my previous email, which is both less complicated, and
> "secure-by-default".

I don't see any particular need to change the implementation; what we
have works and it's flexible.  I do think we should change the default
password_encryption setting soon.  IIRC, we agreed to default to FALSE
at a time when we didn't have md5 password support in the jdbc and odbc
drivers.  We probably should have revisited the decision once we knew
that 7.2 would ship with md5 support in all client libraries --- but
we didn't think to.

It seems unlikely to me that FALSE will be the preferred setting for
very many 7.3 installations.  There might be a few people out there
still using 7.1 clients with 7.3 servers, but a majority?  No.

			regards, tom lane

Thu, 25 Apr 2002 14:54:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: md5 passwords and pg_shadow 
Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > How many pre-7.2 clients are actually out there? If 'crypt' authentication
> > is deprecated in 7.2, is there any chance it will be removed in
> > 7.3? If it is, it should be safe to switch to the scheme I mentioned
> > in my previous email, which is both less complicated, and
> > "secure-by-default".
> 
> I don't see any particular need to change the implementation; what we
> have works and it's flexible.  I do think we should change the default
> password_encryption setting soon.  IIRC, we agreed to default to FALSE
> at a time when we didn't have md5 password support in the jdbc and odbc
> drivers.  We probably should have revisited the decision once we knew
> that 7.2 would ship with md5 support in all client libraries --- but
> we didn't think to.

I did think of it but decided we couldn't release 7.2 that had crypt
broken for 7.1 clients.  90% of folks move moving to 7.2 are from 7.1,
and they don't want to be required to upgrade all their clients at the
same time as the server upgrade.

If no one objects, I will change the default to md5 encrypted pg_shadow
passwords for 7.3.

Objections?  To use crypt in pre-7,2 clients, people will have to change
their postgresql.conf setting _before_ loading the database.

-- 
  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, 25 Apr 2002 16:26:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: md5 passwords and pg_shadow