Thread: SR slaves and .pgpass
The docs state, w.r.t. $subject: The password can be provided either in the primary_conninfo string or in a separate ~/.pgpass file on the standby server. I tried this with a database name of "replication" in the .pgpass file, which matches what we need to use in pg_hba.conf, but it failed miserably, and only worked when I used a wildcard for the database name in the .pgpass file. If this is expected it needs to be documented more clearly; if not, it's a bug. I expect this to be quite a common thing to do - many people will be like me and dislike putting passwords in config files. cheers andrerw
On Mon, Jun 7, 2010 at 5:42 AM, Andrew Dunstan <andrew@dunslane.net> wrote: > > The docs state, w.r.t. $subject: > > The password can be provided either in the primary_conninfo string > or in a separate ~/.pgpass file on the standby server. > > I tried this with a database name of "replication" in the .pgpass file, > which matches what we need to use in pg_hba.conf, but it failed miserably, > and only worked when I used a wildcard for the database name in the .pgpass > file. > > If this is expected it needs to be documented more clearly; if not, it's a > bug. Yep, this is expected, so we need to improve the doc. What about: diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 5c0d9ab..458a4e2 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -819,7 +819,9 @@ host replication foo 192.168.1.100/32 md5 <para> The host name and port number of the primary, connection user name, and passwordare specified in the <filename>recovery.conf</> file or - the corresponding environment variable on the standby. + in a separate <filename>~/.pgpass</> on the standby (In the latter case, + <literal>database</> field in a <filename>~/.pgpass</> file must be + <literal>*</>). For example, if the primary is running on host IP <literal>192.168.1.50</>, port <literal>5432</literal>, the superuser's name for replication is <literal>foo</>,and the password is <literal>foopass</>, the administrator diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 439db3f..cc351f8 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -268,9 +268,11 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows primary (see <xref linkend="streaming-replication-authentication">). A password needs to be provided too, if the primary demands password - authentication. (The password can be provided either in + authentication. The password can be provided either in the <varname>primary_conninfo</varname> stringor in a separate - <filename>~/.pgpass</> file on the standby server.) + <filename>~/.pgpass</> file on the standby server (in the latter case, + <literal>database</> field in a <filename>~/.pgpass</> file must be + <literal>*</>). Do not specify a database name in the <varname>primary_conninfo</varname> string. </para> Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Fujii Masao <masao.fujii@gmail.com> writes:
> On Mon, Jun 7, 2010 at 5:42 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> I tried this with a database name of "replication" in the .pgpass file,
>> which matches what we need to use in pg_hba.conf, but it failed miserably,
>> and only worked when I used a wildcard for the database name in the .pgpass
>> file.
>>
>> If this is expected it needs to be documented more clearly; if not, it's a
>> bug.
> Yep, this is expected, so we need to improve the doc.
Why don't we improve the code, instead? In particular make
libpqrcv_connect() do
- snprintf(conninfo_repl, sizeof(conninfo_repl), "%s replication=true", conninfo);
+ snprintf(conninfo_repl, sizeof(conninfo_repl), "%s database=replication replication=true", conninfo);
I don't think it's unlikely that someone would try to enter a
replication-specific password into ~/.pgpass.
regards, tom lane
Tom Lane wrote: > Why don't we improve the code, instead? In particular make > libpqrcv_connect() do > > - snprintf(conninfo_repl, sizeof(conninfo_repl), "%s replication=true", conninfo); > + snprintf(conninfo_repl, sizeof(conninfo_repl), "%s database=replication replication=true", conninfo); > > I don't think it's unlikely that someone would try to enter a > replication-specific password into ~/.pgpass. > > > +1. It's highly likely - that's how we got here in the first place. It seems to me like a perfectly reasonable thing to do. cheers andrew
On Tue, Jun 8, 2010 at 12:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Fujii Masao <masao.fujii@gmail.com> writes: >> On Mon, Jun 7, 2010 at 5:42 AM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> I tried this with a database name of "replication" in the .pgpass file, >>> which matches what we need to use in pg_hba.conf, but it failed miserably, >>> and only worked when I used a wildcard for the database name in the .pgpass >>> file. >>> >>> If this is expected it needs to be documented more clearly; if not, it's a >>> bug. > >> Yep, this is expected, so we need to improve the doc. > > Why don't we improve the code, instead? In particular make > libpqrcv_connect() do > > - snprintf(conninfo_repl, sizeof(conninfo_repl), "%s replication=true", conninfo); > + snprintf(conninfo_repl, sizeof(conninfo_repl), "%s database=replication replication=true", conninfo); What if the real database named "replication" exists? How can we specify the password only for replication purpose in that case? BTW, to distinguish the replication connection from the connection to the real database named "replication", I proposed changing the .pgpass code so that it accepts the keyword only for replication, like pg_hba.conf. But it was rejected, and as the result of the discussion, we had consensus to not change the code. http://archives.postgresql.org/pgsql-hackers/2010-01/msg00400.php > I don't think it's unlikely that someone would try to enter a > replication-specific password into ~/.pgpass. Agreed. But I think that we don't need to specify other than the wildcard in the database field of .pgpass to use the replication-specific password if the replication-specific user is supplied in .pgpass. So the current code is enough for me. Am I missing something? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Fujii Masao <masao.fujii@gmail.com> writes:
> But I think that we don't need to specify other than the wildcard
> in the database field of .pgpass to use the replication-specific
> password if the replication-specific user is supplied in .pgpass.
> So the current code is enough for me. Am I missing something?
You're looking at it from the perspective of somebody who knows
exactly how the code works. What Andrew tried is exactly what
95% of other people would try. There doesn't seem to me to be
any very good argument against making it work for them.
regards, tom lane
On Tue, Jun 8, 2010 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Fujii Masao <masao.fujii@gmail.com> writes: >> But I think that we don't need to specify other than the wildcard >> in the database field of .pgpass to use the replication-specific >> password if the replication-specific user is supplied in .pgpass. >> So the current code is enough for me. Am I missing something? > > You're looking at it from the perspective of somebody who knows > exactly how the code works. What Andrew tried is exactly what > 95% of other people would try. There doesn't seem to me to be > any very good argument against making it work for them. Hmm.. is it worth going back to my proposal? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Fujii Masao <masao.fujii@gmail.com> writes:
> Hmm.. is it worth going back to my proposal?
I don't recall exactly what proposal you might be referring to, but
I'm hesitant to put any large amount of work into hacking .pgpass
processing for this. The whole business of replication authorization
is likely to get revisited in 9.1, no? I think a cheap-and-cheerful
solution is about right for the moment.
regards, tom lane
On Tue, Jun 8, 2010 at 1:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Fujii Masao <masao.fujii@gmail.com> writes: >> Hmm.. is it worth going back to my proposal? > > I don't recall exactly what proposal you might be referring to, but http://archives.postgresql.org/pgsql-hackers/2010-01/msg00400.php > I'm hesitant to put any large amount of work into hacking .pgpass > processing for this. The whole business of replication authorization > is likely to get revisited in 9.1, no? I think a cheap-and-cheerful > solution is about right for the moment. Fair enough. My proposal patch might be too large to apply at this point. >> - snprintf(conninfo_repl, sizeof(conninfo_repl), "%s replication=true", conninfo); >> + snprintf(conninfo_repl, sizeof(conninfo_repl), "%s database=replication replication=true", conninfo); Tom's proposal is very small, but we cannot distinguish the password for replication purpose from that for the real database named "replication". Is this OK? I can live with this as far as it's documented. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Fujii Masao wrote: > Tom's proposal is very small, but we cannot distinguish the password > for replication purpose from that for the real database named "replication". > Is this OK? I can live with this as far as it's documented. > > There is precedent for .pgpass being a bit ambiguous. See the way "localhost" is used. cheers andrew
On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > There is precedent for .pgpass being a bit ambiguous. See the way > "localhost" is used. OK. The attached patch allows us to use "replication" in the database field of the .pgpass file, for the replication connection. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Attachment
On 09/06/10 08:24, Fujii Masao wrote: > On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew@dunslane.net> wrote: >> There is precedent for .pgpass being a bit ambiguous. See the way >> "localhost" is used. > > OK. The attached patch allows us to use "replication" in the database > field of the .pgpass file, for the replication connection. Thanks, committed with some rewording of the docs and comments. I hope I made them better, not worse. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Fri, Jun 11, 2010 at 7:14 PM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 09/06/10 08:24, Fujii Masao wrote: >> >> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew@dunslane.net> >> wrote: >>> >>> There is precedent for .pgpass being a bit ambiguous. See the way >>> "localhost" is used. >> >> OK. The attached patch allows us to use "replication" in the database >> field of the .pgpass file, for the replication connection. > > Thanks, committed with some rewording of the docs and comments. I hope I > made them better, not worse. Yep. Thank a lot. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Fri, Jun 11, 2010 at 7:14 PM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > On 09/06/10 08:24, Fujii Masao wrote: >> >> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew@dunslane.net> >> wrote: >>> >>> There is precedent for .pgpass being a bit ambiguous. See the way >>> "localhost" is used. >> >> OK. The attached patch allows us to use "replication" in the database >> field of the .pgpass file, for the replication connection. > > Thanks, committed with some rewording of the docs and comments. I hope I > made them better, not worse. I could confirm that this has been committed via git log, but not find the related post on the pgsql-committers. Is there a problem in the mailing-list? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Mon, Jun 14, 2010 at 04:56, Fujii Masao <masao.fujii@gmail.com> wrote: > On Fri, Jun 11, 2010 at 7:14 PM, Heikki Linnakangas > <heikki.linnakangas@enterprisedb.com> wrote: >> On 09/06/10 08:24, Fujii Masao wrote: >>> >>> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew@dunslane.net> >>> wrote: >>>> >>>> There is precedent for .pgpass being a bit ambiguous. See the way >>>> "localhost" is used. >>> >>> OK. The attached patch allows us to use "replication" in the database >>> field of the .pgpass file, for the replication connection. >> >> Thanks, committed with some rewording of the docs and comments. I hope I >> made them better, not worse. > > I could confirm that this has been committed via git log, but not find > the related post on the pgsql-committers. Is there a problem in the > mailing-list? The cvs server has a delivery of the email to the mailinglist server at least, so it's not there that has the problem. However, there were a lot of unexplained issues across multiple hub.org hosted machines that day (and nearby, iirc), including the mailserver and the dns servers. It was probably eaten by one of those issues. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/