[pgpool-general: 7140] Re: md5 authentication failed on pgpool failover

[Bo Peng]

Hello,

Thank you for providing config files.

I found the followig settings in your pg_hba.conf.

===================
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host	all		all		10.11.0.200/32		trust
host	all		all		10.11.0.201/32		md5
host	all		all		10.11.0.202/32		md5
# pgpool connections
host    postgres        pgpool          10.11.0.200/32          trust
host    postgres        pgpool          10.11.0.201/32          trust
host    postgres        pgpool          10.11.0.202/32          trust
===================

You are setting:

   host ... 10.11.0.200/32		trust
   host ... 10.11.0.201/32		md5

Before failover "trust" is applied and after failover "md5" is applied.
I think that's the reason why authentication after failover fails.

If you can connection to PostgreSQL (10.11.0.200) directly from 10.11.0.201,
the password in "pool_passwd" file is incorrect.
If not, you need to confirm the password in ".pgpass" file.

BTW, you are using pool_hba.conf. 
You need to enable "enable_pool_hba = on" to apply the configuration in pool_hba.conf.

[Doc]
https://www.pgpool.net/docs/latest/en/html/index.html

On Wed, 8 Jul 2020 13:03:55 +0000
Wolf Schwurack <wolf at uen.org> wrote:

> I attached pgpool.conf, pool_hba.conf and pg_hba.conf from all 3 nodes. 
> 
> Wolf
> 
> On 7/7/20, 10:44 PM, "Bo Peng" <pengbo at sraoss.co.jp> wrote:
> 
>     Hello,
>     
>     On Wed, 8 Jul 2020 03:09:27 +0000
>     Wolf Schwurack <wolf at uen.org> wrote:
>     
>     > I have 3 pgpool/postgresql nodes with watchdog.  When I start pgpool on the primary node 0. All my application host connect without any errors. When stop pgpool on node 0 to test the failover. Watchdog fails over to node 1 but in pgpool.log on node 1 I’m getting an error md5 authentication failed
>     > 
>     > Before failover
>     > Node 0 pgpool.log
>     > 
>     > 2020-07-07 14:02:17: pid 1062067: DETAIL:  connecting host=hosted-prev-01.uen.org port=40268
>     > 
>     > 2020-07-07 14:02:17: pid 1068342: LOG:  new connection received
>     > 
>     > After failover
>     > Node 1 pgpool.log
>     > 
>     > 2020-07-07 20:49:04: pid 3850: DETAIL:  connecting host=hosted-prev-01.uen.org port=40498
>     > 
>     > 2020-07-07 20:49:04: pid 3850: ERROR:  md5 authentication failed
>     > 
>     > 2020-07-07 20:49:04: pid 3850: DETAIL:  password does not match
>     > 
>     > pool_hba.conf, pool_passwd, pcp.conf and .pgpass are the same on all 3 nodes
>     
>     I think it may occur due to the settings.
>     Could you share your pgpool.conf, pool_hba.conf and pg_hba.conf?
>     
>     > Host info
>     > Ubuntu 20
>     > Pgpool II 4.1.1
>     > PostgreSQL 12
>     > 
>     > Wolfgang Schwurack
>     > Database/System Administrator
>     > Utah Education Network
>     > 801-587-9444
>     > wolf at uen.org<mailto:wolf at uen.org>
>     > 
>     
>     
>     -- 
>     Bo Peng <pengbo at sraoss.co.jp>
>     SRA OSS, Inc. Japan
>     
> 


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan