[pgpool-general: 9273] Re: pool_passwd authentication failed

[Tatsuo Ishii]

Hi,

Please Cc: to the mailing list so that we can share the valuable
information.

> Hello, thank you for taking the time to test this. There is one difference
> from my setup. While Mr. Tatsuo enabled pool_hba.conf and registered the
> accounts there, my configuration has enable_pool_hba = off and uses
> pool_passwd = 'pool_passwd' to manage accounts via pool_passwd. Could this
> be an important difference? Thank you.

Yes, that would make pgpool to take different code path for
authentication.

Also I want to know if you enable allow_clear_text_frontend_auth.

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

> 2024년 11월 12일 (화) 10:46, Tatsuo Ishii <ishii at postgresql.org>님이 작성:
> 
>> I have run a test but failed to reproduce your problem.  Basically
>> what I did was creating a test cluster using pgpool_setup.  The
>> configuration does not use watchdog but I don't think it makes any
>> deference in terms of authentication. Here are more details about the
>> test.
>>
>> - create a user 'foo'
>> - create password entry in pool_passwd using pg_enc command
>> - enable_pool_hba = on
>> - use scram-shar-256 auth
>>   in pool_hba.conf:
>>   local   all         foo                                    scram-sha-256
>>   in pg_hba.conf:
>>   local      all   foo      scram-sha-256
>>
>> - Then run a script (see attached) which reads new password from
>>   terminal and change password on PostgreSQL, Change password on the
>>   pgpool side using pg_enc command.
>>
>> - reload pgpool.conf
>>
>> - try to connec to pgpool as user foo
>>
>> Is there anything quite different from my test setting in your side?
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>
>> > Yes, I agree. I need to invest the code used by the child process on
>> > the Pgpool-II side.
>> >
>> >> I feel the same way. However, I have a question. The fact that the
>> first 10
>> >> attempts all fail, and that the failure count gradually decreases, seems
>> >> like it could be related to the child processes on the PGPOOL side.
>> What do
>> >> you think?
>> >>
>> >>
>> >> 2024년 11월 11일 (월) 16:16, Tatsuo Ishii <ishii at postgresql.org>님이 작성:
>> >>
>> >>> > Hello, I performed a reload using systemctl reload pgpool. (The
>> reload
>> >>> > command is as follows.) /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf
>> >>> reload
>> >>>
>> >>> Strange. That should have made each pgpool process re-open pool_passwd
>> >>> (pgpool does not have cache of pool_passwd) and upon authentication
>> >>> request pgpool should read the latest contents of pool_passwd. I'll
>> >>> look into this more.
>> >>>
>> >>> Best reagards,
>> >>> --
>> >>> Tatsuo Ishii
>> >>> SRA OSS K.K.
>> >>> English: http://www.sraoss.co.jp/index_en/
>> >>> Japanese:http://www.sraoss.co.jp
>> >>>
>> >>> > 2024년 11월 11일 (월) 15:33, Tatsuo Ishii <ishii at postgresql.org>님이 작성:
>> >>> >
>> >>> >> > Hello, I have just subscribed to pgpool-general for the first time
>> >>> >> today. I
>> >>> >> > am writing this email with a question. Currently, I am using
>> >>> PostgreSQL
>> >>> >> > version 10.23-4 and PGPOOL-II version 4.2.10. There are three
>> nodes in
>> >>> >> the
>> >>> >> > DB Replication Cluster, and each node is monitored by Watchdog.
>> (The
>> >>> >> actual
>> >>> >> > operations run through the delegate IP of Watchdog.) Account
>> >>> >> authentication
>> >>> >> > is managed with pool_passwd. Here is my question: When I create a
>> new
>> >>> >> > account or change an existing account's password and update
>> >>> pool_passwd,
>> >>> >> I
>> >>> >> > encounter an error related to password authentication failure.
>> >>> >> > Interestingly, if I try 10 times initially, all 10 attempts fail.
>> >>> Then,
>> >>> >> > when I retry, about 7 attempts fail, then after retrying again,
>> about
>> >>> 5
>> >>> >> > attempts fail. Eventually, it connects successfully after several
>> >>> >> retries.
>> >>> >> > Is there a chance that child processes are caching authentication
>> >>> >> > information separately? How can I resolve this issue?
>> >>> >>
>> >>> >> Have you executed "pgpool reload" or pcp_reload_config command after
>> >>> >> changing pool_passwd?
>> >>> >> --
>> >>> >> Tatsuo Ishii
>> >>> >> SRA OSS K.K.
>> >>> >> English: http://www.sraoss.co.jp/index_en/
>> >>> >> Japanese:http://www.sraoss.co.jp
>> >>> >>
>> >>>
>>