Routing Postgres connections by SNI

HTTP made this easy for everyone. A request carries a `Host` header, so one IP and one port can serve ten thousand sites — the reverse proxy reads the header and forwards accordingly. Every web platform is built on that one line of the spec.

Postgres has no such header. The wire protocol opens with a startup message containing a username and a database name, and nothing that identifies which server the client meant to reach. So when a managed platform gives every database a hostname like `<id>.db.example.com` and they all resolve to the same endpoint on port 5432, something has to work out where each connection goes. The answer is in the TLS handshake.

SNI: the hostname arrives before anything else

Server Name Indication is a TLS extension that exists to solve the certificate problem. Before a server can present a certificate, it has to know which hostname the client is trying to reach — otherwise it can't pick the right one. So the client puts the hostname in the very first message of the handshake, the ClientHello, in plaintext.

That means a proxy can read the intended hostname from the first packet of a connection, before any TLS session is established and long before any application protocol is spoken. It's the earliest possible routing signal, it's protocol-agnostic, and it's what makes hostname-based routing work for anything that speaks TLS — not just HTTP.

So the routing table is simply hostname to backend. `abc123.db.pandastack.ai` maps to the microVM running that database. The proxy reads SNI, looks up the mapping, and connects.

The Postgres quirk: TLS doesn't start at the start

Here's what makes Postgres different from routing HTTPS, and it surprises everyone implementing this for the first time.

A Postgres connection does not begin with a TLS ClientHello. It begins in cleartext: the client sends an SSLRequest message — eight bytes, essentially 'do you support TLS?' — and the server replies with a single byte, `S` for yes or `N` for no. Only after an `S` does the client start the TLS handshake and send its ClientHello with the SNI extension.

Client                          Proxy                        Database VM
  |                               |                                |
  |-- SSLRequest (8 bytes) ------>|                                |
  |                               |  (cleartext, no hostname yet)  |
  |<------------------- 'S' ------|                                |
  |                               |                                |
  |-- TLS ClientHello ----------->|                                |
  |     server_name = abc123.db.pandastack.ai                      |
  |                               |                                |
  |                               |-- look up abc123 -> VM ------->|
  |<===== TLS established ========|===== connection to backend ===>|
  |                               |                                |
  |-- StartupMessage (user, db) ----------------------------------->|

The hostname appears in message 3, not message 1. A proxy that expects
a TLS handshake on the first byte will fail every Postgres connection.

So the proxy has to speak just enough of the Postgres protocol to recognise the SSLRequest, answer it, and only then behave like a TLS-terminating proxy. Generic TLS proxies that assume a handshake begins on byte one do not work here without explicit Postgres support — which is why the feature list of any TCP proxy will mention Postgres separately if it handles it at all.

Why this architecture is worth the trouble

• Stable connection strings. The hostname is the identity; the backend behind it can move — a restart, a rebuild on another host, a wake from suspension — and the connection string in your application config never changes.
• One endpoint, one port. No per-database port assignment, no port ranges to document, no firewall rules to update whenever someone creates a database. Corporate networks that allow outbound 5432 and nothing else keep working.
• TLS everywhere by default. Since routing requires TLS, there is no unencrypted path to accidentally leave enabled.
• The databases need no public exposure. Only the proxy is reachable from the internet; the VMs sit on internal addressing. That's a materially smaller attack surface than a database listening on a public IP.
• Wake-on-connect becomes possible. Because the proxy is in the path and holds the connection, it can restore a suspended database and complete the connection afterwards instead of refusing it.

What to check as a user

Mostly this is invisible, which is the point. Three things do surface.

Your client must send SNI. Every modern Postgres client does when connecting by hostname with TLS enabled, but a client connecting by IP address cannot send a meaningful SNI value, so connecting to the endpoint's IP will fail. Always connect by hostname.

Your `sslmode` must be at least `require`. And `require` alone does not verify the server's certificate — it encrypts without checking who you're talking to. For anything holding real data use `verify-full`, which checks both the certificate chain and that the hostname matches. This is not specific to SNI routing, but a proxied architecture is exactly the context where certificate verification earns its keep.

# Correct: hostname, TLS, full verification.
psql "postgres://pandastack:$PW@abc123.db.pandastack.ai:5432/pandastack?sslmode=verify-full"

# Fails: no hostname means no SNI means nothing to route on.
psql "postgres://pandastack:$PW@203.0.113.10:5432/pandastack"

# Fails: no TLS means no SNI at all.
psql "postgres://pandastack:$PW@abc123.db.pandastack.ai:5432/pandastack?sslmode=disable"

# Check what the endpoint presents for a given hostname:
openssl s_client -connect abc123.db.pandastack.ai:5432 \
  -servername abc123.db.pandastack.ai -starttls postgres </dev/null

That last command is genuinely useful for debugging. `openssl s_client` understands the Postgres SSLRequest dance via `-starttls postgres`, so it will show you the certificate the proxy presents for a given SNI value — which tells you immediately whether the routing layer recognises your hostname.

The trade-offs

A proxy in the connection path is not free, and the honest accounting is short.

• One extra network hop. Small in the same region, meaningful if the proxy and the database are far apart. Ask where the proxy sits relative to your database.
• The proxy is a dependency. If it's down, every database is unreachable even though every database is fine. It needs to be redundant, and its health is a thing to monitor separately from the databases.
• Connection setup does more work — the SSLRequest exchange, the routing lookup, then the backend connection. Negligible for pooled connections held open, noticeable for workloads that connect per query. Which is another argument for pooling, alongside all the usual ones.
• Long-lived connections are pinned to the proxy instance that accepted them, so draining a proxy for maintenance means either waiting for connections to close or breaking them. Every stateful proxy has this problem; it's worth knowing it exists.

In exchange you get a stable address for a thing that moves, one port for a fleet of any size, mandatory encryption, no publicly exposed databases, and the ability to wake something up while a client politely waits. For a multi-tenant database platform that's a straightforwardly good trade — and it all rests on the fact that TLS puts the hostname in the first message, in the clear, for the benefit of certificate selection. A detail designed for one purpose that turned out to be the foundation of hostname-based routing for every protocol that isn't HTTP.