Check Postgres SSL Cert

check_postgres_ssl_cert.sh

# Heroku Postgres spins up instances that could be using self-signed certificates
# You can view the details of a Postgres certificate to validate it

openssl version
# version 1.1.1+ supports postgres client connections
# RHEL 8 / CentOS 8 should have a new enough version of openssl
# macOS 11 uses LibreSSL and does not have support for this

POSTGRES_HOST="my_postgres_db.lan"
POSTGRES_PORT="5432"
openssl s_client -starttls postgres -connect ${POSTGRES_HOST}:${POSTGRES_PORT}

# A few things to notice in the output that identify the self signed certificate is
# initially the certificate chain and the fact that the certificate subject and issuer
# is the same. In part of the connection details it even calls out that there was a
# verification error relating to the self signed certificate

# ---
# Certificate chain
#  0 s:CN = ip-10-0-43-106.ec2.internal
#    i:CN = ip-10-0-43-106.ec2.internal
# ---
# ...
# subject=CN = ip-10-0-43-106.ec2.internal
# issuer=CN = ip-10-0-43-106.ec2.internal
# ...
# ---
# SSL handshake has read 1470 bytes and written 795 bytes
# Verification error: self signed certificate
# ---
# ...
# Verify return code: 18 (self signed certificate)
# ---