[pgpool-general: 8437] Re: Enhancement request in scram-sha-256 authentication mode with pgpool II 4.3

[Tatsuo Ishii]

>> > We are running about 20 pgpool II clusters with multiple databases on each, and thus multiple users that we need to maintain in each pool_passwd file.
>> > This is becoming more and more painfull  when security team requests for password change every 90 days, or if a password is changed on the database side, but is not updated in the pool_passwd file.
>> > To solve this issue and avoid using the pool_passwd file, we tried to use the password keyword in pool_hba.conf, but quickly noticed that this was not acceptable in terms of security, because the password is requested in a clear text format on the client side, and we don't want to spend too much time on configuring SSL between the clients and the servers.
>> > So our question is : can we expect an enhancement request that would allow pgpool to request the password in the desired format (e.g : scram-sha-256 specified in pool_hba.conf) to the client, and pass it through as is to the database engine , if the corresponding user is not defined in the pool_passwd file ?
>> > This new functionality could be driven by a pgpool.conf parameter .
>> 
>> You expect something like this without using pool_passwd?
>> 
>> client <-- scram-shar-256 --> pgpool <-- scram-shar-256 --> PostgreSQL
>> 
>> That's theoretically impossible. When client connects to pgpool using
>> scram-shar-256, the client never sends password in an unecrypted form,
>> thus pgpool never knows the clients password (that's one of the
>> reasons why scram-shar-256 is secure). If pgpool doesn't know the
>> password, it cannot connect to PostgreSQL via scram-shar-256.
> 
> Would it be acceptable to do this?
> 
> client <-- password over ssl --> pgpool <-- scram-sha-256 --> PostgreSQL
> 
> Tatsuo, am I correct that this would work?

Yes.

> Is the scram challenge handled by pgpool or is it passed back to the client?

The scram challenge is handled by pgpool.
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp