How to query PostgreSQL without a driver in golang - Under the hood seriesWed, 07 Oct 2020
I have decided to start an "Under the hood series", literally a "series" of articles to discover what is going on behind the scene of my favourite libraries, frameworks, runtimes and languages. In this first article, I am going to demonstrate how to connect and make a simple query to a PostgreSQL database without using any library.
DISCLAIMER: The code I am going to present does not take into account for edge-cases and error handling. If you are looking for database drivers for golang jackc/pgx is well maintained and performant, as an alternative you can look into lib/pq.
The big picture
Postgres uses a message-based binary protocol over TCP/IP, the current protocol version is 3.0 that has been used since version 7.4 and later. There are many type of messages, but, for the purpose of this article, we are going to focus on few of them: startup, authentication and query.
In the picture above we can see the typical message flow without SSL/TLS encryption. After we have established a TCP connection with the Postgres backend, the first message that we send is the startup message in which we include information about our database: name, username and possibly some other options (client encoding, etc..). The response will tell us which method of authentication we need to use, if any. To identify ourselves, we need to send an authentication message containing our credentials. There are many types of authentication in Postgres, the one we are going to use is md5 encrypted password. If successful, we will receive in separated messages:
Auth result
A series of key-value pairs runtime informations, called parameters status.
A "BackendKeyData" which we might need in case of request cancellation.
A "Ready for query" message.
After this whole "initiation ritual", we are finally ready to send our first query.
The query response message will be a detailed description of the row structure (types, columns name, format) and after that the data itself, (DataRow). Each message corresponds to a row, we will need to iterate them until the command complete message is met. Finally we obtain, again, the ReadyForQuery and we may place our next query. All of this if there are no errors.
All the messages in this demonstration are going to be converted or decoded to/from an array of bytes. The variable buff could contains multiple messages together, that is why call it message would be misleading.
Decoding messages
To decode a message coming from Postgres, we simply need to traverse the whole array of bytes, but how do we know what's in there? Each message, except for the startup one, starts with an ASCII Identifier, it is the first byte we encounter: ID = buff[0], and ends with a null byte.
You can consult the postgres documentation to see all the incoming messages format.
We will encounter recurring patterns and for each one of them we can create a helper function. Those functions will help us to traverse the array of bytes by taking as input the buffer and the index, and returning the value and the incremented index.
1 byte ASCII identifier, which identify the type of message, except for the startup one.
func GetASCIIIdentifier(buff []byte, index *int) (id string) { id = string(buff[*index]) *index = *index + 1 return id }16 bits integer, 2 bytes
func GetUint16Value(buff []byte, index *int) (value uint16) { value = binary.BigEndian.Uint16(buff[*index : *index+Int16ByteLen]) newIndex := *index + Int16ByteLen *index = newIndex return }
32 bits integer, 4 bytes
func GetUint32Value(buff []byte, index *int) (value uint32) { value = binary.BigEndian.Uint32(buff[*index : *index+Int32ByteLen]) newIndex := *index + Int32ByteLen *index = newIndex return }string value given a length (some cases we do not have a length but a null-terminated string)
func GetStringValue(buff []byte, length uint32, index *int) (value string) { value = string(buff[*index : *index+int(length)]) *index = *index + int(length) return value }
string value without a specified length.
func GetStringValueWithNullTermination(buff []byte, index *int) (value string) { count := 0 for { b := buff[count] // Field is delimited by a 0 byte if b == 0 { break } // we count the number of bytes until we encounter the 0 byte // then we stop and return the casted slice containing the string count++ } *index = *index + count + 1 // add the 0 byte return string(buff[:count]) }
If you want to skip the whole detailed explanation you can find all the code in my github repository, the code is slightly different but the concepts are the same. In my github repository I have added a "psql's style" table printer, therefore there is a some additional logic that is not needed in here. Let's now see in details each one of these messages.
Startup
After we have established a TCP connection with the Postgres instance, the first message we have to send is the startup message. The startup message is the only one that does not start with an ASCII character identifier. Let's see its structure:
32 bits integer message length + 32 bits integer protocol + string name + 1 byte null termination + string value + 1 byte null termination + ...The string name and value are a list of key-value pairs of connection options: database name, user name, etc... The first 4 bytes (32 bits) represent the length of the message, but at this point we don't know how long it is going to be, so let's just append 4 null bytes
buff := make([]byte, 0, 1024)
buff = append(buff, 0, 0, 0, 0)next is another 4 bytes representing the protocol
buff = pgio.AppendUint32(buff, 196608) // 3.0How do we get that number? From the Postgres documentation "In the protocol number, the most significant 16 bits are the major version..."; if you convert 196608 to its binary representation the first 16 bits will represent the number 3:
fmt.Printf("%032b", 196608) // 00000000 00000011 00000000 00000000Next we have the list of key-value pairs which we are simply going to append and separate with a null byte:
buff = append(buff, "user"...)
buff = append(buff, 0)
buff = append(buff, "postgres"...)
buff = append(buff, 0)
buff = append(buff, "database"...)
buff = append(buff, 0)
buff = append(buff, "test-db"...)
buff = append(buff, 0)
buff = append(buff, 0) // closing byteNow that we have the full message we can calculate the total length and attach it at the beginning:
lengthOfTheMessage := int32(len(buff[0:]))
binary.BigEndian.PutUint32(buff[0:], uint32(lengthOfTheMessage))One important remark for the length calculation: it should include itself but not the ASCII identifier, this means the 4 bytes representing the length plus whatever comes next including the null byte.
Now we can just write the encoded request into our TCP connection and check the response:
func Execute(conn net.Conn, message []byte) ([]byte, error) {
if _, err := conn.Write(message); err != nil {
return nil, err
}
reply := make([]byte, 1024)
if _, err := conn.Read(reply); err != nil {
return nil, err
}
return reply, nil
}The startup message response, in case of success, is going to have the following structure:
1 byte ASCII identifier ("R") + 32 bits integer message length + 32 bits integer authentication method + optional data"R" means an authentication request.
index := 0
identifierChar := utils.GetASCIIIdentifier(buff, &index) // "R"Next we check the length.
length := utils.GetUint32Value(buff, &index)Now we will get the authentication method
authMethod := utils.GetUint32Value(buff, &index) // 5This part is important, it will establish what we are going to find next. The number "5" is the code for AuthenticationMD5Password, this means that the next 4 bytes are going to be the salt to encrypt the credentials:
salt := buff[index : index+4] // [54, 46, 23, 90] This value will be a randomly generate by the backend at each request and it is needed in the next step to perform credentials encryption. There are many other methods of authentication, for example If the method is "0" it means that the authentication was successful or there is no need for the frontend to be authenticated (Trust authentication), as alway you can refer to the postgres documentation for all the types of authentication.
Authentication message
This is its structure:
1 byte ASCII identifier ("p") + 32 bits integer message length + encrypted credentialsbased on the previous message we have a MD5 password type authentication and a salt. The complete formula to hash our credentials for the Postgres backend is:
credentialsHash = md5(password + username).hexdigest()
hash = "md5" + md5(credentialsHash + salt).hexdigest()Let's compose the message starting with the identifier "p" which stands for password authentication:
buff := make([]byte, 0, 1024)
buff = append(buff, 'p')Let's translate the hashing formula into golang code, after that calculate the total length and finally send the message:
digestedPassword := "md5" + utils.HexMD5(utils.HexMD5(utils.Password+utils.User)+string(salt))
buff = pgio.AppendInt32(buff, int32(4+len(digestedPassword)+1))
buff = append(buff, digestedPassword...)
buff = append(buff, 0)Usually the response is going to be a mix of subsequent messages, but we are only interested in two of them: authentication result and ready for query. If the authentication was successful the response format should look something like this
1 byte ASCII identifier ("R") + 32 bits integer message length + 32 bits integer authentication method ("0")The first character is going to be "R", while the authentication method this time is "0", which means "AuthenticationOk":
identifierChar := utils.GetASCIIIdentifier(buff, &index) // R
length := utils.GetUint32Value(buff, &index) // 8
authResult := utils.GetUint32Value(buff, &index) // 0There are other messages in the middle (parameter status, backend key data, ...), but the last one we receive is the ReadyForQuery:
1 byte ASCII identifier ("Z") + 32 bits integer message length + 1 byte transaction statusPossible values for the status indicator are: 'I' if idle (not in a transaction block), 'T' if in a transaction block, or 'E' if in a failed transaction block (queries will be rejected until block is ended). In our case is going to be "I":
readyForQueryId := utils.GetASCIIIdentifier(buff, &index) // Z
rfqLength := utils.GetUint32Value(buff, &index) // 5
status := utils.GetASCIIIdentifier(buff, &index) // INow we are ready to send our query
Query
There are two protocols for sending queries: simple query and extended query.
A simple query message includes a SQL command (or commands) as a simple text string:
1 byte ASCII identifier ("R") + 32 bits integer message length + query a stringThe extended query protocol is needed for things like prepared statements and supplying values as separate parameters instead of having to insert them directly into a query string. Is it going to split the procedure into multiple steps: parse, bind and execute. For this post we are going to touch only the simple query, so let's have a look at our statement:
SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;Formatting this message is very simple as we just need to transform a string into an array of bytes and get its length; the identifier for this message is "Q":
buff := make([]byte, 0, 1024)
buff = append(buff, 'Q')
query := "SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;"
lengthOfTheMessage := int32(4 + len(query) + 1) // As always add the length itself (4) and the null-termination byte (1)
buff = pgio.AppendInt32(buff, lengthOfTheMessage)
buff = append(buff, query...)
buff = append(buff, 0) // null byteWe can send it as it is and read the response from our TCP connection.
The first message we get back is the Row description, which is basically a description of the table and is divided in "two" parts: header and field descriptions
1 byte ASCII identifier ("T") + 32 bits integer message length + 16 bits integer number of fields retrievedLet's decode the header:
index := 0
ASCIIId := utils.GetASCIIIdentifier(buff, &index) // "T"
length := utils.GetUint32Value(buff, &index)
numOfFields := utils.GetUint16Value(buff, &index)The second part, always in the same message, is the description of a field:
(string column name + \0 + 32 bits integer table oid + 16 bits integer colno + 32 bits integer type oid + 16 bits integer type length + 32 bits integer type mod + 16 bits integer format) x numOfFieldsThis field description is going to be repeated numOfFields times. What we care about, for this demonstration, is the column name and the type. The string column name is a null-terminated string:
for {
columnName := utils.GetStringValueWithNullTermination(buff[index:], &index)
// ... Skip 48 bits ...
typeOid := utils.GetUint32Value(buff, &index)
typeName := utils.GetTypeName(typeOid)
types = append(types, typeName)
// ... Skip 64 bits ...
numOfFields--
if numOfFields == 0 {
break
}
}The type comes in form of oid which is basically a 2 bytes integer; to get the concrete type name we should query the pg_type table:
SELECT typname FROM pg_type WHERE oid='<found oid>'for simplicity, I have just pre-mapped them into a pg type without making another query.
After the row and fields description, we finally are going to get the data we queried, this time is going to be one separate message per row:
1 byte ASCII identifier ("D") + 32 bits integer message length + 16 bits number of fields + (32 bits integer length of fields + string value) x number of fieldsLet's decode them one by one
for {
if string(buff[index]) != "D" {
break
}
index = index + 1
fullLengthOfMessage := utils.GetUint32Value(buff, &index)
numOfFields := utils.GetUint16Value(buff, &index)
count := numOfFields
for {
fieldLength := utils.GetUint32Value(buff, &index)
data := utils.GetStringValue(buff, fieldLength, &index)
fmt.Println(data)
count--
if count <= 0 {
break
}
}
}As long as the message starts with the identifier "D", can be considered as a row data and processed as such.
In the previous code snippet we get the number of fields present in the current row and for each one we extract the its length:
fieldLength := utils.GetUint32Value(buff, &index)with that length we can easily extract the string value:
data := utils.GetStringValue(buff, fieldLength, &index)If everything was successful, the final message should be CommandComplete, that means the previous query response is completed:
1 byte ASCII identifier ("C") + 32 bits integer message length + string tagThe string tag is a single word that identifies which SQL command was completed. Finally we should receive the ReadyForQuery which tells us we are ready to place our new query.
Closing the connection
There is one last message in case we want to close the connection gracefully:
1 byte ASCII identifier ("X") + 32 bits integer message lengthbuff := make([]byte, 0, 5)
buff = append(buff, "X"...)
buff = pgio.AppendInt32(buff, int32(utils.Int32ByteLen))The conclusion
We barely scratched the surface, we have no error handling, only one type of authentication supported, no replication, but it was still very interesting, at least for me. There is much going on under the hood and acquiring a connection is not exactly cheap, that is why things like connection pooling are so handy. In a serverless environment, where connection pooling is not easily available, this is quite a big problem. AWS has just released the AWS RDS Proxy, but unfortunately, at the time I am writing this article, is still not available for PostgreSQL and it won't be cheap. If you are using nodejs there is the Jeremy Daily package, but it is only for MySQL, I have created a package for Postgres.
I hope you learned something here, for sure I did and it was fun. If you have any correction or suggestion, feel free to send me an email.
Cheers.