Thread: [HACKERS] Enabling replication connections by default in pg_hba.conf
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
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
On Thu, Feb 2, 2017 at 6:08 AM, Peter Eisentraut <peter.eisentraut@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.
On 23 January 2017 at 04:29, Michael Paquier <michael.paquier@gmail.com> 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 Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 02/02/17 14:32, Simon Riggs wrote: > On 23 January 2017 at 04:29, Michael Paquier <michael.paquier@gmail.com> 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
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
On 2 February 2017 at 18:48, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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 Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 2 February 2017 at 18:48, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> 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
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
On 2/3/17 17:47, Michael Paquier wrote: > On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs <simon@2ndquadrant.com> 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 <https://www.postgresql.org/message-id/7a33990f-75b1-5a4f-e7c0-223e15b84c11@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. 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
On Thursday, March 2, 2017, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On 2/3/17 17:47, Michael Paquier wrote:
> On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs <simon@2ndquadrant.com> 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
<https://www.postgresql.org/message-id/7a33990f-75b1-5a4f- e7c0-223e15b84c11@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/
On Sat, Mar 4, 2017 at 9:47 AM, Magnus Hagander <magnus@hagander.net> wrote: > On Thursday, March 2, 2017, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: >> >> On 2/3/17 17:47, Michael Paquier wrote: >> > On Fri, Feb 3, 2017 at 4:59 AM, Simon Riggs <simon@2ndquadrant.com> >> > 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 >> >> <https://www.postgresql.org/message-id/7a33990f-75b1-5a4f-e7c0-223e15b84c11@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. 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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Tue, Mar 7, 2017 at 5:03 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Wed, Mar 8, 2017 at 11:29 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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 <filename>pg_hba.conf</> (<literal>host</literal> + and <literal>local</literal> lines). <command>initdb</command> will + prepopulate <filename>pg_hba.conf</filename> 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
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
On Thu, Mar 9, 2017 at 10:43 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 3/8/17 02:34, Michael Paquier wrote: >> This patch looks good to me. > > committed Thanks. -- Michael