wire / wire-format / postgres-protocol

PostgreSQL frontend/backend protocol

PostgreSQL's wire protocol is a message-based TCP protocol. Each message: 1-byte type (for frontend messages; backend messages have a type byte too), 4-byte length (including itself), then payload. Startup: SSL request (optional), StartupMessage (protocol version 3.0 + parameters), Authentication (md5/scram), then ReadyForQuery. Queries: SimpleQuery ('Q' + SQL string) or Extended Query (Parse/Bind/Execute/Sync).

wire-format kind wire-format status standard verification verified tier B wire-format@1

aka: PostgreSQL frontend/backend protocol

wire format

spec: PostgreSQL documentation (protocol)

PostgreSQL's wire protocol is a message-based TCP protocol. Each message: 1-byte type (for frontend messages; backend messages have a type byte too), 4-byte length (including itself), then payload. Startup: SSL request (optional), StartupMessage (protocol version 3.0 + parameters), Authentication (md5/scram), then ReadyForQuery. Queries: SimpleQuery ('Q' + SQL string) or Extended Query (Parse/Bind/Execute/Sync).

fieldsizemeaning
Message type1 byteFrontend: 'Q' (query), 'P' (parse), 'B' (bind), 'E' (execute), 'S' (sync), 'X' (terminate), 'p' (password). Backend: 'R' (auth), 'S' (parameter status), 'K' (backend key), 'Z' (ready for query), 'T' (row description), 'D' (data row), 'C' (command complete), 'E' (error), 'N' (notice).
Length4 bytes (int32)Message length including the length field itself (big-endian).
Payload= length - 4Message-specific data (SQL string, bind parameters, row values, etc.).
StartupMessagevariableProtocol version (0x00030000 for v3.0) + null-terminated key-value pairs (user, database, application_name, ...).
DataRowvariableNumber of columns (int16) + for each: column length (int32, -1 for NULL) + column data bytes.
AuthenticationSASLvariableSASL mechanism names (e.g. 'SCRAM-SHA-256') + NUL. Followed by SASL challenge/response.

example:

51 00 00 00 0c 53 45 4c 45 43 54 20 31 00

PostgreSQL SimpleQuery: 'Q' + length 12 + 'SELECT 1\x00'


provenance

agent: curl -H 'accept: application/json' wire.phall.io/wire-format/postgres-protocol or /wire-format/postgres-protocol.json