Thread: Re: initdb recommendations

On 2019-04-06 20:08, Noah Misch wrote:
>>> I think we should just change the defaults.  There is a risk of warning
>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>> gotten the information.
>>>
>>
>> I've been suggesting that for years, so definite strong +1 for doing that.
> 
> +1

To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".

I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.

Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX.  I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing.  But what should the defaults be on
Windows?  It doesn't have local sockets, so the lack of peer wouldn't
matter.  But is it OK to default to a password method, or would that
upset people particularly?

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

On 5/23/19 12:54 PM, Peter Eisentraut wrote:
> On 2019-04-06 20:08, Noah Misch wrote:
>>>> I think we should just change the defaults.  There is a risk of warning
>>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>>> gotten the information.
>>>>
>>>
>>> I've been suggesting that for years, so definite strong +1 for doing that.
>>
>> +1
>
> To recap, the idea here was to change the default authentication methods
> that initdb sets up, in place of "trust".
>
> I think the ideal scenario would be to use "peer" for local and some
> appropriate password method (being discussed elsewhere) for host.

+1.

> Looking through the buildfarm, I gather that the only platforms that
> don't support peer are Windows, AIX, and HP-UX.  I think we can probably
> figure out some fallback or alternative default for the latter two
> platforms without anyone noticing.  But what should the defaults be on
> Windows?  It doesn't have local sockets, so the lack of peer wouldn't
> matter.  But is it OK to default to a password method, or would that
> upset people particularly?

+1 for password method. Definitely better than trust :)

Jonathan

On 5/23/19 6:47 PM, Jonathan S. Katz wrote:
> On 5/23/19 12:54 PM, Peter Eisentraut wrote:
>> On 2019-04-06 20:08, Noah Misch wrote:
>>>>> I think we should just change the defaults.  There is a risk of warning
>>>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>>>> gotten the information.
>>>>>
>>>>
>>>> I've been suggesting that for years, so definite strong +1 for doing that.
>>>
>>> +1
>>
>> To recap, the idea here was to change the default authentication methods
>> that initdb sets up, in place of "trust".
>>
>> I think the ideal scenario would be to use "peer" for local and some
>> appropriate password method (being discussed elsewhere) for host.
>
> +1.
>
>> Looking through the buildfarm, I gather that the only platforms that
>> don't support peer are Windows, AIX, and HP-UX.  I think we can probably
>> figure out some fallback or alternative default for the latter two
>> platforms without anyone noticing.  But what should the defaults be on
>> Windows?  It doesn't have local sockets, so the lack of peer wouldn't
>> matter.  But is it OK to default to a password method, or would that
>> upset people particularly?
>
> +1 for password method. Definitely better than trust :)

Attached is v2 of the patch.

For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).

However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.

So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?

Thanks,

Jonathan

"Jonathan S. Katz" <jkatz@postgresql.org> writes:
> For now I have left in the password based method to be scram-sha-256 as
> I am optimistic about the support across client drivers[1] (and FWIW I
> have an implementation for crystal-pg ~60% done).

> However, this probably means we would need to set the default password
> encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> may be moot to leave it in.

> So, thinking out loud about that, we should probably use "md5" and once
> we decide to make the encryption method "scram-sha-256" by default, then
> we update the recommendation?

Meh.  If we're going to break things, let's break them.  Set it to
scram by default and let people who need to cope with old clients
change the default.  I'm tired of explaining that MD5 isn't actually
insecure in our usage ...

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
> > For now I have left in the password based method to be scram-sha-256 as
> > I am optimistic about the support across client drivers[1] (and FWIW I
> > have an implementation for crystal-pg ~60% done).
>
> > However, this probably means we would need to set the default password
> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> > may be moot to leave it in.
>
> > So, thinking out loud about that, we should probably use "md5" and once
> > we decide to make the encryption method "scram-sha-256" by default, then
> > we update the recommendation?
>
> Meh.  If we're going to break things, let's break them.  Set it to
> scram by default and let people who need to cope with old clients
> change the default.  I'm tired of explaining that MD5 isn't actually
> insecure in our usage ...

+many.

Thanks,

Stephen

On 5/23/19 10:30 PM, Stephen Frost wrote:
> Greetings,
> 
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
>> > For now I have left in the password based method to be scram-sha-256 as
>> > I am optimistic about the support across client drivers[1] (and FWIW I
>> > have an implementation for crystal-pg ~60% done).
>> 
>> > However, this probably means we would need to set the default password
>> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
>> > may be moot to leave it in.
>> 
>> > So, thinking out loud about that, we should probably use "md5" and once
>> > we decide to make the encryption method "scram-sha-256" by default, then
>> > we update the recommendation?
>> 
>> Meh.  If we're going to break things, let's break them.  Set it to
>> scram by default and let people who need to cope with old clients
>> change the default.  I'm tired of explaining that MD5 isn't actually
>> insecure in our usage ...
> 
> +many.

many++

Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On 2019-05-24 13:48, Joe Conway wrote:
> Are we doing this for pg12?

no

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

Greetings,

* Joe Conway (mail@joeconway.com) wrote:
> On 5/23/19 10:30 PM, Stephen Frost wrote:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
> >> > For now I have left in the password based method to be scram-sha-256 as
> >> > I am optimistic about the support across client drivers[1] (and FWIW I
> >> > have an implementation for crystal-pg ~60% done).
> >>
> >> > However, this probably means we would need to set the default password
> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> >> > may be moot to leave it in.
> >>
> >> > So, thinking out loud about that, we should probably use "md5" and once
> >> > we decide to make the encryption method "scram-sha-256" by default, then
> >> > we update the recommendation?
> >>
> >> Meh.  If we're going to break things, let's break them.  Set it to
> >> scram by default and let people who need to cope with old clients
> >> change the default.  I'm tired of explaining that MD5 isn't actually
> >> insecure in our usage ...
> >
> > +many.
>
> many++
>
> Are we doing this for pg12? In any case, I would think we better loudly
> point out this change somewhere.

Sure, we should point it out, but I don't know that it needs to be
screamed from the rooftops considering the packagers have already been
largely ignoring our defaults here anyway...

Thanks,

Stephen

On 5/24/19 8:13 AM, Stephen Frost wrote:
> Greetings,
> 
> * Joe Conway (mail@joeconway.com) wrote:
>> On 5/23/19 10:30 PM, Stephen Frost wrote:
>> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> >> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
>> >> > For now I have left in the password based method to be scram-sha-256 as
>> >> > I am optimistic about the support across client drivers[1] (and FWIW I
>> >> > have an implementation for crystal-pg ~60% done).
>> >> 
>> >> > However, this probably means we would need to set the default password
>> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
>> >> > may be moot to leave it in.
>> >> 
>> >> > So, thinking out loud about that, we should probably use "md5" and once
>> >> > we decide to make the encryption method "scram-sha-256" by default, then
>> >> > we update the recommendation?
>> >> 
>> >> Meh.  If we're going to break things, let's break them.  Set it to
>> >> scram by default and let people who need to cope with old clients
>> >> change the default.  I'm tired of explaining that MD5 isn't actually
>> >> insecure in our usage ...
>> > 
>> > +many.
>> 
>> many++
>> 
>> Are we doing this for pg12? In any case, I would think we better loudly
>> point out this change somewhere.
> 
> Sure, we should point it out, but I don't know that it needs to be
> screamed from the rooftops considering the packagers have already been
> largely ignoring our defaults here anyway...

Yeah, I thought about that, but anyone not using those packages will be
in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
change, but I predict many related questions on the lists, and anything
we can do to mitigate that should be done.

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Greetings,

* Joe Conway (mail@joeconway.com) wrote:
> On 5/24/19 8:13 AM, Stephen Frost wrote:
> > * Joe Conway (mail@joeconway.com) wrote:
> >> On 5/23/19 10:30 PM, Stephen Frost wrote:
> >> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> >> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
> >> >> > For now I have left in the password based method to be scram-sha-256 as
> >> >> > I am optimistic about the support across client drivers[1] (and FWIW I
> >> >> > have an implementation for crystal-pg ~60% done).
> >> >>
> >> >> > However, this probably means we would need to set the default password
> >> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> >> >> > may be moot to leave it in.
> >> >>
> >> >> > So, thinking out loud about that, we should probably use "md5" and once
> >> >> > we decide to make the encryption method "scram-sha-256" by default, then
> >> >> > we update the recommendation?
> >> >>
> >> >> Meh.  If we're going to break things, let's break them.  Set it to
> >> >> scram by default and let people who need to cope with old clients
> >> >> change the default.  I'm tired of explaining that MD5 isn't actually
> >> >> insecure in our usage ...
> >> >
> >> > +many.
> >>
> >> many++
> >>
> >> Are we doing this for pg12? In any case, I would think we better loudly
> >> point out this change somewhere.
> >
> > Sure, we should point it out, but I don't know that it needs to be
> > screamed from the rooftops considering the packagers have already been
> > largely ignoring our defaults here anyway...
>
> Yeah, I thought about that, but anyone not using those packages will be
> in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
> change, but I predict many related questions on the lists, and anything
> we can do to mitigate that should be done.

You think there's someone who builds from the source and just trusts
what we have put in for the defaults in pg_hba.conf..?

I've got a really hard time with that idea...

I'm all for making people aware of it, but I don't think it justifies
being the top item of the release notes or some such.  Frankly, anything
that starts with "If you build from source, then..." is already going to
be pretty low impact and therefore low on the list of things we need to
cover in the release notes, et al.

Thanks,

Stephen

Greetings,

* Magnus Hagander (magnus@hagander.net) wrote:
> The thing that will potentially hit *end users* is when the RPMs, DEBs or
> Windows Installers switch to SCRAM (because of clients with older drivers).

Agreed.  I'm not sure that our change to SCRAM as default would actually
make them change...  It might, but I'm not sure and it's really a bit of
a different discussion in any case because we need to provide info about
how to go about making the migration.

> Making the default change away from trust in the source distro will affect
> few people.

Agreed.

> Making the default change of password_encryption -> scram will affect a
> *lot* of people. That one needs to be more carefully coordinated.

We need to provide better documentation about how to get from md5 to
SCRAM, in my view.  I'm not sure where that should live, exactly.
I really wish we had put more effort into making the migration easy to
do over a period of time, and we might actually have to do that before
the packagers would be willing to make that change.

Thanks,

Stephen

On 5/24/19 8:33 AM, Stephen Frost wrote:
> Greetings,
>
> * Magnus Hagander (magnus@hagander.net) wrote:
>> The thing that will potentially hit *end users* is when the RPMs, DEBs or
>> Windows Installers switch to SCRAM (because of clients with older drivers).
>
> Agreed.  I'm not sure that our change to SCRAM as default would actually
> make them change...  It might, but I'm not sure and it's really a bit of
> a different discussion in any case because we need to provide info about
> how to go about making the migration.

Yeah, that's the key piece. Even with (almost) all the drivers now
supporting SCRAM, the re-hashing from md5 => scram-sha-256 does not come
automatically.

>> Making the default change away from trust in the source distro will affect
>> few people.
>
> Agreed.

+1

>> Making the default change of password_encryption -> scram will affect a
>> *lot* of people. That one needs to be more carefully coordinated.

Per some of the upthread comments though, if we go down this path we
should at least make the packagers abundantly aware if we do change the
default. I think some of the work they do could help ease the upgrade pain.

> We need to provide better documentation about how to get from md5 to
> SCRAM, in my view.  I'm not sure where that should live, exactly.
> I really wish we had put more effort into making the migration easy to
> do over a period of time, and we might actually have to do that before
> the packagers would be willing to make that change.

+100...I think we should do this regardless, and I was already thinking
of writing something up around it. I would even suggest that we have
said password upgrade documentation backpatched to 10.

Jonathan

Greetings,

* Jonathan S. Katz (jkatz@postgresql.org) wrote:
> On 5/24/19 8:33 AM, Stephen Frost wrote:
> > We need to provide better documentation about how to get from md5 to
> > SCRAM, in my view.  I'm not sure where that should live, exactly.
> > I really wish we had put more effort into making the migration easy to
> > do over a period of time, and we might actually have to do that before
> > the packagers would be willing to make that change.
>
> +100...I think we should do this regardless, and I was already thinking
> of writing something up around it. I would even suggest that we have
> said password upgrade documentation backpatched to 10.

Not sure that backpatching is necessary, but I'm not actively against
it.

What I was really getting at though was the ability to have multiple
authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
ability to use either one (idk, md5_or_scram auth method?), and then
automatically set both on password change until everything is using
SCRAM and then remove all MD5 stuff.

Or something along those lines.  In other words, I'm talking about new
development work to ease the migration (while also providing some oft
asked about features, like the ability to do rolling passwords...).

Thanks,

Stephen

On 5/24/19 8:56 AM, Jonathan S. Katz wrote:
> On 5/24/19 8:33 AM, Stephen Frost wrote:
>> * Magnus Hagander (magnus@hagander.net) wrote:
>>> Making the default change away from trust in the source distro will affect
>>> few people.
>> 
>> Agreed.
> 
> +1

Fewer people, but likely disproportionately high representation on pgsql
lists. Anyway, nuff said -- I guess the future will tell one way or the
other.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On 5/24/19 9:01 AM, Stephen Frost wrote:
> Greetings,
>
> * Jonathan S. Katz (jkatz@postgresql.org) wrote:
>> On 5/24/19 8:33 AM, Stephen Frost wrote:
>>> We need to provide better documentation about how to get from md5 to
>>> SCRAM, in my view.  I'm not sure where that should live, exactly.
>>> I really wish we had put more effort into making the migration easy to
>>> do over a period of time, and we might actually have to do that before
>>> the packagers would be willing to make that change.
>>
>> +100...I think we should do this regardless, and I was already thinking
>> of writing something up around it. I would even suggest that we have
>> said password upgrade documentation backpatched to 10.
>
> Not sure that backpatching is necessary, but I'm not actively against
> it.

Well, for someone who wants to cut over and has to manually guide the
process, a guide will help in absence of new development.

>
> What I was really getting at though was the ability to have multiple
> authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
> ability to use either one (idk, md5_or_scram auth method?), and then
> automatically set both on password change until everything is using
> SCRAM and then remove all MD5 stuff.
>
> Or something along those lines.  In other words, I'm talking about new
> development work to ease the migration (while also providing some oft
> asked about features, like the ability to do rolling passwords...).

Cool, I have been thinking about a similar feature as well to help ease
the transition (and fwiw was going to suggest it in my previous email).

I think an interim step at least is to document how we can at least help
ease the transition.

Thanks,

Jonathan

On 24/05/2019 16:01, Stephen Frost wrote:
> What I was really getting at though was the ability to have multiple
> authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
> ability to use either one (idk, md5_or_scram auth method?), and then
> automatically set both on password change until everything is using
> SCRAM and then remove all MD5 stuff.

Umm, that's what "md5" already does. Per documentation 
(https://www.postgresql.org/docs/current/auth-password.html):

 > To ease transition from the md5 method to the newer SCRAM method, if
 > md5 is specified as a method in pg_hba.conf but the user's password on
 > the server is encrypted for SCRAM (see below), then SCRAM-based
 > authentication will automatically be chosen instead.

The migration path is:

1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256' 
in postgresql.conf.

2. Wait until all users have reset their passwords, so that all users 
have a SCRAM-SHA-256 verifier.

3. Replace "md5" with "scram-sha-256" in pg_hba.conf.

Step 3 is kind of optional; once all users have a SCRAM verifier instead 
of an MD5 hash, they will all use SCRAM even without changing 
pg_hba.conf. It just prevents MD5 authentication in case a user forces a 
new MD5 hash into the system e.g. by changing password_encryption, or by 
setting an MD5 password explicitly with ALTER USER.

- Heikki

Greetings,

* Heikki Linnakangas (hlinnaka@iki.fi) wrote:
> On 24/05/2019 16:01, Stephen Frost wrote:
> >What I was really getting at though was the ability to have multiple
> >authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
> >ability to use either one (idk, md5_or_scram auth method?), and then
> >automatically set both on password change until everything is using
> >SCRAM and then remove all MD5 stuff.
>
> Umm, that's what "md5" already does. Per documentation
> (https://www.postgresql.org/docs/current/auth-password.html):

I remembered that we did something here but hadn't gone and looked at
it recently, so sorry for misremembering.  Perhaps all the more reason
for detailed migration documentation.

> > To ease transition from the md5 method to the newer SCRAM method, if
> > md5 is specified as a method in pg_hba.conf but the user's password on
> > the server is encrypted for SCRAM (see below), then SCRAM-based
> > authentication will automatically be chosen instead.
>
> The migration path is:
>
> 1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256' in
> postgresql.conf.
>
> 2. Wait until all users have reset their passwords, so that all users have a
> SCRAM-SHA-256 verifier.

Wait though- once a password is changed then they *have* to use SCRAM
for auth from that point on, right?  That's great if you can be sure
that everything you're connecting from supports it, but that isn't going
to necessairly be the case.  I think this is what I recall being unhappy
about and what I was trying to remember about what we did.

We also haven't got a way to tell very easily when a given md5 (or
scram, for that matter...) authenticator was last used, making it hard
to see if it's still actually being used or not.  Nor is there a very
nice way to see when all users have reset their passwords to scram
without inspecting the password hash itself...

> 3. Replace "md5" with "scram-sha-256" in pg_hba.conf.
>
> Step 3 is kind of optional; once all users have a SCRAM verifier instead of
> an MD5 hash, they will all use SCRAM even without changing pg_hba.conf. It
> just prevents MD5 authentication in case a user forces a new MD5 hash into
> the system e.g. by changing password_encryption, or by setting an MD5
> password explicitly with ALTER USER.

Yes, which you'd certainly want to do, so I don't consider it to be
optional.  Further, we should really have a way for an admin to say
"never allow storing an md5 password again" which I don't think we do.

Thanks,

Stephen

On 5/24/19 9:49 AM, Heikki Linnakangas wrote:
> On 24/05/2019 16:01, Stephen Frost wrote:
>> What I was really getting at though was the ability to have multiple
>> authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
>> ability to use either one (idk, md5_or_scram auth method?), and then
>> automatically set both on password change until everything is using
>> SCRAM and then remove all MD5 stuff.
>
> Umm, that's what "md5" already does. Per documentation
> (https://www.postgresql.org/docs/current/auth-password.html):

Tested manually and verified in code, it does do that check:

/*
 * If 'md5' authentication is allowed, decide whether to perform 'md5' or
 * 'scram-sha-256' authentication based on the type of password the user
 * has.  If it's an MD5 hash, we must do MD5 authentication, and if it's a
 * SCRAM verifier, we must do SCRAM authentication.
 *
 * If MD5 authentication is not allowed, always use SCRAM.  If the user
 * had an MD5 password, CheckSCRAMAuth() will fail.
 */
if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5)
    auth_result = CheckMD5Auth(port, shadow_pass, logdetail);
else
    auth_result = CheckSCRAMAuth(port, shadow_pass, logdetail);


>> To ease transition from the md5 method to the newer SCRAM method, if
>> md5 is specified as a method in pg_hba.conf but the user's password on
>> the server is encrypted for SCRAM (see below), then SCRAM-based
>> authentication will automatically be chosen instead.
>
> The migration path is:
>
> 1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256'
> in postgresql.conf.
>
> 2. Wait until all users have reset their passwords, so that all users
> have a SCRAM-SHA-256 verifier.

And "a superuser can verify this has occurred by inspecting the
pg_authid table (appropriate SQL)"

>
> 3. Replace "md5" with "scram-sha-256" in pg_hba.conf.
>
> Step 3 is kind of optional; once all users have a SCRAM verifier instead
> of an MD5 hash, they will all use SCRAM even without changing
> pg_hba.conf.

Verified this is true.

> It just prevents MD5 authentication in case a user forces a
> new MD5 hash into the system e.g. by changing password_encryption, or by
> setting an MD5 password explicitly with ALTER USER.

Cool. Thanks for the explanation.

I do think we should document said upgrade path, my best guess being
around here[1].

Jonathan

[1] https://www.postgresql.org/docs/current/auth-password.html

On 24/05/2019 17:02, Jonathan S. Katz wrote:
> On 5/24/19 9:49 AM, Heikki Linnakangas wrote:
>> It just prevents MD5 authentication in case a user forces a
>> new MD5 hash into the system e.g. by changing password_encryption, or by
>> setting an MD5 password explicitly with ALTER USER.
> 
> Cool. Thanks for the explanation.
> 
> I do think we should document said upgrade path, my best guess being
> around here[1].
> 
> [1] https://www.postgresql.org/docs/current/auth-password.html

You mean, like this? From the bottom of that page :-)

 > To upgrade an existing installation from md5 to scram-sha-256, after
 > having ensured that all client libraries in use are new enough to
 > support SCRAM, set password_encryption = 'scram-sha-256' in
 > postgresql.conf, make all users set new passwords, and change the
 > authentication method specifications in pg_hba.conf to scram-sha-256.

It would be nice to expand that a little bit, though:

* How do you verify if all client libraries support SCRAM? Would be good 
to mention the minimum libpq version here, at least. Can we give more 
explicit instructions? It would be nice if there was a way to write an 
entry to the log, whenever an older client connects. Not sure how you'd 
do that..

* How does one "make all users to set new passwords"? Related to that, 
how do you check if all users have reset their password to SCRAM? Give 
the exact SQL needed to check that.

- Heikki

On 5/24/19 10:26 AM, Heikki Linnakangas wrote:
> On 24/05/2019 17:02, Jonathan S. Katz wrote:
>> On 5/24/19 9:49 AM, Heikki Linnakangas wrote:
>>> It just prevents MD5 authentication in case a user forces a
>>> new MD5 hash into the system e.g. by changing password_encryption, or by
>>> setting an MD5 password explicitly with ALTER USER.
>>
>> Cool. Thanks for the explanation.
>>
>> I do think we should document said upgrade path, my best guess being
>> around here[1].
>>
>> [1] https://www.postgresql.org/docs/current/auth-password.html
>
> You mean, like this? From the bottom of that page :-)

...yes ;) I think what I'm saying is that it should be its own section.

>> To upgrade an existing installation from md5 to scram-sha-256, after
>> having ensured that all client libraries in use are new enough to
>> support SCRAM, set password_encryption = 'scram-sha-256' in
>> postgresql.conf, make all users set new passwords, and change the
>> authentication method specifications in pg_hba.conf to scram-sha-256.
>
> It would be nice to expand that a little bit, though:
>
> * How do you verify if all client libraries support SCRAM? Would be good
> to mention the minimum libpq version here, at least. Can we give more
> explicit instructions? It would be nice if there was a way to write an
> entry to the log, whenever an older client connects. Not sure how you'd
> do that..

Yeah, this one is hard, because a lot of that depends on how the client
deals with not supporting SCRAM. Typically the server sends over
AuthenticationSASL and the client raises an error. All the server will
see is the connection closed, but it could be for any reason.

For example, I tested this with an unpatched asyncpg and noted similar
behavior. I'm not sure there's anything we can do given we don't know
that the client does not support SCRAM ahead of time.

I think the best we can do is mention minimums and, if we're ok with it,
link to the drivers wiki page so people can see which min. versions of
their preferred connection library support it.

> * How does one "make all users to set new passwords"? Related to that,
> how do you check if all users have reset their password to SCRAM? Give
> the exact SQL needed to check that.

Yeah this is a big one. I already hinted at the latter point, but also
explaining how to change passwords is helpful too (and I feel can also
cause quite a debate as well. Within psql it's a straightforward choice.
Outside of it, to do it safely you have to do a bit of extra work).

Jonathan

On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> On Thu, May 23, 2019, 18:54 Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
> > To recap, the idea here was to change the default authentication methods
> > that initdb sets up, in place of "trust".
> >
> > I think the ideal scenario would be to use "peer" for local and some
> > appropriate password method (being discussed elsewhere) for host.
> >
> > Looking through the buildfarm, I gather that the only platforms that
> > don't support peer are Windows, AIX, and HP-UX.  I think we can probably
> > figure out some fallback or alternative default for the latter two
> > platforms without anyone noticing.  But what should the defaults be on
> > Windows?  It doesn't have local sockets, so the lack of peer wouldn't
> > matter.  But is it OK to default to a password method, or would that
> > upset people particularly?
> 
> I'm sure password would be fine there. It's what "everybody else" does
> (well sqlserver also cord integrated security, but people are used to it).

Our sspi auth is a more-general version of peer auth, and it works over TCP.
It would be a simple matter of programming to support "peer" on Windows,
consisting of sspi auth with an implicit pg_ident map.  Nonetheless, I agree
password would be fine.

On Fri, May 24, 2019 at 08:23:57AM -0700, Noah Misch wrote:
> Our sspi auth is a more-general version of peer auth, and it works over TCP.
> It would be a simple matter of programming to support "peer" on Windows,
> consisting of sspi auth with an implicit pg_ident map.

I am not sure that it is much worth complicating the HBA rules with an
extra alias knowing that it is possible to map pg_ident to use a regex
matching pattern.

> Nonetheless, I agree password would be fine.

Fine for me.
--
Michael

On Tue, May 28, 2019 at 12:15:35PM -0400, Magnus Hagander wrote:
> On Fri, May 24, 2019 at 11:24 AM Noah Misch <noah@leadboat.com> wrote:
> > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > On Thu, May 23, 2019, 18:54 Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
> > > > To recap, the idea here was to change the default authentication methods
> > > > that initdb sets up, in place of "trust".
> > > >
> > > > I think the ideal scenario would be to use "peer" for local and some
> > > > appropriate password method (being discussed elsewhere) for host.
> > > >
> > > > Looking through the buildfarm, I gather that the only platforms that
> > > > don't support peer are Windows, AIX, and HP-UX.  I think we can probably
> > > > figure out some fallback or alternative default for the latter two
> > > > platforms without anyone noticing.  But what should the defaults be on
> > > > Windows?  It doesn't have local sockets, so the lack of peer wouldn't
> > > > matter.  But is it OK to default to a password method, or would that
> > > > upset people particularly?
> > >
> > > I'm sure password would be fine there. It's what "everybody else" does
> > > (well sqlserver also cord integrated security, but people are used to it).
> > 
> > Our sspi auth is a more-general version of peer auth, and it works over TCP.
> > It would be a simple matter of programming to support "peer" on Windows,
> > consisting of sspi auth with an implicit pg_ident map.  Nonetheless, I agree
> > password would be fine.
>
> I hope oyu don't mean "make peer use sspi on windows". I think that's a
> really bad idea from a confusion perspective.

I don't mean "make peer an alias for SSPI", but I do mean "implement peer on
Windows as a special case of sspi, using the same Windows APIs".  To the
client, "peer" would look like "sspi".  If that's confusion-prone, what's
confusing about it?

> However, what we could do there is have the defaut pg_hba.conf file contain
> a "reasonable setup using sspi" that's a different story.

That's another way to do it.  Currently, to behave like "peer" behaves, one
hard-codes the machine's SSPI realm into pg_ident.conf.  If we introduced
pg_ident.conf syntax to remove that need (e.g. %MACHINE_REALM%), that approach
would work.

> But I wonder if that isn't better implemented at the installer level. I
> think we're better off doing something like scram as the config when you
> build from source ,and then encourage installers to do other things based on
> the fact that they know more information about the setup (such as usernames
> actually used).

If initdb has the information needed to configure the recommended
authentication, that's the best place to do it, since there's one initdb and
many installers.  So far, I haven't seen a default auth configuration proposal
involving knowledge of OS usernames or other information initdb lacks.

Greetings,

* Noah Misch (noah@leadboat.com) wrote:
> On Tue, May 28, 2019 at 12:15:35PM -0400, Magnus Hagander wrote:
> > On Fri, May 24, 2019 at 11:24 AM Noah Misch <noah@leadboat.com> wrote:
> > > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > > On Thu, May 23, 2019, 18:54 Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
> > > > > To recap, the idea here was to change the default authentication methods
> > > > > that initdb sets up, in place of "trust".
> > > > >
> > > > > I think the ideal scenario would be to use "peer" for local and some
> > > > > appropriate password method (being discussed elsewhere) for host.
> > > > >
> > > > > Looking through the buildfarm, I gather that the only platforms that
> > > > > don't support peer are Windows, AIX, and HP-UX.  I think we can probably
> > > > > figure out some fallback or alternative default for the latter two
> > > > > platforms without anyone noticing.  But what should the defaults be on
> > > > > Windows?  It doesn't have local sockets, so the lack of peer wouldn't
> > > > > matter.  But is it OK to default to a password method, or would that
> > > > > upset people particularly?
> > > >
> > > > I'm sure password would be fine there. It's what "everybody else" does
> > > > (well sqlserver also cord integrated security, but people are used to it).
> > >
> > > Our sspi auth is a more-general version of peer auth, and it works over TCP.
> > > It would be a simple matter of programming to support "peer" on Windows,
> > > consisting of sspi auth with an implicit pg_ident map.  Nonetheless, I agree
> > > password would be fine.
> >
> > I hope oyu don't mean "make peer use sspi on windows". I think that's a
> > really bad idea from a confusion perspective.
>
> I don't mean "make peer an alias for SSPI", but I do mean "implement peer on
> Windows as a special case of sspi, using the same Windows APIs".  To the
> client, "peer" would look like "sspi".  If that's confusion-prone, what's
> confusing about it?

I tend to agree with Magnus here.  It's confusing because 'peer' in our
existing parlance discusses connections over a unix socket, which
certainly isn't what's happening on Windows.  I do agree with the
general idea of making SSPI work by default on Windows.

> > However, what we could do there is have the defaut pg_hba.conf file contain
> > a "reasonable setup using sspi" that's a different story.
>
> That's another way to do it.  Currently, to behave like "peer" behaves, one
> hard-codes the machine's SSPI realm into pg_ident.conf.  If we introduced
> pg_ident.conf syntax to remove that need (e.g. %MACHINE_REALM%), that approach
> would work.

I would be in favor of something like this, provided the variables are
defined in such a way that we could avoid conflicting with real values
(and remember that you'd need a regexp in pg_ident.conf for this to
work...).  %xyz%, while supporting %% to mean a literal percent, seems
likely to work.  Not sure if that's what you were thinking though.

> > But I wonder if that isn't better implemented at the installer level. I
> > think we're better off doing something like scram as the config when you
> > build from source ,and then encourage installers to do other things based on
> > the fact that they know more information about the setup (such as usernames
> > actually used).
>
> If initdb has the information needed to configure the recommended
> authentication, that's the best place to do it, since there's one initdb and
> many installers.  So far, I haven't seen a default auth configuration proposal
> involving knowledge of OS usernames or other information initdb lacks.

I agree with doing it at initdb time.

Note that the current default auth configuration (to some extent) does
depend on the OS username- but that's also something that initdb knows,
and therefore it isn't an issue here.  I don't see a reason that we
wouldn't be able to have initdb handle this.

Thanks!

Stephen

On 2019-05-23 18:54, Peter Eisentraut wrote:
> To recap, the idea here was to change the default authentication methods
> that initdb sets up, in place of "trust".
> 
> I think the ideal scenario would be to use "peer" for local and some
> appropriate password method (being discussed elsewhere) for host.

Patch for that attached.

> Looking through the buildfarm, I gather that the only platforms that
> don't support peer are Windows, AIX, and HP-UX.

Note that with this change, running initdb without arguments will now
error on those platforms: You need to supply either a password or select
a different default authentication method.

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

On Tue, Jun 18, 2019 at 10:33 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
>
> On 2019-05-23 18:54, Peter Eisentraut wrote:
> > To recap, the idea here was to change the default authentication methods
> > that initdb sets up, in place of "trust".
> >
> > I think the ideal scenario would be to use "peer" for local and some
> > appropriate password method (being discussed elsewhere) for host.

I'm also personally all for that change.

> Patch for that attached.

Patch applies and compiles cleanly, same for documentation.  The
change works as intended, so I don't have much to say.

> Note that with this change, running initdb without arguments will now
> error on those platforms: You need to supply either a password or select
> a different default authentication method.

Should we make this explicitly stated in the documentation?  As a
reference, it's saying:

The default client authentication setup is such that users can connect
over the Unix-domain socket to the same database user name as their
operating system user names (on operating systems that support this,
which are most modern Unix-like systems, but not Windows) and
otherwise with a password. To assign a password to the initial
database superuser, use one of initdb's -W, --pwprompt or -- pwfile
options.

On Thu, Jul 11, 2019 at 09:34:25PM +0200, Julien Rouhaud wrote:
> On Tue, Jun 18, 2019 at 10:33 PM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
> >
> > On 2019-05-23 18:54, Peter Eisentraut wrote:
> > > To recap, the idea here was to change the default authentication methods
> > > that initdb sets up, in place of "trust".
> > >
> > > I think the ideal scenario would be to use "peer" for local and some
> > > appropriate password method (being discussed elsewhere) for host.
> 
> I'm also personally all for that change.
> 
> > Patch for that attached.
> 
> Patch applies and compiles cleanly, same for documentation.  The
> change works as intended, so I don't have much to say.
> 
> > Note that with this change, running initdb without arguments will now
> > error on those platforms: You need to supply either a password or select
> > a different default authentication method.
> 
> Should we make this explicitly stated in the documentation?  As a
> reference, it's saying:
> 
> The default client authentication setup is such that users can connect
> over the Unix-domain socket to the same database user name as their
> operating system user names (on operating systems that support this,
> which are most modern Unix-like systems, but not Windows)

It turns out that really recent versions of Windows do have it.

https://bsmadhu.wordpress.com/2018/08/22/unix-domain-socket-support-in-windows/

Not that this is relevant, or will be, for another couple of years...

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 2019-07-11 21:34, Julien Rouhaud wrote:
>> Note that with this change, running initdb without arguments will now
>> error on those platforms: You need to supply either a password or select
>> a different default authentication method.
> Should we make this explicitly stated in the documentation?  As a
> reference, it's saying:
> 
> The default client authentication setup is such that users can connect
> over the Unix-domain socket to the same database user name as their
> operating system user names (on operating systems that support this,
> which are most modern Unix-like systems, but not Windows) and
> otherwise with a password. To assign a password to the initial
> database superuser, use one of initdb's -W, --pwprompt or -- pwfile
> options.

Do you have a suggestion for where to put this and exactly how to phrase
this?

I think the initdb reference page would be more appropriate than
runtime.sgml.

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

On Sat, Jul 13, 2019 at 2:44 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
>
> On 2019-07-11 21:34, Julien Rouhaud wrote:
> >> Note that with this change, running initdb without arguments will now
> >> error on those platforms: You need to supply either a password or select
> >> a different default authentication method.
> > Should we make this explicitly stated in the documentation?  As a
> > reference, it's saying:
> >
> > The default client authentication setup is such that users can connect
> > over the Unix-domain socket to the same database user name as their
> > operating system user names (on operating systems that support this,
> > which are most modern Unix-like systems, but not Windows) and
> > otherwise with a password. To assign a password to the initial
> > database superuser, use one of initdb's -W, --pwprompt or -- pwfile
> > options.
>
> Do you have a suggestion for where to put this and exactly how to phrase
> this?
>
> I think the initdb reference page would be more appropriate than
> runtime.sgml.

Yes initdb.sgml seems more suitable.  I was thinking something very
similar to your note, maybe like (also attached if my MUA ruins it):

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index c47b9139eb..764cf737c7 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -143,6 +143,15 @@ PostgreSQL documentation
         connections.
        </para>

+       <note>
+        <para>
+         Running initdb without arguments on platforms lacking
+         <literal>peer</literal> or Unix-domain socket connections will exit
+         with an error.  On such environments, you need to either provide a
+         password or choose a different authentication method.
+        </para>
+       </note>
+
        <para>
         Do not use

On 2019-07-13 18:58, Julien Rouhaud wrote:
>>> The default client authentication setup is such that users can connect
>>> over the Unix-domain socket to the same database user name as their
>>> operating system user names (on operating systems that support this,
>>> which are most modern Unix-like systems, but not Windows) and
>>> otherwise with a password. To assign a password to the initial
>>> database superuser, use one of initdb's -W, --pwprompt or -- pwfile
>>> options.
>>
>> Do you have a suggestion for where to put this and exactly how to phrase
>> this?
>>
>> I think the initdb reference page would be more appropriate than
>> runtime.sgml.
> 
> Yes initdb.sgml seems more suitable.  I was thinking something very
> similar to your note, maybe like (also attached if my MUA ruins it):

Pushed with that note.  Thanks.

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

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Pushed with that note.  Thanks.

This has completely broken the buildfarm.

            regards, tom lane

I wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> Pushed with that note.  Thanks.

> This has completely broken the buildfarm.

On inspection, it seems the reason for that is that the buildfarm
script runs initdb with '-U buildfarm', so that peer-auth connections
will only work if the buildfarm is being run by an OS user named
exactly "buildfarm".  That happens to be true on my macOS animals,
which is why they're not broken ... but apparently, nobody else
does it that way.

I'm afraid we're going to have to revert this, at least till
such time as a fixed buildfarm client is in universal use.

As for the nature of that fix, I don't quite understand why
the forced -U is there --- maybe we could just remove it?
But there are multiple places in the buildfarm client that
have hard-wired references to "buildfarm".

            regards, tom lane

I wrote:
> I'm afraid we're going to have to revert this, at least till
> such time as a fixed buildfarm client is in universal use.

> As for the nature of that fix, I don't quite understand why
> the forced -U is there --- maybe we could just remove it?
> But there are multiple places in the buildfarm client that
> have hard-wired references to "buildfarm".

BTW, it looks like the Windows buildfarm critters have a
separate problem: they're failing with

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

One would imagine that even if we'd given a password to initdb,
subsequent connection attempts would fail for lack of a password.
There might not be any practical fix except forcing trust auth
for the Windows critters.

            regards, tom lane

On 7/22/19 12:25 PM, Tom Lane wrote:
> I wrote:
>> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>>> Pushed with that note.  Thanks.
>> This has completely broken the buildfarm.
> On inspection, it seems the reason for that is that the buildfarm
> script runs initdb with '-U buildfarm', so that peer-auth connections
> will only work if the buildfarm is being run by an OS user named
> exactly "buildfarm".  That happens to be true on my macOS animals,
> which is why they're not broken ... but apparently, nobody else
> does it that way.
>
> I'm afraid we're going to have to revert this, at least till
> such time as a fixed buildfarm client is in universal use.
>
> As for the nature of that fix, I don't quite understand why
> the forced -U is there --- maybe we could just remove it?
> But there are multiple places in the buildfarm client that
> have hard-wired references to "buildfarm".



This goes back quite a way:


    commit 7528701abb88ab84f6775448c59b392ca7f33a07
    Author: Andrew Dunstan <andrew@dunslane.net>
    Date:   Tue Nov 27 13:47:38 2012 -0500

        Run everything as buildfarm rather than local user name.
       
        This will help if we ever want to do things like comparing dump
    diffs.
        Done by setting PGUSER and using initdb's -U option.


The pg_upgrade test (not the cross-version one) doesn't use this - it
explicitly unsets PGUSER.

There are a few things we could do. We could force trust auth, or we
could add an ident map that allowed $USER to login as buildfarm. Finding
all the places we would need to fix that could be a fun project ...

We could also maybe teach initdb to honor an environment setting
INTDB_DEFAULT_AUTH or some such.


I agree this should be reverted for now until we work out what we want
to do.


cheers


andrew



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

Hi,

On 2019-07-22 13:02:13 -0400, Andrew Dunstan wrote:
> There are a few things we could do. We could force trust auth, or we
> could add an ident map that allowed $USER to login as buildfarm. Finding
> all the places we would need to fix that could be a fun project ...

Perhaps we could actually do so automatically when the initdb invoking
user isn't the same as the OS user? Imo that'd be generally quite
useful, and not just for the regression tets.

Greetings,

Andres Freund

I wrote:
> BTW, it looks like the Windows buildfarm critters have a
> separate problem: they're failing with
> initdb: error: must specify a password for the superuser to enable md5 authentication

I tried doing a run on gaur (old HPUX, so no "peer" auth) before the
revert happened.  It got as far as initdb-check [1], which failed quite
thoroughly with lots of the same error as above.  Depressingly, a lot of
the test cases that expected some type of error "succeeded", indicating
they're not actually checking to see which error they got.  Boo hiss.

Presumably Noah's AIX menagerie would have failed in about the
same way if it had run.

So we've got a *lot* of buildfarm work to do before we can think about
changing this.

Frankly, this episode makes me wonder whether changing the default is
even a good idea at this point.  People who care about security have
already set up their processes to select a useful-to-them auth option,
while people who do not care are unlikely to be happy about having
security rammed down their throats, especially if it results in the
sort of push-ups we're looking at having to do in the buildfarm.
I think this has effectively destroyed the argument that only
trivial adjustments will be required.

            regards, tom lane

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=gaur&dt=2019-07-22%2017%3A08%3A27

On 7/22/19 12:39 PM, Tom Lane wrote:
> I wrote:
>> I'm afraid we're going to have to revert this, at least till
>> such time as a fixed buildfarm client is in universal use.
>> As for the nature of that fix, I don't quite understand why
>> the forced -U is there --- maybe we could just remove it?
>> But there are multiple places in the buildfarm client that
>> have hard-wired references to "buildfarm".
> BTW, it looks like the Windows buildfarm critters have a
> separate problem: they're failing with
>
> initdb: error: must specify a password for the superuser to enable md5 authentication
>
> One would imagine that even if we'd given a password to initdb,
> subsequent connection attempts would fail for lack of a password.
> There might not be any practical fix except forcing trust auth
> for the Windows critters.
>
>             



Yeah.


Modulo this issue, experimentation shows that adding '-A trust' to the
line in run_build.pl where initdb is called fixes the issue. If we're
going to rely on a buildfarm client fix that one seems simplest. there
are a couple of not very widely used modules that need similar treatment
- TestSepgsql and TesUpgradeXVersion


cheers


andrew


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

On 7/22/19 3:15 PM, Tom Lane wrote:
> I wrote:
>> BTW, it looks like the Windows buildfarm critters have a
>> separate problem: they're failing with
>> initdb: error: must specify a password for the superuser to enable md5 authentication
> I tried doing a run on gaur (old HPUX, so no "peer" auth) before the
> revert happened.  It got as far as initdb-check [1], which failed quite
> thoroughly with lots of the same error as above.  Depressingly, a lot of
> the test cases that expected some type of error "succeeded", indicating
> they're not actually checking to see which error they got.  Boo hiss.
>
> Presumably Noah's AIX menagerie would have failed in about the
> same way if it had run.
>
> So we've got a *lot* of buildfarm work to do before we can think about
> changing this.



Ouch. I'll test more on Windows.



>
> Frankly, this episode makes me wonder whether changing the default is
> even a good idea at this point.  People who care about security have
> already set up their processes to select a useful-to-them auth option,
> while people who do not care are unlikely to be happy about having
> security rammed down their throats, especially if it results in the
> sort of push-ups we're looking at having to do in the buildfarm.
> I think this has effectively destroyed the argument that only
> trivial adjustments will be required.
>
>             regards, tom lane
>
> [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=gaur&dt=2019-07-22%2017%3A08%3A27
>


There's a strong tendency these days to be secure by default, so I
understand the motivation.


cheers


andrew

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

I wrote:
> I tried doing a run on gaur (old HPUX, so no "peer" auth) before the
> revert happened.  It got as far as initdb-check [1], which failed quite
> thoroughly with lots of the same error as above.
> ...
> Presumably Noah's AIX menagerie would have failed in about the
> same way if it had run.

Oh --- actually, Noah's machines *did* report in on that commit,
and they got past initdb-check, only to fail at install-check-C
much the same as most of the rest of the world.

Studying their configure output, the reason is that they have
getpeereid(), so that AIX *does* support peer auth.  At least
on that version of AIX.  That makes it only HPUX and Windows
that can't do it.

BTW, after looking at the patch a bit more, I'm pretty distressed
by this:

--- a/src/include/port.h
+++ b/src/include/port.h
@@ -361,6 +361,11 @@ extern int fls(int mask);
 extern int getpeereid(int sock, uid_t *uid, gid_t *gid);
 #endif

+/* must match src/port/getpeereid.c */
+#if defined(HAVE_GETPEEREID) || defined(SO_PEERCRED) || defined(LOCAL_PEERCRED) || defined(HAVE_GETPEERUCRED)
+#define HAVE_AUTH_PEER 1
+#endif
+
 #ifndef HAVE_ISINF
 extern int isinf(double x);
 #else

I seriously doubt that port.h includes, or should be made to include,
whatever headers provide SO_PEERCRED and/or LOCAL_PEERCRED.  That means
that the result of this test is going to be different in different .c
files depending on what was or wasn't included.  It could also get
silently broken on specific platforms by an ill-advised #include removal
(and, once we fix the buildfarm script to not fail on PEER-less platforms,
the buildfarm wouldn't detect the breakage either).

Another objection to this is that it's entirely unclear from the
buildfarm logs whether HAVE_AUTH_PEER got set on a particular system.

I think that when/if we try again, configure itself ought to be
responsible for setting HAVE_AUTH_PEER after probing for these
various antecedent symbols.

            regards, tom lane

On 7/22/19 3:20 PM, Andrew Dunstan wrote:
>
> On 7/22/19 3:15 PM, Tom Lane wrote:
>>
>> Frankly, this episode makes me wonder whether changing the default is
>> even a good idea at this point.  People who care about security have
>> already set up their processes to select a useful-to-them auth option,
>> while people who do not care are unlikely to be happy about having
>> security rammed down their throats, especially if it results in the
>> sort of push-ups we're looking at having to do in the buildfarm.
>> I think this has effectively destroyed the argument that only
>> trivial adjustments will be required.
>
> There's a strong tendency these days to be secure by default, so I
> understand the motivation.

So perhaps to bring back the idea that spawned this thread[1], as an
interim step, we provide some documented recommendations on how to set
things up. The original patch has a warning box (and arguably defaulting
to "trust" deserves a warning) but could be revised to be inline with
the text.

Jonathan

[1]
https://www.postgresql.org/message-id/bec17f0a-ddb1-8b95-5e69-368d9d0a3390%40postgresql.org

On 2019-07-22 21:16, Andrew Dunstan wrote:
> Modulo this issue, experimentation shows that adding '-A trust' to the
> line in run_build.pl where initdb is called fixes the issue. If we're
> going to rely on a buildfarm client fix that one seems simplest.

Yes, that is the right fix.  It's what the in-tree test drivers
(pg_regress, PostgresNode.pm) do.

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

On 7/22/19 1:40 PM, Andres Freund wrote:
> Hi,
>
> On 2019-07-22 13:02:13 -0400, Andrew Dunstan wrote:
>> There are a few things we could do. We could force trust auth, or we
>> could add an ident map that allowed $USER to login as buildfarm. Finding
>> all the places we would need to fix that could be a fun project ...
> Perhaps we could actually do so automatically when the initdb invoking
> user isn't the same as the OS user? Imo that'd be generally quite
> useful, and not just for the regression tets.
>

yeah, although I think that's a separate exercise.


So we'd have something like


in pg_hba.conf


    local    all all    peer map=datadir_owner


and in pg_ident.conf


    datadir_owner $USER $superuser


cheers


andrew




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

On 7/23/19 2:12 AM, Peter Eisentraut wrote:
> On 2019-07-22 21:16, Andrew Dunstan wrote:
>> Modulo this issue, experimentation shows that adding '-A trust' to the
>> line in run_build.pl where initdb is called fixes the issue. If we're
>> going to rely on a buildfarm client fix that one seems simplest.
> Yes, that is the right fix.  It's what the in-tree test drivers
> (pg_regress, PostgresNode.pm) do.
>


I have done that, I will put out a new release probably right after the
CF closes.


I think we also need to change vcregress.pl to use trust explicitly for
upgrade checks, just like the Unix upgrade test script does. That should
help to future-proof us a bit.


cheers


andrew


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

On 2019-07-24 16:00, Andrew Dunstan wrote:
> I think we also need to change vcregress.pl to use trust explicitly for
> upgrade checks, just like the Unix upgrade test script does. That should
> help to future-proof us a bit.

Right, I'll add that to my patch.

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

On 7/24/19 10:00 AM, Andrew Dunstan wrote:
> On 7/23/19 2:12 AM, Peter Eisentraut wrote:
>> On 2019-07-22 21:16, Andrew Dunstan wrote:
>>> Modulo this issue, experimentation shows that adding '-A trust' to the
>>> line in run_build.pl where initdb is called fixes the issue. If we're
>>> going to rely on a buildfarm client fix that one seems simplest.
>> Yes, that is the right fix.  It's what the in-tree test drivers
>> (pg_regress, PostgresNode.pm) do.
>>
>
> I have done that, I will put out a new release probably right after the
> CF closes.
>
>
> I think we also need to change vcregress.pl to use trust explicitly for
> upgrade checks, just like the Unix upgrade test script does. That should
> help to future-proof us a bit.
>
>

Here's a patch along those lines that pretty much syncs up
vcregress.pl's initdb with pg_upgrade's test.sh.


cheers


andrew


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

On 2019-07-22 19:40, Andres Freund wrote:
> On 2019-07-22 13:02:13 -0400, Andrew Dunstan wrote:
>> There are a few things we could do. We could force trust auth, or we
>> could add an ident map that allowed $USER to login as buildfarm. Finding
>> all the places we would need to fix that could be a fun project ...
> 
> Perhaps we could actually do so automatically when the initdb invoking
> user isn't the same as the OS user? Imo that'd be generally quite
> useful, and not just for the regression tets.

It seems to me that there is something missing in our client
authentication system here.

If I'm logged in as the OS user that owns the data directory, I should
be able to log in to the database system via local socket as any user.
Because why stop me?  I can just change pg_hba.conf to let me in.

That would also address this problem that when you use the initdb -U
option, the proposed default "peer" setting doesn't help you much.
Making a pg_ident.conf map automatically helps for that particular user
combination, but then not for other users.  (There is no "sameuser plus
these additional mappings".)

I think we could just define that if geteuid == getpeereid, then
authentication succeeds.  Possibly make that a setting if someone wants
to turn it off.

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

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> If I'm logged in as the OS user that owns the data directory, I should
> be able to log in to the database system via local socket as any user.
> Because why stop me?  I can just change pg_hba.conf to let me in.

Hmm ... there's probably some minor loss of safety there, but not
much, as you say.

> I think we could just define that if geteuid == getpeereid, then
> authentication succeeds.  Possibly make that a setting if someone wants
> to turn it off.

We would still need to make the proposed buildfarm changes, though,
because Windows.  (And HPUX, though if it were the only holdout
maybe we could consider blowing it off.)

I'm not that excited about weakening our authentication rules
just to make things easier for the buildfarm.

It's possible that what you suggest is a good idea anyway to reduce
the user impact of switching from trust to peer as default auth.
However, I'm a little worried that we'll start getting a lot of "it
works in psql but I can't connect via JDBC-or-whatever" complaints.
So I dunno if it will really make things easier for users.

            regards, tom lane

On 2019-07-24 22:18, Tom Lane wrote:
>> I think we could just define that if geteuid == getpeereid, then
>> authentication succeeds.  Possibly make that a setting if someone wants
>> to turn it off.
> 
> We would still need to make the proposed buildfarm changes, though,
> because Windows.  (And HPUX, though if it were the only holdout
> maybe we could consider blowing it off.)
> 
> I'm not that excited about weakening our authentication rules
> just to make things easier for the buildfarm.

Yes, this idea is separate from those buildfarm changes.

> It's possible that what you suggest is a good idea anyway to reduce
> the user impact of switching from trust to peer as default auth.
> However, I'm a little worried that we'll start getting a lot of "it
> works in psql but I can't connect via JDBC-or-whatever" complaints.

Well, the existence of "local" vs. "host" already has that effect anyway.

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