Re: initdb recommendations

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
52 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
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


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Heikki Linnakangas
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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
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


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Noah Misch-2
In reply to this post by Magnus Hagander-2
On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:

> On Thu, May 23, 2019, 18:54 Peter Eisentraut <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Magnus Hagander-2
In reply to this post by Noah Misch-2
On Fri, May 24, 2019 at 11:24 AM Noah Misch <[hidden email]> wrote:
On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> On Thu, May 23, 2019, 18:54 Peter Eisentraut <[hidden email]> 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.

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.

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).

--
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Noah Misch-2
On Tue, May 28, 2019 at 12:15:35PM -0400, Magnus Hagander wrote:

> On Fri, May 24, 2019 at 11:24 AM Noah Misch <[hidden email]> wrote:
> > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > On Thu, May 23, 2019, 18:54 Peter Eisentraut <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Noah Misch ([hidden email]) 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 <[hidden email]> wrote:
> > > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > > On Thu, May 23, 2019, 18:54 Peter Eisentraut <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Peter Eisentraut-6
In reply to this post by Peter Eisentraut-6
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

v1-0001-initdb-Change-authentication-defaults.patch (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Julien Rouhaud
On Tue, Jun 18, 2019 at 10:33 PM Peter Eisentraut
<[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

David Fetter
On Thu, Jul 11, 2019 at 09:34:25PM +0200, Julien Rouhaud wrote:

> On Tue, Jun 18, 2019 at 10:33 PM Peter Eisentraut
> <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Peter Eisentraut-6
In reply to this post by Julien Rouhaud
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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Julien Rouhaud
On Sat, Jul 13, 2019 at 2:44 PM Peter Eisentraut
<[hidden email]> 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

initdb_doc.diff (1000 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Peter Eisentraut-6
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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Tom Lane-2
Peter Eisentraut <[hidden email]> writes:
> Pushed with that note.  Thanks.

This has completely broken the buildfarm.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Tom Lane-2
I wrote:
> Peter Eisentraut <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Andrew Dunstan-8
In reply to this post by Tom Lane-2

On 7/22/19 12:25 PM, Tom Lane wrote:

> I wrote:
>> Peter Eisentraut <[hidden email]> 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 <[hidden email]>
    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




Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Andres Freund
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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Tom Lane-2
In reply to this post by Tom Lane-2
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


123