[pgpool-general: 7141] Re: md5 authentication failed on pgpool failover
Wolf Schwurack
wolf at uen.org
Fri Jul 10 22:56:37 JST 2020
Hey Bo
I set all to trust in pg_hba.org on all of the nodes and reloaded the file using pg_ctl reload. This seem to fix this issue.
I don't understand " If you can connection to PostgreSQL (10.11.0.200) directly from 10.11.0.201,
the password in "pool_passwd" file is incorrect"
I can connect from 10.11.0.201 to 10.11.0.200. Should I not be able to connect that way?
postgres at pgdev-02:~$ psql -h 10.11.0.200 -p 5432
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.
postgres=#
On enabling "enable_pool_hab = on" -- If I set this parameter to 'on' I get a lot of errors. I will follow up on this in another email
Wolfgang Schwurack
Database/System Administrator
Utah Education Network
801-587-9444
wolf at uen.org
On 7/9/20, 11:36 PM, "Bo Peng" <pengbo at sraoss.co.jp> wrote:
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
More information about the pgpool-general
mailing list