Horton
Menu

Thread

  1. 'replication' keyword on .pgpass (Streaming Replication)

    Fujii Masao <masao.fujii@gmail.com> — 2010-01-06T03:53:50Z

    On Sat, Dec 26, 2009 at 10:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Fri, Dec 25, 2009 at 9:56 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> I don't see the use case for it - .pgpass is for single users, not a whole
>> cluster. And it does support wildcards, which takes care of the 'all' case.
>> In the case of pg_hba.conf we don't know in advance who will actually be
>> connecting. But in the case of .pgpass we do, so the extra utility of
>> 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me.
>
> OK, I might need to focus only on the use of replication, without
> being avaricious.

The attached patch supports new keyword 'replication' on .pgpass file.
This keyword is used to specify the password for the standby server to
connect to the primary server.

Without this keyword, since replication doesn't correspond to the real
database, the password cannot be supplied in .pgpass file, instead,
must be specified in the conninfo string or the environment variable
PGPASSWORD.

For example, if the primary is running on host IP 192.168.1.50, port 5432,
the superuser's name for replication is foo, and the password is foopass,
the following line should be added to the .pgpass file on the standby so
that it can connect to the primary without prompting for password.

    192.168.1.50:5432:replication:foo:foopass

Thought?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  2. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-01-06T14:02:41Z

    Fujii Masao escribió:
> On Sat, Dec 26, 2009 at 10:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> > On Fri, Dec 25, 2009 at 9:56 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> >> I don't see the use case for it - .pgpass is for single users, not a whole
> >> cluster. And it does support wildcards, which takes care of the 'all' case.
> >> In the case of pg_hba.conf we don't know in advance who will actually be
> >> connecting. But in the case of .pgpass we do, so the extra utility of
> >> 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me.
> >
> > OK, I might need to focus only on the use of replication, without
> > being avaricious.
> 
> The attached patch supports new keyword 'replication' on .pgpass file.
> This keyword is used to specify the password for the standby server to
> connect to the primary server.

Would it make more sense to have a separate password file for the
replication stuff?  Say .pgreplicationpass or whatever.


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  3. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Magnus Hagander <magnus@hagander.net> — 2010-01-06T14:11:51Z

    On Wed, Jan 6, 2010 at 15:02, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Fujii Masao escribió:
>> On Sat, Dec 26, 2009 at 10:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> > On Fri, Dec 25, 2009 at 9:56 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> >> I don't see the use case for it - .pgpass is for single users, not a whole
>> >> cluster. And it does support wildcards, which takes care of the 'all' case.
>> >> In the case of pg_hba.conf we don't know in advance who will actually be
>> >> connecting. But in the case of .pgpass we do, so the extra utility of
>> >> 'sameuser', 'samerole' and 'samegroup' in this case is not apparent to me.
>> >
>> > OK, I might need to focus only on the use of replication, without
>> > being avaricious.
>>
>> The attached patch supports new keyword 'replication' on .pgpass file.
>> This keyword is used to specify the password for the standby server to
>> connect to the primary server.
>
> Would it make more sense to have a separate password file for the
> replication stuff?  Say .pgreplicationpass or whatever.

I haven't read up on the rest of the patch, but where do we put the
rest of the information about the replication master? Like which IP
and port to connect to? Perhaps it could/should go there?

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

  4. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-06T16:51:30Z

    Fujii Masao <masao.fujii@gmail.com> writes:
> The attached patch supports new keyword 'replication' on .pgpass file.
> This keyword is used to specify the password for the standby server to
> connect to the primary server.

This strikes me as a completely bad idea.  We need get no farther than
the point that it assumes nobody can have a database named "replication"
(although I notice the patch also appears to assume that libpq knows
internally that the connection is for replication --- I thought we were
going to avoid libpq changes for SR?)

I don't see any real strong reason why a .pgpass entry for this purpose
couldn't depend on having "*" in the database field.  But the later
comments that the password should be in some other configuration file
altogether are probably an even better idea.

			regards, tom lane

  5. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Fujii Masao <masao.fujii@gmail.com> — 2010-01-07T06:19:29Z

    On Wed, Jan 6, 2010 at 11:11 PM, Magnus Hagander <magnus@hagander.net> wrote:
> I haven't read up on the rest of the patch, but where do we put the
> rest of the information about the replication master? Like which IP
> and port to connect to? Perhaps it could/should go there?

Such information are supplied in the parameter 'primary_conninfo' of
recovery.conf. For example;

    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo'

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  6. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Fujii Masao <masao.fujii@gmail.com> — 2010-01-07T08:26:19Z

    On Thu, Jan 7, 2010 at 1:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> This strikes me as a completely bad idea.  We need get no farther than
> the point that it assumes nobody can have a database named "replication"

Though I might misunderstand your point. My proposal would force the users
who have a database named "replication" to change their .pgpass file and
enclose the "replication" database field in double quote when they upgrade
the Postgres to v8.5. For example;

   192.168.1.50:5432:"replication":foo:foopass

The same problem also exists in pg_hba.conf. It's because I introduced
new keyword "replication" in pg_hba.conf to authenticate the standby
server. This restriction is not acceptable? If so, I'd need to consider
an authentication configuration for replication again: introduce new
configuration file? just change the keyword name to "unpopular" one?...

> (although I notice the patch also appears to assume that libpq knows
> internally that the connection is for replication --- I thought we were
> going to avoid libpq changes for SR?)

Yes, but I changed the libpq just a bit; if the conninfo string including
"replication=1" is given to PQconnectdb(), the libpq determines that this
connection is for replication, and puts the replication-request in a startup
packet. This is for a backend to switch to walsender mode when the startup
packet arrives. Otherwise, we would have to authenticate such backend twice
on different context, i.e., a normal backend and walsender. So the settings
for each context would be required in pg_hba.conf. This is odd, I think.

> I don't see any real strong reason why a .pgpass entry for this purpose
> couldn't depend on having "*" in the database field.

Oh, you are right. Since a role cannot use a different password per database,
"*" in the database field seems to be enough.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  7. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Magnus Hagander <magnus@hagander.net> — 2010-01-07T08:44:21Z

    On Thu, Jan 7, 2010 at 07:19, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Jan 6, 2010 at 11:11 PM, Magnus Hagander <magnus@hagander.net> wrote:
>> I haven't read up on the rest of the patch, but where do we put the
>> rest of the information about the replication master? Like which IP
>> and port to connect to? Perhaps it could/should go there?
>
> Such information are supplied in the parameter 'primary_conninfo' of
> recovery.conf. For example;
>
>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo'

So the password can just go there, no?

If an untrusted user has direct read access to your PGDATA directory
(where recovery.conf goes), you've lost already...

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

  8. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Magnus Hagander <magnus@hagander.net> — 2010-01-07T08:46:19Z

    On Thu, Jan 7, 2010 at 09:26, Fujii Masao <masao.fujii@gmail.com> wrote:

> The same problem also exists in pg_hba.conf. It's because I introduced
> new keyword "replication" in pg_hba.conf to authenticate the standby
> server. This restriction is not acceptable? If so, I'd need to consider
> an authentication configuration for replication again: introduce new
> configuration file? just change the keyword name to "unpopular" one?...

I certainly think there are a lot of installations out there with a
database named "replication". That doesn't mean it's unacceptable
though.

However, wouldn't it make more logical sense to replace "host/hostssl"
with "replication/replicationssl" rather than overload the database
field?


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

  9. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Fujii Masao <masao.fujii@gmail.com> — 2010-01-07T09:13:41Z

    On Thu, Jan 7, 2010 at 5:44 PM, Magnus Hagander <magnus@hagander.net> wrote:
>> Such information are supplied in the parameter 'primary_conninfo' of
>> recovery.conf. For example;
>>
>>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo'
>
> So the password can just go there, no?

Yeah, the password can be supplied in primary_conninfo.

   primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'

Also you can use the environment variable PGPASSWORD.
Is this enough? I was thinking that some people would
complain that only the password for replication cannot
be supplied in .pgpass.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  10. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Fujii Masao <masao.fujii@gmail.com> — 2010-01-07T09:21:10Z

    On Thu, Jan 7, 2010 at 5:46 PM, Magnus Hagander <magnus@hagander.net> wrote:
> However, wouldn't it make more logical sense to replace "host/hostssl"
> with "replication/replicationssl" rather than overload the database
> field?

Seems good. How about the following formats?

  replication     user  CIDR-address  auth-method  [auth-options]
  replicationssl  user  CIDR-address  auth-method  [auth-options]
  replication     user  IP-address  IP-mask  auth-method  [auth-options]
  replicationssl  user  IP-address  IP-mask  auth-method  [auth-options]

Note that "database" field has been removed since it's useless
for replication.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  11. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-01-07T12:34:06Z

    Fujii Masao wrote:
> On Thu, Jan 7, 2010 at 5:44 PM, Magnus Hagander <magnus@hagander.net> wrote:
>>> Such information are supplied in the parameter 'primary_conninfo' of
>>> recovery.conf. For example;
>>>
>>>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo'
>> So the password can just go there, no?
> 
> Yeah, the password can be supplied in primary_conninfo.
> 
>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
> 
> Also you can use the environment variable PGPASSWORD.
> Is this enough? I was thinking that some people would
> complain that only the password for replication cannot
> be supplied in .pgpass.

That seems enough to me.

BTW, how do you set up authentication using an SSL certificate? ISTM
that's the way we should be encouraging people to configure
authentication between a master and standby, rather than type a password
to a file.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

  12. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-01-07T12:39:41Z

    Tom Lane wrote:
> Fujii Masao <masao.fujii@gmail.com> writes:
>> The attached patch supports new keyword 'replication' on .pgpass file.
>> This keyword is used to specify the password for the standby server to
>> connect to the primary server.
> 
> This strikes me as a completely bad idea.  We need get no farther than
> the point that it assumes nobody can have a database named "replication"
> (although I notice the patch also appears to assume that libpq knows
> internally that the connection is for replication --- I thought we were
> going to avoid libpq changes for SR?)

As the patch stands there's an extra startup packet, similar to
SSLRequest, in the connection handshake. But I think we should get rid
of that too, and just send an extra "replication=on" option along with
username and other options in the startup packet.

(as Fujii pointed out already, you can have a database called
"replication". But you need to quote it in pg_hba.conf, like you need to
quote "all" and "sameuser/group/role".)

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

  13. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-01-07T12:44:43Z

    Magnus Hagander wrote:
> However, wouldn't it make more logical sense to replace "host/hostssl"
> with "replication/replicationssl" rather than overload the database
> field?

It makes more sense to me to overload the database field. When you
connect for replication, you're not connecting to any particular
database, but to a special walsender mode.

Note that 'local' makes sense for replication too; you can connect for
replication via a Unix-domain socket. Replication isn't a connection
method like host/hostssl/local are.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

  14. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Magnus Hagander <magnus@hagander.net> — 2010-01-07T12:48:12Z

    On Thu, Jan 7, 2010 at 10:21, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Thu, Jan 7, 2010 at 5:46 PM, Magnus Hagander <magnus@hagander.net> wrote:
>> However, wouldn't it make more logical sense to replace "host/hostssl"
>> with "replication/replicationssl" rather than overload the database
>> field?
>
> Seems good. How about the following formats?
>
>  replication     user  CIDR-address  auth-method  [auth-options]
>  replicationssl  user  CIDR-address  auth-method  [auth-options]
>  replication     user  IP-address  IP-mask  auth-method  [auth-options]
>  replicationssl  user  IP-address  IP-mask  auth-method  [auth-options]
>
> Note that "database" field has been removed since it's useless
> for replication.

Hm, no, I think I withdraw my comment about pg_hba.conf. It seems
better to overload the database name here. I'm not particularly keen
on yet another different set of columns, which is what happens when
you remove the database field.

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

  15. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Magnus Hagander <magnus@hagander.net> — 2010-01-07T12:50:12Z

    On Thu, Jan 7, 2010 at 13:34, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> Fujii Masao wrote:
>> On Thu, Jan 7, 2010 at 5:44 PM, Magnus Hagander <magnus@hagander.net> wrote:
>>>> Such information are supplied in the parameter 'primary_conninfo' of
>>>> recovery.conf. For example;
>>>>
>>>>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo'
>>> So the password can just go there, no?
>>
>> Yeah, the password can be supplied in primary_conninfo.
>>
>>    primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
>>
>> Also you can use the environment variable PGPASSWORD.
>> Is this enough? I was thinking that some people would
>> complain that only the password for replication cannot
>> be supplied in .pgpass.
>
> That seems enough to me.
>
> BTW, how do you set up authentication using an SSL certificate? ISTM
> that's the way we should be encouraging people to configure
> authentication between a master and standby, rather than type a password
> to a file.

Connectoin parameters: sslcert, sslkey and sslrootcert in most cases.
Or just put the key in ~/.postgresql/postgresql.key.


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

  16. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-01-07T14:53:12Z

    Fujii Masao <masao.fujii@gmail.com> writes:
> On Thu, Jan 7, 2010 at 5:46 PM, Magnus Hagander <magnus@hagander.net> wrote:
>> However, wouldn't it make more logical sense to replace "host/hostssl"
>> with "replication/replicationssl" rather than overload the database
>> field?

> Seems good. How about the following formats?

>   replication     user  CIDR-address  auth-method  [auth-options]
>   replicationssl  user  CIDR-address  auth-method  [auth-options]
>   replication     user  IP-address  IP-mask  auth-method  [auth-options]
>   replicationssl  user  IP-address  IP-mask  auth-method  [auth-options]

> Note that "database" field has been removed since it's useless
> for replication.

I'm getting more and more confused here.  I thought we were talking
about client-side .pgpass.  This seems to be talking about pg_hba.conf.

			regards, tom lane

  17. Re: 'replication' keyword on .pgpass (Streaming Replication)

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-01-07T15:09:27Z

    Tom Lane wrote:
> I'm getting more and more confused here.  I thought we were talking
> about client-side .pgpass.  This seems to be talking about pg_hba.conf.

Yeah, the topic was covertly changed.

It seems we have consensus to not change .pgpass, and to leave
pg_hba.conf as it is now in the patch as well.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com