Adding an example for replication configuration to pg_hba.conf
I reviewed the process for configuring replication, and found that we
don't have an example for allowing replication access in pg_hba.conf.
Before we release 9.1, I think we should add this example to make it
more obvious this is a necessary part of replication configuration.
At the risk of starting an epic bikeshedding thread, I've attached a
small patch that includes an example for both ipv4 and ipv6 localhost
configuration.
-selena
Attachments:
pg_hba_bikeshed1.patchapplication/octet-stream; name=pg_hba_bikeshed1.patchDownload
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
new file mode 100644
index 0a90b68..7e19229
*** a/src/backend/libpq/pg_hba.conf.sample
--- b/src/backend/libpq/pg_hba.conf.sample
***************
*** 80,82 ****
--- 80,85 ----
host all all 127.0.0.1/32 @authmethod@
# IPv6 local connections:
host all all ::1/128 @authmethod@
+ # Allow replication connections to localhost, by a user with the replication privilege:
+ # host replication postgres 127.0.0.1/32 @authmethod@
+ # host replication postgres ::1/128 @authmethod@
Attachments:
pg_hba_bikeshed2.patchapplication/octet-stream; name=pg_hba_bikeshed2.patchDownload
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
new file mode 100644
index 0a90b68..91adfcb
*** a/src/backend/libpq/pg_hba.conf.sample
--- b/src/backend/libpq/pg_hba.conf.sample
***************
*** 80,82 ****
--- 80,85 ----
host all all 127.0.0.1/32 @authmethod@
# IPv6 local connections:
host all all ::1/128 @authmethod@
+ # Allow replication connections to localhost, by a user with the replication privilege:
+ # host replication postgres 127.0.0.1/32 @authmethod@
+ # host replication postgres ::1/128 @authmethod@
On Wed, May 18, 2011 at 12:17 PM, Selena Deckelmann <selena@chesnok.com> wrote:
At the risk of starting an epic bikeshedding thread, I've attached a
small patch that includes an example for both ipv4 and ipv6 localhost
configuration.
My "bikeshedding" would be to ensure that the sample pg_hba.conf
includes some kind of reference comment indicating where to look in
the documentation for more about this. Section numbers aren't stable
enough; section title is probably apropos.
The most relevant docs seem to be "Preparing the Master for Standby Servers"
http://www.postgresql.org/docs/9.1/static/warm-standby.html#PREPARING-MASTER-FOR-STANDBY
or
Streaming Replication Authentication
http://www.postgresql.org/docs/9.1/static/warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
On Wed, May 18, 2011 at 9:35 AM, Christopher Browne <cbbrowne@gmail.com> wrote:
On Wed, May 18, 2011 at 12:17 PM, Selena Deckelmann <selena@chesnok.com> wrote:
At the risk of starting an epic bikeshedding thread, I've attached a
small patch that includes an example for both ipv4 and ipv6 localhost
configuration.My "bikeshedding" would be to ensure that the sample pg_hba.conf
includes some kind of reference comment indicating where to look in
the documentation for more about this. Section numbers aren't stable
enough; section title is probably apropos.
We don't do this in any of the other configuration files, so while I
like the focus on helping users out, it would be a significant
departure from convention.
Creating an annotated config file in the wiki would be welcome. :)
-selena
Two things that could be changed from this example to make it more useful:
-Document at least a little bit more how this is different from the
"all/all" rule. I can imagine users wondering "do I use this instead of
the other one? In addition? Is it redundant if I have 'all' in there?
A little more description here aiming at the expected FAQs here would
make this much more useful.
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Excerpts from Greg Smith's message of mié may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.
You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? (In this case the patch is no longer
2 lines, but still should be trivial enough).
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, May 18, 2011 at 8:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Excerpts from Greg Smith's message of mié may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? (In this case the patch is no longer
2 lines, but still should be trivial enough).
That would be nice. So, we just add that token to initdb? Seems simple.
I added some explanation of the all vs replication bit in the header comments.
Revision attached.
-selena
Attachments:
pg_hba_bikeshed3.patchapplication/octet-stream; name=pg_hba_bikeshed3.patchDownload
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
new file mode 100644
index 91adfcb..03f76df
*** a/src/backend/libpq/pg_hba.conf.sample
--- b/src/backend/libpq/pg_hba.conf.sample
***************
*** 22,28 ****
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
! # database name, or a comma-separated list thereof.
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
--- 22,30 ----
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
! # database name, or a comma-separated list thereof. The "all"
! # keyword does not include "replication". Access to replication
! # must be enabled in a separate record (example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
***************
*** 80,85 ****
host all all 127.0.0.1/32 @authmethod@
# IPv6 local connections:
host all all ::1/128 @authmethod@
! # Allow replication connections to localhost, by a user with the replication privilege:
! # host replication postgres 127.0.0.1/32 @authmethod@
! # host replication postgres ::1/128 @authmethod@
--- 82,87 ----
host all all 127.0.0.1/32 @authmethod@
# IPv6 local connections:
host all all ::1/128 @authmethod@
! # Allow replication connections to localhost, by a user with the replication privilege.
! # host replication @default_username@ 127.0.0.1/32 @authmethod@
! # host replication @default_username@ ::1/128 @authmethod@
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
new file mode 100644
index 56a396b..4949af9
*** a/src/bin/initdb/initdb.c
--- b/src/bin/initdb/initdb.c
*************** setup_config(void)
*** 1086,1091 ****
--- 1086,1096 ----
"@authcomment@",
strcmp(authmethod, "trust") ? "" : 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);
On Thu, May 19, 2011 at 2:44 PM, Selena Deckelmann <selena@chesnok.com> wrote:
On Wed, May 18, 2011 at 8:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Greg Smith's message of mié may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? (In this case the patch is no longer
2 lines, but still should be trivial enough).That would be nice. So, we just add that token to initdb? Seems simple.
I added some explanation of the all vs replication bit in the header comments.
Revision attached.
Looks good to me.
As I mentioned offlist, I'd like it in teal please.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, May 19, 2011 at 11:09, Dave Page <dpage@pgadmin.org> wrote:
On Thu, May 19, 2011 at 2:44 PM, Selena Deckelmann <selena@chesnok.com> wrote:
On Wed, May 18, 2011 at 8:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Greg Smith's message of mié may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? (In this case the patch is no longer
2 lines, but still should be trivial enough).That would be nice. So, we just add that token to initdb? Seems simple.
I added some explanation of the all vs replication bit in the header comments.
Revision attached.
Looks good to me.
As I mentioned offlist, I'd like it in teal please.
Applied with some further minor bikeshedding (remove trailing spaces,
rewrap so columns aren't wider than 80 chars, etc)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander wrote:
On Thu, May 19, 2011 at 11:09, Dave Page <dpage@pgadmin.org> wrote:
On Thu, May 19, 2011 at 2:44 PM, Selena Deckelmann <selena@chesnok.com> wrote:
On Wed, May 18, 2011 at 8:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Greg Smith's message of mi? may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. ?I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? ?(In this case the patch is no longer
2 lines, but still should be trivial enough).That would be nice. So, we just add that token to initdb? Seems simple.
I added some explanation of the all vs replication bit in the header comments.
Revision attached.
Looks good to me.
As I mentioned offlist, I'd like it in teal please.
Applied with some further minor bikeshedding (remove trailing spaces,
rewrap so columns aren't wider than 80 chars, etc)
Let me just point out that people who have already run initdb during
beta will not see this in their pg_hba.conf, nor in their
share/pg_hba.conf.sample, even after they have upgraded to a later beta,
unless they run initdb. However, we have bumped the catalog version for
something else so they should then get this change.
My point is if we change configuration files and then don't bump the
catalog version, the share/*.sample files get out of sync with the files
in /data, which can be kind of confusing.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Tue, May 24, 2011 at 10:53, Bruce Momjian <bruce@momjian.us> wrote:
Magnus Hagander wrote:
On Thu, May 19, 2011 at 11:09, Dave Page <dpage@pgadmin.org> wrote:
On Thu, May 19, 2011 at 2:44 PM, Selena Deckelmann <selena@chesnok.com> wrote:
On Wed, May 18, 2011 at 8:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:Excerpts from Greg Smith's message of mi? may 18 23:07:13 -0400 2011:
Two things that could be changed from this example to make it more useful:
-The default database is based on your user name, which is postgres in
most packaged builds but not if you compile your own. ?I don't know
whether it's practical to consider substituting that into this file, or
if it's just enough to mention that as an additional doc comment.You mean the default username, not the default database, but yeah; so do
we need a @default_username@ token to be replaced by initdb with
whatever it has as effective_user? ?(In this case the patch is no longer
2 lines, but still should be trivial enough).That would be nice. So, we just add that token to initdb? Seems simple.
I added some explanation of the all vs replication bit in the header comments.
Revision attached.
Looks good to me.
As I mentioned offlist, I'd like it in teal please.
Applied with some further minor bikeshedding (remove trailing spaces,
rewrap so columns aren't wider than 80 chars, etc)Let me just point out that people who have already run initdb during
beta will not see this in their pg_hba.conf, nor in their
share/pg_hba.conf.sample, even after they have upgraded to a later beta,
unless they run initdb. However, we have bumped the catalog version for
something else so they should then get this change.
Why would they not see it in their share/pg_hba.conf.sample?
It will not affect the existing one in $PGDATA, but why wouldn't the
installed .sample change?
My point is if we change configuration files and then don't bump the
catalog version, the share/*.sample files get out of sync with the files
in /data, which can be kind of confusing.
They would - but what you are saying above is that they would not get
out of sync, because the share/*.sample also don't update. Just a
mistake in what you said above, or am I missing something?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander wrote:
As I mentioned offlist, I'd like it in teal please.
Applied with some further minor bikeshedding (remove trailing spaces,
rewrap so columns aren't wider than 80 chars, etc)Let me just point out that people who have already run initdb during
beta will not see this in their pg_hba.conf, nor in their
share/pg_hba.conf.sample, even after they have upgraded to a later beta,
Oops, yes, I was wrong here. Sorry.
unless they run initdb. ?However, we have bumped the catalog version for
something else so they should then get this change.Why would they not see it in their share/pg_hba.conf.sample?
It will not affect the existing one in $PGDATA, but why wouldn't the
installed .sample change?
Yes, the problem is the sample will change, but the $PGDATA will not, so
anyone doing a diff of the two files to see the localized changes will
see the changes that came in as part of that commit.
My point is if we change configuration files and then don't bump the
catalog version, the share/*.sample files get out of sync with the files
in /data, which can be kind of confusing.They would - but what you are saying above is that they would not get
out of sync, because the share/*.sample also don't update. Just a
mistake in what you said above, or am I missing something?
Yes, my mistake.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Tue, May 24, 2011 at 2:48 PM, Bruce Momjian <bruce@momjian.us> wrote:
Yes, the problem is the sample will change, but the $PGDATA will not, so
anyone doing a diff of the two files to see the localized changes will
see the changes that came in as part of that commit.
I don't think that's a serious problem. I wouldn't want to make a
change like that in a released version, but doing it during beta seems
OK.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, May 24, 2011 at 2:48 PM, Bruce Momjian <bruce@momjian.us> wrote:
Yes, the problem is the sample will change, but the $PGDATA will not, so
anyone doing a diff of the two files to see the localized changes will
see the changes that came in as part of that commit.
I don't think that's a serious problem. I wouldn't want to make a
change like that in a released version, but doing it during beta seems
OK.
Given that we've already forced initdb for beta2, it seems like a
complete non-issue right now, anyway.
regards, tom lane
Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, May 24, 2011 at 2:48 PM, Bruce Momjian <bruce@momjian.us> wrote:
Yes, the problem is the sample will change, but the $PGDATA will not, so
anyone doing a diff of the two files to see the localized changes will
see the changes that came in as part of that commit.I don't think that's a serious problem. I wouldn't want to make a
change like that in a released version, but doing it during beta seems
OK.Given that we've already forced initdb for beta2, it seems like a
complete non-issue right now, anyway.
Yes, agreed. I was just pointing it out because people often don't
realize the effect this has.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +