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

[Tatsuo Ishii]

> Sorry, correction.
> 
>>> 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)
> 
> This is not correct. Whether a connection is cached or not, it is
> irrelevant to pool_passwd. The reason why new users look instantly
> available is, the buffering logic in fread(3) (or friends like
> fgets(3)) which is being used by pgpool to read pool_passwd. In my
> testing with fread(3), at least on my Ubuntu Linux, fread reads newly
> added record by other process instantly. So if the new user was added
> at the bottle of pool_passwd, it is instantly picked up by
> pgpool. Though if the file is trimmed or modified, fread could return
> wrong contents. I have run attached small program, which continuously
> rewinds and reads a file, while other process (like "echo a > /tmp/a)
> modifies the file. Lines starting with "#" is added by me.
> 
> -----------------------------------------------
> # (1) initially empty (echo /dev/null /tmp/a)
> contents: 
> 
> # (2) "a" is added (echo "a" >> /tmp/a)
> contents: 
> a
> 
> # (3) "b" is added (echo "b" >> /tmp/a)
> contents: 
> a
> b
> 
> # (4) truncated. Only "a". (echo "a" > /tmp/a)
> 
> contents: 
> a
> b
> 
> # (5) "c" is added right next to "a" (echo "c" >> /tmp/a)
> contents: 
> a
> c
> 
> # (6) truncated. Only "a" again. (echo "a" > /tmp/a)
> contents: 
> a
> c
> 
> # (7) "a" changed to "b" (echo "b" > /tmp/a)
> contents: 
> b
> c
> -----------------------------------------------
> 
> As you can see case 1, 2, 3, 5 showed correct result but others showed
> wrong result. In my understanding this is not a bug of fread(3), but
> we just see its buffering effect.
> 
> I feel the fread(3) behavior explained above is too internal and maybe
> changed by the system or glib C versions. So I do not want to rely on
> it. Rather, I think relying on pgpool reload is better.
> 
>>> (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.
> 
> Actually child_max_connections or child_life_time results in killing
> the pgpool process. So "cached connections are recycled" may not be
> appropriate wording here. Also you can immediately reflect the changes
> by using pgpool reload. By pgpool reload, pgpool closes the
> pool_passwd file and read the contents. I believe it is guaranteed
> that fread(3) reads the latest contents of the file at the time it is
> opened.
> 
> In conclusion, every time modifying pool_passwd (regardless
> add/remove/modify user), you should immediately run pgpool reload. By
> this reason, my last changes to the docs saying "pgpool reload is not
> necessary" was wrong. I will revert the commit.

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

>>> 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!
>>>>
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general