[pgpool-general: 9319] Re: Issue with Password Authentication for Pgpool

[Tatsuo Ishii]

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