<div dir="ltr"><div dir="ltr">On Mon, May 18, 2020 at 5:25 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> Hello folks,<br>
> <br>
> I need your expert advice on using SSL authentication in pgpool.<br>
> <br>
> Environment:<br>
> - OS - RHEL 7.6<br>
> - PostgreSQL- 11.6 ( Master and Replica on different servers)<br>
> - pgpool - 4.0.2 - active on master node ( sorry but I need this version)<br>
> <br>
> My requirement is to have secure communication between client<=> pgpool and<br>
> pgoll <=> postgres.<br>
> Maintaining pool_passwd file is not possible ( no control over user and<br>
> password) in my use case.<br>
<br>>> You can use allow_clear_text_frontend_auth to not store passwords in pool_passwd file.<br>
<br>
>> <a href="https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH</a></blockquote><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Thanks I will explore this option.<br></blockquote><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
> Another option is Certificate Authentication (SSL) between both client<=><br>
> pgpool and pgpool<=> postgres ( using same server cert).<br>
<br>
Unfortunately Certificate Authentication is not supported between<br>
pgpool <=> postgres.<br>
<br>
> In order to achieve above , I performed below steps-<br>
> *- generated self signed certificate *<br>
> *- updated pgpool.conf*<br>
> *- updated postgresql.conf , and pg_hba.conf *<br>
> *- restarted whole setup.*<br>
> <br>
> I can successfully login to postgresql using cert( i.e. user can log in<br>
> using client cert) but SSL between pgpool<=> postgres is not working.<br>
> <br>
> pgpool log:<br>
<br>
[snip]<br>
<br>
> As I did not find any related document and the document that I found<br>
> <<a href="https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/" rel="noreferrer" target="_blank">https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-pgpool-ii/</a>>is<br>
> not working as expected. There is another contradictory information in<br>
> pgpool doc source-6.2.4<br>
> <<a href="https://www.pgpool.net/docs/40/en/html/auth-methods.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/40/en/html/auth-methods.html</a>> , which says<br>
> certificate authentication between pgppol <=> postgres is not possible.<br>
<br>
Yes, it's not possible.<br>
<br>
> Am I doing something wrong? or this is not at all a possible use case.<br>
<br>>> I recommend you following:<br>
<br>>> 0. enable allow_clear_text_frontend_auth.<br></blockquote><div>I wounder whether this is secure option to choose?</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">>> 1. client<=>> pgpool<br>
<br>>> Use SSL connection and clear text password authenticatoion. You don't<br>
need to set up pool_passwd. Password will be provided by client.<br>
<br>>> 2. pgppol <=> postgres<br>
<br>>>Use SSL connection and md5 or SCRAM authenticatoion. The password used<br>>>for the authentication is provided by client if<br>>>allow_clear_text_frontend_auth is enabled.<br>
<br></blockquote><div>
I assume I can't avoid pool_passwd if I don't want to compromise on security. I have tested below- could you please validate?</div><div>1- Create server and client certs </div><div>2- Update pgpool.conf ( using master slave mode)</div><div> <span style="font-family:monospace">ssl=on</span></div><div><font face="monospace"> ssl_key = '/database11/ssl/erver.key'</font></div><div><font face="monospace"> ssl_cert = '/database11/ssl/server.crt</font></div><div><font face="monospace"> ssl_ca_cert = '/database11/ssl/root.crt'</font></div><div><font face="monospace"> pool_passwd = 'pool_passwd'</font></div><div><font face="monospace"><br></font> 3- Updated postgresql.conf</div><div> <font face="monospace">ssl=on</font></div><div><font face="monospace"> ssl_key = '/database11/ssl/erver.key'</font></div><div><font face="monospace"> ssl_cert = '/database11/ssl/server.crt</font></div><div><font face="monospace"> ssl_ca_cert = '/database11/ssl/root.crt'</font></div><div><font face="monospace"></font> </div><div> 4- Updated pg_hba.conf</div><div><font face="monospace"> host postgres postgres md5</font></div><div><font face="monospace"> host postgres pgpool IP trust ## pgpool- health check user </font></div><div><span style="font-family:monospace"><br></span></div><div><font face="arial, sans-serif">5- Restart whole setup.</font></div><div><span style="font-family:monospace">6- </span><font face="arial, sans-serif">Test </font></div><div><ol><li><font face="arial, sans-serif">connect via psql using pgpool port --> Successful and using SSL connection</font></li></ol></div><div><font face="monospace"># psql -U postgres -d postgres -p 8888<br>Password for user postgres:</font><br>psql (11.6)<br>SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256, compression: off)<br>Type "help" for help.<br><font face="monospace">postgres=> \c</font><br>SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256, compression: off)<br>You are now connected to database "postgres" as user "postgres".<br><font face="monospace">postgres=></font><br></div><div><br></div><div><b><u>Postgres log:</u></b></div><div> LOG: connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, compression=off)</div><div><font face="monospace"><br></font></div><div> 2. Connect using PGAdmin<br></div><div>client<==> pgpool : client certificate + md5 password </div><div>pgpool<==>postgres : SSL + md5 password</div><div><br></div><div><u><b>pgpool logs: </b></u></div><div><div><div><img src="cid:ii_kagkcx2o1" alt="image.png" width="472" height="288"><br></div></div></div><div> </div><div><b><u>Some further questions: </u></b></div><div>- Should healthcheck user ( pgpool ) be authenticating as either plain password in pgpool.conf or trust in pg_hba.conf?</div><div> I assume if pool_passwd is enabled , then pgpool should use md5 password for backend authentication or it needs to be trust </div><div><br></div><div>- Some Detail msg in pgpool log that I an mot sure of ? what is server here ( pgpool or postgres) - <font color="#0000ff">server doesn't want to talk SSL
</font></div><div><font face="monospace">2020-05-21 19:16:20: pid 6664: DEBUG: authenticate backend: key data received<br>2020-05-21 19:16:20: pid 6664: DEBUG: authenticate backend: transaction state: I<br>2020-05-21 19:16:20: pid 6664: DEBUG: attempting to negotiate a secure connection<br>2020-05-21 19:16:20: pid 6664: DETAIL: sending client->server SSL request<br>2020-05-21 19:16:20: pid 6664: DEBUG: attempting to negotiate a secure connection<br>2020-05-21 19:16:20: pid 6664: DETAIL: client->server SSL response: N<br>2020-05-21 19:16:20: pid 6664: DEBUG: attempting to negotiate a secure connection<br>2020-05-21 19:16:20: pid 6664: DETAIL: <font color="#0000ff"><b>server doesn't want to talk SSL</b></font><br>2020-05-21 19:16:20: pid 6664: DEBUG: authenticate kind = 0</font><br></div><div><br></div><div>Thanks in advance </div><div>Regards,</div><div>Raj</div><div><br></div><div><br></div><div>Best regards,</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div></div>