[pgpool-general: 7015] SSL authentication in Pgpool

[Rajni Baliyan]

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.

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

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:

2020-05-13 11:40:35: pid 17598: DEBUG:  attempting to negotiate a secure
connection
2020-05-13 11:40:35: pid 17598: DETAIL:  client->server SSL response: S
2020-05-13 11:40:35: pid 17598: LOCATION:  pool_ssl.c:110
2020-05-13 11:40:35: pid 17598: LOG:  pool_ssl: "SSL_connect": "certificate
verify failed"
2020-05-13 11:40:35: pid 17598: LOCATION:  pool_ssl.c:369
2020-05-13 11:40:35: pid 17598: ERROR:  failed to authenticate
2020-05-13 11:40:35: pid 17598: DETAIL:  invalid authentication message
response type, Expecting 'R' and received ''
2020-05-13 11:40:35: pid 17598: LOCATION:  pool_auth.c:127
2020-05-13 11:40:35: pid 17598: DEBUG:  verify_backend_node_status: there's
no primary node
2020-05-13 11:40:35: pid 17598: LOCATION:  pgpool_main.c:3129
2020-05-13 11:40:35: pid 17598: DEBUG:  node status[0]: 0
2020-05-13 11:40:35: pid 17598: LOCATION:  pool_worker_child.c:180
2020-05-13 11:40:40: pid 17598: DEBUG:  attempting to negotiate a secure
connection
2020-05-13 11:40:40: pid 17598: DETAIL:  sending client->server SSL request
2020-05-13 11:40:40: pid 17598: LOCATION:  pool_ssl.c:98
2020-05-13 11:40:40: pid 17598: DEBUG:  attempting to negotiate a secure
connection
2020-05-13 11:40:40: pid 17598: DETAIL:  client->server SSL response: S
2020-05-13 11:40:40: pid 17598: LOCATION:  pool_ssl.c:110
2020-05-13 11:40:40: pid 17598: LOG:  pool_ssl: "SSL_connect": "certificate
verify failed"

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.

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

Please help to suggest right approach.

Thanks in advance
Regards,
Raj
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20200513/7ab74a8a/attachment.htm>