[Pgpool-general] Authentication when using streaming replication

[Tatsuo Ishii]

> 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

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

> * 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.

> 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