View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000501 | Pgpool-II | Bug | public | 2019-04-17 17:34 | 2020-02-25 11:21 |
| Reporter | siva | Assigned To | hoshiai | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 3.7.9 | ||||
| Summary | 0000501: password authentication failed for user "postgres" in 3.7.9 | ||||
| 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 | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
Hi Team, Could you please help us to troubleshoot the issue. Thanks a lot for your support. Regards, Siva. |
|
|
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) |
|
|
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. |
|
|
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. |
|
|
I closed this ticket. If you have a new question, Please email pgpool-general or pgpool-hackers. |
| 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 | |
| 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 |
