[pgpool-general: 9224] Re: pgpool-ii configurations

[Long Nguyen]

Thanks a lot Ron. I installed pgpool from source using the online
documentation.

I am still not clear on the use of various users used by pgpool-ii, so more
questions here.

1) sr_check_user
The postgresql streaming replication uses db role 'replicator' for
connection from standby to primary. Should I use this db role as
sr_check_user, or should I create a new db role to be used as sr_check_user?

2) health_check_user
I guess  I can create a new db role for this use. Or should I use
'replicator' above for this user?

At present I have created a db role 'pgpool', grant  pg_read_all_data to
it, and used it as both sr_check_user and health_check_use.Would this be
fine?

3) pgpool_passwd
This file is used by pgpool to manage user connection to postgresql. I am
curious why can't pgpool just pass credentials supplied by clients to
postgresql and leave it to postgresql to validate access requests, rather
than having a separate user/pwd file.

Thanks
Long

On Wed, 16 Oct 2024 at 03:05, Ron Johnson <ronljohnsonjr at gmail.com> wrote:

> On Tue, Oct 15, 2024 at 11:45 AM Long Nguyen <nguyenxl059 at gmail.com>
> wrote:
>
>> Hi,
>>
>> I am new to pgpool-ii and am setting it up. My env:
>> - postgresql 16 on ubuntu 22.04 on both primary and standby servers
>> - postgresql streaming replication is use
>> - install pgpool 4.5.4 on Primary server with streaming replication mode
>>
>> I have followed online documentation and have some general questions.
>>
>> 1) Since pgpool accepts client connection and passes the same credential
>> supplied by client to postgresql server, do we have the same entries for
>> pgpool_hba.conf as in pg_hba.conf?
>>
>
> Typically yes,  but do not have to.  Really, it depends on your PgPool
> configuration.  That's because the Postgresql instance sees the server
> where the PgPool VIP is located.
>
> For example, we have a three node PgPool cluster:
> node_a: the database primary server. PG and PgPool installed.
> node_b: the database streaming replica server. PG and PgPool installed.
> node_c: where the VIP normally lives.  Only PgPool installed.
>
> The node_a pg_stat_activity.client_hostname field sees "node_c", not the
> application server(s) and user(s).  Thus, you don't need them in the node_a
> and node_b pg_hba.conf files, but you do need them in the three
> pool_hba.conf files.
>
>
>> 2) The documentation recommends a separate OS user to be used to run
>> pgpool. Do people do that or just use root? I created a 'pgpool' linux user
>> and used it, but encountered various issues with file  access permissions.
>>
>
> I had a similar question when I first tried configuring PgPool.
>
> The RPM packages supplied by https://www.pgpool.net/yum/rpms/4.5/ all use
> the same OS user "postgres" that PostgreSQL itself uses.  APT probably does
> something similar.
>
>
>> 3) With the user we specify in the pcp.conf file, is it an OS user or
>> postgresql server user? Or is it neither? (The doco does not make this
>> clear.)
>>
>
> It's a postgresql role.
>
>
>> 4) When is the user specified in the pcp.conf file referred to? Is this
>> when we issue PCP commands (e.g. pcp_attach_node). How about $HOME/.pcppass
>> file? If .pcppass is used then the pcp.conf file would not be referred to?
>>
>
> This is the same as Postgresql storing the role's password hash in
> pg_authid and the plaintext in ~/.pgpass.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> crustacean!
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20241017/a798ba0c/attachment.htm>