[pgpool-general: 9196] 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.

>> 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
-------------- next part --------------
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
int	main()
{
	FILE	*fd = fopen("/tmp/a", "r");
	size_t	size;
	char	buf[128];
	char	buf2[10];

	if (!fd)
	{
		fprintf(stderr, "cannot open a. %m\n");
		exit(1);
	}

	for (;;)
	{
		rewind(fd);
		size = fread(buf, 100, 1, fd);
		fprintf(stderr, "contents: \n%s", buf);
		read(0, buf2, 1);
	}
	fclose(fd);
}