[pgpool-general: 9192] Re: reloading of pool_passwd file

[Tatsuo Ishii]

Hi Michail,

> Hello Tatsuo,
> 
> Yes, my test is as follows (on Pgpool 4.4.6, running on a RedHat 8
> container [1]).
> 
> The pool_passwd file is located at a custom path:
> $ grep /etc/pgpool-II/pgpool.conf -P -e 'pool_passwd\s*='
> pool_passwd = '/var/lib/pgpool/pool-passwd/pool_passwd'
> 
> Create a list of new users to feed pg_enc utility:
> $ echo 'user1:secret1' > /tmp/new-users.txt
> $  pg_enc -k $PGPOOLKEYFILE -i /tmp/new-users.txt -m # writes entries to
> /etc/pgpool-II/pool_passwd
> 
> Append new entries (assuming /etc/pgpool-II/pool_passwd was initially
> empty) to our custom pool_passwd (contents change, inode of target remains
> the same):
> $ cat /etc/pgpool-II/pool_passwd >> /var/lib/pgpool/pool-passwd/pool_passwd
> 
> Connect with new user "user1" (it works, without reloading).

I have looked into the case more and found that:

1) If the pgpool child process had never accepted connections from
client, the process reads the contents of pool_passwd and the changes
you made were picked up.

2) Actually pgpool reads pool_passwd every time when authentication is
required by client.

So you are right. The change made to pool_passwd will be effective
without reload.

Note that if changes are made to pool_hba.conf, reload is required.

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

> [1]
> https://github.com/OpertusMundi/postgresql-cluster.helm/blob/master/pgpool/redhat/Dockerfile
> 
> Kind regards, Michail
> 
> 
> On Mon, Aug 19, 2024 at 12:14 PM Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > Hello Tatsuo and thanks for your quick response!
>> >
>> > My impression is that (as Ron also mentioned) the contents of pool_passwd
>> > are read on every connection (authentication) attempt. I mean, at least
>> for
>> > a handful of tests I performed, the new users were seen without a need
>> for
>> > a reload (but this could also be luck[1]). So, is a reload really
>> necessary
>> > here?
>> >
>> > [1] e.g some Pgpool child processes see the updated version of
>> pool_passwd,
>> > while others see the old (cached?) one
>>
>> I confirmed using gdb that without pgpool reload, pool_passwd is never
>> re-read at least on master branch. Are you sure that you are the only
>> user of pgpool at that point? I suspect someone else executed pgpool
>> reload.
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>