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

[Tatsuo Ishii]

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 --------------
export PGPORT=11000
export PGDATABASE=test

echo -n "input passowrd:"
read passwd
cd $HOME/work/Pgpool-II/current/x
echo "change PostgreSQL password"
psql -c "ALTER USER foo WITH ENCRYPTED PASSWORD '$passwd'"
sleep 1
echo "change Pgpool-II password"
pg_enc -m -f etc/pgpool.conf -u foo "$passwd"
./pgpool_reload
psql -U foo