Parsing the postgres protocol - 16/02/2024

Kviklet as an application already allows you to execute arbitrary SQL statements on your database. However a feedback we heard multiple times is that using a simple web tool for query execution is not the same as e.g. a designated DB access software like Datagrip.

And engineers did not always want to have to approve every individual statement. Especially when debugging a production error, they need break glass kind of access. We already have a temp access mode that lets you execute any amount of statements for 1 hour. But the UI for this is yah well… lacking. We definitely can’t compete with datagrip.

So I wanted to experiment… in theory nothing stops us from starting a proxy server and listening in on the postgres network traffic between a client application and the server. This would allow us to add all the cool kviklet features, like an auditlog on SSO level in a very seemless manner almost unnoticable for the actual engineers that had to connect to prod.

This post is about how this works, I found it interesting to poke around on such a low level of the postgres implementation. So maybe you do too.

Postgres runs on a binary frontend/backend protocol. This traffic is potentially SSL encrypted but if we ignore that, listening in on the traffic results in fun stuff to read like:

P�����select name, is_dst from pg_catalog.pg_timezone_names
B�����������

Which is not very useful by itself. As noted above I wanted to figure out which statements the user runs on the DB. However we can parse the traffic into individual messages to make more sense of them.

If we look at the postgres docs we can for example see that ‘P’ from the frontend stand for a Parse Message. Which is defined as such:

Byte1(‘P’) Identifies the message as a Parse command.

Int32 Length of message contents in bytes, including self.

String The name of the destination prepared statement (an empty string selects the unnamed prepared statement).

String The query string to be parsed.

Int16 The number of parameter data types specified (can be zero). Note that this is not an indication of the number of parameters that might appear in the query string, only the number that the frontend wants to prespecify types for.

Then, for each parameter, there is the following:

Int32 Specifies the object ID of the parameter data type. Placing a zero here is equivalent to leaving the type unspecified.

This however isn’t a statement execution by itself which is what I wanted to track in Kviklet. An execution always consists of a Parse Message, followed by a Bind message that binds specific parameters. Only once this has been done an Execute message is sent.

Now the fact that the statement is first send with placeholder params which are later binded is a bit problematic for me. Afterall I want to add the whole statement to the auditlog with all the parameters filled not just ”?” placeholders.

So I also need to parse the Bind message:

Byte1(‘B’) Identifies the message as a Bind command.

Int32 Length of message contents in bytes, including self.

String The name of the destination portal (an empty string selects the unnamed portal).

String The name of the source prepared statement (an empty string selects the unnamed prepared statement).

Int16 The number of parameter format codes that follow (denoted C below). This can be zero to indicate that there are no parameters or that the parameters all use the default format (text); or one, in which case the specified format code is applied to all parameters; or it can equal the actual number of parameters.

Int16[C] The parameter format codes. Each must presently be zero (text) or one (binary).

Int16 The number of parameter values that follow (possibly zero). This must match the number of parameters needed by the query.

Next, the following pair of fields appear for each parameter:

Int32 The length of the parameter value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL parameter value. No value bytes follow in the NULL case.

Byten The value of the parameter, in the format indicated by the associated format code. n is the above length.

After the last parameter, the following fields appear:

Int16 The number of result-column format codes that follow (denoted R below). This can be zero to indicate that there are no result columns or that the result columns should all use the default format (text); or one, in which case the specified format code is applied to all result columns (if any); or it can equal the actual number of result columns of the query.

Int16[R] The result-column format codes. Each must presently be zero (text) or one (binary).

Oof this is getting complicated. So we have parameters and for each parameter we can get the Object ID of the type from the Parse message.
Object IDs are what postgres internally uses to refer to rows. The IDs listed here refer to a table in postgres called pg_type.
In my postgres instance this table contains 500 rows of all kinds of different types my database system supports:

Now this is relevant because in the Bind message the docs refer to the params formatter codes. If the code is 1 the format is binary. Meaning I don’t get the UTF-8 version of the parameter but instead just a binary value. I need to know what kind of type the parameter is so that I can transform it to a string.

Since my code is kotlin the formatting for that looks like this:

"bool" -> {
    (bytes[0] == 0x01.toByte()).toString()
}
"char" -> {
    bytes[0].toInt().toChar().toString()
}
"name" -> {
    bytes.toHexString()
}
"int8" -> {
    ByteBuffer.wrap(bytes).long.toString()
}

With this decyphering done I can now print a query when I receive the Execute Message. This is fun since I can now see all the introspection and settings commands that datagrip executes:

Now for my purpose that wasn’t quite enough. Just proxying the traffic is only useful if the engineer already has the password to production. However we want to avoid this since this often leads to password sharing and thereby access inflation over time.

Instead I want to integrate my proxy with the kviklet user management, which could also be an SSO provider (google or keycloak). For this I had to implement the login process of the postgres protocol.

Postgres supports a miriad different ways of login in, there is the old way of just sending a username and password via plaintext, there is an md5 hashed version and there is newer options like SCRAM which is defined in RFC-7677. Since this is a beta and I want to add SSL later anyways, I simply went with the md5 version, this is also what my locally hosted postgres instance was using so it was easier to reimplement.

The flow is the following:

1. The client sends a startup message containing a user a desired database and a bunch of meta information.
2. The server responds with an Authentication Request that contains a salt.
3. The client hashes the username + password + salt and sends it to the server.
4. If everything is correct the server respondsw with AuthenticationOK + ReadyForQuery messages.

After this flow I can again forward all messages to my original postgres connection. This one I btw manage directly via jdbc so I don’t have to implement any of the authentication logic on that side.

The exact flow is a bit more complex but you can look at the details in the code if you’d like to: https://github.com/kviklet/kviklet/blob/main/backend/src/main/kotlin/dev/kviklet/kviklet/proxy/PostgresProxy.kt#L188

With this implemented I can now generate a random password that is only valid for 1 hour, start the proxy with it and forward all traffic to e.g. the root user. All without having to share the password for the root user and with all statements logged in our auditlog. Pretty cool right?

Note this is all quite experimental and still needs some further work before I’d call it production ready. But feel free to play around with it and let me know if you find any problems in a github issue!