Thread: Issue with Password Authentication for Pgpool

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?

This behavior might be related to how Pgpool handles authentication.

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

> 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