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

Sat Jul 11 00:33:31 JST 2020

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

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