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

[Ron Johnson]

On Wed, Oct 16, 2024 at 10:10 PM Long Nguyen <nguyenxl059 at gmail.com> wrote:

> 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?
>

For these two, I created a role named "pool_health_check" (same name as the
associated database), and explicit as to what it does.


> At present I have created a db role 'pgpool',
>

That's fine instead of "pool_health_check".


> grant  pg_read_all_data to it, and used it as both sr_check_user and
> health_check_use.Would this be fine?
>

I'm pretty sure the docs are explicit about that.  You only need:
CREATE ROLE pgpool WITH LOGIN PASSWORD 'some_password';
create database pool_health_check;
GRANT CONNECT ON DATABASE pool_health_check TO pgpool;
GRANT pg_monitor TO pgpool;


> 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.
>

That's also explained in the docs: the devs don't want to send passwords in
cleartext.  *YOU CAN* configure it that way if you want, though.


>
> 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
>>
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20241017/a1da9def/attachment.htm>