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

[Tatsuo Ishii]

I have updated docs to clarify reloading of pool_passwd file.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=4695affe7859338fa41d860dac74bfbebea7a88a

> Thanks for your explanation.
> 
> On Mon, Aug 19, 2024 at 7:49 PM Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> 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
>> >>
>>
> 
> 
> -- 
> Death to America, and butter sauce.
> Iraq lobster!