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

[Tatsuo Ishii]

>> >>> Can you elaborate what is your security concern?
>>
> That the password will be in plain text ( not encrypted )  and can be
> compromised. Or I am missing something here.

If you are talking about passwords flying between frontend and pgpool
using enable allow_clear_text_frontend_auth, then yes. But you can use
SSL to protect passwords from attacker.

> 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.
> # psql -p 9999-h hostname -U pgpool
> psql: ERROR:  unable to read message length
> DETAIL:  message length (23) in slot 1 does not match with slot 0(42)
> ERROR:  unable to read message length
> DETAIL:  message length (23) in slot 1 does not match with slot 0(42)

> *Pgpool log:*
> 2020-05-22 16:24:54: pid 11774: ERROR:  unable to read message length
> 2020-05-22 16:24:54: pid 11774: DETAIL:  message length (23) in slot 1 does
> not match with slot 0(42)
> * 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.
> 
> Questions:
> - What am I doing wrong in above step?

It seems the entry for the "pgpool" user in pg_hba.conf is different
among backends.

> - What are the steps , if I need to use combination of md5 and SCRAM
> passwords?
> - when do I need to use pool_hba?

In this case (using allow_clear_text_frontend_auth) you do not need to
use pool_hba.conf.

> - Is it true that pool_passwd file works only for md5 passwords?

No.

> If yes,
> then how users with SCRAM password enabled will be able to connect using
> pgpool?

You need to set up SCRAM password in pool_passwd.

>> >>>A password in pool_passwd is used if health_check_password is an empty
>> >>>string.
>>
>> > - Some Detail msg in pgpool log that I an mot sure of  ?   what is server
>> > here ( pgpool or postgres) - server doesn't want to talk SSL
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate backend: key data
>> > received
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate backend: transaction
>> > state: I
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
>> > connection
>> > 2020-05-21 19:16:20: pid 6664: DETAIL:  sending client->server SSL
>> request
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
>> > connection
>> > 2020-05-21 19:16:20: pid 6664: DETAIL:  client->server SSL response: N
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  attempting to negotiate a secure
>> > connection
>> > 2020-05-21 19:16:20: pid 6664: DETAIL:  *server doesn't want to talk SSL*
>> > 2020-05-21 19:16:20: pid 6664: DEBUG:  authenticate kind = 0
>>
>> >>> "server" means PostgreSQL here.
>> Thanks
>>
> What is the meaning of this message ? server doesn't want to talk SSL

PostgreSQL is not ready for accepting SSL connection from pgpool.

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