change password_encryption default to scram-sha-256?

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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

On Sun, Apr 07, 2019 at 12:59:05PM -0400, Tom Lane wrote:

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

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

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

Michael Paquier <michael@paquier.xyz> 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

Hi,

On 2019-04-08 01:34:42 -0400, Tom Lane wrote:

Michael Paquier <michael@paquier.xyz> 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

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

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

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

On Mon, Apr 8, 2019 at 2:38 AM Michael Paquier <michael@paquier.xyz> 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 Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

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

On Mon, Apr 8, 2019 at 2:38 PM Jonathan S. Katz <jkatz@postgresql.org>
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/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 4/8/19 8:49 AM, Magnus Hagander wrote:

On Mon, Apr 8, 2019 at 2:38 PM Jonathan S. Katz <jkatz@postgresql.org
<mailto:jkatz@postgresql.org>> 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]https://wiki.postgresql.org/wiki/List_of_drivers 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]https://github.com/semiocast/pgsql?

- 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]https://github.com/jackc/pgx - 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

"Jonathan S. Katz" <jkatz@postgresql.org> 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

On 4/8/19 10:08 AM, Tom Lane wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> 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

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 Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Andres Freund <andres@anarazel.de> 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

On 4/8/19 2:28 PM, Tom Lane wrote:

Andres Freund <andres@anarazel.de> 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]https://developers.redhat.com/rhel8/ 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/