<div dir="ltr">Hello folks,<div><br><div>I need your expert advice on using SSL authentication in pgpool.</div><div><br></div><div>Environment:</div><div>- OS - RHEL 7.6</div><div>- PostgreSQL- 11.6 ( Master and Replica on different servers)</div><div>- pgpool - 4.0.2 - active on master node ( sorry but I need this version)</div><div><br></div><div>My requirement is to have secure communication between client<=> pgpool and pgoll <=> postgres. </div><div>Maintaining pool_passwd file is not possible ( no control over user and password) in my use case.<br></div><div><br>Another option is Certificate Authentication (SSL) between both client<=> pgpool and pgpool<=> postgres ( using same server cert).</div><div><br></div><div>In order to achieve above , I performed below steps-</div><div><b>- generated self signed certificate </b></div><div><b>- updated pgpool.conf</b></div><div><b>- updated postgresql.conf , and pg_hba.conf </b></div><div><b>- restarted whole setup.</b></div><div><br></div><div>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.</div><div><br></div><div>pgpool log: </div><div><br></div><div><font color="#ff0000">2020-05-13 11:40:35: pid 17598: DEBUG: attempting to negotiate a secure connection<br>2020-05-13 11:40:35: pid 17598: DETAIL: client->server SSL response: S<br>2020-05-13 11:40:35: pid 17598: LOCATION: pool_ssl.c:110<br>2020-05-13 11:40:35: pid 17598: LOG: pool_ssl: "SSL_connect": "certificate verify failed"<br>2020-05-13 11:40:35: pid 17598: LOCATION: pool_ssl.c:369<br>2020-05-13 11:40:35: pid 17598: ERROR: failed to authenticate<br>2020-05-13 11:40:35: pid 17598: DETAIL: invalid authentication message response type, Expecting 'R' and received ''<br>2020-05-13 11:40:35: pid 17598: LOCATION: pool_auth.c:127<br>2020-05-13 11:40:35: pid 17598: DEBUG: verify_backend_node_status: there's no primary node<br>2020-05-13 11:40:35: pid 17598: LOCATION: pgpool_main.c:3129<br>2020-05-13 11:40:35: pid 17598: DEBUG: node status[0]: 0<br>2020-05-13 11:40:35: pid 17598: LOCATION: pool_worker_child.c:180<br>2020-05-13 11:40:40: pid 17598: DEBUG: attempting to negotiate a secure connection<br>2020-05-13 11:40:40: pid 17598: DETAIL: sending client->server SSL request<br>2020-05-13 11:40:40: pid 17598: LOCATION: pool_ssl.c:98<br>2020-05-13 11:40:40: pid 17598: DEBUG: attempting to negotiate a secure connection<br>2020-05-13 11:40:40: pid 17598: DETAIL: client->server SSL response: S<br>2020-05-13 11:40:40: pid 17598: LOCATION: pool_ssl.c:110<br>2020-05-13 11:40:40: pid 17598: LOG: pool_ssl: "SSL_connect": "certificate verify failed"<br></font></div><div><br></div><div>As I did not find any related document and the document that I <a href="https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/">found </a>is not working as expected. There is another contradictory information in pgpool doc
<span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><a href="https://www.pgpool.net/docs/40/en/html/auth-methods.html" style="color:rgb(5,99,193)">source-6.2.4</a></span> , which says certificate authentication between pgppol <=> postgres is not possible.</div><div> <br>Am I doing something wrong? or this is not at all a possible use case. <br></div><div><br></div><div>Please help to suggest right approach.</div><div><br></div><div>Thanks in advance </div><div>Regards,</div><div>Raj </div></div></div>