As a cloud service, Azure Database for PostgreSQL uses a gateway proxy to route connections to a node hosting the actual server. Potentially there could be multiple hops (for example client, optional proxy at the client like pgbouncer for connection pooling, Azure gateway proxy, backend server) in between the client, and the server. For various reasons (client firewall rules, network issues etc.), the connection can be dropped before it is fully authenticated at one of these hops, and it becomes extremely difficult to say where and why the connection is dropped.
The proposal is to tweak the connectivity wire protocol, and add a connection id (GUID) filed in the startup message. We can trace the connection using this GUID and investigate further on where the connection failed.
Client adds a connection id in the startup message and send it to the server it is trying to connect to. Proxy logs the connection id information in its logs, and passes it to the server. Server logs the connection Id in the server log, and set it in the GUC variable (ConnectionId).
When an attempt to connection to the server fails, the connection failed message must include the connection id in the message. This Id can be used to trace the connection end to end.
Customers can provide this Id to the support team to investigate the connectivity issues to the server, along with the server information.
This field can be an optional field driven by the connection parameters for PSql (-C or--include-clientId).
P.S: I am looking for initial feedback on this idea and can provide more design details if needed.
Thanks,
Satya