%unimsg 0 systems/postgresql-protocol/v0 -- The PostgreSQL frontend/backend protocol, described as a system you could -- implement. -- -- Read the scope block first, because the title of this document is narrower -- than the name in it. This is not how to implement PostgreSQL. Implementing -- PostgreSQL is a parser, a planner, an executor, multiversion storage, a -- write-ahead log, replication, a type and catalog system and half a dozen -- index methods, and no document enables it. This is the protocol those things -- speak, which is a bounded and frequently implemented thing: every driver -- speaks it, and so does every pooler and every database that claims -- PostgreSQL compatibility. -- -- Every byte in the vectors was captured from a live server over a raw socket, -- including a complete SCRAM-SHA-256 authentication performed by hand. Nothing -- here is recalled. Where something is described rather than captured, the -- scope block says which. postgresql-protocol @systems/postgresql-protocol/v0 { title "The PostgreSQL frontend/backend protocol, version 3.0" status :provisional verified-against "PostgreSQL 16.14 on Debian, over TCP, protocol 3.0" premise "A connection is a stream of typed, length-prefixed messages in both directions, punctuated by the server announcing that it is ready. Everything else — authentication, queries, results, errors, transactions — is a pattern of those messages. Get the envelope and the ready signal right and the rest is vocabulary." scope { captured "The message envelope, startup, SCRAM-SHA-256 authentication, the simple query cycle, the extended query cycle, error responses, and transaction status. Each has vectors below taken from a real session." described "COPY, the function call and cancel paths, notification, binary result formats beyond the fact of them, replication, and TLS negotiation. Named so you know they exist and where they sit." not-here [ "SQL semantics — a different and far larger subject", "on-disk storage, the write-ahead log, and multiversion visibility", "the planner, the executor, and anything about performance", "protocol 2, which is long obsolete" ] what-this-enables "A driver, a pooler, a proxy, or a server that other people's PostgreSQL clients can talk to. That last one is how several databases with no PostgreSQL code inside them are usable from psql." } -- =================================================================== -- THE ENVELOPE -- =================================================================== framing { rule "One byte naming the message type, then a 32-bit big-endian length, then the body. The length counts itself and excludes the type byte, so a message with an empty body announces 4." the-exception "The startup message and its relatives have no type byte: the first four bytes on a new connection are a length, and the next four are either a protocol version or a request code. The type byte begins with the second message. An implementation that assumes a type byte from the first byte of the connection fails immediately and confusingly." everything-is-big-endian "Integers on the wire are network order throughout, including inside message bodies." strings-are-nul-terminated "Not length-prefixed. A body may hold several, and their lengths come from scanning." } -- =================================================================== -- STARTUP -- =================================================================== startup { message "A length, the protocol version as 196608 — which is 3 in the high sixteen bits and 0 in the low — then key and value pairs as NUL-terminated strings, then a final zero byte." required-parameter "user. Everything else, database included, is optional and defaults to the user's name." what-comes-back [ | message type-byte carries | :Authentication "R" "a 32-bit code saying what is required; 0 means nothing further" | :ParameterStatus "S" "a server setting, as two strings; many arrive" | :BackendKeyData "K" "a process id and a secret, eight bytes, for cancellation later" | :ReadyForQuery "Z" "one byte of transaction status; the handshake is over" ] parameter-status-is-not-optional "The server sends a dozen or more of these unprompted, and sends more later if a setting changes mid-session. A client that does not expect them at arbitrary points will desynchronise the first time one arrives outside startup." } authentication { doc "The server names a method; the client satisfies it; the server sends code 0. Modern servers ask for SCRAM." codes [ | code method | 0 "authentication succeeded" | 3 "cleartext password" | 5 "MD5 password, with a salt" | 10 "SASL; the body lists mechanisms as NUL-terminated strings" | 11 "SASL continue" | 12 "SASL final" ] scram { mechanism "SCRAM-SHA-256" exchange [ | step who sends | 1 :server "code 10 and the mechanism list" | 2 :client "the mechanism name, a 32-bit length, then `n,,n=,r=` and a nonce" | 3 :server "code 11 and the server-first message: combined nonce, salt, iteration count" | 4 :client "`c=biws,r=` the combined nonce, and the proof" | 5 :server "code 12 with its own signature, then code 0" ] deriving-the-proof [ | n compute | 1 "SaltedPassword = PBKDF2-HMAC-SHA256(password, salt, iterations, 32 bytes)" | 2 "ClientKey = HMAC(SaltedPassword, `Client Key`)" | 3 "StoredKey = SHA256(ClientKey)" | 4 "AuthMessage = client-first-bare, server-first, client-final-without-proof, joined by commas" | 5 "ClientSignature = HMAC(StoredKey, AuthMessage)" | 6 "ClientProof = ClientKey XOR ClientSignature, base64 encoded" ] the-part-that-catches-people "AuthMessage is built from the *bare* client-first — the part after `n,,` — and from the client-final *without* the proof field. Using the full strings produces a proof the server rejects with no explanation of why, and both mistakes look identical from the client." c-equals-biws "`biws` is base64 of `n,,`, the channel-binding preamble echoed back. It is a constant for connections without channel binding, which is why it appears verbatim in every implementation and is worth recognising rather than deriving." } } -- =================================================================== -- QUERYING -- =================================================================== simple-query { request "Q, then the SQL as one NUL-terminated string. It may contain several statements separated by semicolons, and each produces its own results." response [ | message type-byte carries | :RowDescription "T" "the shape of what follows: field count, then per field" | :DataRow "D" "one row: column count, then per column a length and bytes" | :CommandComplete "C" "a tag such as SELECT 1 or INSERT 0 1" | :ReadyForQuery "Z" "transaction status; the cycle is over" ] row-description-field "The name, the table's object id and the column's number within it — both zero when the column is not a stored column — the type's object id, the type's length, a type modifier, and a format code." a-null-is-minus-one "A column length of -1 means SQL NULL. It is not a length of zero, which is an empty value and a different thing." no-results-is-not-no-messages "A statement returning nothing sends CommandComplete with no RowDescription. A client keyed on RowDescription arriving will hang." } extended-query { doc "Parse, bind, execute — separated, so a statement may be prepared once and run many times, and so parameters travel as values rather than as text spliced into SQL." messages [ | message type-byte does | :Parse "P" "name a statement, give its SQL and any parameter type hints" | :Bind "B" "name a portal, supply parameter values and format codes" | :Describe "D" "ask for the shape of a statement or portal" | :Execute "E" "run a portal, up to a row limit; zero means all" | :Sync "S" "end the cycle; the server replies ReadyForQuery" | :Close "C" "discard a statement or portal" ] replies [ | message type-byte | :ParseComplete "1" | :BindComplete "2" | :CloseComplete "3" | :PortalSuspended "s" | :NoData "n" ] the-unnamed-statement "An empty name is the unnamed statement or portal, replaced by the next one of its kind. Convenient, and the reason a client that pipelines carelessly destroys its own prepared statement." sync-is-the-recovery-point "After an error the server discards messages until it sees Sync. So a pipelined batch fails as a unit, and a client that omits Sync waits forever for a ReadyForQuery that is not coming." parameters-are-not-typed-by-the-client "Parse may send zero type hints and let the server infer, which is what a cast in the SQL is for. The vector below sends none." } -- =================================================================== -- ERRORS AND STATE -- =================================================================== errors { shape "E, then a sequence of fields, each a single type byte followed by a NUL-terminated string, ended by a zero byte where a type byte would be." fields [ | byte field | "S" "severity, localised" | "V" "severity, never localised — prefer this one" | "C" "SQLSTATE, five characters" | "M" "the primary message" | "D" "detail" | "H" "a hint" | "P" "a position in the query, counted in characters from 1" | "F" "the server source file, and other internal fields" ] read-the-code-not-the-message "SQLSTATE is stable and machine-readable; the message is prose and may be translated. A client branching on message text breaks in another locale." notice-is-the-same-shape "NoticeResponse uses type byte N and the identical field structure, and may arrive at any time, including outside a query." } transaction-status [ | byte means | "I" "idle, no transaction" | "T" "inside a transaction block" | "E" "inside a failed transaction; everything is rejected until rollback" ] the-failed-state-is-real "After an error inside a transaction the server answers every statement with an error until it sees rollback. A client that retries the failed statement loops, and the status byte is how it should have known." -- =================================================================== -- VECTORS -- =================================================================== -- Captured from PostgreSQL 16.14 over a raw socket. Hexadecimal, whole -- messages including the type byte and length where one is present. vectors { startup [ | id what bytes | :q-001 "StartupMessage, user and database postgres" "00000029000300007573657200706f73746772657300646174616261736500706f7374677265730000" | :q-002 "AuthenticationSASL, offering SCRAM-SHA-256" "52000000170000000a534352414d2d5348412d3235360000" ] startup-note "q-001 is 41 bytes: a length of 0x29, the version 0x00030000, then the pairs and a terminating zero. There is no type byte, which is the exception described above. In q-002 the type byte R is present, the length is 23, the code is 10, and the mechanism list is NUL-terminated strings ended by an empty one." simple [ | id what bytes | :q-010 "Query: SELECT 1 AS n" "510000001253454c4543542031204153206e00" | :q-011 "RowDescription for it" "540000001a00016e00000000000000000000170004ffffffff0000" | :q-012 "DataRow holding the text 1" "440000000b00010000000131" | :q-013 "CommandComplete, tag SELECT 1" "430000000d53454c454354203100" | :q-014 "ReadyForQuery, idle" "5a0000000549" ] row-description-decoded "One field. Name n. Table oid 0 and column 0, because it is a computed column. Type oid 23, which is int4. Type length 4. Type modifier -1. Format code 0, meaning text — so the value in q-012 is the single character 1, not a binary integer." extended [ | id what bytes | :q-020 "Parse: SELECT $1::int + 1 AS r, no type hints" "500000001f0053454c4543542024313a3a696e74202b20312041532072000000" | :q-021 "Bind: one text parameter, the character 7" "420000001100000000000100000001370000" | :q-022 "the DataRow that came back" "440000000b00010000000138" ] bind-decoded "Empty portal name, empty statement name, zero parameter format codes — so all parameters are text — one parameter, of length 1, the byte 7, then zero result format codes. The row that came back holds the character 8, which is the server having computed 7 + 1 and rendered it as text." errors [ | id what first-fields | :q-030 "SELECT nope, an unknown column" "S ERROR, V ERROR, C 42703, M column \"nope\" does not exist, P 8" ] error-note "SQLSTATE 42703 is undefined_column. Position 8 counts characters from 1, so it points at the start of nope." transaction [ | id after status-byte | :q-040 "BEGIN" "T" | :q-041 "an error inside that transaction" "E" | :q-042 "ROLLBACK" "I" ] } -- =================================================================== -- HAZARDS -- =================================================================== hazards [ | hazard consequence | "expecting a type byte on the first message" "startup is unreadable; the failure is immediate and puzzling" | "treating the length as excluding itself" "every message boundary is off by four and the stream desynchronises" | "building the SCRAM AuthMessage from the full strings" "the proof is rejected with no indication of which part was wrong" | "not expecting ParameterStatus mid-session" "a setting change desynchronises a working client hours in" | "reading a column length of -1 as a length" "NULL becomes a huge read, or a crash" | "omitting Sync after a pipelined batch" "the client waits for a ReadyForQuery that will never come" | "branching on error message text" "correct until the server speaks another language" | "ignoring the transaction status byte" "retrying inside a failed transaction loops" ] -- =================================================================== -- PROVENANCE -- =================================================================== provenance { method "A Python client written for this document, using raw sockets and no driver. It performed the startup, a full SCRAM-SHA-256 authentication including the PBKDF2 and HMAC derivation, a simple query, a deliberate error, and an extended query cycle. The bytes in the vectors are what came back." why-by-hand "A driver would have hidden exactly the things worth pinning. Implementing the handshake was the check: an authentication that completes is evidence the derivation described above is right, in a way that reading about it is not." described-not-captured "COPY, cancellation, notification, binary formats, replication and TLS negotiation are named in scope and not pinned. Treat them as a map of what is left rather than as instructions." version "One server, one version, one platform. Protocol 3.0 has been stable for a long time, but a claim from a single observation is a claim from a single observation." not-a-reproduction "Formats and behaviour, so that an independent implementation is possible — which is what the many existing independent implementations of this protocol demonstrate is intended." } }