[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