Re: 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(at)dunslane(dot)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 password are 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> string or 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(dot)fujii(at)gmail(dot)com> writes:
> On Mon, Jun 7, 2010 at 5:42 AM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>> On Mon, Jun 7, 2010 at 5:42 AM, Andrew Dunstan <andrew(at)dunslane(dot)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(dot)fujii(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)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(dot)fujii(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)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(at)dunslane(dot)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

On 09/06/10 08:24, Fujii Masao wrote:
> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew(at)dunslane(dot)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(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 09/06/10 08:24, Fujii Masao wrote:
>>
>> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew(at)dunslane(dot)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(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 09/06/10 08:24, Fujii Masao wrote:
>>
>> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew(at)dunslane(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Fri, Jun 11, 2010 at 7:14 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> On 09/06/10 08:24, Fujii Masao wrote:
>>>
>>> On Wed, Jun 9, 2010 at 12:52 PM, Andrew Dunstan<andrew(at)dunslane(dot)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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/