[pgpool-general: 8434] 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.

> Any help, advice, or alternative solution to our issue, would be highly appreciated,
> Jean-Michel.

You can create a custom application to handle user's password change
request.  The application accepts password from user then update
password in PostgreSQL and pgpool.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp