I recently wrote pg-check-cert, a small tool to check whether a postgresql server’s SSL certificate is about to expire. It was featured in PostgreSQL Weekly Issue 164.

This script connects to a postgresql instance, checks the certificate and displays the amount of days left before it expires. It’s intended to be used for monitoring your postgresql certificates, using a monitoring tool like Zabbix or Nagios.

Why openssl is not enough

I used to monitor my postgresql certificates using openssl. Unfortunately, the openssl s_client option does not support the postgresql handshake, and can therefore only look at the .crt file to monitor the expiration date. As postgresql needs to be restarted after the .crt file was replaced, the actual file might be updated, but postgresql is still using the old certificate in-memory, until the server is restarted (as of postgresql-9.5, reload is not sufficient to read in the new certificate).

Installation

Precompiled versions (linux-amd64, osx-amd64) are available on the release page. Download the file, extract it and move it to e.g. /usr/local/bin.

Build 

go build -o pg-check-cert *.go

Usage 

pg-check-cert localhost:5432

Thanks

  • thusoy/postgres-migm for the inspiration.
  • buf.go and conn.go are taken from lib/pq, see copyright notice in the respective files.