On perpetrating protocol mimicry
Introduction
Today we’ll have a look at the PostgreSQL message flow, and implement a minimum workable server in Python. The aim for part one is to implement the handshake successfully to the extent that psql
sees us as a PostgreSQL server, and have a look at what else would be needed for other clients. In part two, we’ll work on returning data to the client.
First of all, why implement the protocol if the intent is not to offer an actual database implementation?
Security research
Running something that merely pretends to be PostgreSQL is quite useful as a security research honeypot, as it has a much smaller attack surface, and you can accept anything as authentication, for effectively any client version. This can also use far less resources than an actual PostgreSQL server. Of course, the flipside of this thought is that one could then implement a kind of purposefully malicious server in order to test the hardening of an application towards hostile database servers, as rare as such scenarios may be. An extension of this idea follows below under acting as an intermediary below.
Reusing the protocol for other purposes
There is something quite attractive about the possibility of offering transient data via a known database protocol. If you’re already using PostgreSQL for everything else, then delivering any of the following via the same means could be very useful:
- Live metrics (Grafana already ships with a PostgreSQL plugin)
- In-memory (or otherwise) cached data
- Dummy data generated on the fly, e.g. for development purposes
You could even set up a Foreign Data Wrapper from a real PostgreSQL server to a pseudo instance to combine the two capabilities.
Acting as an intermediary for performance or high availability
There are valid reasons for wanting to implement and intermediary between the client and the server, such as connection pooling, load balancing, or even for a clever kind of man-in-the-middle attack during pentesting, which would allow you to modify responses on the fly.
Learning
There’s no harm in finding out how something works if you use it every day, and recognising a protocol on the wire can be quite nifty1.
Handshake overview
There are two key documentation pages we’ll reference throughout the implementation: the Message Flow and Message Format. The Message Flow section explains the standard sequence of messages from the first byte onwards, diverging depending on the situation, and the Message Format section explains the composition of each message on the wire.
The first client bytes
The first message is passed from the client to the server to establish the connection, and this can take one of two forms:
StartupMessage
SSLRequest
The first eight bytes of these two messages are the same, consisting of two 32-bit integers, one indicating the length of the message and the other indicating either the client’s protocol version number in the case of the StartupMessage
, or a special flag value of 80877103
which translates as a major protocol version number of 1234
and a minor version number of 5679
. The reason for this flag value is that, for historical reasons, the first message does not contain a leading message type byte, so this is the way in which the server distinguishes between the two message types.
The StartupMessage
can also contain other parameters, such as a username or database, whereas the SSLRequest
cannot. For our purposes, we’ll simply ignore the values in the StartupMessage
if that is the first message, and refuse any client requests for SSL by responding with the appropriate refusal flag.
The authentication step
After receiving the client’s first message (and responding by refusing SSL if needed – the StartupMessage
does not require a response), we’ll request authentication in the form of a clear text password, this is the AuthenticationCleartextPassword
message. The structure of this message is the form R83
, effectively the character 'R'
, identifying the message type as an authentication request, an integer value 8
(bytes) for the total length of the message, and integer value 3
for the remaining integer, indicating a request for a clear text password.
We can read and store the client’s response to this2.
Server parameters
After authenticating, the client will wait for the server to send parameter messages (BackendKeyData
and ParameterStatus
), and finally a ReadyForQuery
message, indicating that the server is now ready to receive more messages from the client.
What is useful here is that we can impersonate a specific server version by simply sending it through as a
ParameterStatus
message to the client, and we can change this according to the version that the client is reporting, making sure that we always match exactly what the client is looking for. The flipside to this is that, if this is for security purposes, our honeypot will be ousted if the client is fraudulent and sends two messages with different versions to see if we’re making it up as we go along – then again, if we were trying to be a honeypot, we’d rather just impersonate a vulnerable server version.
The query loop
Once we’ve sent the ReadyForQuery
message, we’ll expect the client to eventually start sending us some query messages (message type Q
). These follow the same message structure as the others, in the form Q|length|query string
.
We can accept these messages and store their contents3.
Basic implementation
While I’ll be using Python, you’re of course welcome to follow along in any language of your choice – the implementation will be quite simple for now. We’ll set up a socket to listen on localhost
, on the standard PostgreSQL server port:
import socket
s = socket.create_server(('127.0.0.1', 5432), family=socket.AF_INET)
s.listen()
Simplifying the message packing
It’s useful to know the following rules when it comes to encoding our messages to the client:
- All our messages include an integer length prefix, indicating the length of the message, which includes the length prefix itself, but excludes the first byte for the message type
- If we are encoding an integer, we’ll always encode it using four bytes (32-bits)
- If we are encoding a char value, we’ll encode it as one byte (these are used for the message types)
- If we are encoding a string value, we’ll use one byte per character, and end with a null terminator
Note that this is a slightly naive approach, and will fail in some cases, but it’s sufficient for now. To implement this, we’ll make use of two convenience functions:
def pack_payload(args, null=True):
payload = bytearray()
for arg in list(args):
if type(arg) == str:
if len(arg) > 1:
# When encoding a string we add a null terminator
if null:
payload += arg.encode('ascii') + 0x0.to_bytes()
else:
payload += arg.encode('ascii')
else:
# When we encode a char, we only encode the char byte
payload += arg.encode('ascii')
elif type(arg) == int:
# All integer values are encoded as 32-bits (4 bytes)
payload += arg.to_bytes(4)
return payload
def create_message_pack(key:str, payload, null=True):
payload = pack_payload(payload, null=null)
length = len(payload)+4 # We add four bytes for the len param itself
size = (length).to_bytes(4)
key = key.encode('ascii')
return key + size + payload
Let’s start by having a look at create_message_pack
first. It takes three arguments:
key
: This is the message typepayload
: The contents of the messagenull
: A boolean indicating whether we want our strings to be null-terminated4
Essentially it just deals with the boilerplate of making sure that we have everything in the right order, and that the length parameters of all our messages are correct.
Next up, pack_payload
takes the payload from create_message_pack
and dutifully converts each member of the payload to bytes we can transmit on the wire. You’ll note that the integer conversion is extremely naive and always uses four bytes. In the next installment of this series we’ll have to improve that since you can have various integer sizes in PostgreSQL data types.
Implementing the flow
First up, we’ll accept the client connection:
conn, details = s.accept()
ip, port = details
# The first request in a connection is either SSLRequest or StartupMessage
startup = conn.recv(1024*4096)
length = startup[:4]
value = startup[4:]
if value == (80877103).to_bytes(4):
print(f'[{ip}] [{datetime.now()}] Refusing SSLRequest')
# We have received an SSLRequest, refuse it
nossl = 'N'.encode('ascii')
conn.send(nossl) # Refuse SSL
else:
# We received a StartupMessage, we can ignore it
print(f'[{ip}] [{datetime.now()}] Ignoring StartupMessage')
pass
details
is there for convenience and contains the IP and port. We set up a big buffer and just receive all the bytes the client sent (in reality the buffer we need for this opening message is actually extremely small, but it’s nice to have headroom when you’re exploring a new protocol). Here we handle the two different message types the client can send to open the connection, and if we receive an SSLRequest we’ll just respond with an N
for No. This makes things simpler.
Now we can create some standard messages and fire them all off to complete the remainder of the handshake:
authpass = create_message_pack('R', [3,])
authok = create_message_pack('R', [0,])
backendkeydata = create_message_pack('K', [1,1])
p1 = create_message_pack('S', ['server_version', '15.1'])
p2 = create_message_pack('S', ['server_encoding', 'utf-8'])
readyforquery = create_message_pack('Z', ['I'])
authpass
is a password authentication message to the client. authok
is our standard response to say whatever password you supplied is correct, regardless of what it was because we don’t care.
backendkeydata
is a set of two server generated parameters, a process ID and a secret key, which a client can later use to send a CancelRequest
message. We simply send a PID of 1
and and key of 1
.
Next up we’ll pretend to be version 15.1 of PostgreSQL, and say that we support UTF-8 even though the naive code we just wrote uses ascii
encoding.
Finally, we’ll send the readyforquery
message to let the client know that it can start sending queries to us. These queries won’t be actioned until the next installment of this series.
Now that we’ve created these messages, we can send them off to the client:
conn.send(authpass) # Request plaintext password
data = conn.recv(1024*1024) # Receive password
print(f'[{ip}] [{datetime.now()}]', split_reply(data))
conn.send(authok) # Respond that the password is okay
conn.send(backendkeydata) # Send the PID and key
conn.send(p1) # Send our version
conn.send(p2) # Send our encoding
conn.send(readyforquery) # Tell the client it can send queries
while(True): # Interaction loop
data = conn.recv(1024*1024)
if len(data) == 0:
continue
reply = split_reply(data)
print(f'[{ip}] [{datetime.now()}]', reply)
if reply[0] == 'TERMINATE':
# Close the connection when we recive a termination signal
return
conn.send(readyforquery)
At the end we have a loop where we keep printing what the client sends, and we just send another ReadyForQuery
message back each time, unless the client sends a termination message. One last thing, you’ll notice a split_reply
function call there. It’s a simple way for us to parse and print what the client sends to us:
def split_reply(data):
string = data.decode('ascii', errors='ignore')
values = string.split('\0')
values = [ v for v in values if v != '' ]
match values[0]:
case 'p':
values[0] = 'PASSWORD'
case 'P':
values[0] = 'FUNCTION CALL'
case 'Q':
values[0] = 'QUERY'
values[1] = values[1][1:] # Remove ampersand prefix
case 'X':
values[0] = 'TERMINATE'
case '':
values[0] = 'STARTUP'
return values
Let’s see how our clients respond to this.
Hoodwinking our first clients
The psql
binary
We can run the code above, and then run the following in the terminal:
$ psql -h 127.0.0.1 -p 5432 -U ian -W postgres
Password:
psql (16.1 (Homebrew), server 15.1)
Type "help" for help.
postgres=> select 1;
postgres=>
It worked! Except for the lack of a response from us when psql
sends the query. Notice our server version popping up in the console there? By the way, we’re using the username ian
and the password ian
(that’s relevant for the next bit). Our server prints the following (note that I’ve trimmed the output a bit by removing the timestamps):
$ ./psql_server.py
[127.0.0.1] Refusing SSLRequest
[127.0.0.1] ['O', '\x03', 'user', 'ian', 'database', 'postgres', 'application_name', 'psql', 'client_encoding', 'UTF8']
[127.0.0.1] ['PASSWORD', '\x08ian']
[127.0.0.1] ['QUERY', 'select 1;']
There’s our client parameters, including the username and database, and the client’s name psql
, with our password, and the query. That’s quite nifty.
PgAdmin 4
Let’s see what a graphical client makes of all of this:
That’s a little disappointing, let’s check our server to see why:
[127.0.0.1] ['QUERY', "ET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='utf-8';"]
[127.0.0.1] ['TERMINATE', '\x04']
Ignoring for a moment the truncation of SET
there, we can see that PgAdmin is sending a bunch of parameters to SET
on it’s session with our server, and we’re not responding to them appropriately, so it gives up and closes the connection. We’ll fix that next time.
PostgreSQL itself
We can, in the future, better test our ability to impersonate a PostgreSQL server by having another PostgreSQL server connect to us through a foreign data wrapper.
This will need quite a bit more work, as we’ll need to handle requests for schema objects to allow PostgreSQL to find and import them. When this has been accomplished I’ll add a link to the post explaining the work.
Other future work
Multithreading
Though performance is not of major concern to this project, we’ll use multiple threads just to make sure we can juggle multiple connections at the same time. A naive approach of one thread per connection, up to a predefined maximum number of threads will suffice for this.
Responding to the client for multiple request types
We’ll handle returning some data for SET and SELECT
queries in Part two, coming soonish.
A brief aside
Earlier I mentioned that one of the perks of doing something along these lines is that it serves as a learning experience. Interestingly enough, I had a case come up the other day where it was useful to know the innards of the message flow, as it showed up in a log message (this is an example; the message in my case was a bit more involved, but the principle remains):
kind mismatch among backends. Possible last query was: "COMMIT"
kind details are: 0[C] 1[E: canceling statement due to statement timeout]
This is a pgPool log message, and it’s saying that the two responses from the backend PostgreSQL servers didn’t match up with each other, i.e. the response was inconsistent. What’s nifty is that the kind details is actually the message types from PostgreSQL for each node (0 being the first node responding with C
for CommandComplete
, and the other responding with E
for ErrorResponse
) – so you can instantly tell what’s going on here. PgPool sometimes follows up messages like these with another, more alarming one5 saying that the cluster may be in an inconsistent state, but that’s clearly not true here. If you didn’t understand the message though, you could be sent down an entirely different rabbit hole.
While this example is simple, if you have more nodes and more complicated problems to solve, being able to immediately notice the message kinds in the log makes the situation much clearer – and these kinds of errors are rare enough that there’s not a whole lot of help online for you to rely on either.
Thanks for reading, I hope this was a helpful introduction to the PostgreSQL message flow so far. If you have any comments or ideas, please let me know in the comments below.
- More on this later. ↩︎
- Or pass it on to a real PostgreSQL server somewhere else. ↩︎
- Or generate a reply, as we’ll do next time. ↩︎
- In hindsight, this argument could have been given a more descriptive name. ↩︎
- https://github.com/pgpool/pgpool2/blob/658792a4d28ac849b4f0162313b589b318c0a25e/src/protocol/pool_process_query.c#L3822 ↩︎
Thx for this great article. I didn’t know that the password is sent over plain text. Checked all our servers so this isn’t possible anymore.
Thanks for stopping by, I’m glad you liked the article.
So the protocol still supports it as an option, even if your servers are set up to only allow TLS. In our case it’s just nice to see everything coming through.
here the python 3.10 working code for those interested
https://gist.github.com/parweb/5ae1d8cd139aa0ec28933f25072f19e6
Nice, thanks for putting it together 🙂 I’ll put the full code on Github when the series is done too.