Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-09 Thread Michael Paquier 
On Thu, Mar 9, 2017 at 10:43 PM, Peter Eisentraut
 wrote:
> On 3/8/17 02:34, Michael Paquier wrote:
>> This patch looks good to me.
>
> committed

Thanks.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-09 Thread Peter Eisentraut 
On 3/8/17 02:34, Michael Paquier wrote:
> This patch looks good to me.

committed

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-07 Thread Michael Paquier 
On Wed, Mar 8, 2017 at 11:29 AM, Peter Eisentraut
 wrote:
> On 3/6/17 21:11, Michael Paquier wrote:
>> I think that the documentation of initdb should mention that
>> pg_hba.conf entries are configured for replication connections as
>> well, something like a sentence in the Description paragraph:
>> initdb sets pg_hba.conf entries using the specified authentication
>> method (trust by default) for non-replication as well as replication
>> connections.
>
> OK, I was looking for a way to document this.  Your ideas seems the most
> sensible.
>
> See attached patch that puts it all together.

Thanks for the new version.

+This option specifies the default authentication method for local
+users used in pg_hba.conf (host
+and local lines).  initdb will
+prepopulate pg_hba.conf entries using the
+specified authentication method for non-replication as well as
+replication connections.
Fine for me with a paragraph here.

This patch looks good to me.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-07 Thread Peter Eisentraut 
On 3/6/17 21:11, Michael Paquier wrote:
> I think that the documentation of initdb should mention that
> pg_hba.conf entries are configured for replication connections as
> well, something like a sentence in the Description paragraph:
> initdb sets pg_hba.conf entries using the specified authentication
> method (trust by default) for non-replication as well as replication
> connections.

OK, I was looking for a way to document this.  Your ideas seems the most
sensible.

See attached patch that puts it all together.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 43fe1c6426f541767749989ccc14cbe056f25b35 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Tue, 7 Mar 2017 21:07:07 -0500
Subject: [PATCH v2] Enable replication connections by default in pg_hba.conf

---
 doc/src/sgml/ref/initdb.sgml | 16 +++-
 src/backend/libpq/pg_hba.conf.sample |  6 +++---
 src/bin/initdb/initdb.c  |  5 -
 src/bin/pg_basebackup/t/010_pg_basebackup.pl |  7 ++-
 src/test/perl/PostgresNode.pm| 19 ++-
 5 files changed, 18 insertions(+), 35 deletions(-)

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index 1aaa4901af..d9faa96021 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -120,11 +120,17 @@ Options
   --auth=authmethod
   

-This option specifies the authentication method for local users used
-in pg_hba.conf (host
-and local lines).  Do not use trust
-unless you trust all local users on your system.  trust is
-the default for ease of installation.
+This option specifies the default authentication method for local
+users used in pg_hba.conf (host
+and local lines).  initdb will
+prepopulate pg_hba.conf entries using the
+specified authentication method for non-replication as well as
+replication connections.
+   
+
+   
+Do not use trust unless you trust all local users on your
+system.  trust is the default for ease of installation.

   
  
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index e0fbfcb026..507278b2cc 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -84,6 +84,6 @@ hostall all 127.0.0.1/32@authmethodhost@
 hostall all ::1/128 @authmethodhost@
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
-@remove-line-for-nolocal@#local   replication @default_username@@authmethodlocal@
-#hostreplication @default_username@127.0.0.1/32@authmethodhost@
-#hostreplication @default_username@::1/128 @authmethodhost@
+@remove-line-for-nolocal@local   replication all @authmethodlocal@
+hostreplication all 127.0.0.1/32@authmethodhost@
+hostreplication all ::1/128 @authmethodhost@
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 1ed0d20504..ea7b47e26d 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1227,11 +1227,6 @@ setup_config(void)
 			  "@authcomment@",
 			  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
 
-	/* Replace username for replication */
-	conflines = replace_token(conflines,
-			  "@default_username@",
-			  username);
-
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
 	writefile(path, conflines);
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index aafb138fd5..14bd813896 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -4,7 +4,7 @@
 use Config;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 73;
+use Test::More tests => 72;
 
 program_help_ok('pg_basebackup');
 program_version_ok('pg_basebackup');
@@ -15,15 +15,12 @@
 my $node = get_new_node('main');
 
 # Initialize node without replication settings
-$node->init(hba_permit_replication => 0);
+$node->init;
 $node->start;
 my $pgdata = $node->data_dir;
 
 $node->command_fails(['pg_basebackup'],
 	'pg_basebackup needs target directory specified');
-$node->command_fails(
-	[ 'pg_basebackup', '-D', "$tempdir/backup" ],
-	'pg_basebackup fails because of hba');
 
 # Some Windows ANSI code pages may reject this filename, in which case we
 # quietly proceed without this bit of test coverage.
diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index e5cb348f4c..7e530676b2 100644
---

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-06 Thread Michael Paquier 
On Tue, Mar 7, 2017 at 5:03 AM, Peter Eisentraut
 wrote:
> On 3/3/17 20:30, Michael Paquier wrote:
>> Yeah, it looks sensible to me to keep "replication" for physical
>> replication, and switch logical replication checks to match a database
>> name in hba comparisons.
>
> I think we are OK to move ahead with this.
>
> Another question would be why only enable connections for
> @default_username@ by default, instead of all.

That would make sense as well.

> Also, with this change, some test code that sets up pg_hba.conf for
> replication can be removed.  See attached patch.

Indeed.

I think that the documentation of initdb should mention that
pg_hba.conf entries are configured for replication connections as
well, something like a sentence in the Description paragraph:
initdb sets pg_hba.conf entries using the specified authentication
method (trust by default) for non-replication as well as replication
connections.

What do you think?
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-06 Thread Peter Eisentraut 
On 3/3/17 20:30, Michael Paquier wrote:
> Yeah, it looks sensible to me to keep "replication" for physical
> replication, and switch logical replication checks to match a database
> name in hba comparisons.

I think we are OK to move ahead with this.

Another question would be why only enable connections for
@default_username@ by default, instead of all.

Also, with this change, some test code that sets up pg_hba.conf for
replication can be removed.  See attached patch.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 6f1c79dd34d67bf36a317d454eb6e6349598a97d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Mon, 6 Mar 2017 14:53:27 -0500
Subject: [PATCH] Enable replication connections by default in pg_hba.conf

---
 src/backend/libpq/pg_hba.conf.sample |  6 +++---
 src/bin/pg_basebackup/t/010_pg_basebackup.pl |  7 ++-
 src/test/perl/PostgresNode.pm| 19 ++-
 3 files changed, 7 insertions(+), 25 deletions(-)

diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index e0fbfcb026..b0852c82c0 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -84,6 +84,6 @@ hostall all 127.0.0.1/32@authmethodhost@
 hostall all ::1/128 @authmethodhost@
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
-@remove-line-for-nolocal@#local   replication @default_username@@authmethodlocal@
-#hostreplication @default_username@127.0.0.1/32@authmethodhost@
-#hostreplication @default_username@::1/128 @authmethodhost@
+@remove-line-for-nolocal@local   replication @default_username@@authmethodlocal@
+hostreplication @default_username@127.0.0.1/32@authmethodhost@
+hostreplication @default_username@::1/128 @authmethodhost@
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index aafb138fd5..14bd813896 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -4,7 +4,7 @@
 use Config;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 73;
+use Test::More tests => 72;
 
 program_help_ok('pg_basebackup');
 program_version_ok('pg_basebackup');
@@ -15,15 +15,12 @@
 my $node = get_new_node('main');
 
 # Initialize node without replication settings
-$node->init(hba_permit_replication => 0);
+$node->init;
 $node->start;
 my $pgdata = $node->data_dir;
 
 $node->command_fails(['pg_basebackup'],
 	'pg_basebackup needs target directory specified');
-$node->command_fails(
-	[ 'pg_basebackup', '-D', "$tempdir/backup" ],
-	'pg_basebackup fails because of hba');
 
 # Some Windows ANSI code pages may reject this filename, in which case we
 # quietly proceed without this bit of test coverage.
diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index e5cb348f4c..7e530676b2 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -349,11 +349,7 @@ sub set_replication_conf
 
 	open my $hba, ">>$pgdata/pg_hba.conf";
 	print $hba "\n# Allow replication (set up by PostgresNode.pm)\n";
-	if (!$TestLib::windows_os)
-	{
-		print $hba "local replication all trust\n";
-	}
-	else
+	if ($TestLib::windows_os)
 	{
 		print $hba
 "host replication all $test_localhost/32 sspi include_realm=1 map=regress\n";
@@ -373,9 +369,6 @@ a directory that's only accessible to the current user to ensure that.
 On Windows, we use SSPI authentication to ensure the same (by pg_regress
 --config-auth).
 
-pg_hba.conf is configured to allow replication connections. Pass the keyword
-parameter hba_permit_replication => 0 to disable this.
-
 WAL archiving can be enabled on this node by passing the keyword parameter
 has_archiving => 1. This is disabled by default.
 
@@ -396,8 +389,6 @@ sub init
 	my $pgdata = $self->data_dir;
 	my $host   = $self->host;
 
-	$params{hba_permit_replication} = 1
-	  unless defined $params{hba_permit_replication};
 	$params{allows_streaming} = 0 unless defined $params{allows_streaming};
 	$params{has_archiving}= 0 unless defined $params{has_archiving};
 
@@ -451,7 +442,7 @@ sub init
 	}
 	close $conf;
 
-	$self->set_replication_conf if $params{hba_permit_replication};
+	$self->set_replication_conf if $params{allows_streaming};
 	$self->enable_archiving if $params{has_archiving};
 }
 
@@ -591,9 +582,6 @@ Does not start the node after initializing it.
 
 A recovery.conf is not created.
 
-pg_hba.conf is configured to allow replication connections. Pass the keyword
-parameter hba_permit_replication => 0 to disable this.
-
 Streaming replication can be

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-03 Thread Michael Paquier 
On Sat, Mar 4, 2017 at 9:47 AM, Magnus Hagander  wrote:
> On Thursday, March 2, 2017, Peter Eisentraut
>  wrote:
>>
>> On 2/3/17 17:47, Michael Paquier wrote:
>> > On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs 
>> > wrote:
>> >>> It's weirdly inconsistent now.  You need a "replication" line in
>> >>> pg_hba.conf to connect for logical decoding, but you can't restrict
>> >>> that
>> >>> to a specific database because the database column in pg_hba.conf is
>> >>> occupied by the "replication" key word.
>> >> Agreed. Change needed.
>> > That sounds really apealling indeed after thinking about its
>> > implications. So we would simply authorize a WAL sender sending
>> > "replication" to connect if the user name matches. That's in short
>> > check_db() in hba.c.
>>
>> In
>>
>> 
>> patch 0006 it is proposed to no longer use the "replication" keyword in
>> pg_hba.conf for logical
>> replication and use the normal database entries instead.
>>
>> However, I don't think we can reasonably get rid of the replication
>> keyword for physical replication.  Say if you have a pg_hba.conf like
>>
>> host  db1  someusers  ...
>> host  db2  someusers  ...
>> host  db3  someusers  ...
>>
>> how would you decide access for physical replication?  Since physical
>> replication is not to a database, you need a way to call it out
>> separately if your pg_hba.conf style is to enumerate databases.
>
> That's the reason we created the "replication" keyword in the first place,
> isn't it? I think it makes sense to keep that, but it also makes sense to
> not use it for logical.

Yeah, it looks sensible to me to keep "replication" for physical
replication, and switch logical replication checks to match a database
name in hba comparisons.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-03 Thread Magnus Hagander 
On Thursday, March 2, 2017, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 2/3/17 17:47, Michael Paquier wrote:
> > On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs  > wrote:
> >>> It's weirdly inconsistent now.  You need a "replication" line in
> >>> pg_hba.conf to connect for logical decoding, but you can't restrict
> that
> >>> to a specific database because the database column in pg_hba.conf is
> >>> occupied by the "replication" key word.
> >> Agreed. Change needed.
> > That sounds really apealling indeed after thinking about its
> > implications. So we would simply authorize a WAL sender sending
> > "replication" to connect if the user name matches. That's in short
> > check_db() in hba.c.
>
> In
>  e7c0-223e15b84...@2ndquadrant.com>
> patch 0006 it is proposed to no longer use the "replication" keyword in
> pg_hba.conf for logical
> replication and use the normal database entries instead.
>
> However, I don't think we can reasonably get rid of the replication
> keyword for physical replication.  Say if you have a pg_hba.conf like
>
> host  db1  someusers  ...
> host  db2  someusers  ...
> host  db3  someusers  ...
>
> how would you decide access for physical replication?  Since physical
> replication is not to a database, you need a way to call it out
> separately if your pg_hba.conf style is to enumerate databases.
>

That's the reason we created the "replication" keyword in the first place,
isn't it? I think it makes sense to keep that, but it also makes sense to
not use it for logical.


What we could do to make things simpler is to include "replication" in
> the "all" keyword.
>

The downside of that is that if somebody isn't reading the release notes
carefully, or just applying some templates from their puppet or chef or
whatnot, they may end up opening their database up for replication for
users and IP addresses they did not intend to. (For users it's probably not
an issue thanks to the replication privilege at the user level, but for IP
addresses it definitely does)

//Magnus



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

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-03-02 Thread Peter Eisentraut 
On 2/3/17 17:47, Michael Paquier wrote:
> On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs  wrote:
>>> It's weirdly inconsistent now.  You need a "replication" line in
>>> pg_hba.conf to connect for logical decoding, but you can't restrict that
>>> to a specific database because the database column in pg_hba.conf is
>>> occupied by the "replication" key word.
>> Agreed. Change needed.
> That sounds really apealling indeed after thinking about its
> implications. So we would simply authorize a WAL sender sending
> "replication" to connect if the user name matches. That's in short
> check_db() in hba.c.

In

patch 0006 it is proposed to no longer use the "replication" keyword in
pg_hba.conf for logical
replication and use the normal database entries instead.

However, I don't think we can reasonably get rid of the replication
keyword for physical replication.  Say if you have a pg_hba.conf like

host  db1  someusers  ...
host  db2  someusers  ...
host  db3  someusers  ...

how would you decide access for physical replication?  Since physical
replication is not to a database, you need a way to call it out
separately if your pg_hba.conf style is to enumerate databases.

What we could do to make things simpler is to include "replication" in
the "all" keyword.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-06 Thread Peter Eisentraut 
On 2/2/17 2:59 PM, Simon Riggs wrote:
> We currently have REPLICATION to mean "physical replication".

Well, it doesn't really mean that.  The same facilities are used to
control access to both logical and physical replication.

> I think if we have another capability for logical replication then we
> would be able to easily allow one or the other, or both, so I don't
> see a problem here that forces us to keep pg_hba.conf the way it is.

I think change is possible and possibly desirable.  One issue is that we
might need to adjust dump/restore so that it keeps existing usages
working.  Details depend on the actual changes, of course.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-03 Thread Michael Paquier 
On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs  wrote:
> On 2 February 2017 at 18:48, Peter Eisentraut
>  wrote:
>> On 2/2/17 8:32 AM, Simon Riggs wrote:
>>> I think we should remove the "replication" false database concept in
>>> pg_hba.conf altogether and allow any valid pg_hba rule to invoke a
>>> replication connection, if one is requested. Roles would still need
>>> the REPLICATION capability before this would be allowed. Having both
>>> of those things doesn't materially improve security control.
>>
>> It's weirdly inconsistent now.  You need a "replication" line in
>> pg_hba.conf to connect for logical decoding, but you can't restrict that
>> to a specific database because the database column in pg_hba.conf is
>> occupied by the "replication" key word.
>
> Agreed. Change needed.

That sounds really apealling indeed after thinking about its
implications. So we would simply authorize a WAL sender sending
"replication" to connect if the user name matches. That's in short
check_db() in hba.c.

>>> It would also be useful to be able prevent users with REPLICATION
>>> capability from connecting as normal users, if the are marked as
>>> NOLOGIN.
>>
>> That sounds useful.
>>
>> (Superusers not have the replication attribute by default is an
>> additional possible annoyance.)

Replication users are doing logins when requesting an access, that
seems inconsistent to me. And there are applications not defining a
replication role with NOLOGIN and still connecting via psql to perform
some sanity checks.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-02 Thread Simon Riggs 
On 2 February 2017 at 18:48, Peter Eisentraut
 wrote:
> On 2/2/17 8:32 AM, Simon Riggs wrote:
>> I think we should remove the "replication" false database concept in
>> pg_hba.conf altogether and allow any valid pg_hba rule to invoke a
>> replication connection, if one is requested. Roles would still need
>> the REPLICATION capability before this would be allowed. Having both
>> of those things doesn't materially improve security control.
>
> It's weirdly inconsistent now.  You need a "replication" line in
> pg_hba.conf to connect for logical decoding, but you can't restrict that
> to a specific database because the database column in pg_hba.conf is
> occupied by the "replication" key word.

Agreed. Change needed.

> However, you would still want a way to configure a user for logical
> decoding for any database but no physical replication, or vice versa.
> Just getting rid of the replication key word would prevent that, I think.

We currently have REPLICATION to mean "physical replication".

I think if we have another capability for logical replication then we
would be able to easily allow one or the other, or both, so I don't
see a problem here that forces us to keep pg_hba.conf the way it is.

>> It would also be useful to be able prevent users with REPLICATION
>> capability from connecting as normal users, if the are marked as
>> NOLOGIN.
>
> That sounds useful.
>
> (Superusers not have the replication attribute by default is an
> additional possible annoyance.)

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-02 Thread Peter Eisentraut 
On 2/2/17 8:32 AM, Simon Riggs wrote:
> I think we should remove the "replication" false database concept in
> pg_hba.conf altogether and allow any valid pg_hba rule to invoke a
> replication connection, if one is requested. Roles would still need
> the REPLICATION capability before this would be allowed. Having both
> of those things doesn't materially improve security control.

It's weirdly inconsistent now.  You need a "replication" line in
pg_hba.conf to connect for logical decoding, but you can't restrict that
to a specific database because the database column in pg_hba.conf is
occupied by the "replication" key word.

However, you would still want a way to configure a user for logical
decoding for any database but no physical replication, or vice versa.
Just getting rid of the replication key word would prevent that, I think.

> It would also be useful to be able prevent users with REPLICATION
> capability from connecting as normal users, if the are marked as
> NOLOGIN.

That sounds useful.

(Superusers not have the replication attribute by default is an
additional possible annoyance.)

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-02 Thread Petr Jelinek 
On 02/02/17 14:32, Simon Riggs wrote:
> On 23 January 2017 at 04:29, Michael Paquier  
> wrote:
>> Hi all,
>>
>> As now wal_level = replica has become the default for Postgres 10,
>> could we consider as well making replication connections enabled by
>> default in pg_hba.conf?
> 
> Agreed
> 
>> This requires just uncommenting a couple of
>> lines in pg_hba.conf.sample.
> 
> I don't think that is the right way to do this. Changing the default
> doesn't reduce the complexity.
> 
> I think we should remove the "replication" false database concept in
> pg_hba.conf altogether and allow any valid pg_hba rule to invoke a
> replication connection, if one is requested. Roles would still need
> the REPLICATION capability before this would be allowed. Having both
> of those things doesn't materially improve security control.
> 

+1

> It would also be useful to be able prevent users with REPLICATION
> capability from connecting as normal users, if the are marked as
> NOLOGIN.
> 

+1

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-02 Thread Simon Riggs 
On 23 January 2017 at 04:29, Michael Paquier  wrote:
> Hi all,
>
> As now wal_level = replica has become the default for Postgres 10,
> could we consider as well making replication connections enabled by
> default in pg_hba.conf?

Agreed

> This requires just uncommenting a couple of
> lines in pg_hba.conf.sample.

I don't think that is the right way to do this. Changing the default
doesn't reduce the complexity.

I think we should remove the "replication" false database concept in
pg_hba.conf altogether and allow any valid pg_hba rule to invoke a
replication connection, if one is requested. Roles would still need
the REPLICATION capability before this would be allowed. Having both
of those things doesn't materially improve security control.

It would also be useful to be able prevent users with REPLICATION
capability from connecting as normal users, if the are marked as
NOLOGIN.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-02 Thread Magnus Hagander 
On Thu, Feb 2, 2017 at 6:08 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 1/22/17 10:29 PM, Michael Paquier wrote:
> > As now wal_level = replica has become the default for Postgres 10,
> > could we consider as well making replication connections enabled by
> > default in pg_hba.conf? This requires just uncommenting a couple of
> > lines in pg_hba.conf.sample.
>
> Yes, I think this makes sense, as one of the reason for these changes is
> to simplify the use of pg_basebackup.
>

+1.


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

Re: [HACKERS] Enabling replication connections by default in pg_hba.conf

2017-02-01 Thread Peter Eisentraut 
On 1/22/17 10:29 PM, Michael Paquier wrote:
> As now wal_level = replica has become the default for Postgres 10,
> could we consider as well making replication connections enabled by
> default in pg_hba.conf? This requires just uncommenting a couple of
> lines in pg_hba.conf.sample.

Yes, I think this makes sense, as one of the reason for these changes is
to simplify the use of pg_basebackup.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Enabling replication connections by default in pg_hba.conf

2017-01-22 Thread Michael Paquier 
Hi all,

As now wal_level = replica has become the default for Postgres 10,
could we consider as well making replication connections enabled by
default in pg_hba.conf? This requires just uncommenting a couple of
lines in pg_hba.conf.sample.
Thanks,
-- 
Michael


pghba_rep_default.patch
Description: application/download

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers