<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, May 21, 2020 at 10:43 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">>> >> I recommend you following:<br>
>><br>
>> >> 0. enable allow_clear_text_frontend_auth.<br>
>><br>
> I wounder whether this is secure option to choose?<br>
> <br>
>> >> 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>
>> I assume I can't avoid pool_passwd if I don't want to compromise on<br>
> security.<br>
<br>>>> Can you elaborate what is your security concern?<br></blockquote><div>That the password will be in plain text ( not encrypted ) and can be compromised. Or I am missing something here.</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
> I have tested below- could you please validate?<br>
> 1- Create server and client certs<br>
> 2- Update pgpool.conf ( using master slave mode)<br>
> ssl=on<br>
> ssl_key = '/database11/ssl/erver.key'<br>
> ssl_cert = '/database11/ssl/server.crt<br>
> ssl_ca_cert = '/database11/ssl/root.crt'<br>
> pool_passwd = 'pool_passwd'<br>
> <br>
> 3- Updated postgresql.conf<br>
> ssl=on<br>
> ssl_key = '/database11/ssl/erver.key'<br>
> ssl_cert = '/database11/ssl/server.crt<br>
> ssl_ca_cert = '/database11/ssl/root.crt'<br>
> <br>
> 4- Updated pg_hba.conf<br>
> host postgres postgres md5<br>
> host postgres pgpool IP trust ## pgpool- health check user<br>
> <br>
> 5- Restart whole setup.<br>
> 6- Test<br>
> <br>
> 1. connect via psql using pgpool port --> Successful and using SSL<br>
> connection<br>
> <br>
> # psql -U postgres -d postgres -p 8888<br>
> Password for user postgres:<br>
> psql (11.6)<br>
> SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256,<br>
> compression: off)<br>
> Type "help" for help.<br>
> postgres=> \c<br>
> SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256,<br>
> compression: off)<br>
> You are now connected to database "postgres" as user "postgres".<br>
> postgres=><br>
> <br>
> *Postgres log:*<br>
> LOG: connection authorized: user=postgres database=postgres SSL enabled<br>
> (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256,<br>
> compression=off)<br>
> <br>
> 2. Connect using PGAdmin<br>
> client<==> pgpool : client certificate + md5 password<br>
> pgpool<==>postgres : SSL + md5 password<br>
> <br>
> *pgpool logs: *<br>
> [image: image.png]<br>
<br>>>> I think it works as I expected.<br>
Great!<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">
> *Some further questions: *<br>
> - Should healthcheck user ( pgpool ) be authenticating as either plain<br>
> password in pgpool.conf or trust in pg_hba.conf?<br>
> I assume if pool_passwd is enabled , then pgpool should use md5 password<br>
> for backend authentication or it needs to be trust<br>
<br>>>>Actually you can use plain text password, md5 password or AES256-CBC<br>>>> encrypted password in health_check_password. AES256-CBC is most secure<br>>>> but If you prefer md5 password, yes, you need to set md5<br>>>> authentication in pg_hba.conf. Plain text and AES256 do not have such<br>>>> a restriction.<br>
<br></blockquote><div>I tried AES password for healthcheck user and it worked . Thanks </div><div><br></div><div>But when I am trying to login via psql (using pgpool user) , it is giving me belwo error.
I tried using pool_password file ( pgpool:AESxxxxx) as well but no luck.
</div><div><font face="monospace"># psql -p 9999-h hostname -U pgpool</font><br>psql: ERROR: unable to read message length<br>DETAIL: message length (23) in slot 1 does not match with slot 0(42)<br>ERROR: unable to read message length<br>DETAIL: message length (23) in slot 1 does not match with slot 0(42)<br></div><div><br></div><div><b><u>Pgpool log:</u></b><br>2020-05-22 16:24:54: pid 11774: ERROR: unable to read message length<br>2020-05-22 16:24:54: pid 11774: DETAIL: message length (23) in slot 1 does not match with slot 0(42)<br></div><div><font color="#ff0000">* All users who has md5 password and is has entry in pool_passwd file are logging successfully( using psql and pgpool) but problem is with user having SCRAM password.</font><br></div><div><br></div><div>Questions: <br></div><div>- What am I doing wrong in above step?<br>- What are the steps , if I need to use combination of md5 and SCRAM passwords?<br>- when do I need to use pool_hba?</div><div>- Is it true that pool_passwd file works only for md5 passwords? If yes, then how users with SCRAM password enabled will be able to connect using pgpool?</div><div><br></div><div></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">>>>A password in pool_passwd is used if health_check_password is an empty<br>>>>string.<br>
<br>
> - Some Detail msg in pgpool log that I an mot sure of ? what is server<br>
> here ( pgpool or postgres) - server doesn't want to talk SSL<br>
> 2020-05-21 19:16:20: pid 6664: DEBUG: authenticate backend: key data<br>
> received<br>
> 2020-05-21 19:16:20: pid 6664: DEBUG: authenticate backend: transaction<br>
> state: I<br>
> 2020-05-21 19:16:20: pid 6664: DEBUG: attempting to negotiate a secure<br>
> 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<br>
> 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<br>
> connection<br>
> 2020-05-21 19:16:20: pid 6664: DETAIL: *server doesn't want to talk SSL*<br>
> 2020-05-21 19:16:20: pid 6664: DEBUG: authenticate kind = 0<br>
<br>>>> "server" means PostgreSQL here.<br>
Thanks <br></blockquote><div>What is the meaning of this message ? server doesn't want to talk SSL</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Best regards,<br>
--<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>