Issue with Password Authentication for Pgpool

On Wednesday, January 8, 2025, vijay patil <vijay.postgres@gmail.com> 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.

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 <
david.g.johnston@gmail.com> wrote:

On Wednesday, January 8, 2025, vijay patil <vijay.postgres@gmail.com> 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.

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

On Thu, Jan 9, 2025 at 11:14 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, January 8, 2025, vijay patil <vijay.postgres@gmail.com> 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.

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!

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

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.

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,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Thanks David.

On Thu, Jan 9, 2025 at 11:42 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

assuming you are implementing kubernetes

kubectl exec -it $(kubectl get pods -l app.kubernetes.io/component=pgpool,app.kubernetes.io/name=postgresql-ha -o jsonpath='{.items[0].metadata.name}') -- pg_md5 -m --config-file="/opt/bitnami/pgpool/conf/pgpool.conf" -u "foo" "bar"

//cat out pgpool afterwards

pgpool:
customUsers:
usernames: "keycloak vkp"
passwords: "my_keycloak*PWD vkp*PWD"

Users and passwords are separated by a space character.

ju<https://github.com/juan131&gt;

________________________________
From: vijay patil <vijay.postgres@gmail.com>
Sent: Thursday, January 9, 2025 12:06 AM
To: pgpool-general@pgpool.net <pgpool-general@pgpool.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Issue with Password Authentication for Pgpool

Hello Everyone,

Need assistance on below requirements.

My requirement is to prompt for a password when connecting through Pgpool (port 9999) and to avoid using the pool_password file. However, I’m currently getting the error related to the lack of an entry in the pool_passwd file.

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?

Here is the relevant configuration from my pool_hba.conf:

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 10.125.0.79/32 scram-sha-256 # Node 3
host all all 0.0.0.0/0 scram-sha-256 # All nodes

error :-
[postgres@scrbtrheldbaas002 PG_DATA]$ psql -h 10.125.0.79 -U vkp -d postgres -p 9999
psql: error: connection to server at "10.125.0.79", port 9999 failed: FATAL: SCRAM authentication failed
DETAIL: pool_passwd file does not contain an entry for "vkp"