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

[Tatsuo Ishii]

Hi,

I found a bug with the pgpool code when enable_pool_hba = off.  Upon
reloading pgpool it failed to re-open pool_passwd if enable_pool_hba
is off. As a result changes to pgpool_passwd did not take effect. The
reason why the changes to pool_passwd eventually takes effect is,
probably pgpool child process restart because of child_life_time or
child_max_connections.

Patch attached (it will appear in the next pgpool minor releases, that
are scheduled to be out on November 21).
https://pgpool.net/mediawiki/index.php/Roadmap

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

> Hi, the value what you said is disabled
> 
> 
> 2024년 11월 12일 (화) 13:16, Tatsuo Ishii <ishii at postgresql.org>님이 작성:
> 
>> 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
>> >> >>> >>
>> >> >>>
>> >>
>>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: update_pool_passwd.patch
Type: text/x-patch
Size: 458 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20241113/671b3ed3/attachment.bin>