Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Tom Lane]

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

Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Andrew Dunstan]

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

diff --git a/src/tools/msvc/vcregress.pl b/src/tools/msvc/vcregress.pl
index 5495066b4d..05446c3f59 100644
--- a/src/tools/msvc/vcregress.pl
+++ b/src/tools/msvc/vcregress.pl
@@ -496,12 +496,28 @@ sub recoverycheck
 }
 
 # Run "initdb", then reconfigure authentication.
+# mimics what is done in src/bin/pg_upgrade/test.sh:standard_initdb()
 sub standard_initdb
 {
-	return (
-		system('initdb', '-N') == 0 and system(
-			"$topdir/$Config/pg_regress/pg_regress", '--config-auth',
-			$ENV{PGDATA}) == 0);
+	my @opts = qw(-N --wal-segsize 1 -g -A trust);
+	my $status = system('initdb',@opts);
+	if ($status == 0)
+	{
+		if (defined($ENV{TEMP_CONFIG}) && -r $ENV{TEMP_CONFIG})
+		{
+			open(my $handle, '<', $ENV{TEMP_CONFIG})
+			  || die "file $ENV{TEMP_CONFIG} not found";
+			my $contents = <$handle>;
+			close($handle);
+			open($handle, '>>', "$ENV{PGDATA}/postgresql.conf")
+			  || die "file $ENV{PGDATA}/postgresql.conf not found";
+			print $handle $contents;
+			close($handle);
+		}
+		$status = system("$topdir/$Config/pg_regress/pg_regress",
+		 '--config-auth', $ENV{PGDATA})
+	}
+	return $status;
 }
 
 # This is similar to appendShellString().  Perl system(@args) bypasses

Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Andrew Dunstan]

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

Re: initdb recommendations

[Andrew Dunstan]

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

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

Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Jonathan S. Katz]

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



signature.asc
Description: OpenPGP digital signature

Re: initdb recommendations

[Tom Lane]

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

Re: initdb recommendations

[Andrew Dunstan]

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

Re: initdb recommendations

[Andrew Dunstan]

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

Re: initdb recommendations

[Tom Lane]

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=2019-07-22%2017%3A08%3A27

Re: initdb recommendations

[Andrew Dunstan]

On 7/22/19 12:25 PM, Tom Lane wrote:
> I wrote:
>> Peter Eisentraut  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 
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 Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: initdb recommendations

[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

Re: initdb recommendations

[Tom Lane]

I wrote:
> Peter Eisentraut  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

Re: initdb recommendations

[Tom Lane]

Peter Eisentraut  writes:
> Pushed with that note.  Thanks.

This has completely broken the buildfarm.

regards, tom lane

Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Julien Rouhaud]

On Sat, Jul 13, 2019 at 2:44 PM Peter Eisentraut
 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.


+   
+
+ Running initdb without arguments on platforms lacking
+ peer 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.
+
+   
+

 Do not use
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.

 
+   
+
+ Running initdb without arguments on platforms lacking
+ peer 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.
+
+   
+

 Do not use trust unless you trust all local users on your
 system.

Re: initdb recommendations

[Peter Eisentraut]

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

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
>  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  http://fetter.org/
Phone: +1 415 235 3778

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

Re: initdb recommendations

[Julien Rouhaud]

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

Re: initdb recommendations

[Peter Eisentraut]

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
From 88de6f226233bb183dc75ee047501fae5051e287 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Tue, 18 Jun 2019 22:20:23 +0200
Subject: [PATCH v1] initdb: Change authentication defaults

Change the defaults for the pg_hba.conf generated by initdb to "peer"
for local (if supported, else "md5") and "md5" for host.

(Changing from "md5" to SCRAM is left as a separate exercise.)

"peer" is currently not supported on AIX, HP-UX, and Windows.  Users
on those operating systems will now either have to provide a password
to initdb or choose a different authentication method when running
initdb.

Discussion: 
https://www.postgresql.org/message-id/flat/bec17f0a-ddb1-8b95-5e69-368d9d0a3390%40postgresql.org
---
 doc/src/sgml/ref/initdb.sgml|  9 -
 doc/src/sgml/runtime.sgml   | 23 +
 doc/src/sgml/standalone-install.xml |  9 -
 src/bin/initdb/initdb.c | 31 ++---
 src/include/port.h  |  5 +
 src/test/regress/pg_regress.c   |  2 +-
 6 files changed, 33 insertions(+), 46 deletions(-)

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index 7fc3152c6d..c47b9139eb 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -136,9 +136,16 @@ Options
 replication connections.

 
+   
+The default is peer for Unix-domain socket
+connections on operating systems that support it, otherwise
+md5, and md5 for TCP/IP
+connections.
+   
+

 Do not use trust unless you trust all local users 
on your
-system.  trust is the default for ease of 
installation.
+system.

   
  
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 365ec75aad..305698aa0e 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -156,24 +156,19 @@ Creating a Database Cluster
   
 
   
-   However, while the directory contents are secure, the default
-   client authentication setup allows any local user to connect to the
-   database and even become the database superuser. If you do not
-   trust other local users, we recommend you use one of
+   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 to assign a password to the
-   database superuser.
+   or --pwfile options.
  password
  of the superuser

-   Also, specify -A md5 or
-   -A password so that the default trust 
authentication
-   mode is not used; or modify the generated pg_hba.conf
-   file after running initdb, but
-   before you start the server for the first time. (Other
-   reasonable approaches include using peer authentication
-   or file system permissions to restrict connections. See  for more information.)
+   This configuration is secure and sufficient to get started.  Later, see
+for more information about setting
+   up client authentication.
   
 
   
diff --git a/doc/src/sgml/standalone-install.xml 
b/doc/src/sgml/standalone-install.xml
index f584789f9a..749a071061 100644
--- a/doc/src/sgml/standalone-install.xml
+++ b/doc/src/sgml/standalone-install.xml
@@ -63,15 +63,6 @@ Getting Started
 

 
-   
-
- At this point, if you did not use the initdb 
-A
- option, you might want to modify pg_hba.conf to 
control
- local access to the server before you start it.  The default is to
- trust all local users.
-
-   
-

 
  The previous initdb step should have told you how to
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ad5cd4194a..fd69c5c95e 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -185,7 +185,6 @@ static const char *default_timezone = NULL;
 "# allows any local user to connect as any PostgreSQL user, including\n" \
 "# the database superuser.  If you do 

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Noah Misch (n...@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  wrote:
> > > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > > On Thu, May 23, 2019, 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.
> > > > >
> > > > > 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
Description: PGP signature

Re: initdb recommendations

[Noah Misch]

On Tue, May 28, 2019 at 12:15:35PM -0400, Magnus Hagander wrote:
> On Fri, May 24, 2019 at 11:24 AM Noah Misch  wrote:
> > On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > > On Thu, May 23, 2019, 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.
> > > >
> > > > 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.

Re: initdb recommendations

[Magnus Hagander]

On Fri, May 24, 2019 at 11:24 AM Noah Misch  wrote:

> On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> > On Thu, May 23, 2019, 18:54 Peter Eisentraut <
> peter.eisentr...@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.

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

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

Re: initdb recommendations

[Michael Paquier]

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
Description: PGP signature

Re: initdb recommendations

[Noah Misch]

On Thu, May 23, 2019 at 06:56:49PM +0200, Magnus Hagander wrote:
> On Thu, May 23, 2019, 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.
> >
> > 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.

Re: initdb recommendations

[Jonathan S. Katz]

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
Description: OpenPGP digital signature

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

Re: initdb recommendations

[Jonathan S. Katz]

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
Description: OpenPGP digital signature

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Heikki Linnakangas (hlinn...@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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Heikki Linnakangas]

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

Re: initdb recommendations

[Jonathan S. Katz]

On 5/24/19 9:01 AM, Stephen Frost wrote:
> Greetings,
> 
> * Jonathan S. Katz (jk...@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



signature.asc
Description: OpenPGP digital signature

Re: initdb recommendations

[Joe Conway]

On 5/24/19 8:56 AM, Jonathan S. Katz wrote:
> On 5/24/19 8:33 AM, Stephen Frost wrote:
>> * Magnus Hagander (mag...@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

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Jonathan S. Katz (jk...@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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Jonathan S. Katz]

On 5/24/19 8:33 AM, Stephen Frost wrote:
> Greetings,
> 
> * Magnus Hagander (mag...@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



signature.asc
Description: OpenPGP digital signature

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Magnus Hagander (mag...@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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Magnus Hagander]

On Fri, May 24, 2019 at 2:19 PM Stephen Frost  wrote:

> Greetings,
>
> * Joe Conway (m...@joeconway.com) wrote:
> > On 5/24/19 8:13 AM, Stephen Frost wrote:
> > > * Joe Conway (m...@joeconway.com) wrote:
> > >> On 5/23/19 10:30 PM, Stephen Frost wrote:
> > >> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > >> >> "Jonathan S. Katz"  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.
>

I think changing away from "trust" is going to be a much smaller change
than people seem to worry about.

It will hit people *in the developer community*.

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).
But they have *already* stopped using trust many many years ago.

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

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

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

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Joe Conway (m...@joeconway.com) wrote:
> On 5/24/19 8:13 AM, Stephen Frost wrote:
> > * Joe Conway (m...@joeconway.com) wrote:
> >> On 5/23/19 10:30 PM, Stephen Frost wrote:
> >> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> >> "Jonathan S. Katz"  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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Joe Conway]

On 5/24/19 8:13 AM, Stephen Frost wrote:
> Greetings,
> 
> * Joe Conway (m...@joeconway.com) wrote:
>> On 5/23/19 10:30 PM, Stephen Frost wrote:
>> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> >> "Jonathan S. Katz"  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

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Joe Conway (m...@joeconway.com) wrote:
> On 5/23/19 10:30 PM, Stephen Frost wrote:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> "Jonathan S. Katz"  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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Peter Eisentraut]

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

Re: initdb recommendations

[Dave Cramer]

On Fri, 24 May 2019 at 07:48, Joe Conway  wrote:

> On 5/23/19 10:30 PM, Stephen Frost wrote:
> > Greetings,
> >
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> "Jonathan S. Katz"  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.
>
>
+many as well given the presumption that we are going to break existing
behaviour

Dave

Re: initdb recommendations

[Joe Conway]

On 5/23/19 10:30 PM, Stephen Frost wrote:
> Greetings,
> 
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> "Jonathan S. Katz"  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

Re: initdb recommendations

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> "Jonathan S. Katz"  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


signature.asc
Description: PGP signature

Re: initdb recommendations

[Tom Lane]

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

Re: initdb recommendations

[Jonathan S. Katz]

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
From d610f4e575b6ee634b94dc6cb9125c4dbaedc305 Mon Sep 17 00:00:00 2001
From: "Jonathan S. Katz" 
Date: Fri, 5 Apr 2019 12:02:40 -0400
Subject: [PATCH] Add a warning about the client authentication defaults that
 initdb provides.

This also provides advice on how to securely set up initial client connection
configurations, and removes the section that explains similar steps that is
below the directory setup. This information should be around where its explained
how initdb is first called, anyway.
---
 doc/src/sgml/runtime.sgml | 46 +-
 1 file changed, 25 insertions(+), 21 deletions(-)

diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index e053e2ee34..040aacf87f 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -85,6 +85,31 @@
described in the previous section.
   
 
+  
+
+  By default initdb sets up trust
+  client authentication for connecting to the database. This is not
+  recommended on multi-user systems where you do not trust all users, nor 
if
+  the database server will be made accessible to remote systems.
+
+
+  We recommend using the -W, --pwprompt,
+  or --pwfile flags to assign a password to the database
+  superuser, and to override the pg_hba.conf default
+  generation using -auth-local peer for local connections,
+  (except on Windows, use -auth-local scram-sha-256 as
+  peer authentication is not supported) and
+  -auth-host scram-sha-256 for remote connections. See
+   for more information on client
+  authentication methods.
+
+
+  If installing PostgreSQL from a distribution, we recommend you validate
+  your initially generated pg_hba.conf file to ensure
+  it meets your operational requirements.
+
+  
+
   

 As an alternative to the -D option, you can set
@@ -155,27 +180,6 @@ postgres$ initdb -D 
/usr/local/pgsql/data
for directories and 0640 for files.
   
 
-  
-   However, while the directory contents are secure, the default
-   client authentication setup allows any local user to connect to the
-   database and even become the database superuser. If you do not
-   trust other local users, we recommend you use one of
-   initdb's -W, --pwprompt
-   or --pwfile options to assign a password to the
-   database superuser.
- password
- of the superuser
-   
-   Also, specify -A md5 or
-   -A password so that the default trust 
authentication
-   mode is not used; or modify the generated pg_hba.conf
-   file after running initdb, but
-   before you start the server for the first time. (Other
-   reasonable approaches include using peer authentication
-   or file system permissions to restrict connections. See  for more information.)
-  
-
   
initdb also initializes the default
localelocale for the database 
cluster.
-- 
2.14.3 (Apple Git-98)



signature.asc
Description: OpenPGP digital signature

Re: initdb recommendations

[Jonathan S. Katz]

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



signature.asc
Description: OpenPGP digital signature

Re: initdb recommendations

[Magnus Hagander]

On Thu, May 23, 2019, 18:54 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> 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.
>
> 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).

/Magnus

Re: initdb recommendations

[Peter Eisentraut]

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