View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000803Pgpool-IIBugpublic2023-06-20 20:142024-06-12 05:06
Reporterphilmcc Assigned Topengbo  
PriorityhighSeverityblockReproducibilityalways
Status assignedResolutionopen 
PlatformK8s, RDS PostgresOSpgpool docker image 
Product Version4.4.3 
Summary0000803: Pgpool will not connect to RDS Postgres via RDS Proxy
DescriptionI am using pgpool primarily to split read and write queries which it is doing well. I would also like to reduce the number of connections to the actual database. I do not seem to be able to do this with pgpool, I think because our applications use their own connection pooling and hold connections open.

I am trying to use RDS proxy between pgpool and RDS PostgresIn order to use its pooling to reduce the total connections to the actual database.

I can connect using psql through pgproxy using the passwords that I have configured.

My setup in pgpool works fine when connecting directly to the instance but as soon as I try to connect through RDS Proxy I start getting an error on every connection:

ERROR: authentication failed DETAIL: unknown response "Z" before processing BackendKeyData
Steps To ReproduceConneect pgpool to an RDS Proxy instance.
TagsNo tags attached.

Activities

pengbo

2023-06-21 13:56

developer   ~0004402

Sorry. I am not familiar with RDS Proxy.

How did you configure pgpool to connect to RDS Proxy?
If it is possible, could you share your pgpool.log, pool_hba.conf and pg_hba.conf?
Due to pgpool's authentication mechanism, pgpool may not work with RDS Proxy.
If it is possible, could you try if it will be resolved by using "password" authentication in pool_hba.conf?

philmcc

2023-06-21 19:35

reporter   ~0004405

Hi,

Connectng to RDS Proxy is just via an endpoint so it just goes in the backend_hostname0 variable.

I can connect through RDS Proxy to the database instance directly.
I can also connect via pgpool to my database when I use the database instance endpoint instead of the RDS Proxy endpoint.
It is just when I put try to connect pgpool through the rds proxy endpoint to the database that it throws an error.

The contents of the pool_hba.conf are:
   pool_hba.conf: |-
      local all all password
      host all all 127.0.0.1/32 password
      host all all ::1/128 password
      host all all 0.0.0.0/0 password

I dont think that I have access to the pg_hba.conf on AWS RDS Postgres
pgpool.txt (15,523 bytes)    
Configuring Pgpool-II...
Custom pgpool.conf file detected. Use custom configuration files.
Generating pool_passwd...
Skip password encryption. Use the encrypted password.
Skip generating pcp.conf. PGPOOL_PCP_USER or PGPOOL_PCP_PASSWORD isn't defined.
Custom pool_hba.conf file detected. Use custom pool_hba.conf.
Starting Pgpool-II...
2023-06-21 10:18:11.562: main pid 60: LOG:  Backend status file /tmp/pgpool_status does not exist
2023-06-21 10:18:11.562: main pid 60: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2023-06-21 10:18:11.562: main pid 60: LOG:  memory cache initialized
2023-06-21 10:18:11.562: main pid 60: DETAIL:  memcache blocks :64
2023-06-21 10:18:11.563: main pid 60: LOG:  allocating (134768480) bytes of shared memory segment
2023-06-21 10:18:11.563: main pid 60: LOG:  allocating shared memory segment of size: 134768480
2023-06-21 10:18:11.686: main pid 60: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2023-06-21 10:18:11.686: main pid 60: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2023-06-21 10:18:11.686: main pid 60: LOG:  memory cache initialized
2023-06-21 10:18:11.686: main pid 60: DETAIL:  memcache blocks :64
2023-06-21 10:18:11.688: main pid 60: LOG:  pool_discard_oid_maps: discarded memqcache oid maps
2023-06-21 10:18:11.696: main pid 60: LOG:  unix_socket_directories[0]: /tmp/.s.PGSQL.5432
2023-06-21 10:18:11.696: main pid 60: LOG:  listen address[0]: *
2023-06-21 10:18:11.696: main pid 60: LOG:  Setting up socket for 0.0.0.0:5432
2023-06-21 10:18:11.696: main pid 60: LOG:  Setting up socket for :::5432
2023-06-21 10:18:11.697: main pid 60: LOG:  find_primary_node_repeatedly: waiting for finding a primary node
2023-06-21 10:18:11.697: main pid 60: LOG:  listen address[0]: *
2023-06-21 10:18:11.697: main pid 60: LOG:  Setting up socket for 0.0.0.0:9898
2023-06-21 10:18:11.698: main pid 60: LOG:  Setting up socket for :::9898
2023-06-21 10:18:11.698: pcp_main pid 73: LOG:  PCP process: 73 started
2023-06-21 10:18:11.698: sr_check_worker pid 74: LOG:  process started
2023-06-21 10:18:11.699: health_check pid 75: LOG:  process started
2023-06-21 10:18:11.702: main pid 60: LOG:  pgpool-II successfully started. version 4.4.2 (nurikoboshi)
2023-06-21 10:18:11.702: main pid 60: LOG:  node status[0]: 0
2023-06-21 10:18:11.726: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:1)
2023-06-21 10:18:12.669: psql pid 69: ERROR:  authentication failed
2023-06-21 10:18:12.669: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:13.665: psql pid 69: ERROR:  authentication failed
2023-06-21 10:18:13.665: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:15.183: psql pid 71: ERROR:  authentication failed
2023-06-21 10:18:15.183: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:20.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:18:20.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:21.735: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:2)
2023-06-21 10:18:25.189: psql pid 72: ERROR:  authentication failed
2023-06-21 10:18:25.189: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:30.191: psql pid 69: ERROR:  authentication failed
2023-06-21 10:18:30.191: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:31.740: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:3)
2023-06-21 10:18:35.181: psql pid 72: ERROR:  authentication failed
2023-06-21 10:18:35.181: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:40.260: psql pid 70: ERROR:  authentication failed
2023-06-21 10:18:40.260: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:41.747: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:4)
2023-06-21 10:18:45.184: psql pid 70: ERROR:  authentication failed
2023-06-21 10:18:45.184: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:50.185: psql pid 68: ERROR:  authentication failed
2023-06-21 10:18:50.185: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:18:51.762: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:5)
2023-06-21 10:18:55.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:18:55.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:00.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:00.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:01.779: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:6)
2023-06-21 10:19:05.182: psql pid 71: ERROR:  authentication failed
2023-06-21 10:19:05.182: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:10.178: psql pid 68: ERROR:  authentication failed
2023-06-21 10:19:10.178: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:11.789: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:7)
2023-06-21 10:19:15.180: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:15.180: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:20.196: psql pid 68: ERROR:  authentication failed
2023-06-21 10:19:20.196: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:21.798: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:8)
2023-06-21 10:19:25.190: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:25.190: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:30.185: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:30.185: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:31.807: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:9)
2023-06-21 10:19:35.186: psql pid 72: ERROR:  authentication failed
2023-06-21 10:19:35.186: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:40.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:40.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:40.361: psql pid 69: ERROR:  authentication failed
2023-06-21 10:19:40.361: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:41.813: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:10)
2023-06-21 10:19:45.184: psql pid 69: ERROR:  authentication failed
2023-06-21 10:19:45.184: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:50.185: psql pid 70: ERROR:  authentication failed
2023-06-21 10:19:50.185: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:19:51.819: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:11)
2023-06-21 10:19:55.193: psql pid 68: ERROR:  authentication failed
2023-06-21 10:19:55.193: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:00.188: psql pid 68: ERROR:  authentication failed
2023-06-21 10:20:00.188: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:01.830: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:12)
2023-06-21 10:20:05.185: psql pid 69: ERROR:  authentication failed
2023-06-21 10:20:05.185: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:10.186: psql pid 70: ERROR:  authentication failed
2023-06-21 10:20:10.186: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:11.836: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:13)
2023-06-21 10:20:15.183: psql pid 71: ERROR:  authentication failed
2023-06-21 10:20:15.183: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:20.176: psql pid 72: ERROR:  authentication failed
2023-06-21 10:20:20.176: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:21.844: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:14)
2023-06-21 10:20:25.177: psql pid 71: ERROR:  authentication failed
2023-06-21 10:20:25.177: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:30.180: psql pid 68: ERROR:  authentication failed
2023-06-21 10:20:30.180: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:31.850: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:15)
2023-06-21 10:20:35.179: psql pid 72: ERROR:  authentication failed
2023-06-21 10:20:35.179: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:40.178: psql pid 72: ERROR:  authentication failed
2023-06-21 10:20:40.178: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:41.858: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:16)
2023-06-21 10:20:45.176: psql pid 69: ERROR:  authentication failed
2023-06-21 10:20:45.176: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:50.195: psql pid 69: ERROR:  authentication failed
2023-06-21 10:20:50.195: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:20:51.868: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:17)
2023-06-21 10:20:55.192: psql pid 71: ERROR:  authentication failed
2023-06-21 10:20:55.192: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:00.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:21:00.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:00.359: psql pid 71: ERROR:  authentication failed
2023-06-21 10:21:00.359: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:01.878: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:18)
2023-06-21 10:21:05.183: psql pid 70: ERROR:  authentication failed
2023-06-21 10:21:05.183: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:10.195: psql pid 69: ERROR:  authentication failed
2023-06-21 10:21:10.195: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:11.888: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:19)
2023-06-21 10:21:15.182: psql pid 68: ERROR:  authentication failed
2023-06-21 10:21:15.182: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:20.185: psql pid 70: ERROR:  authentication failed
2023-06-21 10:21:20.185: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:21.894: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:20)
2023-06-21 10:21:25.189: psql pid 69: ERROR:  authentication failed
2023-06-21 10:21:25.189: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:30.182: psql pid 70: ERROR:  authentication failed
2023-06-21 10:21:30.182: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:31.904: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:21)
2023-06-21 10:21:35.182: psql pid 69: ERROR:  authentication failed
2023-06-21 10:21:35.182: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:40.184: psql pid 68: ERROR:  authentication failed
2023-06-21 10:21:40.184: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:41.912: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:22)
2023-06-21 10:21:45.186: psql pid 69: ERROR:  authentication failed
2023-06-21 10:21:45.186: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:50.184: psql pid 68: ERROR:  authentication failed
2023-06-21 10:21:50.184: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:21:51.927: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:23)
2023-06-21 10:21:55.184: psql pid 68: ERROR:  authentication failed
2023-06-21 10:21:55.184: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:00.184: psql pid 69: ERROR:  authentication failed
2023-06-21 10:22:00.184: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:01.942: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:24)
2023-06-21 10:22:05.181: psql pid 70: ERROR:  authentication failed
2023-06-21 10:22:05.181: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:10.188: psql pid 72: ERROR:  authentication failed
2023-06-21 10:22:10.188: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:11.949: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:25)
2023-06-21 10:22:15.187: psql pid 71: ERROR:  authentication failed
2023-06-21 10:22:15.187: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:20.184: psql pid 72: ERROR:  authentication failed
2023-06-21 10:22:20.184: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:21.959: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:26)
2023-06-21 10:22:25.181: psql pid 72: ERROR:  authentication failed
2023-06-21 10:22:25.181: psql pid 72: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:30.182: psql pid 68: ERROR:  authentication failed
2023-06-21 10:22:30.182: psql pid 68: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:30.360: psql pid 70: ERROR:  authentication failed
2023-06-21 10:22:30.360: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:31.965: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:27)
2023-06-21 10:22:35.194: psql pid 70: ERROR:  authentication failed
2023-06-21 10:22:35.194: psql pid 70: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:40.181: psql pid 69: ERROR:  authentication failed
2023-06-21 10:22:40.181: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:41.973: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:28)
2023-06-21 10:22:45.181: psql pid 71: ERROR:  authentication failed
2023-06-21 10:22:45.181: psql pid 71: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:50.188: psql pid 69: ERROR:  authentication failed
2023-06-21 10:22:50.188: psql pid 69: DETAIL:  unknown response "Z" before processing BackendKeyData
2023-06-21 10:22:51.982: health_check0 pid 75: LOG:  health check retrying on DB node: 0 (round:29)
pgpool.txt (15,523 bytes)   

pengbo

2023-06-21 22:15

developer   ~0004407

> I can connect through RDS Proxy to the database instance directly.
> I can also connect via pgpool to my database when I use the database instance endpoint instead of the RDS Proxy endpoint.

As I mentioned it may be caused because of the pgpool's authentication mechanism.

Does your RDS Proxy connect to multiple database instances?
When you connect to the database instance directly, how did you set backend_hostnameX?
Which authentication method are your database using?

> The contents of the pool_hba.conf are:
> pool_hba.conf: |-
> local all all password
> host all all 127.0.0.1/32 password
> host all all ::1/128 password
> host all all 0.0.0.0/0 password

Did you get the same error if you tried the above setting and "enable_pool_hba = on"?

philmcc

2023-06-21 23:06

reporter   ~0004409

Yes, that error is the same with both "enable_pool_hba = on" and "enable_pool_hba = off"

When I connect directly to the database through pgpool, I just change the backend hostname to point to the instance endpoint for the database.

> Which authentication method are your database using?
I'm not sure about this, how would I check?

pengbo

2023-06-22 18:36

developer   ~0004410

Sorry, I am not familiar with RDS Proxy. Does RDS Proxy perform authentication with pgpool or just forward the request to RDS backends?

Since the error message doesn't show any helpful information, if it is possible could you enable Pgpool-II's DEBUG mode by using "log_min_messages = debug5"?

> I am using pgpool primarily to split read and write queries which it is doing well. I would also like to reduce the number of connections to the actual database.
> I do not seem to be able to do this with pgpool, I think because our applications use their own connection pooling and hold connections open.

If you don't want to use connection pooling between pgpool and postgres, I think you can disable connection pooling by using "connection_cache = off".

philmcc

2023-06-22 20:21

reporter   ~0004411

RDS Proxy relies on a secret which must have the username/password combo for the user in it. When it gets a request, it checks the password against that before connecting.

When connecting via psql it is seamless and you don't notice that there is a proxy in the middle.
pgpool-2.txt (483,112 bytes)

philmcc

2023-06-29 20:42

reporter   ~0004415

In case this helps, I have had a response from AWS who have been looking at it from the RDS Proxy side:

-----------------------------------
The message format of
RSSSSSSSSSSSSSSSKZ

is a limitation that is documented publicly in our docs

For PostgreSQL, RDS Proxy doesn't currently support canceling a query from a client by issuing a CancelRequest. This is the case, for example, when you cancel a long-running query in an interactive psql session by using Ctrl+C.

The 'K' in the message format that pgoool is sending can't be processed as stated above
[2]
BackendKeyData (B)

Byte1('K')

Identifies the message as cancellation key data. The frontend must save these values if it wishes to be able to issue CancelRequest messages later.
Int32(12)

Length of message contents in bytes, including self.
Int32

The process ID of this backend.
Int32

The secret key of this backend.


CX should investigate whether there are any settings that they can tweak so pgpool does not send the 'K' message byte

-----------------------------------

Is there any way to stop pgpool from sending this 'k' message byte?

Issue History

Date Modified Username Field Change
2023-06-20 20:14 philmcc New Issue
2023-06-21 09:18 pengbo Assigned To => pengbo
2023-06-21 09:18 pengbo Status new => assigned
2023-06-21 13:56 pengbo Note Added: 0004402
2023-06-21 19:35 philmcc Note Added: 0004405
2023-06-21 19:35 philmcc File Added: pgpool.txt
2023-06-21 22:15 pengbo Note Added: 0004407
2023-06-21 23:06 philmcc Note Added: 0004409
2023-06-22 18:36 pengbo Note Added: 0004410
2023-06-22 20:21 philmcc Note Added: 0004411
2023-06-22 20:21 philmcc File Added: pgpool-2.txt
2023-06-29 20:42 philmcc Note Added: 0004415