[pgpool-general: 7145] Re: md5 authentication failed on pgpool failover
Wolf Schwurack
wolf at uen.org
Tue Jul 14 22:39:13 JST 2020
I got things worked out. Found out that when I test the failover it takes about 10 min before an application node can see the virtual IP on the new master node. I guess that how our internal network works.
Thx
Wolf
On 7/13/20, 9:03 PM, "Bo Peng" <pengbo at sraoss.co.jp> wrote:
Hello,
>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"
If you can connect PostgreSQL directly, this means the authentication problem is between Pgpool and PostgreSQL.
You need to verify the pool_passwd file which is used for authentication between Pgpool and postgreSQL.
> Sorry still having issues with this. After change I don't get the md5 errors on the standby node, but if watchdog is running on one of the standby pgpool nodes. And I try to login from an application node using the command line
> wolf at idp-dev-05:~$ psql -h wd-pgdev -p 9999 -U wso2_user -d bps_db
>
> It just hangs I don't get a password prompt
I think the hangup is not Pgpool's issue.
Is "wd-pgdev" the VIP?
Can you connect using IP address instead of "wd-pgdev"?
> When watchdog is running on the primary node I can connect
> wolf at idp-dev-05:~$ psql -h wd-pgdev -p 9999 -U wso2_user -d bps_db
> Password for user wso2_user:
> psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1), server 11.8 (Ubuntu 11.8-1.pgdg20.04+1))
> WARNING: psql major version 10, server major version 11.
> Some psql features might not work.
> Type "help" for help.
>
> bps_db=>
>
> Wolf
>
> On 7/10/20, 7:56 AM, "pgpool-general-bounces at pgpool.net on behalf of Wolf Schwurack" <pgpool-general-bounces at pgpool.net on behalf of wolf at uen.org> wrote:
>
> 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
>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
More information about the pgpool-general
mailing list