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

[michail alexakis]

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)

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!
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240820/98a138c7/attachment.htm>