View Revisions: Issue #501

Summary 0000501: password authentication failed for user "postgres" in 3.7.9
Revision 2019-04-18 14:23 by hoshiai
Description Hi Team,

Good Morning.

Configuration :
DB servers: 172.16.251.26(Master) , 172.16.251.27(Slave)
pool server: 172.16.251.25 , vip :172.16.251.113

We have recently upgraded from 3.7.8 to 3.7.9 , after that we are getting issue related to the md5 authentication.
Getting below issue while connecting to the database from the pool , but if we directly accessing the DB we can able to connect the DB.

Please find below issue details.

[root@s2n pgpool-II]# psql -h 172.16.251.25 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
psql: ERROR: authentication failed
DETAIL: password authentication failed for user "postgres"

[root@s2n pgpool-II]# psql -h 172.16.251.26 -p 5432 -U postgres postgres -c "\l+"
Password for user postgres:
LOG: duration: 7.640 ms statement: SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
                                                                    List of databases
   Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7368 kB | pg_default | default administrative connection database
 repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 MB | pg_default |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7113 kB | pg_default | unmodifiable empty database
           | | | | | postgres=CTc/postgres | | |
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7113 kB | pg_default | default template for new databases
           | | | | | postgres=CTc/postgres | | |
(4 rows)

we have configure md5 authentication as per md5 table URL : http://www.pgpool.net/mediawiki/index.php/FAQ

only "trust" method is allowing and md5 method is failing to authenticate


[root@s2n pgpool-II]# psql -h 172.16.251.25 -p 9999 -U pgpool postgres -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0 | 172.16.251.26 | 5432 | up | 0.500000 | primary | 2 | false | 0
 1 | 172.16.251.27 | 5432 | up | 0.500000 | standby | 0 | true | 0
(2 rows)

pool_passwd entries:
[root@s2n pgpool-II]# cat pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
repmgr:md58ea99ab1ec3bd8d8a6162df6c8e1ddcd
test:md505a671c66aefea124cc08b76ea6d30bb
test1:md542b72f913c3201fc62660d512f5ac746

Pool_hba.conf values:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all all 172.19.130.20/32 md5
#host all all ::1/128 md5

Master DB pg_hba.conf values:

# "local" is for Unix domain socket connections only
#local all all peer

local all all md5

host repmgr repmgr 172.16.251.26/32 trust
host replication repmgr 172.16.251.26/32 trust

# 172.16.251.27 connections
host repmgr repmgr 172.16.251.27/32 trust
host replication repmgr 172.16.251.27/32 trust


host all all 172.16.251.26/32 trust
#host all all 172.16.251.26/32 trust
host all all 172.16.251.27/32 trust

# pgpool master server and Slave IPs

#host all all 172.16.251.0/24 trust

host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all pgpool 172.16.251.26/32 trust
host all all 172.16.251.26/32 md5

#Application ip
host all all 172.19.130.20/32 md5

#local repmgr repmgr trust
#host repmgr repmgr 127.0.0.1/32 trust
#host repmgr repmgr 172.16.251.26/32 trust

#local replication repmgr trust
#host replication repmgr 127.0.0.1/32 trust
#host replication repmgr 172.16.251.26/24 trust


# IPv4 local connections:
#host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Slave DB pg_hba.conf values:

# "local" is for Unix domain socket connections only
#local all all peer

local all all md5

host repmgr repmgr 172.16.251.27/32 trust
host replication repmgr 172.16.251.27/32 trust
host all all 172.16.251.27/32 trust

# 172.16.251.26 connections This file is automatically copies while doing clone. I have changed 27 to 26 after clonned
host repmgr repmgr 172.16.251.26/32 trust
host replication repmgr 172.16.251.26/32 trust
host all all 172.16.251.26/32 trust

#host all all 172.16.251.26/32 trust
#host all all 172.16.251.27/32 trust

# pgpool master and Slave server ip details

host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all pgpool 172.16.251.26/32 trust
host all all 172.16.251.26/32 md5

#local repmgr repmgr trust
#host repmgr repmgr 127.0.0.1/32 trust
#host repmgr repmgr 172.16.251.26/32 trust

#local replication repmgr trust
#host replication repmgr 127.0.0.1/32 trust
#host replication repmgr 172.16.251.26/24 trust


# IPv4 local connections:
#host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Please find the attachments (ph_hba,pool_hba, pgpool.cong , pool.log files for troubleshooting
Revision 2019-04-17 17:34 by siva
Description Hi Team,

Good Morning.

Configuration :
DB servers: 172.16.251.26(Master) , 172.16.251.27(Slave)
pool server: 172.16.251.25 , vip :172.16.251.113

We have recently upgraded from 3.7.8 to 3.7.9 , after that we are getting issue related to the md5 authentication.
Getting below issue while connecting to the database from the pool , but if we directly accessing the DB we can able to connect the DB.

Please find below issue details.

[root@s2n pgpool-II]# psql -h 172.16.251.25 -p 9999 -U postgres postgres -c "show pool_nodes"
Password for user postgres:
psql: ERROR: authentication failed
DETAIL: password authentication failed for user "postgres"

[root@s2n pgpool-II]# psql -h 172.16.251.26 -p 5432 -U postgres postgres -c "\l+"
Password for user postgres:
LOG: duration: 7.640 ms statement: SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
                                                                    List of databases
   Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7368 kB | pg_default | default administrative connection database
 repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 15 MB | pg_default |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7113 kB | pg_default | unmodifiable empty database
           | | | | | postgres=CTc/postgres | | |
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7113 kB | pg_default | default template for new databases
           | | | | | postgres=CTc/postgres | | |
(4 rows)

we have configure md5 authentication as per md5 table URL : http://www.pgpool.net/mediawiki/index.php/FAQ

only "trust" method is allowing and md5 method is failing to authenticate


[root@s2n pgpool-II]# psql -h 172.16.251.25 -p 9999 -U pgpool postgres -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0 | 172.16.251.26 | 5432 | up | 0.500000 | primary | 2 | false | 0
 1 | 172.16.251.27 | 5432 | up | 0.500000 | standby | 0 | true | 0
(2 rows)

pool_passwd entries:
[root@s2n pgpool-II]# cat pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
repmgr:md58ea99ab1ec3bd8d8a6162df6c8e1ddcd
test:md505a671c66aefea124cc08b76ea6d30bb
test1:md542b72f913c3201fc62660d512f5ac746

Pool_hba.conf values:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all all 172.19.130.20/32 md5
#host all all ::1/128 md5

Master DB pg_hba.conf values:

# "local" is for Unix domain socket connections only
#local all all peer

local all all md5

host repmgr repmgr 172.16.251.26/32 trust
host replication repmgr 172.16.251.26/32 trust

# 172.16.251.27 connections
host repmgr repmgr 172.16.251.27/32 trust
host replication repmgr 172.16.251.27/32 trust


host all all 172.16.251.26/32 trust
#host all all 172.16.251.26/32 trust
host all all 172.16.251.27/32 trust

# pgpool master server and Slave IPs

#host all all 172.16.251.0/24 trust

host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all pgpool 172.16.251.26/32 trust
host all all 172.16.251.26/32 md5

#Application ip
host all all 172.19.130.20/32 md5

#local repmgr repmgr trust
#host repmgr repmgr 127.0.0.1/32 trust
#host repmgr repmgr 172.16.251.26/32 trust

#local replication repmgr trust
#host replication repmgr 127.0.0.1/32 trust
#host replication repmgr 172.16.251.26/24 trust


# IPv4 local connections:
#host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Slave DB pg_hba.conf values:

# "local" is for Unix domain socket connections only
#local all all peer

local all all md5

host repmgr repmgr 172.16.251.27/32 trust
host replication repmgr 172.16.251.27/32 trust
host all all 172.16.251.27/32 trust

# 172.16.251.26 connections This file is automatically copies while doing clone. I have changed 27 to 26 after clonned
host repmgr repmgr 172.16.251.26/32 trust
host replication repmgr 172.16.251.26/32 trust
host all all 172.16.251.26/32 trust

#host all all 172.16.251.26/32 trust
#host all all 172.16.251.27/32 trust

# pgpool master and Slave server ip details

host all pgpool 172.16.251.25/32 trust
host all all 172.16.251.25/32 md5
host all pgpool 172.16.251.26/32 trust
host all all 172.16.251.26/32 md5

#local repmgr repmgr trust
#host repmgr repmgr 127.0.0.1/32 trust
#host repmgr repmgr 172.16.251.26/32 trust

#local replication repmgr trust
#host replication repmgr 127.0.0.1/32 trust
#host replication repmgr 172.16.251.26/24 trust


# IPv4 local connections:
#host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Please find the attachments (ph_hba,pool_hba, pgpool.cong , pool.log files for troubleshooting