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

[Antoine Jean-Michel]

I was expecting some kind of passthru mode to transmit the scram-sha-256 password from client --> pgpool --> postgres and avoid using the pool_passwd file, but I understand this is not feasible ☹
Many thanks for your prompt and very clear answer.
Jean-Michel.

-----Message d'origine-----
De : pgpool-general <pgpool-general-bounces at pgpool.net> De la part de Tatsuo Ishii
Envoyé : mardi 4 octobre 2022 15:33
À : Antoine Jean-Michel <jean-michel.antoine.easyteam at cgifinance.fr>
Cc : pgpool-general at pgpool.net
Objet : [pgpool-general: 8434] Re: Enhancement request in scram-sha-256 authentication mode with pgpool II 4.3

[EMETTEUR EXTERNE] / [EXTERNAL SENDER]
Soyez vigilant avant d'ouvrir les pièces jointes ou de cliquer sur les liens. En cas de doute, signalez le message via le bouton "Message suspect" ou consultez go/secu.
Be cautious before opening attachments or clicking on any links. If in doubt, use "Suspicious email" button or visit go/secu.


> 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 _______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general