Re: password_encryption default

[Michael Paquier]

On Wed, Jun 10, 2020 at 10:51:22AM -0400, Jonathan S. Katz wrote:
> On 6/10/20 10:47 AM, Peter Eisentraut wrote:
>> committed
> 
> Yay!!! Thank you!

Thanks, all.
--
Michael


signature.asc
Description: PGP signature

Re: password_encryption default

[Jonathan S. Katz]

On 6/10/20 10:47 AM, Peter Eisentraut wrote:
> On 2020-05-28 15:28, Jonathan S. Katz wrote:
>> On 5/28/20 8:10 AM, Peter Eisentraut wrote:
>>> On 2020-05-27 15:25, Jonathan S. Katz wrote:
 $ initdb -D data --auth-local=scram-sha-256 --auth-host=md5

 Got an error message:

 "initdb: error: must specify a password for the superuser to enable md5
 authentication"

 For the last two, that behavior is to be expected (after all, you've
 set
 the two login vectors to require passwords), but the error message
 seems
 odd now. Perhaps we tweak it to be:


 "initdb: error: must specify a password for the superuser when
 requiring
 passwords for both local and host authentication."
>>>
>>> That message is a bit long.  Maybe just
>>>
>>> must specify a password for the superuser to enable password
>>> authentication
>>>
>>> without reference to the specific method.  I think the idea is clear
>>> there.
>>
>> +1
> 
> committed

Yay!!! Thank you!

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-28 15:28, Jonathan S. Katz wrote:

On 5/28/20 8:10 AM, Peter Eisentraut wrote:

On 2020-05-27 15:25, Jonathan S. Katz wrote:

$ initdb -D data --auth-local=scram-sha-256 --auth-host=md5

Got an error message:

"initdb: error: must specify a password for the superuser to enable md5
authentication"

For the last two, that behavior is to be expected (after all, you've set
the two login vectors to require passwords), but the error message seems
odd now. Perhaps we tweak it to be:


"initdb: error: must specify a password for the superuser when requiring
passwords for both local and host authentication."


That message is a bit long.  Maybe just

must specify a password for the superuser to enable password authentication

without reference to the specific method.  I think the idea is clear there.


+1


committed

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: password_encryption default

[Tom Lane]

Stephen Frost  writes:
> * Jonathan S. Katz (jk...@postgresql.org) wrote:
>> By that logic, I would +1 removing ENCRYPTED & UNENCRYPTED, given
>> ENCRYPTED effectively has no meaning either after all this time too.
>> Perhaps a stepping stone is to emit a deprecation warning on PG14 and
>> remove in PG15, but I think it's safe to remove.

> We're terrible about that, and people reasonably complain about such
> things because we don't *know* we're gonna remove it in 15.

If we're changing associated defaults, there's already some risk of
breaking badly-written applications.  +1 for just removing these
keywords in v14.

regards, tom lane

Re: password_encryption default

[Jonathan S. Katz]

On 5/29/20 9:22 AM, Stephen Frost wrote:
> Greetings,
> 
> * Jonathan S. Katz (jk...@postgresql.org) wrote:
>> On 5/29/20 3:33 AM, Michael Paquier wrote:
>>> On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote:
 More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED
 keywords from CREATE and ALTER ROLE.  AFAICT, these have never been emitted
 by pg_dump or psql, so there are no concerns from that end.  Thoughts?
>>>
>>> +0.5.  I think that you have a good point about the removal of
>>> UNENCRYPTED (one keyword gone!) as we don't support it since 10.  For
>>> ENCRYPTED, I'd rather keep it around for compatibility reasons for a
>>> longer time, just to be on the safe side.
>>
>> By that logic, I would +1 removing ENCRYPTED & UNENCRYPTED, given
>> ENCRYPTED effectively has no meaning either after all this time too. If
>> it's not emitted by any of our scripts, and it's been effectively moot
>> for 4 years (by the time of PG14), and we've been saying in the docs "he
>> ENCRYPTED keyword has no effect, but is accepted for backwards
>> compatibility" I think we'd be safe with removing it.
>>
>> Perhaps a stepping stone is to emit a deprecation warning on PG14 and
>> remove in PG15, but I think it's safe to remove.
> 
> We're terrible about that, and people reasonably complain about such
> things because we don't *know* we're gonna remove it in 15.
> 
> I'll argue again for the approach I mentioned before somewhere: when we
> commit the patch for 14, we go back and update the older docs to note
> that it's gone as of v14.  Deprecation notices and other such don't work
> and we instead end up carrying legacy things on forever.

Yeah, my first preference is to just remove it. I'm ambivalent towards
updating the older docs, but I do think it would be helpful.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Stephen Frost]

Greetings,

* Jonathan S. Katz (jk...@postgresql.org) wrote:
> On 5/29/20 3:33 AM, Michael Paquier wrote:
> > On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote:
> >> More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED
> >> keywords from CREATE and ALTER ROLE.  AFAICT, these have never been emitted
> >> by pg_dump or psql, so there are no concerns from that end.  Thoughts?
> > 
> > +0.5.  I think that you have a good point about the removal of
> > UNENCRYPTED (one keyword gone!) as we don't support it since 10.  For
> > ENCRYPTED, I'd rather keep it around for compatibility reasons for a
> > longer time, just to be on the safe side.
> 
> By that logic, I would +1 removing ENCRYPTED & UNENCRYPTED, given
> ENCRYPTED effectively has no meaning either after all this time too. If
> it's not emitted by any of our scripts, and it's been effectively moot
> for 4 years (by the time of PG14), and we've been saying in the docs "he
> ENCRYPTED keyword has no effect, but is accepted for backwards
> compatibility" I think we'd be safe with removing it.
> 
> Perhaps a stepping stone is to emit a deprecation warning on PG14 and
> remove in PG15, but I think it's safe to remove.

We're terrible about that, and people reasonably complain about such
things because we don't *know* we're gonna remove it in 15.

I'll argue again for the approach I mentioned before somewhere: when we
commit the patch for 14, we go back and update the older docs to note
that it's gone as of v14.  Deprecation notices and other such don't work
and we instead end up carrying legacy things on forever.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Stephen Frost]

Greetings,

* Michael Paquier (mich...@paquier.xyz) wrote:
> On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote:
> > More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED
> > keywords from CREATE and ALTER ROLE.  AFAICT, these have never been emitted
> > by pg_dump or psql, so there are no concerns from that end.  Thoughts?
> 
> +0.5.  I think that you have a good point about the removal of
> UNENCRYPTED (one keyword gone!) as we don't support it since 10.  For
> ENCRYPTED, I'd rather keep it around for compatibility reasons for a
> longer time, just to be on the safe side.

It's both inaccurate and would be completely legacy at that point.

I disagree entirely about keeping it around 'for compatibility'.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Jonathan S. Katz]

On 5/29/20 3:33 AM, Michael Paquier wrote:
> On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote:
>> More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED
>> keywords from CREATE and ALTER ROLE.  AFAICT, these have never been emitted
>> by pg_dump or psql, so there are no concerns from that end.  Thoughts?
> 
> +0.5.  I think that you have a good point about the removal of
> UNENCRYPTED (one keyword gone!) as we don't support it since 10.  For
> ENCRYPTED, I'd rather keep it around for compatibility reasons for a
> longer time, just to be on the safe side.

By that logic, I would +1 removing ENCRYPTED & UNENCRYPTED, given
ENCRYPTED effectively has no meaning either after all this time too. If
it's not emitted by any of our scripts, and it's been effectively moot
for 4 years (by the time of PG14), and we've been saying in the docs "he
ENCRYPTED keyword has no effect, but is accepted for backwards
compatibility" I think we'd be safe with removing it.

Perhaps a stepping stone is to emit a deprecation warning on PG14 and
remove in PG15, but I think it's safe to remove.

Perhaps stating the obvious here, but I also think it's a separate patch
from the $SUBJECT, but glad to see the clean up :)

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Michael Paquier]

On Thu, May 28, 2020 at 02:53:17PM +0200, Peter Eisentraut wrote:
> More along these lines: We could also remove the ENCRYPTED and UNENCRYPTED
> keywords from CREATE and ALTER ROLE.  AFAICT, these have never been emitted
> by pg_dump or psql, so there are no concerns from that end.  Thoughts?

+0.5.  I think that you have a good point about the removal of
UNENCRYPTED (one keyword gone!) as we don't support it since 10.  For
ENCRYPTED, I'd rather keep it around for compatibility reasons for a
longer time, just to be on the safe side.
--
Michael


signature.asc
Description: PGP signature

Re: password_encryption default

[Robert Haas]

On Thu, May 28, 2020 at 10:01 AM Stephen Frost  wrote:
> as if we don't know what columns are

Amen to that!

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: password_encryption default

[Stephen Frost]

Greetings,

* Robert Haas (robertmh...@gmail.com) wrote:
> On Thu, May 28, 2020 at 8:53 AM Peter Eisentraut
>  wrote:
> > More along these lines: We could also remove the ENCRYPTED and
> > UNENCRYPTED keywords from CREATE and ALTER ROLE.  AFAICT, these have
> > never been emitted by pg_dump or psql, so there are no concerns from
> > that end.  Thoughts?
> 
> I have a question about this. My understanding of this area isn't
> great. As I understand it, you can specify a password unencrypted and
> let the system compute the validator from it, or you can compute the
> validator yourself and then send that as the 'encrypted' password.
> But, apparently, CREATE ROLE and ALTER ROLE don't really know which
> thing you did. They just examine the string that you passed and decide
> whether it looks like a validator. If so, they assume it is; if not,
> they assume it's just a password.
> 
> But that seems really odd. What if you choose a password that just
> happens to look like a validator? Perhaps that's not real likely, but
> why do we not permit -- or even require -- the user to specify intent?
> It seems out of character for us to, essentially, guess the meaning of
> something ambiguous rather than requiring the user to be clear about
> it.

Indeed, and it's also been a source of bugs...  Watching pgcon atm but
I do recall some history around exactly this.

I'd certainly be in favor of having these things be more explicit-
including doing things like actually splitting out the actual password
validator from the algorithm instead of having them smashed together as
one string as if we don't know what columns are (also recall complaining
about that when scram was first being developed too, though that might
just be in my head).

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Robert Haas]

On Thu, May 28, 2020 at 8:53 AM Peter Eisentraut
 wrote:
> More along these lines: We could also remove the ENCRYPTED and
> UNENCRYPTED keywords from CREATE and ALTER ROLE.  AFAICT, these have
> never been emitted by pg_dump or psql, so there are no concerns from
> that end.  Thoughts?

I have a question about this. My understanding of this area isn't
great. As I understand it, you can specify a password unencrypted and
let the system compute the validator from it, or you can compute the
validator yourself and then send that as the 'encrypted' password.
But, apparently, CREATE ROLE and ALTER ROLE don't really know which
thing you did. They just examine the string that you passed and decide
whether it looks like a validator. If so, they assume it is; if not,
they assume it's just a password.

But that seems really odd. What if you choose a password that just
happens to look like a validator? Perhaps that's not real likely, but
why do we not permit -- or even require -- the user to specify intent?
It seems out of character for us to, essentially, guess the meaning of
something ambiguous rather than requiring the user to be clear about
it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: password_encryption default

[Jonathan S. Katz]

On 5/28/20 8:10 AM, Peter Eisentraut wrote:
> On 2020-05-27 15:25, Jonathan S. Katz wrote:
>> $ initdb -D data --auth-local=scram-sha-256 --auth-host=md5
>>
>> Got an error message:
>>
>> "initdb: error: must specify a password for the superuser to enable md5
>> authentication"
>>
>> For the last two, that behavior is to be expected (after all, you've set
>> the two login vectors to require passwords), but the error message seems
>> odd now. Perhaps we tweak it to be:
>>
>>
>> "initdb: error: must specify a password for the superuser when requiring
>> passwords for both local and host authentication."
> 
> That message is a bit long.  Maybe just
> 
> must specify a password for the superuser to enable password authentication
> 
> without reference to the specific method.  I think the idea is clear there.

+1

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-27 15:59, Stephen Frost wrote:

Agreed- let's remove the legacy options.  As I've mentioned elsewhere,
distros may manage the issue for us, and if we want to get into it, we
could consider adding support to pg_upgrade to complain if it comes
across a legacy setting that isn't valid.  I'm not sure that's
worthwhile though.


More along these lines: We could also remove the ENCRYPTED and 
UNENCRYPTED keywords from CREATE and ALTER ROLE.  AFAICT, these have 
never been emitted by pg_dump or psql, so there are no concerns from 
that end.  Thoughts?


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-27 15:25, Jonathan S. Katz wrote:

$ initdb -D data --auth-local=scram-sha-256 --auth-host=md5

Got an error message:

"initdb: error: must specify a password for the superuser to enable md5
authentication"

For the last two, that behavior is to be expected (after all, you've set
the two login vectors to require passwords), but the error message seems
odd now. Perhaps we tweak it to be:


"initdb: error: must specify a password for the superuser when requiring
passwords for both local and host authentication."


That message is a bit long.  Maybe just

must specify a password for the superuser to enable password authentication

without reference to the specific method.  I think the idea is clear there.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: password_encryption default

[Stephen Frost]

Greetings,

* Jonathan S. Katz (jk...@postgresql.org) wrote:
> On 5/27/20 9:13 AM, Michael Paquier wrote:
> > On Wed, May 27, 2020 at 02:56:34PM +0200, Magnus Hagander wrote:
> >> Seems like the better choice yeah. Since we're changing the default anyway,
> >> maybe now is the time to do that? Or if not, maybe have it log an explicit
> >> deprecation warning when it loads a config with it?
> > 
> > Not sure that's worth it here, so I would just remove the whole.  It
> > would be confusing to keep the past values and have them map to
> > something we think is not an appropriate default.
> 
> +1 to removing the legacy options. It could break some people on legacy
> upgrades, but my guess would be that said situations are very small, and
> we would document the removal of these as "breaking changes" in the
> release notes.

Agreed- let's remove the legacy options.  As I've mentioned elsewhere,
distros may manage the issue for us, and if we want to get into it, we
could consider adding support to pg_upgrade to complain if it comes
across a legacy setting that isn't valid.  I'm not sure that's
worthwhile though.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Jonathan S. Katz]

On 5/27/20 9:13 AM, Michael Paquier wrote:
> On Wed, May 27, 2020 at 02:56:34PM +0200, Magnus Hagander wrote:
>> Seems like the better choice yeah. Since we're changing the default anyway,
>> maybe now is the time to do that? Or if not, maybe have it log an explicit
>> deprecation warning when it loads a config with it?
> 
> Not sure that's worth it here, so I would just remove the whole.  It
> would be confusing to keep the past values and have them map to
> something we think is not an appropriate default.

+1 to removing the legacy options. It could break some people on legacy
upgrades, but my guess would be that said situations are very small, and
we would document the removal of these as "breaking changes" in the
release notes.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Jonathan S. Katz]

On 5/26/20 4:25 AM, Peter Eisentraut wrote:
> On 2020-05-25 17:57, Jonathan S. Katz wrote:
>> I took a look over, it looks good. One question on the initdb.c diff:
>>
>> -    if (strcmp(authmethodlocal, "scram-sha-256") == 0 ||
>> -    strcmp(authmethodhost, "scram-sha-256") == 0)
>> -    {
>> -    conflines = replace_token(conflines,
>> -  "#password_encryption = md5",
>> -  "password_encryption =
>> scram-sha-256");
>> -    }
>> -
>>
>> Would we reverse this, i.e. if someone chooses authmethodlocal to be
>> "md5", we would then set "password_encryption = md5"?
> 
> Yeah, I was too enthusiastic about removing that.  Here is a better patch.

Did some testing. Overall it looks good. Here are my test cases and what
happened:

$ initdb -D data

Deferred password_encryption to the default, confirmed it was indeed scram

$ initdb -D data --auth-local=md5


Set password_encryption to md5

$ initdb -D data --auth-host=md5

Set password_encryption to md5

$ initdb -D data --auth-local=md5 --auth-host=scram-sha-256

Got an error message:

initdb: error: must specify a password for the superuser to enable
scram-sha-256 authentication

$ initdb -D data --auth-local=scram-sha-256 --auth-host=md5

Got an error message:

"initdb: error: must specify a password for the superuser to enable md5
authentication"

For the last two, that behavior is to be expected (after all, you've set
the two login vectors to require passwords), but the error message seems
odd now. Perhaps we tweak it to be:


"initdb: error: must specify a password for the superuser when requiring
passwords for both local and host authentication."

Another option could be to set the message based on whether both
local/host have the same setting, and then default to something like the
above when they differ.

Other than that, looks great!

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Michael Paquier]

On Wed, May 27, 2020 at 02:56:34PM +0200, Magnus Hagander wrote:
> Seems like the better choice yeah. Since we're changing the default anyway,
> maybe now is the time to do that? Or if not, maybe have it log an explicit
> deprecation warning when it loads a config with it?

Not sure that's worth it here, so I would just remove the whole.  It
would be confusing to keep the past values and have them map to
something we think is not an appropriate default.
--
Michael


signature.asc
Description: PGP signature

Re: password_encryption default

[Magnus Hagander]

On Wed, May 27, 2020 at 8:29 AM Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 2020-05-27 08:00, Michael Paquier wrote:
> > On Tue, May 26, 2020 at 10:25:25AM +0200, Peter Eisentraut wrote:
> >> Yeah, I was too enthusiastic about removing that.  Here is a better
> patch.
> >
> > +as an MD5 hash.  (on is also accepted, as an
> alias
> > +for md5.)  The default is
> > +scram-sha-256.
> > Shouldn't password_encryption = on/true/1/yes be an equivalent of
> > scram-sha-256 as the default gets changed?
>
> I think these are mostly legacy options anyway, so if we wanted to make
> a change, we should remove them.
>

Seems like the better choice yeah. Since we're changing the default anyway,
maybe now is the time to do that? Or if not, maybe have it log an explicit
deprecation warning when it loads a config with it?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-27 08:00, Michael Paquier wrote:

On Tue, May 26, 2020 at 10:25:25AM +0200, Peter Eisentraut wrote:

Yeah, I was too enthusiastic about removing that.  Here is a better patch.


+as an MD5 hash.  (on is also accepted, as an alias
+for md5.)  The default is
+scram-sha-256.
Shouldn't password_encryption = on/true/1/yes be an equivalent of
scram-sha-256 as the default gets changed?


I think these are mostly legacy options anyway, so if we wanted to make 
a change, we should remove them.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: password_encryption default

[Michael Paquier]

On Tue, May 26, 2020 at 10:25:25AM +0200, Peter Eisentraut wrote:
> Yeah, I was too enthusiastic about removing that.  Here is a better patch.

+as an MD5 hash.  (on is also accepted, as an alias
+for md5.)  The default is
+scram-sha-256.
Shouldn't password_encryption = on/true/1/yes be an equivalent of
scram-sha-256 as the default gets changed?
--
Michael


signature.asc
Description: PGP signature

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-25 17:57, Jonathan S. Katz wrote:

I took a look over, it looks good. One question on the initdb.c diff:

-   if (strcmp(authmethodlocal, "scram-sha-256") == 0 ||
-   strcmp(authmethodhost, "scram-sha-256") == 0)
-   {
-   conflines = replace_token(conflines,
- 
"#password_encryption = md5",
- 
"password_encryption = scram-sha-256");
-   }
-

Would we reverse this, i.e. if someone chooses authmethodlocal to be
"md5", we would then set "password_encryption = md5"?


Yeah, I was too enthusiastic about removing that.  Here is a better patch.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From fdf1fdd396073307e917a4eaccb58427926f2312 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Tue, 26 May 2020 10:08:22 +0200
Subject: [PATCH v2] Change default of password_encryption to scram-sha-256

Discussion: 
https://www.postgresql.org/message-id/flat/d5b0ad33-7d94-bdd1-caac-43a1c782cab2%402ndquadrant.com
---
 doc/src/sgml/config.sgml  | 12 +++-
 src/backend/commands/user.c   |  2 +-
 src/backend/utils/misc/guc.c  |  2 +-
 src/backend/utils/misc/postgresql.conf.sample |  2 +-
 src/bin/initdb/initdb.c   | 14 ++
 5 files changed, 20 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a2694e548a..9cbaff0c51 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1013,11 +1013,13 @@ Authentication
   

 When a password is specified in  or
-, this parameter determines the 
algorithm
-to use to encrypt the password. The default value is 
md5,
-which stores the password as an MD5 hash (on is also
-accepted, as alias for md5). Setting this parameter 
to
-scram-sha-256 will encrypt the password with 
SCRAM-SHA-256.
+, this parameter determines the
+algorithm to use to encrypt the password.  Possible values are
+scram-sha-256, which will encrypt the password with
+SCRAM-SHA-256, and md5, which stores the password
+as an MD5 hash.  (on is also accepted, as an alias
+for md5.)  The default is
+scram-sha-256.


 Note that older clients might lack support for the SCRAM authentication
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 1ef00d6e89..9ce9a66921 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -43,7 +43,7 @@ Oid   binary_upgrade_next_pg_authid_oid = 
InvalidOid;
 
 
 /* GUC parameter */
-intPassword_encryption = PASSWORD_TYPE_MD5;
+intPassword_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 2f3e0a70e0..390d5d9655 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4735,7 +4735,7 @@ static struct config_enum ConfigureNamesEnum[] =
 "this parameter determines 
whether the password is to be encrypted.")
},
&Password_encryption,
-   PASSWORD_TYPE_MD5, password_encryption_options,
+   PASSWORD_TYPE_SCRAM_SHA_256, password_encryption_options,
NULL, NULL, NULL
},
 
diff --git a/src/backend/utils/misc/postgresql.conf.sample 
b/src/backend/utils/misc/postgresql.conf.sample
index 995b6ca155..120a75386c 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -88,7 +88,7 @@
 # - Authentication -
 
 #authentication_timeout = 1min # 1s-600s
-#password_encryption = md5 # md5 or scram-sha-256
+#password_encryption = scram-sha-256   # scram-sha-256 or md5
 #db_user_namespace = off
 
 # GSSAPI using Kerberos
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 67021a6dc1..b1f49abe36 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1198,12 +1198,18 @@ setup_config(void)
  
"#update_process_title = off");
 #endif
 
-   if (strcmp(authmethodlocal, "scram-sha-256") == 0 ||
-   strcmp(authmethodhost, "scram-sha-256") == 0)
+   /*
+* Change password_encryption setting to md5 if md5 was chosen as an
+* authentication method, unless scram-sha-256 was also chosen.
+*/
+   if ((strcmp(authmethodlocal, "md5") == 0 &&
+strcmp(authmethodhost, "scram-sha-256") != 0) ||
+   (strcmp(authmethodhost, "md5")

Re: password_encryption default

[Jonathan S. Katz]

On 5/25/20 5:45 AM, Peter Eisentraut wrote:
> On 2020-05-22 23:23, Jonathan S. Katz wrote:
>>> Yeah.  But there's still something to Jonathan's argument, because 9.6
>>> will go EOL in November 2021, which is pretty close to when v14 will
>>> reach public release (assuming we can hold to the typical schedule).
>>> If we do it in v13, there'll be a full year where still-supported
>>> versions of PG can't do SCRAM, implying that clients would likely
>>> fail to connect to an up-to-date server.
>>
>> ^ that's what I meant.
> 
> Here is a proposed patch for PG14 then.

This makes me happy :D

I took a look over, it looks good. One question on the initdb.c diff:

-   if (strcmp(authmethodlocal, "scram-sha-256") == 0 ||
-   strcmp(authmethodhost, "scram-sha-256") == 0)
-   {
-   conflines = replace_token(conflines,
- 
"#password_encryption = md5",
- 
"password_encryption = scram-sha-256");
-   }
-

Would we reverse this, i.e. if someone chooses authmethodlocal to be
"md5", we would then set "password_encryption = md5"?

Thanks,

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Peter Eisentraut]

On 2020-05-22 23:23, Jonathan S. Katz wrote:

Yeah.  But there's still something to Jonathan's argument, because 9.6
will go EOL in November 2021, which is pretty close to when v14 will
reach public release (assuming we can hold to the typical schedule).
If we do it in v13, there'll be a full year where still-supported
versions of PG can't do SCRAM, implying that clients would likely
fail to connect to an up-to-date server.


^ that's what I meant.


Here is a proposed patch for PG14 then.

--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From ab80089dbaf5d75dac51aa968f8658b4516020d8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Mon, 25 May 2020 11:22:32 +0200
Subject: [PATCH] Change default of password_encryption to scram-sha-256

Discussion: 
https://www.postgresql.org/message-id/flat/d5b0ad33-7d94-bdd1-caac-43a1c782cab2%402ndquadrant.com
---
 doc/src/sgml/config.sgml  | 12 +++-
 src/backend/commands/user.c   |  2 +-
 src/backend/utils/misc/guc.c  |  2 +-
 src/backend/utils/misc/postgresql.conf.sample |  2 +-
 src/bin/initdb/initdb.c   |  8 
 5 files changed, 10 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a2694e548a..9cbaff0c51 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1013,11 +1013,13 @@ Authentication
   

 When a password is specified in  or
-, this parameter determines the 
algorithm
-to use to encrypt the password. The default value is 
md5,
-which stores the password as an MD5 hash (on is also
-accepted, as alias for md5). Setting this parameter 
to
-scram-sha-256 will encrypt the password with 
SCRAM-SHA-256.
+, this parameter determines the
+algorithm to use to encrypt the password.  Possible values are
+scram-sha-256, which will encrypt the password with
+SCRAM-SHA-256, and md5, which stores the password
+as an MD5 hash.  (on is also accepted, as an alias
+for md5.)  The default is
+scram-sha-256.


 Note that older clients might lack support for the SCRAM authentication
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 1ef00d6e89..9ce9a66921 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -43,7 +43,7 @@ Oid   binary_upgrade_next_pg_authid_oid = 
InvalidOid;
 
 
 /* GUC parameter */
-intPassword_encryption = PASSWORD_TYPE_MD5;
+intPassword_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
 
 /* Hook to check passwords in CreateRole() and AlterRole() */
 check_password_hook_type check_password_hook = NULL;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 2f3e0a70e0..390d5d9655 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4735,7 +4735,7 @@ static struct config_enum ConfigureNamesEnum[] =
 "this parameter determines 
whether the password is to be encrypted.")
},
&Password_encryption,
-   PASSWORD_TYPE_MD5, password_encryption_options,
+   PASSWORD_TYPE_SCRAM_SHA_256, password_encryption_options,
NULL, NULL, NULL
},
 
diff --git a/src/backend/utils/misc/postgresql.conf.sample 
b/src/backend/utils/misc/postgresql.conf.sample
index 995b6ca155..120a75386c 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -88,7 +88,7 @@
 # - Authentication -
 
 #authentication_timeout = 1min # 1s-600s
-#password_encryption = md5 # md5 or scram-sha-256
+#password_encryption = scram-sha-256   # scram-sha-256 or md5
 #db_user_namespace = off
 
 # GSSAPI using Kerberos
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 67021a6dc1..234635fe2c 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1198,14 +1198,6 @@ setup_config(void)
  
"#update_process_title = off");
 #endif
 
-   if (strcmp(authmethodlocal, "scram-sha-256") == 0 ||
-   strcmp(authmethodhost, "scram-sha-256") == 0)
-   {
-   conflines = replace_token(conflines,
- 
"#password_encryption = md5",
- 
"password_encryption = scram-sha-256");
-   }
-
/*
 * If group access has been enabled for the cluster then it makes sense 
to
 * ensure that the log files also allow group access.  Otherwise a 
backup
-- 
2.26.2

Re: password_encryption default

[Jonathan S. Katz]

On 5/22/20 5:21 PM, Tom Lane wrote:
> Vik Fearing  writes:
>> On 5/22/20 9:09 PM, Jonathan S. Katz wrote:
>>> As someone who is an unabashed SCRAM fan and was hoping the default
>>> would be up'd for v13, I would actually +1 making it the default in v14,
>>> i.e. because 9.5 will be EOL at that point, and as such we both have
>>> every* driver supporting SCRAM AND every version of PostgreSQL
>>> supporting SCRAM.
> 
>> Wasn't SCRAM introduced in 10?
> 
> Yeah.  But there's still something to Jonathan's argument, because 9.6
> will go EOL in November 2021, which is pretty close to when v14 will
> reach public release (assuming we can hold to the typical schedule).
> If we do it in v13, there'll be a full year where still-supported
> versions of PG can't do SCRAM, implying that clients would likely
> fail to connect to an up-to-date server.

^ that's what I meant.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Tom Lane]

Vik Fearing  writes:
> On 5/22/20 9:09 PM, Jonathan S. Katz wrote:
>> As someone who is an unabashed SCRAM fan and was hoping the default
>> would be up'd for v13, I would actually +1 making it the default in v14,
>> i.e. because 9.5 will be EOL at that point, and as such we both have
>> every* driver supporting SCRAM AND every version of PostgreSQL
>> supporting SCRAM.

> Wasn't SCRAM introduced in 10?

Yeah.  But there's still something to Jonathan's argument, because 9.6
will go EOL in November 2021, which is pretty close to when v14 will
reach public release (assuming we can hold to the typical schedule).
If we do it in v13, there'll be a full year where still-supported
versions of PG can't do SCRAM, implying that clients would likely
fail to connect to an up-to-date server.

regards, tom lane

Re: password_encryption default

[Vik Fearing]

On 5/22/20 9:09 PM, Jonathan S. Katz wrote:
> As someone who is an unabashed SCRAM fan and was hoping the default
> would be up'd for v13, I would actually +1 making it the default in v14,
> i.e. because 9.5 will be EOL at that point, and as such we both have
> every* driver supporting SCRAM AND every version of PostgreSQL
> supporting SCRAM.

Wasn't SCRAM introduced in 10?
-- 
Vik Fearing

Re: password_encryption default

[Jonathan S. Katz]

On 5/22/20 11:34 AM, Tom Lane wrote:
> Stephen Frost  writes:
>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>>> As far as that last goes, we *did* get the buildfarm fixed to be all
>>> v11 scripts, so I thought we were ready to move forward on trying
>>> 09f08930f again.  It's too late to consider that for v13, but
>>> perhaps it'd be reasonable to change the SCRAM default now?  Not sure.
> 
>> I feel like it is.  I'm not even sure that I agree that it's really too
>> late to consider 09f08930f considering that's it's a pretty minor code
>> change and the up-side to that is having reasonable defaults out of the
>> box, as it were, something we have *long* been derided for.
> 
> Well, the argument against changing right now is that it would invalidate
> portability testing done against beta1, which users would be justifiably
> upset about.
> 
> I'm +1 for changing both of these things as soon as we branch for v14,
> but I feel like it's a bit late for v13.  If we aren't feature-frozen
> now, when will we be?

As someone who is an unabashed SCRAM fan and was hoping the default
would be up'd for v13, I would actually +1 making it the default in v14,
i.e. because 9.5 will be EOL at that point, and as such we both have
every* driver supporting SCRAM AND every version of PostgreSQL
supporting SCRAM.

(Would I personally love to do it sooner? Yes...but I think the stars
align for doing it in v14).

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: password_encryption default

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> I'm +1 for changing both of these things as soon as we branch for v14,
> >> but I feel like it's a bit late for v13.  If we aren't feature-frozen
> >> now, when will we be?
> 
> > I really don't consider changing of defaults to be on the same level as
> > implementation of whole features, even if changing those defaults
> > requires a few lines of code to go with the change.
> 
> The buildfarm fiasco with 09f08930f should remind us that changing
> defaults *does* break things, even if theoretically it shouldn't.
> At this phase of the v13 cycle, we should be looking to fix bugs,
> not to break more stuff.

Sure it does- for the special case of the buildfarm, and that takes
buildfarm code to fix.  Having users make changes to whatever scripts
they're using with PG between major versions is certainly not
unreasonable, or even between beta and final.  These things are not set
in stone at this point, they're the defaults, and it's beta time now,
not post release or RC.

If it breaks for regular users who are using the system properly then we
want to know about that and we'd ideally like to get that fixed before
the release.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Tom Lane]

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> I'm +1 for changing both of these things as soon as we branch for v14,
>> but I feel like it's a bit late for v13.  If we aren't feature-frozen
>> now, when will we be?

> I really don't consider changing of defaults to be on the same level as
> implementation of whole features, even if changing those defaults
> requires a few lines of code to go with the change.

The buildfarm fiasco with 09f08930f should remind us that changing
defaults *does* break things, even if theoretically it shouldn't.
At this phase of the v13 cycle, we should be looking to fix bugs,
not to break more stuff.

regards, tom lane

Re: password_encryption default

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> As far as that last goes, we *did* get the buildfarm fixed to be all
> >> v11 scripts, so I thought we were ready to move forward on trying
> >> 09f08930f again.  It's too late to consider that for v13, but
> >> perhaps it'd be reasonable to change the SCRAM default now?  Not sure.
> 
> > I feel like it is.  I'm not even sure that I agree that it's really too
> > late to consider 09f08930f considering that's it's a pretty minor code
> > change and the up-side to that is having reasonable defaults out of the
> > box, as it were, something we have *long* been derided for.
> 
> Well, the argument against changing right now is that it would invalidate
> portability testing done against beta1, which users would be justifiably
> upset about.

I don't think we're in complete agreement about the amount of
portability testing that's done with our beta source builds.  To that
point, however, the lack of such testing happening, if there is a lack,
is on us just as much as anyone else- we should be testing, to the
extent possible, as many variations of our configuration options as we
can across as many platforms as we can in the buildfarm.  If a
non-default setting doesn't work on one platform or another, that's a
bug to fix regardless and doesn't really impact the question of "what
should be the default".

> I'm +1 for changing both of these things as soon as we branch for v14,
> but I feel like it's a bit late for v13.  If we aren't feature-frozen
> now, when will we be?

I really don't consider changing of defaults to be on the same level as
implementation of whole features, even if changing those defaults
requires a few lines of code to go with the change.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Tom Lane]

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> As far as that last goes, we *did* get the buildfarm fixed to be all
>> v11 scripts, so I thought we were ready to move forward on trying
>> 09f08930f again.  It's too late to consider that for v13, but
>> perhaps it'd be reasonable to change the SCRAM default now?  Not sure.

> I feel like it is.  I'm not even sure that I agree that it's really too
> late to consider 09f08930f considering that's it's a pretty minor code
> change and the up-side to that is having reasonable defaults out of the
> box, as it were, something we have *long* been derided for.

Well, the argument against changing right now is that it would invalidate
portability testing done against beta1, which users would be justifiably
upset about.

I'm +1 for changing both of these things as soon as we branch for v14,
but I feel like it's a bit late for v13.  If we aren't feature-frozen
now, when will we be?

regards, tom lane

Re: password_encryption default

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > * Magnus Hagander (mag...@hagander.net) wrote:
> >> On Fri, May 22, 2020 at 4:13 PM Tom Lane  wrote:
> >>> Peter Eisentraut  writes:
>  We didn't get anywhere with making the default authentication method in
>  a source build anything other than trust.
> 
> > I'm +1 on moving the default for password_encryption to be
> > scram.  Even better would be changing the pg_hba.conf default, but I
> > think we still have concerns about that having problems with the
> > regression tests and the buildfarm.
> 
> As far as that last goes, we *did* get the buildfarm fixed to be all
> v11 scripts, so I thought we were ready to move forward on trying
> 09f08930f again.  It's too late to consider that for v13, but
> perhaps it'd be reasonable to change the SCRAM default now?  Not sure.

I feel like it is.  I'm not even sure that I agree that it's really too
late to consider 09f08930f considering that's it's a pretty minor code
change and the up-side to that is having reasonable defaults out of the
box, as it were, something we have *long* been derided for.

> Post-beta1 isn't the best time for such things.

It'd be good to be consistent about this between the packagers and the
source builds, imv, and we don't tend to think about that until we have
packages being built and distributed and used and that ends up being
post-beta1.  If we want that changed then we should go back to having
alphas..

In general though, I'm reasonably comfortable with changing of default
values post beta1.  I do appreciate that not everyone would agree with
that, but with all the effort that's put into getting everything working
with SCRAM, it'd be a real shame to keep md5 as the default for yet
another year and a half..

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Tom Lane]

Stephen Frost  writes:
> * Magnus Hagander (mag...@hagander.net) wrote:
>> On Fri, May 22, 2020 at 4:13 PM Tom Lane  wrote:
>>> Peter Eisentraut  writes:
 We didn't get anywhere with making the default authentication method in
 a source build anything other than trust.

> I'm +1 on moving the default for password_encryption to be
> scram.  Even better would be changing the pg_hba.conf default, but I
> think we still have concerns about that having problems with the
> regression tests and the buildfarm.

As far as that last goes, we *did* get the buildfarm fixed to be all
v11 scripts, so I thought we were ready to move forward on trying
09f08930f again.  It's too late to consider that for v13, but
perhaps it'd be reasonable to change the SCRAM default now?  Not sure.
Post-beta1 isn't the best time for such things.

regards, tom lane

Re: password_encryption default

[Stephen Frost]

Greetings,

* Magnus Hagander (mag...@hagander.net) wrote:
> On Fri, May 22, 2020 at 4:13 PM Tom Lane  wrote:
> > Peter Eisentraut  writes:
> > > We didn't get anywhere with making the default authentication method in
> > > a source build anything other than trust.  But perhaps we should change
> > > the default for password_encryption to nudge people to adopt SCRAM?
> > > Right now, passwords are still hashed using MD5 by default, unless you
> > > specify scram-sha-256 using initdb -A or similar.
> >
> > I think what that was waiting on was for client libraries to become
> > SCRAM-ready.  Do we have an idea of the state of play on that side?
> >
> 
> If the summary table on the wiki at
> https://wiki.postgresql.org/wiki/List_of_drivers is to be trusted, every
> listed driver except Swift does.

Yes, Katz actually went through and worked with folks to make that
happen.  I'm +1 on moving the default for password_encryption to be
scram.  Even better would be changing the pg_hba.conf default, but I
think we still have concerns about that having problems with the
regression tests and the buildfarm.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: password_encryption default

[Magnus Hagander]

On Fri, May 22, 2020 at 4:13 PM Tom Lane  wrote:

> Peter Eisentraut  writes:
> > We didn't get anywhere with making the default authentication method in
> > a source build anything other than trust.  But perhaps we should change
> > the default for password_encryption to nudge people to adopt SCRAM?
> > Right now, passwords are still hashed using MD5 by default, unless you
> > specify scram-sha-256 using initdb -A or similar.
>
> I think what that was waiting on was for client libraries to become
> SCRAM-ready.  Do we have an idea of the state of play on that side?
>

If the summary table on the wiki at
https://wiki.postgresql.org/wiki/List_of_drivers is to be trusted, every
listed driver except Swift does.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: password_encryption default

[Tom Lane]

Peter Eisentraut  writes:
> We didn't get anywhere with making the default authentication method in 
> a source build anything other than trust.  But perhaps we should change 
> the default for password_encryption to nudge people to adopt SCRAM? 
> Right now, passwords are still hashed using MD5 by default, unless you 
> specify scram-sha-256 using initdb -A or similar.

I think what that was waiting on was for client libraries to become
SCRAM-ready.  Do we have an idea of the state of play on that side?

regards, tom lane

password_encryption default

[Peter Eisentraut]

We didn't get anywhere with making the default authentication method in 
a source build anything other than trust.  But perhaps we should change 
the default for password_encryption to nudge people to adopt SCRAM? 
Right now, passwords are still hashed using MD5 by default, unless you 
specify scram-sha-256 using initdb -A or similar.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 6:10 PM, Jonathan S. Katz wrote:
> On 4/8/19 4:20 PM, Alvaro Herrera wrote:
>> On 2019-Apr-08, Jonathan S. Katz wrote:
>>
>>> On 4/8/19 4:10 PM, Alvaro Herrera wrote:
>>
 I wonder why we have two pages
 https://wiki.postgresql.org/wiki/Client_Libraries
 https://wiki.postgresql.org/wiki/List_of_drivers
>>>
>>> No clue, but it appears that first one is the newer of the two[1][2]
>>>
>>> I'd be happy to consolidate them and provide a forwarding reference from
>>> Client Libraries to List of Drivers, given I think we reference "List of
>>> Drivers" in other places.
>>
>> There are two links to List of drivers, and one of them is in Client
>> Libraries :-)
>> https://wiki.postgresql.org/wiki/Special:WhatLinksHere/Client_Libraries
>>
>> +1 for consolidation and setting up a redirect.
> 
> OK, so trying to not be too off topic, I did update the original page as so:
> 
> https://wiki.postgresql.org/wiki/List_of_drivers
> 
> That said, I am still in favor of the PG13 plan, and without objection I
> would like to reach out to the driver authors in the "no" category,
> reference this thread, and that this is at least discussed, if not
> decided upon, and they should considering adding support for SCRAM to
> allow adequate testing time as well as time for their drivers to make it
> into appropriate packaging systems.

OK so a small update, going through the list[1]:

- The golang drivers all now support SCRAM
- I've reached out to the remaining two driver projects on the list to
make them aware of this thread and the timeline discussion, and to offer
any help where needed in adding SCRAM support.

Jonathan

[1]https://wiki.postgresql.org/wiki/List_of_drivers



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Bruce Momjian]

On Mon, Apr  8, 2019 at 10:08:07AM -0400, Tom Lane wrote:
> "Jonathan S. Katz"  writes:
> > On 4/8/19 8:49 AM, Magnus Hagander wrote:
> >> I think the real question is, is it OK to give them basically 5months
> >> warning, by right now saying if you don't have a release out in 6
> >> months, things will break.
> 
> > Given the supported libraries all have open pull requests or issues, it
> > should be fairly easy to inquire if they would be able to support it for
> > PG12 vs PG13. If this sounds like a reasonable plan, I'm happy to reach
> > out and see.
> 
> I think that the right course here is to notify these developers that
> we will change the default in PG13, and it'd be good if they put out
> stable releases with SCRAM support well before that.  This discussion
> seems to be talking as though it's okay if we allow zero daylight
> between availability of fixed drivers and release of a PG version that
> defaults to using SCRAM.  That'd be totally unfair to packagers and
> users.  There needs to be a pretty fair-size window for those fixed
> drivers to propagate into the wild.  A year is not too much; IMO it's
> barely enough.

It would be nice to address channel binding as part of this.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: change password_encryption default to scram-sha-256?

[Tatsuo Ishii]

>> I am not sure all third party programs concerning scram-sha-256 are
>> listed on this. There are some programs that talk to PostgreSQL using
>> frontend/backend protocol, but not based on libpq or other native
>> drivers (for example Pgpool-II). I guess PgBouncer is in the same
>> category too.
>>
> ... and pgbouncer doesn't support scram-sha-256 authentication method.
> There is a bit-rot PR but the discussion died a while ago. It is
> widely used and it would be really sad to turn on SCRAM on v13 without
> pgbouncer SCRAM support.

I don't how hard it would be for pgbouncer to support scram-sha-256,
but it was pretty hard for Pgpool-II to support scram-sha-256. In case
of Pgpool-II (it starts to support it since 4.0), it needed to keep
clients' password lists.

http://www.pgpool.net/docs/latest/en/html/auth-methods.html#AUTH-SCRAM

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: change password_encryption default to scram-sha-256?

[Euler Taveira]

Em seg, 8 de abr de 2019 às 19:43, Tatsuo Ishii  escreveu:
>
> I am not sure all third party programs concerning scram-sha-256 are
> listed on this. There are some programs that talk to PostgreSQL using
> frontend/backend protocol, but not based on libpq or other native
> drivers (for example Pgpool-II). I guess PgBouncer is in the same
> category too.
>
... and pgbouncer doesn't support scram-sha-256 authentication method.
There is a bit-rot PR but the discussion died a while ago. It is
widely used and it would be really sad to turn on SCRAM on v13 without
pgbouncer SCRAM support.


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: change password_encryption default to scram-sha-256?

[Tatsuo Ishii]

> On Sun, Apr 07, 2019 at 12:59:05PM -0400, Tom Lane wrote:
>> Peter Eisentraut  writes:
>> > Should we change the default of the password_encryption setting to
>> > 'scram-sha-256' in PG12?
>> 
>> I thought we were going to wait a bit longer --- that just got added
>> last year, no?  What do we know about the state of support in client
>> libraries?
> 
> Great idea!  Does it make sense to test all, or at least some
> significant fraction of the connectors listed in
> https://wiki.postgresql.org/wiki/Client_Libraries by default?

I am not sure all third party programs concerning scram-sha-256 are
listed on this. There are some programs that talk to PostgreSQL using
frontend/backend protocol, but not based on libpq or other native
drivers (for example Pgpool-II). I guess PgBouncer is in the same
category too.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 4:20 PM, Alvaro Herrera wrote:
> On 2019-Apr-08, Jonathan S. Katz wrote:
> 
>> On 4/8/19 4:10 PM, Alvaro Herrera wrote:
> 
>>> I wonder why we have two pages
>>> https://wiki.postgresql.org/wiki/Client_Libraries
>>> https://wiki.postgresql.org/wiki/List_of_drivers
>>
>> No clue, but it appears that first one is the newer of the two[1][2]
>>
>> I'd be happy to consolidate them and provide a forwarding reference from
>> Client Libraries to List of Drivers, given I think we reference "List of
>> Drivers" in other places.
> 
> There are two links to List of drivers, and one of them is in Client
> Libraries :-)
> https://wiki.postgresql.org/wiki/Special:WhatLinksHere/Client_Libraries
> 
> +1 for consolidation and setting up a redirect.

OK, so trying to not be too off topic, I did update the original page as so:

https://wiki.postgresql.org/wiki/List_of_drivers

When determining what to add, I tried to keep it one-abstraction level
deep, i.e., a driver is implemented on top of libpq, implemented the PG
protocol on its own, or did some driver-like extensions on top of the
base language driver. I steered clear of ORMs or other abstraction
layers unless they met the above criteria.

(There are a lot of handy ORM-ish abstraction layers as well, but I
don't want to go down that path on that page, at least not today).

I also added a deprecation warning on top of the "Client Libraries"
page. If we're feeling satisfied with the consolidation, I'll wipe the
content and indicate where the maintained content is and end the
split-brain situation.

(One thing that I will say is this is one of those sections that may be
worth moving to pgweb, to give it some semi-permanence. Separate
discussion.)

The good news: while going through the added drivers, most of the
non-libpq ones I've added do support SCRAM :)

That said, I am still in favor of the PG13 plan, and without objection I
would like to reach out to the driver authors in the "no" category,
reference this thread, and that this is at least discussed, if not
decided upon, and they should considering adding support for SCRAM to
allow adequate testing time as well as time for their drivers to make it
into appropriate packaging systems.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Dave Cramer  writes:
> That said 42.2.0 was released in January 2018, so by PG13 it's going to be
> 4 years old.

Huh?  13 should come out in the fall of 2020.

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Dave Cramer]

On Mon, 8 Apr 2019 at 16:38, Tom Lane  wrote:

> Dave Cramer  writes:
> >> If someone installs a postgres RPM/DEB from postgresql.org, they could
> >> also install postgresql-jdbc, right ?
>
> > I would guess there might be some distro specific java apps that might
> > actually use what is on the machine but as mentioned any reasonably
> complex
> > Java app is going to ensure it has the correct versions for their app
> using
> > Maven.
>
> I'm not really sure if that makes things better or worse.  If some app
> thinks that it needs version N of the driver, but SCRAM support was
> added in version N-plus-something, how tough is it going to be to get
> it updated?  And are you going to have to go through that dance for
> each app separately?
>
>

I see the problem you are contemplating, but even installing a newer
version of the driver has it's perils (we have been known to break some
expectations in the name of the spec).
So I could see a situation where there is a legacy app that wants to use
SCRAM. They update the JDBC jar on the system and due to the "new and
improved" version their app breaks.
Honestly I don't have a solution to this.

That said 42.2.0 was released in January 2018, so by PG13 it's going to be
4 years old.

Dave

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Dave Cramer  writes:
>> If someone installs a postgres RPM/DEB from postgresql.org, they could
>> also install postgresql-jdbc, right ?

> I would guess there might be some distro specific java apps that might
> actually use what is on the machine but as mentioned any reasonably complex
> Java app is going to ensure it has the correct versions for their app using
> Maven.

I'm not really sure if that makes things better or worse.  If some app
thinks that it needs version N of the driver, but SCRAM support was
added in version N-plus-something, how tough is it going to be to get
it updated?  And are you going to have to go through that dance for
each app separately?

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Dave Cramer]

>
>
>
> > The scenario that worries me here is somebody using a bleeding-edge PGDG
> > server package in an environment where the rest of the Postgres ecosystem
> > is much less bleeding-edge.
>
> If someone installs a postgres RPM/DEB from postgresql.org, they could
> also
> install postgresql-jdbc, right ?
>
>
No, this is not how the majority of people use Java at all. They would use
Maven to pull down the JDBC driver of choice.

I would guess there might be some distro specific java apps that might
actually use what is on the machine but as mentioned any reasonably complex
Java app is going to ensure it has the correct versions for their app using
Maven.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


>

Re: change password_encryption default to scram-sha-256?

[Alvaro Herrera]

On 2019-Apr-08, Tom Lane wrote:

> I'm particularly concerned about the idea that they won't see a problem
> during initial testing, only to have things fall over after they enter
> production and do a "routine" password change.

This is a fair objection.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Alvaro Herrera]

On 2019-Apr-08, Jonathan S. Katz wrote:

> On 4/8/19 4:10 PM, Alvaro Herrera wrote:

> > I wonder why we have two pages
> > https://wiki.postgresql.org/wiki/Client_Libraries
> > https://wiki.postgresql.org/wiki/List_of_drivers
> 
> No clue, but it appears that first one is the newer of the two[1][2]
> 
> I'd be happy to consolidate them and provide a forwarding reference from
> Client Libraries to List of Drivers, given I think we reference "List of
> Drivers" in other places.

There are two links to List of drivers, and one of them is in Client
Libraries :-)
https://wiki.postgresql.org/wiki/Special:WhatLinksHere/Client_Libraries

+1 for consolidation and setting up a redirect.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Justin Pryzby  writes:
> On Mon, Apr 08, 2019 at 02:28:30PM -0400, Tom Lane wrote:
>> The scenario that worries me here is somebody using a bleeding-edge PGDG
>> server package in an environment where the rest of the Postgres ecosystem
>> is much less bleeding-edge.

> If someone installs a postgres RPM/DEB from postgresql.org, they could also
> install postgresql-jdbc, right ?

The client software is very possibly not on the same machine as the server,
and may indeed not be under the server admin's control.  That sort of
complex interdependency is why we need to move slowly on changes that
require client updates.

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 4:10 PM, Alvaro Herrera wrote:
> On 2019-Apr-08, Dave Cramer wrote:
> 
>> On Mon, 8 Apr 2019 at 16:07, Alvaro Herrera 
>> wrote:
> 
>>> I meant an exception to the common situation that SCRAM-SHA-256 is
>>> supported and shipped in stable releases of each driver.  The wiki here
>>> still says it's unsupported on JDBC:
>>> https://wiki.postgresql.org/wiki/List_of_drivers
>>> For once I'm happy to learn that the wiki is outdated :-)
>>
>> Way too many places to update :)
> 
> Yeah.  Actually, it's up to date (it says "yes from 42.2")... I just
> misread it.
> 
> I wonder why we have two pages
> https://wiki.postgresql.org/wiki/Client_Libraries
> https://wiki.postgresql.org/wiki/List_of_drivers

No clue, but it appears that first one is the newer of the two[1][2]

I'd be happy to consolidate them and provide a forwarding reference from
Client Libraries to List of Drivers, given I think we reference "List of
Drivers" in other places.

Jonathan

[1]
https://wiki.postgresql.org/index.php?title=Client_Libraries&action=history
[2]
https://wiki.postgresql.org/index.php?title=List_of_drivers&action=history



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Alvaro Herrera]

On 2019-Apr-08, Dave Cramer wrote:

> On Mon, 8 Apr 2019 at 16:07, Alvaro Herrera 
> wrote:

> > I meant an exception to the common situation that SCRAM-SHA-256 is
> > supported and shipped in stable releases of each driver.  The wiki here
> > still says it's unsupported on JDBC:
> > https://wiki.postgresql.org/wiki/List_of_drivers
> > For once I'm happy to learn that the wiki is outdated :-)
> 
> Way too many places to update :)

Yeah.  Actually, it's up to date (it says "yes from 42.2")... I just
misread it.

I wonder why we have two pages
https://wiki.postgresql.org/wiki/Client_Libraries
https://wiki.postgresql.org/wiki/List_of_drivers

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Dave Cramer]

On Mon, 8 Apr 2019 at 16:07, Alvaro Herrera 
wrote:

> On 2019-Apr-08, Dave Cramer wrote:
>
> > > IIUC the vast majority of clients already support SCRAM auth.  So the
> > > vast majority of PG users can take advantage of the additional
> security.
> > > I think the only massive-adoption exception is JDBC, and apparently
> they
> > > already have working patches for SCRAM.
> >
> > We have more than patches this is already in the driver.
> >
> > What do you mean by "massive-adoption exception"
>
> I meant an exception to the common situation that SCRAM-SHA-256 is
> supported and shipped in stable releases of each driver.  The wiki here
> still says it's unsupported on JDBC:
> https://wiki.postgresql.org/wiki/List_of_drivers
> For once I'm happy to learn that the wiki is outdated :-)
>


Way too many places to update :)


Dave Cramer

da...@postgresintl.com
www.postgresintl.com


>
>

Re: change password_encryption default to scram-sha-256?

[Alvaro Herrera]

On 2019-Apr-08, Dave Cramer wrote:

> > IIUC the vast majority of clients already support SCRAM auth.  So the
> > vast majority of PG users can take advantage of the additional security.
> > I think the only massive-adoption exception is JDBC, and apparently they
> > already have working patches for SCRAM.
> 
> We have more than patches this is already in the driver.
> 
> What do you mean by "massive-adoption exception"

I meant an exception to the common situation that SCRAM-SHA-256 is
supported and shipped in stable releases of each driver.  The wiki here
still says it's unsupported on JDBC:
https://wiki.postgresql.org/wiki/List_of_drivers
For once I'm happy to learn that the wiki is outdated :-)

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Dave Cramer]

On Mon, 8 Apr 2019 at 15:18, Jonathan S. Katz  wrote:

> On 4/8/19 2:28 PM, Tom Lane wrote:
> > Andres Freund  writes:
> >> On 2019-04-08 13:34:12 -0400, Alvaro Herrera wrote:
> >>> I'm not sure I understand all this talk about deferring changing the
> >>> default to pg13.  AFAICS only a few fringe drivers are missing support;
> >>> not changing in pg12 means we're going to leave *all* users, even those
> >>> whose clients have support, without the additional security for 18 more
> >>> months.
> >
> >> Imo making such changes after feature freeze is somewhat poor
> >> form.
> >
> > Yeah.
>
> Yeah, that's fair.
>
> >
> >> If jdbc didn't support scram, it'd be an absolutely clear no-go imo. A
> >> pretty large fraction of users use jdbc to access postgres. But it seems
> >> to me that support has been merged for a while:
> >> https://github.com/pgjdbc/pgjdbc/pull/1014
> >
> > "Merged to upstream" is a whole lot different from "readily available in
> > the field".  What's the actual status in common Linux distros, for
> > example?
>
> Did some limited research just to get a sense.
>
> Well, if it's RHEL7, it's PostgreSQL 9.2 so, unless they're using our
> RPM, that definitely does not have it :)
>
> (While researching this, I noticed on the main RHEL8 beta page[1] that
> PostgreSQL is actually featured, which is kind of neat. I could not
> quickly find which version of the JDBC driver it is shipping with, though)
>
> On Ubuntu, 18.04 LTS ships PG10, but the version of JDBC does not
> include SCRAM support. 18.10 ships JDBC w/SCRAM support.
>
> On Debian, stretch is on 9.4. buster has 11 packaged, and JDBC is
> shipping with SCRAM support.
>
>

Honestly what JDBC driver XYZ distro ships with is a red herring. Any
reasonably complex java program is going to use maven and pull it's
dependencies.

That said from a driver developer, I support pushing this decision off to
PG13

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


>
>

Re: change password_encryption default to scram-sha-256?

[Dave Cramer]

Alvaro,

On Mon, 8 Apr 2019 at 13:34, Alvaro Herrera 
wrote:

> I'm not sure I understand all this talk about deferring changing the
> default to pg13.  AFAICS only a few fringe drivers are missing support;
> not changing in pg12 means we're going to leave *all* users, even those
> whose clients have support, without the additional security for 18 more
> months.
>
> IIUC the vast majority of clients already support SCRAM auth.  So the
> vast majority of PG users can take advantage of the additional security.
> I think the only massive-adoption exception is JDBC, and apparently they
> already have working patches for SCRAM.
>


We have more than patches this is already in the driver.

What do you mean by "massive-adoption exception"

Dave Cramer

da...@postgresintl.com
www.postgresintl.com



>
>

Re: change password_encryption default to scram-sha-256?

[Justin Pryzby]

On Mon, Apr 08, 2019 at 02:28:30PM -0400, Tom Lane wrote:
>On Mon, Apr 08, 2019 at 10:41:07AM -0700, Andres Freund wrote:
>> If jdbc didn't support scram, it'd be an absolutely clear no-go imo. A
>> pretty large fraction of users use jdbc to access postgres. But it seems
>> to me that support has been merged for a while:
>> https://github.com/pgjdbc/pgjdbc/pull/1014
> 
> "Merged to upstream" is a whole lot different from "readily available in
> the field".  What's the actual status in common Linux distros, for
> example?

I found:

https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.1
Version 42.2.0 (2018-01-17)
Added
Support SCRAM-SHA-256 for PostgreSQL 10 in the JDBC 4.2 version (Java 8+) using 
the Ongres SCRAM library. PR 842

I see that's in ubuntu, but not any LTS release:
https://packages.ubuntu.com/search?keywords=libpostgresql-jdbc-java

And in Debian testing, but no released version:
https://packages.debian.org/search?keywords=libpostgresql-jdbc-java

For centos6/7, OS packages would not have scram support:

$ yum list --showdupl postgresql-jdbc
Available Packages
postgresql-jdbc.noarch  
 9.2.1002-6.el7_5   
 base
postgresql-jdbc.noarch  
 42.2.5-1.rhel7.1   
 pgdg11

$ yum list --showdupl postgresql-jdbc
Available Packages
postgresql-jdbc.noarch  
 8.4.704-2.el6  
 base
postgresql-jdbc.noarch  
 42.2.5-1.rhel6.1   
 pgdg11

> The scenario that worries me here is somebody using a bleeding-edge PGDG
> server package in an environment where the rest of the Postgres ecosystem
> is much less bleeding-edge.

If someone installs a postgres RPM/DEB from postgresql.org, they could also
install postgresql-jdbc, right ?

I realize that doesn't mean that people will consistently know to and actually
do that.

If the default were changed, possibly the PGDG package could define something
like (I haven't done packaging in a long time):
Conflicts: postgresql-jdbc<42.2.0

On Mon, Apr 08, 2019 at 03:18:42PM -0400, Jonathan S. Katz wrote:
> Well, if it's RHEL7, it's PostgreSQL 9.2 so, unless they're using our
> RPM, that definitely does not have it :)

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 2:28 PM, Tom Lane wrote:
> Andres Freund  writes:
>> On 2019-04-08 13:34:12 -0400, Alvaro Herrera wrote:
>>> I'm not sure I understand all this talk about deferring changing the
>>> default to pg13.  AFAICS only a few fringe drivers are missing support;
>>> not changing in pg12 means we're going to leave *all* users, even those
>>> whose clients have support, without the additional security for 18 more
>>> months.
> 
>> Imo making such changes after feature freeze is somewhat poor
>> form.
> 
> Yeah.

Yeah, that's fair.

> 
>> If jdbc didn't support scram, it'd be an absolutely clear no-go imo. A
>> pretty large fraction of users use jdbc to access postgres. But it seems
>> to me that support has been merged for a while:
>> https://github.com/pgjdbc/pgjdbc/pull/1014
> 
> "Merged to upstream" is a whole lot different from "readily available in
> the field".  What's the actual status in common Linux distros, for
> example?

Did some limited research just to get a sense.

Well, if it's RHEL7, it's PostgreSQL 9.2 so, unless they're using our
RPM, that definitely does not have it :)

(While researching this, I noticed on the main RHEL8 beta page[1] that
PostgreSQL is actually featured, which is kind of neat. I could not
quickly find which version of the JDBC driver it is shipping with, though)

On Ubuntu, 18.04 LTS ships PG10, but the version of JDBC does not
include SCRAM support. 18.10 ships JDBC w/SCRAM support.

On Debian, stretch is on 9.4. buster has 11 packaged, and JDBC is
shipping with SCRAM support.

> The scenario that worries me here is somebody using a bleeding-edge PGDG
> server package in an environment where the rest of the Postgres ecosystem
> is much less bleeding-edge.  The last time that situation would have
> caused them can't-connect problems was, um, probably when we introduced
> MD5 password encryption.  So they won't be expecting to get blindsided by
> something like this.
> 
> I'm particularly concerned about the idea that they won't see a problem
> during initial testing, only to have things fall over after they enter
> production and do a "routine" password change.

Yeah, I think all of the above is fair. It's been awhile since md5 was
added :)

So I think based on that and a quick look at the different distros
indicate that changing the default to PG12 has too much risk of
breakage, but PG13 would be a fair target as long as we start making
noise sooner (now?).

Jonathan

[1] https://developers.redhat.com/rhel8/



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Andres Freund  writes:
> On 2019-04-08 13:34:12 -0400, Alvaro Herrera wrote:
>> I'm not sure I understand all this talk about deferring changing the
>> default to pg13.  AFAICS only a few fringe drivers are missing support;
>> not changing in pg12 means we're going to leave *all* users, even those
>> whose clients have support, without the additional security for 18 more
>> months.

> Imo making such changes after feature freeze is somewhat poor
> form.

Yeah.

> If jdbc didn't support scram, it'd be an absolutely clear no-go imo. A
> pretty large fraction of users use jdbc to access postgres. But it seems
> to me that support has been merged for a while:
> https://github.com/pgjdbc/pgjdbc/pull/1014

"Merged to upstream" is a whole lot different from "readily available in
the field".  What's the actual status in common Linux distros, for
example?

The scenario that worries me here is somebody using a bleeding-edge PGDG
server package in an environment where the rest of the Postgres ecosystem
is much less bleeding-edge.  The last time that situation would have
caused them can't-connect problems was, um, probably when we introduced
MD5 password encryption.  So they won't be expecting to get blindsided by
something like this.

I'm particularly concerned about the idea that they won't see a problem
during initial testing, only to have things fall over after they enter
production and do a "routine" password change.

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Andres Freund]

Hi,

On 2019-04-08 13:34:12 -0400, Alvaro Herrera wrote:
> I'm not sure I understand all this talk about deferring changing the
> default to pg13.  AFAICS only a few fringe drivers are missing support;
> not changing in pg12 means we're going to leave *all* users, even those
> whose clients have support, without the additional security for 18 more
> months.

Imo making such changes after feature freeze is somewhat poor
form. These arguments would have made a ton more sense at the
*beginning* of the v12 development cycle, because that'd have given all
these driver authors a lot more heads up.


> IIUC the vast majority of clients already support SCRAM auth.  So the
> vast majority of PG users can take advantage of the additional security.
> I think the only massive-adoption exception is JDBC, and apparently they
> already have working patches for SCRAM.

If jdbc didn't support scram, it'd be an absolutely clear no-go imo. A
pretty large fraction of users use jdbc to access postgres. But it seems
to me that support has been merged for a while:
https://github.com/pgjdbc/pgjdbc/pull/1014

Greetings,

Andres Freund

Re: change password_encryption default to scram-sha-256?

[Alvaro Herrera]

I'm not sure I understand all this talk about deferring changing the
default to pg13.  AFAICS only a few fringe drivers are missing support;
not changing in pg12 means we're going to leave *all* users, even those
whose clients have support, without the additional security for 18 more
months.

IIUC the vast majority of clients already support SCRAM auth.  So the
vast majority of PG users can take advantage of the additional security.
I think the only massive-adoption exception is JDBC, and apparently they
already have working patches for SCRAM.

Like many other configuration parameters, setting the default for this
one is a trade-off: give the most benefit to most users, causing the
least possible pain to users for whom the default is not good.  Users
that require opening connections from clients that have not updated
should just set password_encryption to md5.  It's not like things will
suddenly blow up in their faces.

IMO we don't need to wait until every single client in existence has
updated to support SCRAM.  After all, they've already had two years.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 10:08 AM, Tom Lane wrote:
> "Jonathan S. Katz"  writes:
>> On 4/8/19 8:49 AM, Magnus Hagander wrote:
>>> I think the real question is, is it OK to give them basically 5months
>>> warning, by right now saying if you don't have a release out in 6
>>> months, things will break.
> 
>> Given the supported libraries all have open pull requests or issues, it
>> should be fairly easy to inquire if they would be able to support it for
>> PG12 vs PG13. If this sounds like a reasonable plan, I'm happy to reach
>> out and see.
> 
> I think that the right course here is to notify these developers that
> we will change the default in PG13, and it'd be good if they put out
> stable releases with SCRAM support well before that.

+1; I'm happy to reach out with that messaging, referencing this thread.

> This discussion
> seems to be talking as though it's okay if we allow zero daylight
> between availability of fixed drivers and release of a PG version that
> defaults to using SCRAM.  That'd be totally unfair to packagers and
> users.  There needs to be a pretty fair-size window for those fixed
> drivers to propagate into the wild.  A year is not too much; IMO it's
> barely enough.

I agree in principle, esp. related to testing + packaging (and I think
packaging would be my biggest concern), but IMV this primarily would
affect new applications, which is why I thought to provide reasoning for
a more aggressive timeline. You typically keep you pg.conf settings
consistent between version upgrades (with exceptions, e.g. based on
upgrade method). That could also inadvertently block people from
upgrading, too, but the bigger risk would be new application development
on PG12.

Looking at the uncovered user base too, it's not the largest portion of
our users, though accessing PostgreSQL via Go is certainly increasingly
rapidly so I'm very sympathetic that we don't break their accessibility
(and I've personally used asyncpg and would not want my apps to break
either :).

Anyway, I primarily wanted to see if an aggressive timeline to update
our default password approach would make sense esp. given we've had this
feature around for some time, and, again, it is far superior to the
other password based methods. I'm fine with being cautious, just wanted
to ensure we're not being too cautious about getting our users to
utilize a feature with better security.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

"Jonathan S. Katz"  writes:
> On 4/8/19 8:49 AM, Magnus Hagander wrote:
>> I think the real question is, is it OK to give them basically 5months
>> warning, by right now saying if you don't have a release out in 6
>> months, things will break.

> Given the supported libraries all have open pull requests or issues, it
> should be fairly easy to inquire if they would be able to support it for
> PG12 vs PG13. If this sounds like a reasonable plan, I'm happy to reach
> out and see.

I think that the right course here is to notify these developers that
we will change the default in PG13, and it'd be good if they put out
stable releases with SCRAM support well before that.  This discussion
seems to be talking as though it's okay if we allow zero daylight
between availability of fixed drivers and release of a PG version that
defaults to using SCRAM.  That'd be totally unfair to packagers and
users.  There needs to be a pretty fair-size window for those fixed
drivers to propagate into the wild.  A year is not too much; IMO it's
barely enough.

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 8:49 AM, Magnus Hagander wrote:
> On Mon, Apr 8, 2019 at 2:38 PM Jonathan S. Katz  > wrote:

> Counter-argument: SCRAM has been available for 2 years since 10 feature
> freeze, there has been a lot of time already given to implement support
> for it. Given is at least 5 months until PG12 comes out, and each of the
> popular drivers already has patches in place, we could default it for 12
> and let them know this is a reality.
> 
> 
> You can't really count feature freeze, you have to count release I
> think. And basically we're saying they had 2 years. Which in itself
> would've been perfectly reasonable, *if we told them*. But we didn't.
> 
> I think the real question is, is it OK to give them basically 5months
> warning, by right now saying if you don't have a release out in 6
> months, things will break.

Yeah, that's a good and fair question.

> That said, that would be an aggressive approach, so I would not object
> to changing the default for PG13 and giving 17 months vs. 5, but we do
> let md5 persist that much longer.
> 
> 
> I think we definitely should not make it *later* than 13.

+1

> Maybe we should simply reach out to those driver developers, it's not
> that many of them after all, and *ask* if they would think it's a
> problem if we change it in 12.

It wouldn't hurt. I went through the list again[1] to see which ones
don't have it and updated:

- pgsql (Erlang) - this webpage doesn't load, maybe we should remove? It
may have been replaced by this one[2]?

- erlang-pgsql-driver (Erlang) - on the page it says it's unsupported,
so we should definitely remove it from the wiki and from consideration

- node-postgres (JavaScript) - they added SCRAM in 7.9.0 so I've updated
the wiki

- pq (Go) - No; as mentioned there are 3 separate patches in consideration

- crystal-pg (Ruby) No; open issue, not patch

- asyncpg (Python) No; open issue, suggestion on how to implement but no
patch

Let me also add:

- pgx (Go)[3] - another popular Go driver, there is an open patch for
SCRAM support

So IMV it's pq, crystal-pg, asyncpg, & pgx we have to reach out to,
pending resolution on Erlang libs.

Given the supported libraries all have open pull requests or issues, it
should be fairly easy to inquire if they would be able to support it for
PG12 vs PG13. If this sounds like a reasonable plan, I'm happy to reach
out and see.

Jonathan

[1] https://wiki.postgresql.org/wiki/List_of_drivers
[2] https://github.com/semiocast/pgsql
[3] https://github.com/jackc/pgx



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Magnus Hagander]

On Mon, Apr 8, 2019 at 2:38 PM Jonathan S. Katz 
wrote:

> On 4/8/19 8:19 AM, Peter Eisentraut wrote:
> > On 2019-04-08 13:52, Andrew Dunstan wrote:
> >> Yeah, if we're not going to do it now we should announce that we will
> >> do it in the next release.
> >
> > Targeting PG13 seems reasonable.
>

Yeah, that would be fairly consistent with how we usually do htings

Counter-argument: SCRAM has been available for 2 years since 10 feature
> freeze, there has been a lot of time already given to implement support
> for it. Given is at least 5 months until PG12 comes out, and each of the
> popular drivers already has patches in place, we could default it for 12
> and let them know this is a reality.
>

You can't really count feature freeze, you have to count release I think.
And basically we're saying they had 2 years. Which in itself would've been
perfectly reasonable, *if we told them*. But we didn't.

I think the real question is, is it OK to give them basically 5months
warning, by right now saying if you don't have a release out in 6 months,
things will break.



Given it's superior to the existing methods, it'd be better to encourage
> the drivers to get this in place sooner. Given what I know about md5,
> I've tried to avoid building apps with drivers that don't support SCRAM.
>
> That said, that would be an aggressive approach, so I would not object
> to changing the default for PG13 and giving 17 months vs. 5, but we do
> let md5 persist that much longer.
>

I think we definitely should not make it *later* than 13.

Maybe we should simply reach out to those driver developers, it's not that
many of them after all, and *ask* if they would think it's a problem if we
change it in 12.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: change password_encryption default to scram-sha-256?

[Jonathan S. Katz]

On 4/8/19 8:19 AM, Peter Eisentraut wrote:
> On 2019-04-08 13:52, Andrew Dunstan wrote:
>> Yeah, if we're not going to do it now we should announce that we will
>> do it in the next release.
> 
> Targeting PG13 seems reasonable.

Counter-argument: SCRAM has been available for 2 years since 10 feature
freeze, there has been a lot of time already given to implement support
for it. Given is at least 5 months until PG12 comes out, and each of the
popular drivers already has patches in place, we could default it for 12
and let them know this is a reality.

Given it's superior to the existing methods, it'd be better to encourage
the drivers to get this in place sooner. Given what I know about md5,
I've tried to avoid building apps with drivers that don't support SCRAM.

That said, that would be an aggressive approach, so I would not object
to changing the default for PG13 and giving 17 months vs. 5, but we do
let md5 persist that much longer.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: change password_encryption default to scram-sha-256?

[Peter Eisentraut]

On 2019-04-08 13:52, Andrew Dunstan wrote:
> Yeah, if we're not going to do it now we should announce that we will
> do it in the next release.

Targeting PG13 seems reasonable.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Andrew Dunstan]

On Mon, Apr 8, 2019 at 2:38 AM Michael Paquier  wrote:
>
> On Mon, Apr 08, 2019 at 09:08:05AM +0300, Heikki Linnakangas wrote:
> > I wouldn't hold my breath. That's the third PR to add SCRAM support already,
> > see also https://github.com/lib/pq/pull/788 and
> > https://github.com/lib/pq/pull/608. The project seems to lack the committer
> > manpower or round tuits to review and push these.
>
> I am wondering on the contrary if switching the default on Postgres
> side would make things move faster on their side though.


Yeah, if we're not going to do it now we should announce that we will
do it in the next release.

cheers

andrew


-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: change password_encryption default to scram-sha-256?

[Sergei Kornilov]

Hi

> I am wondering on the contrary if switching the default on Postgres
> side would make things move faster on their side though.

I think we need give more time before change default. I suggest not to repeat 
the quick change of default to a new value as it was in the MySQL 8.0 last year 
[1].

*1 
https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

regards, Sergei

Re: change password_encryption default to scram-sha-256?

[Michael Paquier]

On Mon, Apr 08, 2019 at 09:08:05AM +0300, Heikki Linnakangas wrote:
> I wouldn't hold my breath. That's the third PR to add SCRAM support already,
> see also https://github.com/lib/pq/pull/788 and
> https://github.com/lib/pq/pull/608. The project seems to lack the committer
> manpower or round tuits to review and push these.

I am wondering on the contrary if switching the default on Postgres
side would make things move faster on their side though.
--
Michael


signature.asc
Description: PGP signature

Re: change password_encryption default to scram-sha-256?

[Heikki Linnakangas]

On 08/04/2019 08:42, Andres Freund wrote:

Seems go/pq might get it soon-ish: https://github.com/lib/pq/pull/833


I wouldn't hold my breath. That's the third PR to add SCRAM support 
already, see also https://github.com/lib/pq/pull/788 and 
https://github.com/lib/pq/pull/608. The project seems to lack the 
committer manpower or round tuits to review and push these.


- Heikki

Re: change password_encryption default to scram-sha-256?

[Andres Freund]

Hi,

On 2019-04-08 01:34:42 -0400, Tom Lane wrote:
> Michael Paquier  writes:
> > From what I can see, the major drivers not using directly libpq
> > support our SASL protocol: JDBC and npgsql.  However I can count three
> > of them which still don't support it: Crystal, pq (Go) and asyncpg.
> > pq and asyncpg are very popular on github, with at least 3000 stars
> > each, which is a lot I think.  I have also double-checked their source
> > code and I am seeing no trace of SASL or SCRAM, so it seems to me that
> > we may want to wait more before switching the default.
> 
> Perhaps we could reach out to the authors of those libraries,
> and encourage them to provide support in the next year or so?


Seems go/pq might get it soon-ish: https://github.com/lib/pq/pull/833

There doesn't appear to be much movement on the crystal front (
https://github.com/will/crystal-pg/issues/154 ), but I don't think it's
popular enough to really worry.  There's an issue for asyncpg
https://github.com/MagicStack/asyncpg/issues/314 - but not too much
movement either.

Greetings,

Andres Freund

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Michael Paquier  writes:
> From what I can see, the major drivers not using directly libpq
> support our SASL protocol: JDBC and npgsql.  However I can count three
> of them which still don't support it: Crystal, pq (Go) and asyncpg.
> pq and asyncpg are very popular on github, with at least 3000 stars
> each, which is a lot I think.  I have also double-checked their source
> code and I am seeing no trace of SASL or SCRAM, so it seems to me that
> we may want to wait more before switching the default.

Perhaps we could reach out to the authors of those libraries,
and encourage them to provide support in the next year or so?

I don't doubt that switching to scram-sha-256 is a good idea in
the long run.  The idea here was to give driver authors a reasonable
amount of time to update.  I don't really think that one year
counts as a "reasonable amount of time" given how slowly this
project moves overall ... but we don't want to wait forever ...

regards, tom lane

Re: change password_encryption default to scram-sha-256?

[Michael Paquier]

On Sun, Apr 07, 2019 at 08:23:06PM +0200, David Fetter wrote:
> Great idea!  Does it make sense to test all, or at least some
> significant fraction of the connectors listed in
> https://wiki.postgresql.org/wiki/Client_Libraries by default?

This is a more interesting list:
https://wiki.postgresql.org/wiki/List_of_drivers

From what I can see, the major drivers not using directly libpq
support our SASL protocol: JDBC and npgsql.  However I can count three
of them which still don't support it: Crystal, pq (Go) and asyncpg.
pq and asyncpg are very popular on github, with at least 3000 stars
each, which is a lot I think.  I have also double-checked their source
code and I am seeing no trace of SASL or SCRAM, so it seems to me that
we may want to wait more before switching the default.
--
Michael


signature.asc
Description: PGP signature

Re: change password_encryption default to scram-sha-256?

[David Fetter]

On Sun, Apr 07, 2019 at 12:59:05PM -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > Should we change the default of the password_encryption setting to
> > 'scram-sha-256' in PG12?
> 
> I thought we were going to wait a bit longer --- that just got added
> last year, no?  What do we know about the state of support in client
> libraries?

Great idea!  Does it make sense to test all, or at least some
significant fraction of the connectors listed in
https://wiki.postgresql.org/wiki/Client_Libraries by default?

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: change password_encryption default to scram-sha-256?

[Tom Lane]

Peter Eisentraut  writes:
> Should we change the default of the password_encryption setting to
> 'scram-sha-256' in PG12?

I thought we were going to wait a bit longer --- that just got added
last year, no?  What do we know about the state of support in client
libraries?

regards, tom lane

change password_encryption default to scram-sha-256?

[Peter Eisentraut]

Should we change the default of the password_encryption setting to
'scram-sha-256' in PG12?

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services