tzeejay icon




Check PostgreSQL TLS Certificates in Pure Go

TLS certificate expirations are quite embarrassing but still happen somewhat regularly to various services of all sizes. The core system at Guardian Firewall that I maintain have had such outages as well and I was just as embarrassed. The second was not my fault but rather a bug in an automated tool that we use to obtain a Let’s Encrypt certificate for our servers, still though these are things that I should have caught and should not have happened. Not my fault does not mean not my problem.

To prevent these scenarios I built a system that checks a manually defined list of servers once every 24 hours and alarms us in a special Slack channel if the expiration date for the server has crossed a certain threshold. If the threshold dips below 7 days it even @ mentions a group of people which leads to a push notification on all logged in devices for all members of the group. Somebody responsible will be hearing about this problem.

Doing this to HTTP connections in Go is straightforward as everything is very openly exposed by the Go stdlib but we also run PostgreSQL as our core database engine and we wanted to make sure to include it in these routine checks as well. Connections to the DB never hit the public internet in my setup or are always properly encrypted through a local port forward of a SSH session, but sprinkling some TLS 1.3 on top shouldn’t hurt anybody. Having a valid & trusted certificate is easy to come by these days and will remove some friction while using the system.
It’s free real estate operational insurance.
When I initially built this system I was struggling to understand how the TLS connection to PostgreSQL is established as tls.Dial("tcp", "postgres-ip:5432", &tls.Config{}) will try to do the right thing but ultimately fails and leads to an obscure error in the PostgreSQL log:

2022-06-07 12:25:38.754 UTC [7325] [unknown]@[unknown] LOG: invalid length of startup packet

That right there should have been enough of a hint for me to figure this out, as I am a little familiar at the very least with STARTTLS from my adventures into running-your-own-email-server land. The connection is initiated unencrypted, you pass a (not so) secret “phrase” to it, it responds with a (not so) secret “phrase” back and you can finally upgrade the connection to being encrypted by initiating the TLS handshake.
It is very easy to type out in hindsight as this all makes sense to me now, but I was really struggling to wrap my head around it not even 24 hours ago.
Thankfully though I have amazing colleagues, who are quite a bit smarter than me. This explanation made me understand the process immediately and I hope that it will help somebody else as well.

15:14 Uhr
It's a custom protocol.
15:15 Uhr
So you want to send this 00 00 00 08 04 D2 16 2F.
15:16 Uhr
Read the first byte.
15:17 Uhr
It should be character S.
15:17 Uhr
Then write/read the SSL handshake start.
15:18 Uhr
The TLS session only starts after that first byte.
15:19 Uhr
So a normal TLS socket will fail because it will receive that first byte which doesn't really make any sense because it's part of the binary PG protocol.
15:21 Uhr
openssl s_client -starttls postgres basically does that, it sends the PG magic to start the TLS connection.
15:21 Uhr
You can't do it the "normal" way.

The protocol as published by the PostreSQL authors can be found here.

She graciously published her little POC Golang program for me so that I can include it on here as well.

Mirror in case Github does something stupid

Building these little early warning systems yourself is not difficult and I would encourage anybody to do so. Save yourself from the “embarrassment” of having your TLS certificate expire in the year 2022 and beyond by having a little computer give you a nudge every once in a while.

If you find yourself struggling as I did with this task I hope this post helps you.