Subject: Can we stop defaulting to 'ident'?

From: Craig Ringer <[email-masked]>
Date: Thu, 19 Dec 2019 12:58:59 +0800

'ident' doesn't work by default on any RPM disto.

It's not clear why the initdb wrapper for the rpm packages defaults to
generating 'host' entries with 'ident' auth, but I think it's pretty
unhelpful. At least if we used 'md5' the user could set passwords and have
them actually work.

    initdbcmd="$PGENGINE/initdb --pgdata='$PGDATA' --auth='ident'"
    initdbcmd+=" $PGSETUP_INITDB_OPTIONS"

I know you can override it easily enough, but most people won't know to.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise

---

From: "James Cassell" <[email-masked]>
Date: Thu, 19 Dec 2019 10:20:27 -0500

On Wed, Dec 18, 2019, at 11:58 PM, Craig Ringer wrote:
> unhelpful. At least if we used 'md5' the user could set passwords and 
> have them actually work.
> 

For what it's worth, I am quite happy with the current default of ident.

To make it work, you can install the `authd` package, then enable the `auth.socket` systemd service.  I've made it listen only on localhost, and disabled the encryption part of authd because I didn't want to figure out how to give postgres the appropriate key.

All-in-all, it makes for a seamless auth of local users/services to their own postgres databases running on localhost.  Last I checked, ident auth was only specified for the localhost addreses in pg_hba.conf.  (RHEL 8 has marked the "authd" package as deprecated without any explanation, though... it still works fine and is still present.)

V/r,
James Cassell

---

From: Stephen Frost <[email-masked]>
Date: Thu, 19 Dec 2019 11:57:20 -0500

Greetings,

> All-in-all, it makes for a seamless auth of local users/services to their own postgres databases running on localhost.  Last I checked, ident auth was only specified for the localhost addreses in p…

Why in the world would you want that over just using peer..?

'host' with 'ident' should have been outright removed from PG, imv...  I
actually thought it was but maybe it's only been deprecated.

Thanks,

Stephen

---

From: "James Cassell" <[email-masked]>
Date: Thu, 19 Dec 2019 12:22:30 -0500

On Thu, Dec 19, 2019, at 11:57 AM, Stephen Frost wrote:
> * James Cassell wrote:

Peer does not work with TCP connections, and I haven't figured how to get,e.g., third-party Java applications working without TCP.

> actually thought it was but maybe it's only been deprecated.

I guess I haven't paid close attention to deprecation notices. Was there a notice for it?

V/r,
James Cassell

---

From: Christoph Berg <[email-masked]>
Date: Thu, 19 Dec 2019 18:31:34 +0100

Re: James Cassell 2019-12-19 <[email-masked]>
> Peer does not work with TCP connections, and I haven't figured how to get,e.g., third-party Java applications working without TCP.

There's some GitHub repos that mention the use case of using UNIX
sockets with pgjdbc:

https://github.com/fiken/junixsocket
https://github.com/kohlschutter/junixsocket

Doesn't seem mainstream, though. Maybe someone should push for it...

Christoph

---

From: Stephen Frost <[email-masked]>
Date: Thu, 19 Dec 2019 12:32:28 -0500

Greetings,

> Peer does not work with TCP connections, and I haven't figured how to get,e.g., third-party Java applications working without TCP.

The entire point of peer was to segregate the very insecure 'ident' from
the actually quite secure 'peer' auth, so, no, it's not going to work
over TCP connections- that's more-or-less the point.

Regarding a JDBC connection, you can pass in a "socketFactory", as I
understand it (though I'm no JDBC expert, I'd suggest you address issues
you have with that to the JDBC list):

https://jdbc.postgresql.org/documentation/head/connect.html

Thanks,

Stephen

---

From: "James Cassell" <[email-masked]>
Date: Thu, 19 Dec 2019 16:07:47 -0500

Hi,

> over TCP connections- that's more-or-less the point.

I fail to see how ident over TCP is insecure when used on the localhost address. Can you explain? Otherwise, is there a way to make peer authentication work with TCP connections?

V/r,
James Cassell

---

From: Craig Ringer <[email-masked]>
Date: Fri, 20 Dec 2019 11:00:43 +0800

On Fri, 20 Dec 2019 at 05:08, James Cassell <[email-masked]>
wrote:

>
> I fail to see how ident over TCP is insecure when used on the localhost
> address. Can you explain? Otherwise, is there a way to make peer
> authentication work with TCP connections?
>

ident is secure (or as secure as 'peer' for unix sockets) over TCP/IP
loopback connections. But pretty much only then or on networks that you
totally control all hosts on and all access to.

To spoof ident you must be able to open a listening socket on a privileged
port on the loopback host. So you need superuser privileges or the
CAP_NET_BIND_SERVICE capability which must be explicitly granted.

I am not arguing for ident to be removed. I'm arguing for it to stop being
the default for rpm package initdb, since it's *totally useless and
nonfunctional without additional services that the rpms do not depend on*.
It actively gets in the way of users since they cannot then simply

    CREATE USER foo WITH PASSWORD 'bar';

and connect. They have to go in and unf@#$ our generated pg_hba.conf too.

So if you like ident, fine. That's not a problem. You can

    /usr/pgsql-12/bin/postgresql-12-setup -A ident

and nothing else changes for you. But it's a really obsolete and unhelpful
default, and it's also one that adds yet another difference vs the Debian
packages to add to user confusion.

    [craig@ayaki] $ psql -h localhost
    psql: error: could not connect to server: FATAL:  Ident authentication
failed for user "craig"

"WOT?"

Now, we're hardly going to depend on the ident service in the packages.
It's a security policy violation in many places to even run it. So we
should change the default - probably to scram-sha-256 on pg11 and pg12, and
md5 on older releases.

The only BC implication I can see is that someone's scripts might, rather
than invoking

    /usr/pgsql-12/bin/postgresql-12-setup -A md5

be doing

    /usr/pgsql-12/bin/postgresql-12-setup
    sed -i 's/ident/md5/g' /var/lib/pgsql/12/data/pg_hba.conf

or the like. But I don't think that's a big concern: it's an easy fix, only
affects new initdb's, and is sufficient to cover in the 'news' section +
changelog.

---

From: Craig Ringer <[email-masked]>
Date: Fri, 20 Dec 2019 11:06:26 +0800

On Fri, 20 Dec 2019 at 01:32, Stephen Frost <[email-masked]> wrote:

> > Peer does not work with TCP connections, and I haven't figured how to
> get,e.g., third-party Java applications working without TCP.
>

Right. PgJDBC doesn't actually have to support it directly, since you can
pass your own socketFactory, such as one provided by
https://github.com/kohlschutter/junixsocket or
https://github.com/jnr/jnr-unixsocket .

As the Java Language specification does not provide for UNIX socket support
and no widely used JVM bundles AF_UNIX socket support there's no way for
PgJDBC to directly support unix sockets. We could add support for it in
jdbc:postgresql:// URLs, but we'd have to do a runtime search of the
classpath to find a suitable SocketFactory using a list of known unix
socket library implementations ... so why bother? If the user has to
install a 3rd party library to do it anyway, they can specify a JDBC URL
argument too.

So PgJDBC already has everything it needs there IMO, except perhaps a hint
in the documentation. Patches welcome :)

---

From: Devrim Gündüz <[email-masked]>
Date: Fri, 20 Dec 2019 10:45:37 +0300

Hi,

On Thu, 2019-12-19 at 12:58 +0800, Craig Ringer wrote:

> generating 'host' entries with 'ident' auth, 

Historical reasons, like at least 15 years or more.

> but I think it's pretty unhelpful. At least if we used 'md5' the user could
> set passwords and have them actually work.

IMHO the only alternative could be "trust", because I am not holding my breath
for the majority of our users to be able to setup a password that easily
(yeah). I'm also not inclined to setup a default password for RPM installations
(and also RPMs must not do any interactive work, like asking for a password)

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

---

From: Christoph Berg <[email-masked]>
Date: Fri, 20 Dec 2019 11:32:40 +0100

Re: Devrim Gündüz 2019-12-20 <[email-masked]>
> (and also RPMs must not do any interactive work, like asking for a password)

Fwiw, the Debian packages have been using md5 forever, and do not set
a password either. People seem to be able to set a password
themselves. I've never heard any complaint about it. (Except for some
poking that scram might be better.)

Christoph

---

From: Stephen Frost <[email-masked]>
Date: Fri, 20 Dec 2019 10:06:44 -0500

Greetings,

> poking that scram might be better.)

SCRAM is *definitely* better and I strongly support us moving to it,
provided it doesn't break anything existing (which it generally
shouldn't...  but maybe there's some weird edge cases, or possibly older
clients, but still, at some point, we need to move this default to be
SCRAM).

That said- we should be using peer for local unix sockets and SCRAM for
host-based password (local or not...), and ident needs to just die.

Thanks,

Stephen

---

From: Christoph Berg <[email-masked]>
Date: Fri, 20 Dec 2019 16:15:35 +0100

Re: Stephen Frost 2019-12-20 <[email-masked]>
> clients, but still, at some point, we need to move this default to be

TBH I haven't really read the manual section about md5-scram
compatibility yet, but from memory, there's a lot of footnotes that
need to be taken into account before the switch can be flipped, if
upgrades from old servers are to be supported. The process sounds
scary and painful.

Christoph

---

From: Stephen Frost <[email-masked]>
Date: Fri, 20 Dec 2019 10:21:01 -0500

Greetings,

> upgrades from old servers are to be supported. The process sounds

This depends on which 'switch' we are talking about flipping and how
things like passwords are managed today and such...

I encourage reading through the documentation, of course, but my
recollection off-hand is that 'scram' in pg_hba.conf will happily work
with stored md5 passwords too as a fall-back.  Changing how passwords
are stored is actually not related to pg_hba.conf but rather to the
password encryption GUC, which we would want to change because otherwise
you don't actually get any real improvement in security.  The default
for that continues to be 'md5' from PG though and Debian doesn't
currently change it.  I do worry there might be an issue with older
pre-scram-supporting clients/libraries, haven't looked at that recently.

Thanks,

Stephen

---

From: Craig Ringer <[email-masked]>
Date: Mon, 23 Dec 2019 14:04:25 +0800

> Historical reasons, like at least 15 years or more.

Time to revisit it then.

The current default is already broken. It is more broken than, and less
useful than, defaulting to 'md5' for 'host' since at least then users could
make it work by setting a password.

ident requires entirely new and different daemons to be installed,
configured and enabled.

> (and also RPMs must not do any interactive work, like asking for a

The deb use md5 for 'host' and 'peer' for 'local'. While I think they do
support interactive password setting it's extremely common to run debconf
noninteractively, then set an initial password using psql with the peer
auth conn over a unix socket.

That's the approach I suggest for the rpms too. A stanza to the setup shell
script can even be added to give a hint for next steps:

    echo PostgreSQL instance created at /var/lib/pgsql/12/data and set to
listen on port $NEWPGPORT.
    echo
    echo Start it with systemctl start postgresql-12 .

    if [ $local_authmode == 'peer' ]; then
        echo Connect with 'sudo -u postgres psql -p $NEWPGPORT' to create
users, set passwords and create databases.
    fi

or something like that.

---

From: Craig Ringer <[email-masked]>
Date: Mon, 23 Dec 2019 14:06:18 +0800

> upgrades from old servers are to be supported. The process sounds
Yeah. Everyone's already changing the setting after install or overriding
it at setup time anyway though, because 'ident' is so nonsensical hardly
anyone will be deploying with it.

We're not talking about changing the default from 'md5' to 'md5-scram'
which would be rather riskier.

And to be clear, I'm only proposing changing 'host' connections. 'local'
connections should remain 'peer' as is the case now.

---

From: Christoph Berg <[email-masked]>
Date: Mon, 23 Dec 2019 13:45:51 +0100

Re: Craig Ringer 2019-12-23 <CAMsr+YFBeZ+jUs0q1h9LO=OcYDstEN7b=[email-masked]>
> support interactive password setting

As already said upthread, we don't support that. People have to do it
manually, and seem to be fine with that.

> it's extremely common to run debconf

Christoph

---

From: Devrim Gündüz <[email-masked]>
Date: Tue, 19 May 2020 21:22:06 +0100

Hi,

> 'ident' doesn't work by default on any RPM disto.

So, I am thinking of switching to scram-sha-256 by default. We need to
do this sooner or later anyway. We can blame "13", if someone
complains.

Any objections? I'm pulling the trigger for 13beta1. It means I'll also
set the default in postgresql.conf to scram auth as well.

Regards,

---

From: Stephen Frost <[email-masked]>
Date: Tue, 19 May 2020 17:27:10 -0400

Greetings,

> set the default in postgresql.conf to scram auth as well.

+1 to scram-sha-256 for the default for host-based/TCP connections.  For
local, we should be using peer, just like Debian's default has been for
ages.

Thanks,

Stephen

---

From: Devrim Gündüz <[email-masked]>
Date: Tue, 19 May 2020 23:26:07 +0100

Hi,

On Tue, 2020-05-19 at 17:27 -0400, Stephen Frost wrote:

> +1 to scram-sha-256 for the default for host-based/TCP connections. 
> For local, we should be using peer, just like Debian's default has
> been for ages.

YEah, this is what I committed just now. Still, would love to hear from
others if there are any objections.

I used --pwfile=/dev/zero during initdb.

Regards,

---

From: "James Cassell" <[email-masked]>
Date: Tue, 19 May 2020 19:29:09 -0400

> I used --pwfile=/dev/zero during initdb.

I'm still using ident authentication quite successfully on Fedora and RHEL. (authd package)

Here's my trivial config, just tested on Fedora 32 (tested using the stock, not PGDG packages, but same concept applies): https://github.com/jamescassell/ansible-role-postgresql/blob/master/tasks/main.yml

V/r,
James Cassell

---

From: Craig Ringer <[email-masked]>
Date: Wed, 20 May 2020 08:07:45 +0800

> I'm still using ident authentication quite successfully on Fedora and

You still can.

This just changes the default. Right now our default doesn't actually work
without extra packages the user must discover and configure, which is just
silly.

So +lots from me on this change.

---

From: Strahinja Kustudić <[email-masked]>
Date: Wed, 20 May 2020 08:53:06 +0200

> I used --pwfile=/dev/zero during initdb.

I'm all for this change, especially changing to peer for local, because
iden't never made any sense for us.

*Strahinja Kustudic*
Senior System Engineer | Nordeus

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 20 May 2020 09:20:33 +0200

On Wed, 2020-05-20 at 01:08 +0300, David Ghedini wrote:

+1

Yours,
Laurenz Albe

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 20 May 2020 15:09:24 +0200

> others if there are any objections.

The upstream default is still to use md5 passwords by default, and some 
deliberation has gone into that to keep it that way.  So it would make 
sense to have the RPMs also do that.  The Debian packages also still use 
md5.  Some consistency across the board would be good.  Otherwise it 
will be very confusing for users if everyone just goes into their own 
direction.

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

---

From: Christoph Berg <[email-masked]>
Date: Wed, 20 May 2020 15:40:35 +0200

Re: Peter Eisentraut
> deliberation has gone into that to keep it that way.  So it would make sense
> to have the RPMs also do that.  The Debian packages also still use md5.
> Some consistency across the board would be good.  Otherwise it will be very
> confusing for users if everyone just goes into their own direction.

The upstream initdb default is still 'trust', but everyone agrees that
it's good that distributions are changing that so something more
secure, so we are already disconnected from the "true" default here.

We can move the Debian packages to scram as well, if that helps.
I just haven't done that yet because I haven't read up on how a
migration plan should look.

Christoph

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 20 May 2020 15:13:43 +0100

Hi,

On Wed, 2020-05-20 at 15:09 +0200, Peter Eisentraut wrote:
> The upstream default is still to use md5 passwords by default, and
> some deliberation has gone into that to keep it that way.  So it
> would make sense to have the RPMs also do that. 

As Christoph pointed out, upstream default is trust. Still, this is
just a "default". People can change it to whatever they want. That may
also "force" people to switch to scram auth as well.

Regards,

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 20 May 2020 15:18:08 +0100

Hi,

> We can move the Debian packages to scram as well, if that helps.

That would be good actually.

> migration plan should look.

Does this help?

https://info.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram

Cheers,

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 10:45:04 -0400

Greetings,

> migration plan should look.

Yes, I think that would make a lot of sense.  I'd be happy to chat about
what that would look like if it'd help.

I'd also vote for moving the upstream initdb default to scram too, of
course.  It'd certainly be nice to get all of these things in line
together and there's really no good reason to be using md5 these days
for new installs (or, really, even for most old installs..).

Thanks,

Stephen

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 20 May 2020 16:49:35 +0200

> secure, so we are already disconnected from the "true" default here.

Sorry, I should have been more clear.  The upstream default of the GUC 
parameter "password_encryption" is md5.

It is understood that the default client authentication method can be 
changed downstream.

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 10:57:52 -0400

Greetings,

> parameter "password_encryption" is md5.

Which, really, is pretty broken when we're going to be having our
packagers setting up pg_hba.conf to use scram- at the *very* least it's
ridiculously misleading because we're going to have SCRAM in pg_hba.conf
but passwords actually stored as md5 and therefore we won't be getting
the benefits from SCRAM auth (though it should still work, of course,
since the SCRAM mode will fall back to working with an md5 password).

I don't recall a v13 discussion around if we should continue to have md5
as the default for *new* installations for password_encryption.

> It is understood that the default client authentication method can be

While this discussion has been about pg_hba.conf, we really should clean
it all up and encourage users, by setting sane defaults, to use SCRAM.

That necessarily includes setting SCRAM as the password_encryption
method.

Thanks,

Stephen

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 20 May 2020 17:03:32 +0200

> since the SCRAM mode will fall back to working with an md5 password).

Devrim's commit to pgrpms did include a change to the default setting of 
password_encryption, so it appears to be correct as far as it goes.

But this leads to other questions, like, what should pg_upgrade do?

These discussions should be had, but perhaps not on the RPM packaging 
list the night before the release.

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 11:33:37 -0400

Greetings,

> password_encryption, so it appears to be correct as far as it goes.

That's good- we'd want that to be done on the Debian packages too then,
though ideally we'd get the source default to be changed.

> But this leads to other questions, like, what should pg_upgrade do?

Same as it always has- make the user deal with anything they need to
regarding postgresql.conf?  Why would anything change with pg_upgrade?
Even if the user doesn't change anything, everything would still work-
existing passwords with md5 would work with scram set in
password_encryption and scram in pg_hba.conf, and they'd be nicely
automatically upgraded when they change their password if they use a
password-change mechanism that works with SCRAM (and if they use one
that sets md5 passwords, that'd still work too).  Yes, there's some
small potential risk that they change their password using psql's
\password and they have some client library that doesn't work with scram
yet using the same account and they'll have failed logins after running
\password, but that's certainly possible from just typo'ing the password
or something else and hopefully they'll be able to remedy that pretty
quickly.

Obviously the same goes for pg_hba.conf- pg_upgrade doesn't do anything
for that either, so users have to adjust it or copy over their old one
to the new cluster.  Basically, users are likely pretty used to dealing
with having to fix up their configs as part of putting together a new
cluster before or after pg_upgrade on distributions that don't have
tooling around this.

And that's part of it- all of this depends entirely on what distribution
is being used and what tools are being used to do the upgrade anyway.
Debian has pg_upgradecluster that already copies over and updates things
in the postgresql.conf, so on Debian, I expect that we'll see that part
of such an upgrade might check pg_hba.conf for 'md5' entries and if it
finds any, setting password_encryption to 'md5' for those upgraded
clusters if it's not already set to something else in postgresql.conf,
to ensure there isn't any issue on those upgrades.  Maybe it could be
more intelligent and notify the user that they're getting upgraded to
SCRAM and adjust pg_hba.conf to change md5 -> scram and leave
postgresql.conf alone, but ultimately that's probably going to be up to
what Christoph is comfortable with.

Unfortunately, I don't think any of that has made it over to RHEL yet
though.

Maybe we could talk about changing pg_upgrade to actually do something
about postgresql.conf, so that we can have commonality across
distributions, but that's certainly a different discussion.

> These discussions should be had, but perhaps not on the RPM packaging list
> the night before the release.

For my 2c, I don't think it'd be the worst thing to change a default
setting post beta1, so while I agree we should discuss this over on
-hackers, I don't think it's particularly pressing.  Might be
interesting to see what happens with the beta1 release with Devrim's
changes (and ideally similar in Debian), in fact, to see if we actually
get any complaints or push-back from it.

Thanks,

Stephen

---

From: Devrim Gündüz <[email-masked]>
Date: Wed, 20 May 2020 17:08:44 +0100

Hi,

> That necessarily includes setting SCRAM as the password_encryption

My patch already changes that default as well -- RPMs's default
password_encryption will be scram-sha-256.

Regards,

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 12:11:19 -0400

Greetings,

> password_encryption will be scram-sha-256.

+1 for my part.

Thanks,

Stephen

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 12:35:09 -0400

Greetings,

> regarding postgresql.conf?  Why would anything change with pg_upgrade?

This still more-or-less applies, but I missed a step when I was testing
this out (or, rather, I applied the right config change.. to the wrong
cluster, and that makes all the difference).

> or something else and hopefully they'll be able to remedy that pretty

This is, frustratingly, backwards from what we actually decided to do
(which drives me crazy and which I complained about multiple times on
list and yet what we ended up with, but hey, that's part of being
me...).

If you leave it as 'md5' in pg_hba.conf, then *that* will do either md5,
or scram.  If you have 'scram-sha-256' in pg_hba.conf and only an 'md5'
password then it breaks.

> cluster before or after pg_upgrade on distributions that don't have

Of course, since users are probably just going to copy over their
pg_hba.conf from their old cluster with pg_upgrade, this probably won't
end up mattering.

> what Christoph is comfortable with.

On the Debian side, the appropriate approach would be to keep the
existing pg_hba.conf, unless all of the passwords are already scram
(something the pg_upgradecluster could check) and, in that case, move to
scram.  Perhaps also move to scram if there aren't any passwords set in
the cluster.

In all of these cases, we'd need the password_encryption default set to
scram first, so that when users change their password they'll get
upgraded to scram, but for old users to connect the hba has to have md5.

For new installs, I'm still of the opinion that we go with the defaults
of scram, so these considerations are only for how to properly address
the upgrade path.  Ideally there'd be a way to configure to use scram in
pg_hba.conf but still allow md5, but we haven't got that today.

Thanks,

Stephen

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 21 May 2020 00:36:30 +0200

> regarding postgresql.conf?  Why would anything change with pg_upgrade?

Well, one might expect that the user at least gets some kind of 
notification that something is changing.  What happens when you end up 
with a mix of MD5 and SCRAM passwords in pg_authid?  Are users going to 
be notified about this somehow?  Has this been thought through to the 
end?  Have all combinations been tested?

---

From: Stephen Frost <[email-masked]>
Date: Wed, 20 May 2020 19:00:27 -0400

Greetings,

* Peter Eisentraut ([email-masked]) wrote:
> Well, one might expect that the user at least gets some kind of notification
> that something is changing.  What happens when you end up with a mix of MD5
> and SCRAM passwords in pg_authid?  Are users going to be notified about this
> somehow?  Has this been thought through to the end?  Have all combinations
> been tested?

I agree that these things should have been thought through and
considered and that the original patch should have addressed every
possible angle.  That didn't happen though.  There's things that could
be dealt with in the packaging to improve things for users of
pg_upgradecluster, but that's not on the RPM side anyway.

wrt the specific questions- if you have 'md5' in your pg_hba.conf then a
mix of md5 and SCRAM passwords will allow users to still log in- md5
will "upgrade" to SCRAM.  If you have scram in pg_hba.conf then you have
to be using SCRAM to connect (which wasn't a great decision, but that's
what was implemented).  Of course, that's not a problem for *new*
installs, and that's really what we're talking about here.  We aren't
going to be changing any existing configurations with this, just the
*defaults* for new installs, which users who are using pg_upgrade are
going to have to change in just about all cases anyway, particularly on
RHEL.

Thanks,

Stephen

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 22 May 2020 10:52:16 +0200

> password_encryption, so it appears to be correct as far as it goes.

I found that if you use initdb's -A option to set the default 
authentication method, then the passsword_encryption setting is 
automatically adjusted in postgresql.conf.  So this patch probably isn't 
even necessary.

---

From: Devrim Gündüz <[email-masked]>
Date: Fri, 22 May 2020 09:58:01 +0100

Hi,

On Fri, 2020-05-22 at 10:52 +0200, Peter Eisentraut wrote:
> automatically adjusted in postgresql.conf.  So this patch probably
> isn't  even necessary.

Right, but then it also sets scram auth for local connections as well
(which we don't want)

That's why I am using --auth-local and --auth-host at the same time.

Regards,

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 22 May 2020 11:02:19 +0200

> That's why I am using --auth-local and --auth-host at the same time.

It will also work if you set only one of --auth-local and --auth-host to 
scram-sha-256.

---

From: Devrim Gündüz <[email-masked]>
Date: Fri, 22 May 2020 10:06:22 +0100

Hi,

On Fri, 2020-05-22 at 11:02 +0200, Peter Eisentraut wrote:
> It will also work if you set only one of --auth-local and --auth-host 
> to scram-sha-256.

Oh, great. Thanks. I'll do that in beta2.

Regards,

---

From: Stephen Frost <[email-masked]>
Date: Fri, 22 May 2020 09:18:44 -0400

Greetings,

> Oh, great. Thanks. I'll do that in beta2.

Sure, for my part, at least, I'm not partial to exactly how it's set,
just that we move this forward.

Thanks,

Stephen

---

From: Christoph Berg <[email-masked]>
Date: Thu, 28 May 2020 18:38:56 +0200

Re: Stephen Frost
> what Christoph is comfortable with.

Re: Stephen Frost
> or scram.  If you have 'scram-sha-256' in pg_hba.conf and only an 'md5'

Fwiw "comfortable" and "it breaks" are the problem here. The whole
picture is so utterly complicated that I'm still scared from reading
the docs the first time around the time PG10 came about. In trainings
I'm still telling people that md5 is the accepted standard because
there's enough more interesting things to teach about PostgreSQL.

Why do I have to decide *in pg_hba.conf* which hash algorithm is used?
Why can't that just be "password"?

The password_encryption GUC should be the only place concerned with
that, and it should only be used for new passwords. Existing passwords
should just continue to work. *That* would allow seamless upgrades.

Getting this mess fixed would be good for security because then people
will likely start using scram.

Christoph

---

From: Stephen Frost <[email-masked]>
Date: Thu, 28 May 2020 12:44:04 -0400

Greetings,

> there's enough more interesting things to teach about PostgreSQL.

Ah, well, in trainings and talks I'm pushing to completely get rid of
md5 and to only accept using scram. :)

> Why do I have to decide *in pg_hba.conf* which hash algorithm is used?

Where else would you decide..?

> Why can't that just be "password"?

What would that mean?

> should just continue to work. *That* would allow seamless upgrades.

Sure, that'd be nice, but that isn't how it is today.  I argued for
similar during the scram implementation but not what ultimately ended up
happening.

> will likely start using scram.

That's certainly true, though I hope we can convince people to use SCRAM
even given the modest effort required.

The point here though, really, is that *new* installations of PG have
very little reason to not use SCRAM.  The question on upgrades is
different, but that can be addressed with pg_upgradecluster, I would
think, without much trouble.

Thanks,

Stephen

---

From: Christoph Berg <[email-masked]>
Date: Thu, 28 May 2020 18:52:05 +0200

Re: Stephen Frost
> Where else would you decide..?

Connections could just use whatever hash is used for the username in
pg_authid. There's no reason to expose that detail in pg_hba.conf.

> > Why can't that just be "password"?

The above.

> even given the modest effort required.

It's not modest. Or else this thread wouldn't have 20 mails.

> think, without much trouble.

In pg_createcluster, if I move pg_hba.conf and password_encryption to
scram, and I restore a dump from an older PG major, can people
continue to connect using their passwords? From what I got above, the
answer is "no".

Should I only set password_encryption to scram and keep advertising
md5 as the sane default for pg_hba.conf?

Christoph

---

From: Stephen Frost <[email-masked]>
Date: Thu, 28 May 2020 13:09:50 -0400

Greetings,

> pg_authid. There's no reason to expose that detail in pg_hba.conf.

ok, so, that's currently what the 'md5' setting does.  The scram-sha-256
setting is intended to be used to force scram-sha-256 connections and to
not allow md5 or other ones.

> > > Why can't that just be "password"?

So..  it'd be an alias for md5, basically.  I don't think that's
actually a great answer overall as people will want an option that
disallows non-scram password hash usage.

> It's not modest. Or else this thread wouldn't have 20 mails.

This is about the default, not about convincing an individual person or
organization.

> continue to connect using their passwords? From what I got above, the

That really depends on what exactly is in the dump file.  If the
contents of the dump file include md5 hashes then those roles wouldn't
be able to log in.  If the contents have SCRAM-based hashes, then sure.
Is that a huge issue?  Not in my view- it'd be pretty clear quite
quickly that they couldn't log in, and why, and that'd be easy to fix-
they could manually adjust the pg_hba.conf, if they want to, or update
those passwords to be scram.

> md5 as the sane default for pg_hba.conf?

That would allow the above scenario to work, though I don't feel the
"what if they restore a pg_dumpall to perform an upgrade, and don't use
pg_upgradecluster" to be a terribly interesting case to stress about
making everything work perfectly- they'll very likely already be having
to adjust their pg_hba.conf for other reasons, as well as their
postgresql.conf for various settings.  Restoring a pg_dumpall dump
(which is what you're talking about here really, of course) has never
done anything for config files.

Thanks,

Stephen

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 28 May 2020 22:05:40 +0200

On 2020-05-28 18:38, Christoph Berg wrote:
> Why do I have to decide*in pg_hba.conf*  which hash algorithm is used?
> should just continue to work.*That*  would allow seamless upgrades.

You get that if you set the authentication method to "md5".  (Clearly 
not a very clear name, but it exists.)

---

From: Stephen Frost <[email-masked]>
Date: Thu, 28 May 2020 16:08:10 -0400

Greetings,

* Peter Eisentraut ([email-masked]) wrote:
> You get that if you set the authentication method to "md5".  (Clearly not a
> very clear name, but it exists.)

Yeah, the way that was done really wasn't terribly good.

Having 'password' or such, as Chritoph suggest, and then options for
"require=scram" / "require=scram,md5" / nothing (to allow whatever..)
would likely have been better, but that's not what we've got today so
there isn't much point in debating it here.

Thanks,

Stephen

---

From: Christoph Berg <[email-masked]>
Date: Thu, 28 May 2020 22:11:22 +0200

Re: Peter Eisentraut
> very clear name, but it exists.)

Thanks, I'll probably do that.

Do we want that for PG13+, or even for 10+?

Christoph

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 28 May 2020 23:33:48 +0200

> Do we want that for PG13+, or even for 10+?

Isn't that already the default for Debian packages?

---

From: Christoph Berg <[email-masked]>
Date: Fri, 29 May 2020 11:14:42 +0200

Re: Peter Eisentraut
> Isn't that already the default for Debian packages?

I meant setting password_encryption to scram.

Christoph

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 29 May 2020 13:51:23 +0200

> I meant setting password_encryption to scram.

That depends on what you consider your backward compatibility commitment 
to be.

The consensus on pgsql-hackers appears to be to make that change in PG14 
upstream, under the theory that by the time PG14 is released, PG9.6 (the 
last non-SCRAM release) will be (almost) EOL.  So anyone using 
from-source builds under strict observation of EOL dates would not have 
compatibility problems when using their old libpq to connect to a newer 
server.

AFAICT, in Debian you still have 9.6 in stretch until either 2020 or LTS 
until 2022, and in Ubuntu 16.04 you still have 9.5 until 2021.  So, 
well, any choice you end up making can be defended.