[pgpool-general: 7021] Re: SSL authentication in Pgpool

[Tatsuo Ishii]

> Hello folks,
> 
> I need your expert advice on using SSL authentication in pgpool.
> 
> Environment:
> - OS - RHEL 7.6
> - PostgreSQL- 11.6 ( Master and Replica on different servers)
> - pgpool - 4.0.2 - active on master node ( sorry but I need this version)
> 
> My requirement is to have secure communication between client<=> pgpool and
> pgoll <=> postgres.
> Maintaining pool_passwd file is not possible ( no control over user and
> password)  in my use case.

You can use allow_clear_text_frontend_auth to not store passwords in pool_passwd file.

https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH

> Another option is Certificate Authentication (SSL) between both client<=>
> pgpool and pgpool<=> postgres ( using same server cert).

Unfortunately Certificate Authentication is not supported between
pgpool <=> postgres.

> In order to achieve above , I performed below steps-
> *- generated self signed certificate *
> *- updated pgpool.conf*
> *- updated postgresql.conf , and  pg_hba.conf *
> *-  restarted whole setup.*
> 
> I can successfully login to  postgresql using cert( i.e. user can log in
> using client cert) but SSL between pgpool<=> postgres is not working.
> 
> pgpool log:

[snip]

> As I did not find any related document and the document that I found
> <https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/>is
> not working as expected. There is another contradictory information in
> pgpool doc  source-6.2.4
> <https://www.pgpool.net/docs/40/en/html/auth-methods.html>  , which says
> certificate authentication between pgppol <=> postgres is not possible.

Yes, it's not possible.

> Am I doing something wrong? or this is not at all a possible use case.

I recommend you following:

0. enable allow_clear_text_frontend_auth.

1. client<=>> pgpool

Use SSL connection and clear text password authenticatoion. You don't
need to set up pool_passwd. Password will be provided by client.

2. pgppol <=> postgres

Use SSL connection and md5 or SCRAM authenticatoion. The password used
for the authentication is provided by client if
allow_clear_text_frontend_auth is enabled.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp