RDS Proxy incompatibility

[artemvovk]

pgpool-II version 4.6.2 (and 4.5.5, 4.5.4, probably others too)
Given the following configs:

    enable_pool_hba = 'off'
    allow_clear_text_frontend_auth = 'on'
    load_balance_mode = 'off'

and with some basic setup to use an md5 auth for the backend (ssl disabled).
My backend_hostname0 is an RDS Proxy (https://aws.amazon.com/rds/proxy/) in front of a Postgres 13 RDS database

I ran into obscure errors when trying to log in with valid and invalid passwords:

2025-06-12 11:41:01.972: main pid 33: LOCATION:  pgpool_main.c:663
2025-06-12 11:41:01.972: main pid 33: LOG:  node status[0]: 0
2025-06-12 11:41:01.972: main pid 33: LOCATION:  pgpool_main.c:674
2025-06-12 11:41:26.747: psql pid 61: ERROR:  authentication failed
2025-06-12 11:41:26.747: psql pid 61: DETAIL:  unknown response "Z" before processing BackendKeyData
2025-06-12 11:41:26.747: psql pid 61: LOCATION:  pool_auth.c:717
2025-06-12 11:41:39.879: psql pid 53: ERROR:  authentication failed
2025-06-12 11:41:39.880: psql pid 53: DETAIL:  unknown response "Z" before processing BackendKeyData
2025-06-12 11:41:39.880: psql pid 53: LOCATION:  pool_auth.c:717
2025-06-12 11:42:25.887: child pid 51: ERROR:  backend authentication failed
2025-06-12 11:42:25.888: child pid 51: DETAIL:  backend response with kind 'E' when expecting 'R'
2025-06-12 11:42:25.888: child pid 51: HINT:  This issue can be caused by version mismatch (current version 3)
2025-06-12 11:42:25.888: child pid 51: LOCATION:  pool_auth.c:369
2025-06-12 11:43:07.883: psql pid 59: ERROR:  authentication failed
2025-06-12 11:43:07.883: psql pid 59: DETAIL:  unknown response "Z" before processing BackendKeyData
2025-06-12 11:43:07.883: psql pid 59: LOCATION:  pool_auth.c:717
2025-06-12 11:43:13.237: psql pid 61: ERROR:  backend authentication failed
2025-06-12 11:43:13.237: psql pid 61: DETAIL:  backend response with kind 'E' when expecting 'R'
2025-06-12 11:43:13.237: psql pid 61: LOCATION:  pool_auth.c:2204
2025-06-12 11:43:22.344: psql pid 59: ERROR:  authentication failed
2025-06-12 11:43:22.344: psql pid 59: DETAIL:  unknown response "Z" before processing BackendKeyData
2025-06-12 11:43:22.344: psql pid 59: LOCATION:  pool_auth.c:717
2025-06-12 11:48:47.023: psql pid 61: ERROR:  authentication failed
2025-06-12 11:48:47.023: psql pid 61: DETAIL:  unknown response "Z" before processing BackendKeyData
2025-06-12 11:48:47.023: psql pid 61: LOCATION:  pool_auth.c:717

Specifically

2025-06-12 11:48:47.023: psql pid 61: DETAIL:  unknown response "Z" before processing BackendKeyData

Seems to happen on what should be a successful authentication.

I have verified that removing the RDS proxy and connecting pgpool-II directly to the RDS Instance resolves the issue and authentication works.

[tatsuo-ishii]

Pgpool was expecting BackendKeyData message arrived before 'Z' (ReadyForQuery) arrived. It's expected according to frontend\backend protocol.
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-START-UP]

I wonder if RDS proxy follows the protocol.

[artemvovk]

Pretty much every other client/library is able to handle that message being skipped (e.g. CockroachDB also didn't send BackendKeydata) - in general it is considered optional.

[tatsuo-ishii]

Without BackendKeydata, how could RDS Proxy clients cancel a query?

[artemvovk]

Not all postgres servers (and not all SQL proxies) offer an option to cancel queries to their clients.
In case of multiplexing proxies and/or distributed RDMS - the option is to find the pid and execute some kind of kill/cancel function.