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

[Tatsuo Ishii]

> Hello Tatsuo, thanks for your explanation!
> 
> So, if i understand correctly, we have two cases:
> 
> (1) for new users added to pool_passwd: they are instantly available
> because they are always mapped to an empty connection slot (as no cached
> connection will ever be found for a new user)
> 
> (2) for users edited/deleted from pool_passwd: these users may remain stale
> for a while, but will be eventually consistent when cached connections are
> recycled (eg. as a result of child_max_connections or child_life_time)

Yes, correct.

> Kind regards, Michail
> 
> 
> On Tue, Aug 20, 2024 at 2:31 PM Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> 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!
>>