Subject: Re: Issue with Password Authentication for Pgpool

From: vijay patil <[email-masked]>
Date: Thu, 9 Jan 2025 11:01:00 +0530

Thanks David,

I tested the configuration by setting allow_clear_text_frontend_auth = on
and disabling the pool_hba. I made the corresponding entry in the
pg_hba.conf file. However, while connecting through the database port
(5432), it prompts for the password, but when connecting through Pgpool
(port 9999), it does not ask for a password.

Here is the content of pg_hba.conf:

bash
Copy code
# TYPE  DATABASE        USER            ADDRESS
METHOD# "local" is for Unix domain socket connections onlylocal   all
           all                                     trust# IPv4 local
connections:
host    all             all             127.0.0.1/32            trust#
IPv6 local connections:
host    all             all             ::1/128                 trust#
Allow replication connections from localhost, by a user with the#
replication privilege.local   replication     all
               trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    repmgr          repmgr      127.0.0.1/32            trust
host    repmgr          repmgr      10.125.0.90/32             trust
        # Primary
host    replication     repmgr      10.125.0.90/32             trust
host    repmgr          repmgr      10.125.0.91/32             trust
        # Standby
host    replication     repmgr      10.125.0.91/32             trust
host    all         all         10.125.0.90/32       trust            # Node 1
host    all         all         10.125.0.91/32       trust
# Node 2#host    all         all         0.0.0.0/26          trust
host    all         all         10.125.0.79/32           scram-sha-256
host    all         all         0.0.0.0/0           scram-sha-256

When I connect via the database port (5432), it prompts for the password as
expected:

[postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 5432
Password for user vkp:
psql (15.3)
Type "help" for help.

postgres=>
postgres=>
postgres=> exit

However, when connecting through Pgpool (port 9999), it does not prompt for
the password:

[postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 9999
psql (15.3)
Type "help" for help.

postgres=>

This behavior might be related to how Pgpool handles authentication. Let me
know if you need further investigation or configuration changes!

Thanks

Vijay

On Thu, Jan 9, 2025 at 10:47 AM David G. Johnston <
[email-masked]> wrote:

> On Wednesday, January 8, 2025, vijay patil <[email-masked]>
> wrote:
>
>> Could you please advise how I can achieve password authentication without
>> using the pool_password file, while ensuring that Pgpool prompts for the
>> password during connection?
>>
>
>
> https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-ALLOW-CLEAR-TEXT-FRONTEND-AUTH
>
> David J.
>

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 8 Jan 2025 22:44:02 -0700

On Wednesday, January 8, 2025, vijay patil <[email-masked]> wrote:

> This behavior might be related to how Pgpool handles authentication.
>

Having never used pgpool I very well could be wrong but from what I can
gather what you want to do is simply not possible within the current design
of pgpool.  Namely you have multiple nodes so cannot rely on raw mode but
raw mode is required for the kind of pass-through auth you are trying.
Instead you require pool_hba.conf which itself requires the password file.

David J.

---

From: vijay patil <[email-masked]>
Date: Thu, 9 Jan 2025 11:34:23 +0530

If we plan to use pool_hba.conf, then we must use the pool_passwd file to
maintain passwords for all database users. This approach requires that
every time a new user is created, their password is added to the pool_passwd
file. The challenge with this method is that it becomes difficult to
maintain, as we need to manually update the pool_passwd file for every user
creation or password change. Additionally, after updating the pool_passwd
file, Pgpool needs to be restarted for the changes to take effect.

Thanks
Vijay

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 8 Jan 2025 23:12:44 -0700

On Wednesday, January 8, 2025, vijay patil <[email-masked]> wrote:

> Pgpool needs to be restarted for the changes to take effect.
>

You should look for and leverage the difference between when something must
be “restarted” and when it has provided a mechanism for “reloading”.

David J.

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 09 Jan 2025 16:19:32 +0900 (JST)

> know if you need further investigation or configuration changes!

I guess pgpool is running on IP which is accepted by PostgreSQL using
trust auth method, which does not ask a password. Probably you set
backend_hostname to 'localhost'?  Then it matches with the line in
pg_hba.conf:

host    all             all             127.0.0.1/32            trust
or
host    all             all             ::1/128                 trust

in which a password is never asked (or maybe other entries whose auth
method is trust).

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

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 09 Jan 2025 16:23:19 +0900 (JST)

> file.

Not really. You can use pool_hba.conf with the auth method to
'password'. With this setting, if a passowrd is not found in
pool_passwd, then pgpool asks the password to PostgreSQL.

Best reagards,

---

From: vijay patil <[email-masked]>
Date: Thu, 9 Jan 2025 13:06:29 +0530

Thanks David.

On Thu, Jan 9, 2025 at 11:42 AM David G. Johnston <
[email-masked]> wrote:

> You should look for and leverage the difference between when something
> must be “restarted” and when it has provided a mechanism for “reloading”.
>
> David J.
>
>