View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000771 | Pgpool-II | General | public | 2022-10-29 07:52 | 2022-11-01 14:24 |
| Reporter | Sanjay Heisnam | Assigned To | |||
| Priority | urgent | Severity | major | Reproducibility | have not tried |
| Status | new | Resolution | open | ||
| Product Version | 4.3.3 | ||||
| Summary | 0000771: Unable to connect pgpool port 9999 using newly created users and database | ||||
| Description | pgpool --version pgpool-II version 4.3.3 (tamahomeboshi) With 3 Nodes using Postgresql-14 as I was trying to connect database "cdfapiserverdb" with user - "cdfapiserveruser", I received below errors psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 9999 Password for user cdfapiserveruser: psql: connection to server at "16.32.12.9", port 9999 failed: ERROR: backend authentication failed DETAIL: backend response with kind 'E' when expecting 'R' connection to server at "16.32.12.9", port 9999 failed: ERROR: backend authentication failed DETAIL: backend response with kind 'E' when expecting 'R' I have below configurations - cat pool_passwd pgpool:AEShG3DvX96unPcJpwntJ8jww== postgres:AESx6Bz1+xyIOf5/hh4DVLimg== cdfapiserveruser:AESx6Bz1+xyIOf5/hh4DVLimg== cdfidmuser:AESx6Bz1+xyIOf5/hh4DVLimg== bvd:AESx6Bz1+xyIOf5/hh4DVLimg== I have below entries at pool_hba.conf hostssl all cdfidmuser 0.0.0.0/0 scram-sha-256 hostssl all cdfapiserveruser 0.0.0.0/0 scram-sha-256 hostssl cdfidmdb cdfidmuser 0.0.0.0/0 scram-sha-256 hostssl cdfapiserverdb cdfapiserveruser 0.0.0.0/0 scram-sha-256 cat /var/lib/pgsql/.pgpass postgres1:5432:replication:repl:repl postgres2:5432:replication:repl:repl postgres3:5432:replication:repl:repl postgres1:5432:postgres:postgres:postgres postgres2:5432:postgres:postgres:postgres postgres3:5432:postgres:postgres:postgres cat /var/lib/pgsql/.pgpoolkey postgers cat /var/lib/pgsql/.pcppass "*":9898:pgpool:pgpool "*":9898:postgres:postgres "*":9898:cdfidmuser:postgres "*":9898:cdfapiserveruser:postgres [root@postgres1 pgpool-II]# Not sure what I missed here Using default port 5432 for the same user, it can connect psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 5432 Password for user cdfapiserveruser: psql (9.2.24, server 14.5) WARNING: psql version 9.2, server version 14.0. Some psql features might not work. SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256) Type "help" for help. cdfapiserverdb=> | ||||
| Steps To Reproduce | I followed the steps given - https://www.pgpool.net/docs/latest/en/html/example-cluster.html and only difference here is I enabled SSL both for pgpool and postgres | ||||
| Tags | No tags attached. | ||||
|
|
from pool log file, I got this 2022-10-28 17:19:48.198: sr_check_worker pid 13136: CONTEXT: while checking replication time lag 2022-10-28 17:19:48.204: sr_check_worker pid 13136: LOG: verify_backend_node_status: primary 0 owns only 0 standbys out of 2 2022-10-28 17:19:50.811: child pid 13055: LOG: pool_ssl: "SSL_read": "no SSL error reported" 2022-10-28 17:19:50.811: child pid 13055: ERROR: unable to read data from frontend 2022-10-28 17:19:50.811: child pid 13055: DETAIL: socket read failed with error "Success" 2022-10-28 17:19:53.491: child pid 13113: ERROR: backend authentication failed 2022-10-28 17:19:53.491: child pid 13113: DETAIL: backend response with kind 'E' when expecting 'R' 2022-10-28 17:19:53.644: child pid 13096: ERROR: backend authentication failed 2022-10-28 17:19:53.644: child pid 13096: DETAIL: backend response with kind 'E' when expecting 'R' 2022-10-28 17:19:58.205: watchdog pid 12630: LOG: new IPC connection received 2022-10-28 17:19:58.351: sr_check_worker pid 13136: LOG: get_query_result failed: status: -2 2022-10-28 17:19:58.351: sr_check_worker pid 13136: CONTEXT: while checking replication time lag |
|
|
My first impression is pgpool may not be properly configured to use SSL. You can confirm this by adding something like to pg_hba.conf. host all cdfapiserveruser 0.0.0.0/0 scram-sha-256 If you issue the command below and succeed this time, it proves my theory. psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 9999 |
|
|
hello t-ishii, I have the same configuration with the Postgres pg_hba.conf file - please see this and but there is no changes, still unable to connect # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 host all all samenet scram-sha-256 host replication all samenet scram-sha-256 #hostssl all all 0.0.0.0/0 md5 #hostssl replication all 0.0.0.0/0 md5 hostssl all all 0.0.0.0/0 scram-sha-256 hostssl all all samenet scram-sha-256 hostssl replication all samenet scram-sha-256 hostssl all cdfidmuser 0.0.0.0/0 scram-sha-256 hostssl all cdfapiserveruser 0.0.0.0/0 scram-sha-256 #hostssl cdfidmdb cdfidmuser 0.0.0.0/0 scram-sha-256 #hostssl cdfapiserverdb cdfapiserveruser 0.0.0.0/0 scram-sha-256 as I mentioned I still can connect to port 5432 (postgres default port) psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 5432 Password for user cdfapiserveruser: psql (9.2.24, server 14.5) WARNING: psql version 9.2, server version 14.0. Some psql features might not work. SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256) Type "help" for help. cdfapiserverdb=> cdfapiserverdb=> cdfapiserverdb=> \q [root@postgres1 ~]# psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 9999 Password for user cdfapiserveruser: psql: connection to server at "16.32.12.9", port 9999 failed: ERROR: backend authentication failed DETAIL: backend response with kind 'E' when expecting 'R' connection to server at "16.32.12.9", port 9999 failed: ERROR: backend authentication failed DETAIL: backend response with kind 'E' when expecting 'R' [root@postgres1 ~]# [root@postgres1 ~]# |
|
|
When "psql -h 16.32.12.9 -U cdfapiserveruser -d cdfapiserverdb -p 9999" failed, did you find some errors in PostgreSQL log? I still think that the error was caused by that no matching entry was found for pgoool to connect to PostgreSQL in pg_hba.conf. In this case you should see something like: "no pg_hba.conf entry for host..." |
|
|
Ok, these are entries I have - for pool_hba.conf # 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 trust #host all all ::1/128 trust #host all pgpool 0.0.0.0/0 scram-sha-256 #host all postgres 0.0.0.0/0 scram-sha-256 #host all all 0.0.0.0/0 scram-sha-256 hostssl all pgpool 0.0.0.0/0 scram-sha-256 hostssl all postgres 0.0.0.0/0 scram-sha-256 hostssl all all 0.0.0.0/0 scram-sha-256 hostssl all cdfidmuser 16.32.12.9/23 scram-sha-256 hostssl all cdfapiserveruser 16.32.12.9/23 scram-sha-256 and pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: #host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: #host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer #host replication all 127.0.0.1/32 scram-sha-256 #host replication all ::1/128 scram-sha-256 #host all all samenet scram-sha-256 #host replication all samenet scram-sha-256 hostssl all all samenet scram-sha-256 hostssl replication all samenet scram-sha-256 hostssl all all 0.0.0.0/0 scram-sha-256 hostssl all cdfidmuser 0.0.0.0/0 scram-sha-256 hostssl all cdfapiserveruser 0.0.0.0/0 scram-sha-256 #hostssl cdfidmdb cdfidmuser 0.0.0.0/0 scram-sha-256 #hostssl cdfapiserverdb cdfapiserveruser 0.0.0.0/0 scram-sha-256 And I dont see any log/error under $PGDATA/log/* Now i dont know what else I can try |
|
|
I also tested using the setting for pool_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only, but it didnt help local all all trust # IPv4 local connections: #host all all 127.0.0.1/32 trust #host all all ::1/128 trust #host all pgpool 0.0.0.0/0 scram-sha-256 #host all postgres 0.0.0.0/0 scram-sha-256 #host all all 0.0.0.0/0 scram-sha-256 hostssl all pgpool 0.0.0.0/0 scram-sha-256 hostssl all postgres 0.0.0.0/0 scram-sha-256 hostssl all all 0.0.0.0/0 scram-sha-256 hostssl all cdfidmuser 0.0.0.0/0 scram-sha-256 hostssl all cdfapiserveruser 0.0.0.0/0 scram-sha-256 hostssl cdfidmdb cdfidmuser 16.32.12.51/23 scram-sha-256 hostssl cdfapiserverdb cdfapiserveruser 16.32.12.51/23 scram-sha-256 host cdfidmdb cdfidmuser 16.32.12.51/23 scram-sha-256 host cdfapiserverdb cdfapiserveruser 16.32.12.51/23 scram-sha-256 |
|
|
pool_hba.conf is only relevant to clients and pgpool. |
|
|
Now I suspect pgpool does not actually connect to PostgreSQL on 16.32.12.9. I recommend to check backend_hostname0 and backend_hostname1 parameter in pgpool.conf. Also you can verify whether pgpool actually connects to PostgreSQL on 16.32.12.9 by adding following to postgresql.conf. log_connections = on Moreover add following to pgpool.conf will greatly help the issue: log_min_messages = debug5 Notice that this will generate tremendous number of debug logs. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-10-29 07:52 | Sanjay Heisnam | New Issue | |
| 2022-10-29 08:24 | Sanjay Heisnam | Note Added: 0004128 | |
| 2022-10-29 20:15 | t-ishii | Note Added: 0004129 | |
| 2022-11-01 05:44 | Sanjay Heisnam | Note Added: 0004130 | |
| 2022-11-01 09:50 | t-ishii | Note Added: 0004131 | |
| 2022-11-01 10:18 | t-ishii | Note Edited: 0004131 | |
| 2022-11-01 12:38 | Sanjay Heisnam | Note Added: 0004132 | |
| 2022-11-01 13:53 | Sanjay Heisnam | Note Added: 0004133 | |
| 2022-11-01 14:15 | t-ishii | Note Added: 0004134 | |
| 2022-11-01 14:24 | t-ishii | Note Added: 0004135 |
