The Postgres wire protocol: startup, TLS, SCRAM and SNI routing

Postgres's frontend/backend protocol is the most widely implemented SQL wire protocol there is. libpq speaks it, and so do JDBC, Npgsql, pgx, asyncpg, psycopg, every ORM and every BI tool with a "PostgreSQL" entry in its connection dialog. Speaking it means all of them work without a Loams driver. That is the whole argument, and it is why we decided that Postgres wire is the SQL protocol for Loams' Postgres product, with any additions made as opt-in extensions and never as a new wire protocol (D1030).

How the protocol worksCopy Anchor Link

Framing. After startup every message is a one-byte type, a 32-bit big-endian length that counts itself but not the type byte, and a payload. Q is a simple query, P a Parse, D a DataRow (backend) or Describe (frontend): the meaning of a type byte depends on direction. The first packet on a connection is the exception. It has no type byte, only a length and a 4-byte code, because the server has to learn what the client wants before anything else is agreed.

Startup. That first packet is one of four things, told apart by its code:

• StartupMessage: protocol version 196608 (3.0, that is 3 << 16) followed by name\0value\0 pairs (user, database, application_name, options) and a final \0.
• SSLRequest (80877103): "can we switch to TLS?". The server answers with a single unframed byte, S or N. On S the client starts a TLS handshake on the same socket and sends its StartupMessage inside it.
• GSSENCRequest (80877104): the same question for GSSAPI encryption.
• CancelRequest (80877102, 16 bytes): a process id and secret key, sent on a new connection to cancel a query running on another one.

Direct TLS. PostgreSQL 17 added a second way in. With sslnegotiation=direct, libpq skips the SSLRequest round trip and opens with a TLS ClientHello. To keep a TLS-terminating proxy from mistaking the stream for HTTPS, the protocol requires the ALPN protocol postgresql on a direct connection, and libpq closes the connection if the server does not select it. The default is still sslnegotiation=postgres, the SSLRequest dance, and every other driver we know of only does that.

Authentication. After the StartupMessage the server sends an Authentication* message (type R) saying what it wants: cleartext password, MD5, or SASL. Modern servers use SASL with SCRAM-SHA-256 (RFC 7677): AuthenticationSASL lists the mechanisms, the client sends SASLInitialResponse, and two more round trips prove that both sides know the password without sending it. SCRAM-SHA-256-PLUS adds channel binding: the client mixes a hash of the server's TLS certificate (tls-server-end-point, RFC 5929) into the proof, so a man in the middle with a different certificate cannot relay the exchange. Remember that one; it decided part of our gateway design.

Then come AuthenticationOk, a run of ParameterStatus messages (server_version, client_encoding, TimeZone…), BackendKeyData (the process id and secret key for cancels), and ReadyForQuery with a status byte: I idle, T in a transaction, E in a failed transaction.

Simple and extended query. The simple protocol is one Query message with SQL text; the server answers RowDescription, DataRows, CommandComplete and ReadyForQuery. Values come back as text. The extended protocol splits the work: Parse (SQL with $1 placeholders, optionally named so it can be reused), Bind (parameter values, and per column whether results come back as text or binary), Describe, Execute (with a row limit, which is how portals stream), and Sync, which ends the batch and makes the server report errors and ready-state. Drivers pipeline Parse/Bind/Execute/Sync without waiting, which is why prepared statements in Postgres cost no extra round trip.

Why Loams speaks it twiceCopy Anchor Link

Loams has two quite different things behind the Postgres protocol, and they should not be confused.

Loams Postgres is our hard fork of Neon (ostrium-labs/loams-postgres), whose storage lives on the bucket. Its computes are Postgres, so the protocol is Postgres's own implementation. What Loams adds is the path in front of it: PgDog per namespace for TLS, SCRAM, pooling and routing by database name (D708), TLS on every hop with plaintext refused (D710), SCRAM-SHA-256 only (D711), and the gateway this post spends most of its time on. Postgres and MySQL on your bucket and the Loams Postgres Learn page cover the storage side.

The collection listener is something else: the pgwire feature of the loams engine terminates the protocol itself and answers with DataFusion over your collections, so psql, a BI tool or pandas' read_sql can query vectors and documents with SQL. It is the same SQL surface as the native API's SQL endpoint, described in DataFusion and Arrow.

How we implement itCopy Anchor Link

The collection listener (loams --pg-listen)Copy Anchor Link

The listener is built on datafusion-postgres 0.18 and the pgwire crate beneath it, which give us message codecs, the simple and extended query handlers and a pg_catalog emulation good enough for clients that introspect on connect. Loams adds the rest in crates/loams/src/pg:

• Read-only. A hook lets through queries, SET/SHOW and BEGIN/COMMIT/ROLLBACK and refuses everything else with SQLSTATE 25006 (read_only_sql_transaction).
• Bounded. Every query runs inside the same row and time limits as every other SQL surface. Past the time limit a client gets 57014 (query_canceled), past the row limit 54000 (program_limit_exceeded): standard codes, so drivers classify them correctly.
• Both query protocols, with the portal's text or binary result format honoured.
• One namespace per listener (--pg-namespace), up to 256 connections by default.

What it does not do, deliberately:

• No authentication and no TLS. The startup handler accepts anyone, so the listener refuses to bind anything but loopback. That follows D111, which deferred auth and TLS for every secondary listener to one unified plan. The 0.1.0 compose stack builds the engine with pgwire but keeps the listener inside the compose network, and the release notes say not to publish it.
• No DECLARE. The cursor hook would re-plan the query and materialise the entire result outside the limits, so DECLARE is refused; FETCH and CLOSE find no cursor.
• No vector columns in results. The encoder cannot return FixedSizeList or LargeList columns, which is what an embedding is. Selecting one gets 0A000 and a message saying to select scalar columns. Rank with vector_search(...) in SQL and return ids and scores; fetch embeddings over Arrow Flight SQL, which carries them natively.

We do not publish a tested-driver matrix for this listener yet, so treat it as experimental.

The gateway: SNI routing for Postgres (loams-gateway)Copy Anchor Link

Loams Postgres runs one PgDog Deployment per namespace, and clients name their namespace in the hostname: <ns>.<region>.pg.<base-domain> (D708). Something has to read that name and pick the PgDog. The obvious tool is an L4 proxy that routes on TLS SNI, and that is exactly what does not work, for three reasons recorded in design 57:

1. The SNI is not in the first bytes. A default Postgres client sends the 8-byte SSLRequest first and waits for S. A proxy that looks for a ClientHello at the start of the stream (Envoy's tls_inspector, Traefik's HostSNI) sees SSLRequest and finds no SNI. Envoy's Postgres filter can answer S itself, but only by terminating TLS in Envoy, which moves the certificate out of PgDog.
2. PgDog reads no SNI and does TLS only after SSLRequest. Pass it a raw ClientHello and the handshake fails.
3. PgDog negotiates no ALPN. So even a proxy that replayed an SSLRequest to PgDog and then passed a direct client's ClientHello through would fail at the client, because libpq 17 requires postgresql to be selected.

So we decided to build a gateway rather than deploy Envoy or Traefik for this (D1010, D1011), and the same crate will later carry the HTTP, LLM and MCP gateways. Its pg module does this:

• SSLRequest clients are spliced (D1014). The gateway answers S, reads the ClientHello (keeping every byte), takes the namespace from the SNI, sends its own SSLRequest to that namespace's PgDog, requires S, writes the client's ClientHello bytes, and from then on copies bytes both ways. TLS runs end to end between the client and PgDog; the gateway never holds the key and never sees a password or a query. Almost all traffic takes this path, because it is what every driver does by default.
• Direct-TLS clients are terminated (D1014). The gateway needs ALPN postgresql, which only it can select, so it accepts the TLS itself. Here channel binding bites: libpq uses SCRAM-SHA-256-PLUS when offered, and PgDog checks the binding against its certificate. A gateway certificate of its own, however valid, makes every direct client fail authentication with a "password is wrong" error. The gateway's integration test found this. So the gateway connects to PgDog first, verifies its certificate, and presents that very certificate to the client, from a copy it holds. If it holds no copy it refuses with a log line that names the fix rather than letting authentication fail mysteriously.
• Refusals happen before TLS, with clear errors (D1015). A plaintext StartupMessage gets FATAL 28000 and a message saying TLS is required. GSSENCRequest gets N (libpq then falls back to SSLRequest). An unknown SNI gets the TLS alert unrecognized_name; a direct ClientHello without ALPN postgresql gets no_application_protocol. Bytes that arrive after SSLRequest but before the gateway's S close the connection: that is the buffered-data injection pattern of CVE-2021-23214.
• Hard limits before authentication (D1020): 10 s to finish the pre-TLS exchange and the ClientHello, 8 bytes for SSLRequest, 10 KiB for a plaintext startup, 64 KiB for a ClientHello, with fuzz targets for the startup classifier and the replay buffer. Sniffing uses rustls' own ClientHello parser, so Loams writes no TLS parser.
• Clients without SNI (libpq before 14, tools that connect by IP) get a dedicated port per namespace on request (D1016), allocated from a range and never reused within 24 hours.
• Routes come from a file that pg-control renders from its records, polled every second and swapped atomically; an invalid file keeps the old table, and live connections are never cut by a reload (D1018).

GW1 Tasks 1 to 7 are on dev: the crate, the pg module, the routes file and an integration test that runs libpq 17's psql through the gateway to the pinned, unmodified PgDog with channel_binding=require, on both paths, plus a dedicated port and the refusals. It is not in the 0.1.0 release images yet, which is why it is marked experimental.

Protocol extensions, opt-inCopy Anchor Link

Design 58 looked hard at what SQL Server's TDS does better than Postgres wire (in-band cancel, acknowledged feature negotiation, session recovery, server-initiated redirect) and decided against a TDS server (D1031). Instead, the gateway will offer those as Postgres extensions to clients that ask (D1032 to D1038):

• A Loams-aware client offers ALPN loams-pg/1 before postgresql, so the gateway knows to terminate and parse instead of splicing.
• Extensions are requested as startup parameters in Postgres's reserved _pq_. namespace (_pq_.loams.resume=1), stripped before PgDog, and acknowledged in a ParameterStatus named loams.extensions. Unknown ones are listed in the standard NegotiateProtocolVersion reply. Clients send a random "grease" parameter so a middlebox that chokes on unknown _pq_ options is found on day one.
• The first four: resumable sessions (sealed session-state tokens replayed after a reconnect, never across an open transaction), GoAway with an optional redirect, cancel on the same connection, and per-request attributes such as traceparent.

A plain client sees stock Postgres and nothing else. All of this is planned, not built.

GotchasCopy Anchor Link

• sslmode=require is not verification. It encrypts, but libpq does not check the server's identity with it (it validates the certificate chain only if a root CA file happens to exist, and never the host name). Use verify-full with Loams Postgres; SNI routing does not change what the certificate proves.
• Cancels go to a different connection. A plaintext CancelRequest carries no SNI, so on the shared port the gateway cannot route it and drops it (and counts it). Use a dedicated port, or libpq 17's encrypted cancel API (PQcancelCreate), which goes through TLS with SNI like any connection. This is also why the gateway sends every connection of a namespace to the first healthy PgDog replica rather than spreading them (D1017): PgDog's open build does not forward a cancel it did not issue.
• Direct TLS means the gateway holds a copy of PgDog's key. If that matters to you, run tls_mode = "passthrough", which splices everything and refuses direct clients.
• Old clients without SNI need a dedicated port, or a single-node default_namespace.
• The collection listener is loopback-only and unauthenticated. Do not forward it off the host in 0.1.0.
• The Postgres development stack in 0.1.0 has no TLS and default credentials; it is for trying the storage, not for exposure.

Next in the series: the MySQL wire protocol, where the handshake runs the other way round and makes a gateway's life harder.