View Issue Details

IDProjectCategoryView StatusLast Update
0000501Pgpool-IIBugpublic2020-02-25 11:21
ReportersivaAssigned Tohoshiai 
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version3.7.9 
Target VersionFixed in Version 
Summary0000501: password authentication failed for user "postgres" in 3.7.9
DescriptionHi 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
TagsNo tags attached.

Activities

siva

2019-04-17 17:34

reporter  

authentication_issue log.sql (15,975 bytes)
pool_passwd (129 bytes)
pgpool:md5f24aeb1c3b7d05d7eaf2cd648c307092
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
test:md505a671c66aefea124cc08b76ea6d30bb
pool_passwd (129 bytes)
pgpool.log (63,741 bytes)
pgpool.conf (36,147 bytes)

siva

2019-04-17 17:35

reporter   ~0002564

Hi Team,

Could you please help us to troubleshoot the issue.

Thanks a lot for your support.

Regards,
Siva.

hoshiai

2019-04-18 15:29

developer   ~0002569

Hi Siva,

I was checked attached log, is this log correct?
this log contains follows, so pgpool-II is not 3.7.9.
> LOG: pgpool-II successfully started. version 3.7.8 (amefuriboshi)

siva

2019-04-25 13:34

reporter   ~0002574

Hi hoshiai,

Good Morning.
Sorry for that, instead of attaching 3.7.9 , attached 3.7.8..

Will attache the correct logs.

Please suggest us , what might be the reason for this even all configured as per documentation.

Thanks for your support.

hoshiai

2020-02-05 09:44

developer   ~0003135

I couldn't see correct logs. Do you have any questions yet?
If you don't have any problems, I would like to close this issue.

hoshiai

2020-02-25 11:21

developer   ~0003224

I closed this ticket.
If you have a new question, Please email pgpool-general or pgpool-hackers.

Issue History

Date Modified Username Field Change
2019-04-17 17:34 siva New Issue
2019-04-17 17:34 siva File Added: pgpool.conf
2019-04-17 17:34 siva File Added: pgpool.log
2019-04-17 17:34 siva File Added: pool_passwd
2019-04-17 17:34 siva File Added: authentication_issue log.sql
2019-04-17 17:35 siva Note Added: 0002564
2019-04-18 14:23 hoshiai Assigned To => hoshiai
2019-04-18 14:23 hoshiai Status new => assigned
2019-04-18 14:23 hoshiai Description Updated View Revisions
2019-04-18 15:29 hoshiai Note Added: 0002569
2019-04-25 13:34 siva Note Added: 0002574
2020-02-05 09:44 hoshiai Note Added: 0003135
2020-02-05 09:45 hoshiai Status assigned => feedback
2020-02-25 11:21 hoshiai Status feedback => closed
2020-02-25 11:21 hoshiai Resolution open => fixed
2020-02-25 11:21 hoshiai Note Added: 0003224