Hacking the Postgres Wire Protocol

Summary

Hacking the Postgres wire protocol Apr 14th, 2025Lev Kokotov PgDog is a network proxy and it can see every byte sent between Postgres and the clients. It understands SQL and can infer where queries should go, without requiring changes to application code. In this article, we discuss how we handle the Postgres wire protocol and manipulate it to serve queries to multiple databases at the same time. Protocol basics Postgres has two ways to send queries over the network: Simple protocol Extended protocol The simple protocol is called like that on purpose: it’s very simple. It has just one message, called Query which contains everything the server needs to execute it: 'Q' | \x00\x00\x00& | SELECT * FROM users WHERE id = 25\0 Postgres messages have a standard format. Each message starts with a single ASCII letter (1 byte), identifying the message type. It’s followed by a 32-bit signed integer, indicating the length of the payload, in bytes, with 4 bytes added for itself. The payload is unique for each message. To route a query, PgDog needs to understand two things: Does the query read or write data? Does it contain a sharding key, and if so, what is it’s value? To make this work, we need some help. To answer the first question, we could, theoretically, just look at the first word, and if it’s a “SELECT”, assume read intent. While this would work in most cases, we’ll miss some obvious ones, like CTEs. The tricky part is finding the sharding key. For this, we need to actually interpret the query using a tool that understands SQL syntax. That’s called a parser, and luckily the Rust ecosystem already has a great library, called pg_query. The parser produces an Abstract Syntax Tree, a data structure we can read to find what we need: let ast = pg_query::parse("SELECT * FROM users WHERE id = 25"); pg_query is special. It doesn’t actually implement parsing SQL. It works by extracting C source code directly from Postgres and wraps it with a nice Rust interface. This allows PgDog t...