[Pgpool-general] Authentication when using streaming replication

[Andreas H.]

Hi,

I have trouble setting up pgpool2-3.0.2 (from Debian Squeeze backports)
with two PostgreSQL 9.1 servers in streaming replication mode. When I
set the {pool,pg}_hba.conf settings to ``trust``, everything seems to go
smoothly. However, in my production environment, I don't want just
anyone do anything to my database server; at least the password auth
should be enforced, and passwords should be protected from spying eyes.

My pgpool2 instance is running on a seperate server. From what I see, I
cannot use md5 in Postgres' hba with streaming replication. So I could
use SSL for the connection between pgpool and Postgres, I guess,
allowing ``password`` access to Postgres over SSL. But how do I then set
up access in pgpool's hba? For security reasons, I want the Postgres hba
to only allow connections from the pgpool box.

Sorry, if this is a bit confuse. Short version:

How do I set up Postgres' and pgpool's hba files to

* allow access to Postgres from pgpool only
* enforce password use
* not transmit passwords unencrypted

Another question, maybe related: What is the purpose of the pool_passwd
file? I cannot find anything in the docs, but my pgpool complains about
it missing upon startup ...

Thanks for your insight!

Andreas.

[Tatsuo Ishii]

Post by Andreas H.
I have trouble setting up pgpool2-3.0.2 (from Debian Squeeze backports)
with two PostgreSQL 9.1 servers in streaming replication mode. When I
set the {pool,pg}_hba.conf settings to ``trust``, everything seems to go
smoothly. However, in my production environment, I don't want just
anyone do anything to my database server; at least the password auth
should be enforced, and passwords should be protected from spying eyes.
My pgpool2 instance is running on a seperate server. From what I see, I
cannot use md5 in Postgres' hba with streaming replication. So I could
use SSL for the connection between pgpool and Postgres, I guess,
allowing ``password`` access to Postgres over SSL. But how do I then set
up access in pgpool's hba? For security reasons, I want the Postgres hba
to only allow connections from the pgpool box.
How do I set up Postgres' and pgpool's hba files to
* allow access to Postgres from pgpool only

Just properly set pg_hba.conf so that it only accepts incoming
connections from the host which pgpool is running on.

Post by Andreas H.
* enforce password use
* not transmit passwords unencrypted

pgpool acceses PosgreSQL in 3 different ways(assuming you use
streaming replication mode along with health checking):

1) Ordinaly database access. You can use md5 auth. Please look into
the docs coming with pgpool-II for more details.

2) Health checking. Unfortunately pgpool-II cannot use other than
trust auth.

3) Specific checking with streaming replication. You can use md5 auth.

In summary, if you do not plan to use health checking, you can
configure pgpool-II to use md5 auth.

Post by Andreas H.
Another question, maybe related: What is the purpose of the pool_passwd
file? I cannot find anything in the docs, but my pgpool complains about
it missing upon startup ...

Really? It's definitely included in pgpool-en.html coming with
pgpool-II source code.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp