Troubleshooting: psql Hangs on Connect over SSH Tunnel Proxy

When psql hangs on connect over an SSH tunnel proxy, the issue usually stems from network latency, firewall drop policies, or incorrect local port forwarding configuration. Immediate rejection is typical for authentication failures, so a silent hang indicates the TCP handshake is not completing. This guide details how to diagnose and resolve these connectivity stalls using standard SSH and PostgreSQL tools.

Understanding the Connection Flow

To troubleshoot effectively, you must understand the data path. When you use an SSH tunnel to access a remote PostgreSQL database, your local machine connects to the SSH server. The SSH server then acts as a relay, connecting to the target database host. If psql hangs, it means the initial TCP SYN packet is sent, but no SYN-ACK response is received within the timeout period. This behavior differs significantly from authentication errors. As noted in previous discussions, configuration issues in pg_hba.conf typically result in immediate connection refusals with specific error codes, not hangs. Therefore, a hanging connection points squarely to network-level blocking or resource exhaustion rather than database permission settings. You should verify that the SSH tunnel itself is established correctly before blaming the database server.

Verifying the SSH Tunnel State

The first step is to ensure the SSH tunnel is active and listening on the correct local port. Use the -L flag to forward a local port to the remote host. For example:

ssh -L 5432:localhost:5432 user@ssh-gateway

If this command fails to return a prompt, the SSH connection itself is blocked. Check if your local firewall is allowing outbound connections on port 22. Additionally, verify that the SSH gateway has internet access and can reach the PostgreSQL host. If the SSH session hangs indefinitely, the problem lies outside of psql entirely. For more insights on managing remote access securely, see Use SSH tunnel to access work PC apps from personal PC.

Diagnostics with Netcat and Traceroute

Isolate the failure point by testing connectivity at different layers of the network stack. Since psql relies on TCP, you can use netcat (or nc) to test raw socket connectivity without involving the PostgreSQL protocol. Run the following command while your SSH tunnel is active:

nc -vz localhost 5432

If nc also hangs, the issue is with the SSH tunnel or the local network interface. If nc connects instantly but psql hangs, the problem may be related to the PostgreSQL protocol negotiation or DNS resolution. You can also use traceroute or mtr to visualize the path. However, note that traceroute through an SSH tunnel requires careful configuration because the traffic is encapsulated. Standard external traceroutes will only show hops up to the SSH gateway, not beyond it. To debug further, check logs on the SSH gateway for dropped packets or rate limiting.

Checking Local Firewall Rules

Local firewalls on your client machine can sometimes interfere with loopback interfaces used by SSH tunnels. On Linux, check iptables or nftables rules. On macOS or Windows, review the built-in firewall settings. Ensure that the application launching psql is permitted to bind to local ports above 1024. A common pitfall is having multiple SSH agents or conflicting port bindings. Ensure no other process is occupying port 5432 locally. Use lsof -i :5432 to identify any existing listeners. If another service is holding the port, change the local forwarding port in your SSH command.

Optimizing SSH and PostgreSQL Settings

Network latency and timeout settings can cause apparent hangs. Adjusting SSH keep-alive messages can prevent intermediate firewalls from dropping idle connections. Add the following options to your SSH command:

ssh -o ServerAliveInterval=60 -o ServerAliveCountMax=3 -L 5432:localhost:5432 user@gateway

These flags send keep-alive packets every 60 seconds, ensuring that NAT devices or firewalls do not consider the connection dead. On the PostgreSQL side, ensure that the server is configured to accept connections from the SSH gateway's IP address. Check postgresql.conf for the listen_addresses setting. It should include the gateway's IP or be set to * for all addresses. Remember, this does not cause hangs; misconfiguration here causes immediate rejections. For more on handling proxy traffic issues, refer to Proxy Server Forwarding Traffic Over Two Consecutive Proxies.

Common Pitfalls and Resolutions

Several subtle issues can mimic a hang. One is DNS resolution delays. If psql tries to resolve the hostname localhost via IPv6 first and your system lacks IPv6 support, it may timeout before falling back to IPv4. Force IPv4 by using 127.0.0.1 instead of localhost in your psql connection string. Another issue is large query execution time masquerading as a connection hang. If you are running a heavy query immediately after connecting, the client may appear stuck. Use psql -c "SELECT 1;" to test basic connectivity. If this returns instantly, your connection is fine, and the delay was caused by your workload. Finally, check for proxy interference. If your corporate network uses an HTTP proxy, it might intercept SSH traffic incorrectly. Configure your SSH client to bypass proxies for internal hosts using the ProxyCommand directive or environment variables like NO_PROXY.

Frequently Asked Questions

Why does psql reject connections immediately but hang in other cases?

Immediate rejections are handled by the application layer, where PostgreSQL sends an error message upon detecting invalid credentials or unauthorized IPs. Hangs occur at the transport layer when the TCP handshake cannot complete due to firewalls, routing issues, or lost packets.

Can pg_hba.conf settings cause psql to hang?

No, pg_hba.conf configurations result in immediate connection failures with clear error messages like "FATAL: password authentication failed." They do not cause silent timeouts or hangs because the database server actively responds to the connection attempt.

How do I force psql to use IPv4 to avoid DNS timeouts?

Specify the IP address 127.0.0.1 explicitly in your psql command or connection URI instead of using the hostname localhost. This bypasses DNS resolution entirely and forces the client to use the IPv4 loopback interface.

What is the best way to test if my SSH tunnel is working?

Use nc -vz localhost 5432 while the tunnel is active. If netcat reports a successful connection, the tunnel is functioning correctly. If it hangs, the issue is with the SSH configuration or network path, not PostgreSQL.

Should I increase the connection timeout in psql?

Increasing the timeout in psql using the -c option or environment variables only delays the error message; it does not fix the underlying network block. It is better to resolve the firewall or routing issue than to wait longer for a connection that will eventually fail.