Date: Thu, 09 Jan 2025 16:19:32 +0900 (JST)
Message-Id: <20250109.161932.1651956031953165183.ishii@postgresql.org>
To: vijay.postgres@gmail.com
Cc: david.g.johnston@gmail.com, pgpool-general@pgpool.net,
 pgsql-general@lists.postgresql.org
Subject: Re: Issue with Password Authentication for Pgpool
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <CAD5k+7yM3EgYOU8XqzaF9fNTZoLeYb=ocPjgSBt+_1w4hvuDTw@mail.gmail.com>
References: <CAD5k+7yAt2bMUpgcEsDEvtJZA2yhre8_ENjjrWZ6VRjy5J8vzQ@mail.gmail.com>
	<CAKFQuwbAHZNE1M1GA5+STmi_RDaBm+aLUFzTG_C72jNbxC8DVg@mail.gmail.com>
	<CAD5k+7yM3EgYOU8XqzaF9fNTZoLeYb=ocPjgSBt+_1w4hvuDTw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250109.161932.1651956031953165183.ishii%40postgresql.org>
Precedence: bulk

> 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