Secure Connections to PostgreSQL
Securing PostgreSQL using mutual authentication over TLS commonly known as mTLS using a Self Signed Certificate Authority with good enough encryption.
The importance of implementing encryption for data in transit is a basic requirement for GDPR & HIPAA. Through maintaining good security hygiene even if you're not interested in compliance, you're reducing the chances of data being stolen. Rolling out a Certificate Authority is not an easy task. There are multiple levels to consider. For the purposes of this article, I'll show how to configure a Self Signed Root CA. You could take in further and install the Root CA and Intermediate CAs into Linux or Windows
CA Architecture
Certificate Authority architecture is not as complicated as it might sound. There are however significant security considerations to consider when deciding how services will access adjacent or adjoining services. mTLS between services provides two protections, first by enforcing TLS 1.2/1.3 it reduces the chances of information being leaked. The second the server will authenticate the client when it connects and verify the connecting cert is valid relative to the Root CA. Those two protections are enough to provide good enough security for data in transit. Host Name Verification can be used to further increase security between services; more on that later when talking about Common Name and Subject Alt name.
CA Architecture has a Root CA protected on a machine only accessible to the DevOps utilities requesting and distributing certificates, a machine only a Developer can access manually, or inside a Cloud HSM service. There is zero necessity to have the RootCA directly exposed to the internet. If however that is the case.. you should strongly consider removing the RootCA from anything that can be accessed through a public IPAddress
2 Tier CA Architecture
2 Tier Architecture consists of a RootCA and many service Certificates. In the example below, PostgreSQL, Valkey, and Rocket.rs
OpenSSL provides a command line interface that'll allow us to create the RootCA and services. Let's walk through an example for PostgreSQL
$ mkdir ca && cd ca
$ openssl genrsa -out ca.key 2048
$ openssl req -new -x509 -key ca.key -out ca.crt -subj=/C=US/ST=None/L=None/O=Dev/CN=root-ca
$ openssl genrsa -out postgresql.key 2048
$ openssl rsa -in postgresql.key -pubout -out postgresql.pub
$ openssl req -new -key postgresql.key -out postgresql.csr -subj=/C=US/ST=None/L=None/O=Dev/CN=postgresql
$ openssl x509 -req -in postgresql.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out postgresql.crt
3 Tier CA Architecture
3 Tier CA Architecture takes it a step further and allows the creation of Key/Cert pairs for Tenants. Anything signed with Tenant A Intermediate CA can only access services from Tenant A. Same for Tenant B, only PostgreSQL B and Valkey B can access each other. Access from PostgreSQL A to PostgreSQL B and vice versa would not work because the connecting Key/Cert pair connecting from either service to the other, would not be signed by an adjacent Intermediate CA
Similar to a 2 Tier CA Architecture, the 3 Tier CA Architecture keeps the Root CA offline and should only be accessed by orchestration software or a developer when necessary
The process for creating an Intermediate CA is similar to creating Service Key/Cert pairs. However, there are specific parameters available in x509, one being the restriction of additional CA creation. We'll cover those values in a follow up article. For new, lets move forward with generating two tenants, each with a PostgreSQL Key/Cert pair
$ mkdir ca && cd ca
# Root CA
$ openssl genrsa -out ca.key 2048
$ openssl req -new -x509 -key ca.key -out ca.crt -subj=/C=US/ST=None/L=None/O=Dev/CN=root-ca
# Tenant A
$ openssl genrsa -out tenant-a.key 2048
$ openssl rsa -in tenant-a.key -pubout -out tenant-a.pub
$ openssl req -new -key tenant-a.key -out tenant-a.csr -subj=/C=US/ST=None/L=None/O=Dev/CN=tenant-a
$ openssl x509 -req -in tenant-a.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out tenant-a.crt
# PostgreSQL A
$ openssl genrsa -out postgresql-a.key 2048
$ openssl rsa -in postgresql-a.key -pubout -out postgresql-a.pub
$ openssl req -new -key postgresql-a.key -out postgresql-a.csr -subj=/C=US/ST=None/L=None/O=Dev/CN=postgresql-a
$ openssl x509 -req -in postgresql-a.csr -CA tenant-a.crt -CAkey tenant-a.key -CAcreateserial -out postgresql-a.crt
# Tenant B
$ openssl genrsa -out tenant-b.key 2048
$ openssl rsa -in tenant-b.key -pubout -out tenant-b.pub
$ openssl req -new -key tenant-b.key -out tenant-b.csr -subj=/C=US/ST=None/L=None/O=Dev/CN=tenant-b
$ openssl x509 -req -in tenant-b.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out tenant-b.crt
# PostgreSQL B
$ openssl genrsa -out postgresql-b.key 2048
$ openssl rsa -in postgresql-b.key -pubout -out postgresql-b.pub
$ openssl req -new -key postgresql-b.key -out postgresql-b.csr -subj=/C=US/ST=None/L=None/O=Dev/CN=postgresql-b
$ openssl x509 -req -in postgresql-b.csr -CA tenant-b.crt -CAkey tenant-b.key -CAcreateserial -out postgresql-b.crt
Setup PostgreSQL using Docker
For the rest of the article, I'll stick to using a 2 Tier CA Architecture. Let's go ahead and configure PostgreSQL
Configuration
docker-compose.yml
Adding storage as a Docker Service in docker-compose.yml provides a bare minimum configuration for a relatively secure PostgreSQL service running locally. The configuration could easily be expanded and deployed using other orchestration software such as GitOps or k8s. The Docker Image used is postgres:17.2-alpine3.21 which is an official image managed and maintained by the PostgreSQL Docker Community
Using the latest tag for an image is something you could do, but I recommend pinning to a specific version(:17.2-alpine3.21) so when it comes time to update the software, it doesn't migrate the database from a previous version(16) to the latest version(17) without you realizing it. Automation is great, until it fails and you have to figure out what happened
command in docker compose can override CMD with options or arguments. This is possible because the 17.2-alpine3.21 image uses CMD to specify a default command. >- is the Block Chomping Indicator. It flags a yaml parser to remove newlines from the multi-line string. You'll need to coordinate the filepath location relative to where the RootCA and Key/Cert pair is mounted into the container using a few volume entries in the yaml. Finally, set default environment variables for the username, database name, and password to access the container while its in operation and expose the container port 5432 to 8432 or something more to your liking
storage:
restart: always
image: postgres:17.2-alpine3.21
command: >-
-c ssl=on
-c ssl_min_protocol_version=TLSv1.3
-c ssl_cert_file=/var/lib/postgresql/postgresql.crt
-c ssl_key_file=/var/lib/postgresql/postgresql.key
-c ssl_ca_file=/var/lib/postgresql/ca.crt
-c hba_file=/var/lib/postgresql/pg_hba.conf
environment:
POSTGRES_DB: dbname
POSTGRES_USER: dbuser
POSTGRES_PASSWORD: password
ports:
- 8432:5432
volumes:
- ./docker-data/postgresql-data:/var/lib/postgresql/data:rw
- ./docker-data/configs/pg_hba.conf:/var/lib/postgresql/pg_hba.conf:ro
- ./docker-data/postgresql-logs:/var/log/postgresql:rw
- ./docker-data/ca/ca.crt:/var/lib/postgresql/ca.crt:ro
- ./docker-data/ca/postgresql.crt:/var/lib/postgresql/postgresql.crt:ro
- ./docker-data/ca/postgresql.key:/var/lib/postgresql/postgresql.key:ro
pg_hba.conf
pg_hba.conf file provides the configuration entry point to secure connections being made to various databases for all or for each user relative to how it connects. For our purposes, we'll want something that is secure enough by asking for two challenges. First set hostssl and require a Key/Cert pair signed by the RootCA created above. The second challenge is enabled by specifying scram-sha-256 instead of trust, which will prompt the connecting clients for a password
hostssl dbname dbuser 127.0.0.1/32 scram-sha-256
hostssl dbname dbuser ::1/128 scram-sha-256
hostssl dbname dbuser all scram-sha-256
# Replication Privilges
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
The more details for the hb_conf.conf file are available in PostgreSQL source control. By restricting connections to the one database managed by Docker Compose, attackers will need to guess the exact database name to connect into rather than dropping into a default database. We further reduce the attack surface even more by only allowing dbuser to connect
Connection Testing
$ export PGSSLCERT=$PWD/docker-data/ca/postgresql.crt
$ export PGSSLKEY=$PWD/docker-data/ca/postgresql.key
$ export PGSSLROOTCERT=$PWD/docker-data/ca/ca.crt
$ export PGSSLMODE=verify-ca
$ export PGPASSWORD=password
$ psql -h localhost -p 8432 -U dbuser dbname
OpenSSL Debugging
OpenSSL ships with a number of utilities that will help you test your TLS implementation. TLS 1.3 provides a specification that both OpenSSL and rustls implement in order to provide secure connections for transmitting data between services. The specification is also implemented by other languages; I only make a specific note here about OpenSSL and rustls because we'll be using OpenSSL to test rustls in other articles
openssl s_client
s_client will connect to a host using DNS, IPv4, or IPv6 with a Port. Let's use Docker Service storage to test it out
$ openssl s_client -connect localhost:8432 > debug.log 2>&1
Let's take a look at the diagnostic information
$ cat debug.log
CONNECTED(00000005)
---
Certificate chain
0 s:C=US, ST=None, L=None, O=Dev, CN=postgresql
i:C=US, ST=None, L=None, O=Dev, CN=rootca
a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
v:NotBefore: Jan 4 09:00:33 2025 GMT; NotAfter: Feb 3 09:00:33 2025 GMT
1 s:C=US, ST=None, L=None, O=Dev, CN=rootca
i:C=US, ST=None, L=None, O=Dev, CN=rootca
a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
v:NotBefore: Jan 4 09:00:33 2025 GMT; NotAfter: Feb 3 09:00:33 2025 GMT
---
Server certificate
-----BEGIN CERTIFICATE-----
<redacted>
-----END CERTIFICATE-----
subject=C=US, ST=None, L=None, O=Dev, CN=postgresql
issuer=C=US, ST=None, L=None, O=Dev, CN=rootca
---
Acceptable client certificate CA names
C=US, ST=None, L=None, O=Dev, CN=rootca
Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:RSA+SHA224
Shared Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: ECDH, prime256v1, 256 bits
---
SSL handshake has read 2609 bytes and written 747 bytes
Verification error: self-signed certificate in certificate chain
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Protocol: TLSv1.3
Server public key is 2048 bit
This TLS version forbids renegotiation.
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 19 (self-signed certificate in certificate chain)
---
closed
The diagnostic information provides the full cert chain and verifies the TLS configuration is correct enough for us. What I mean by correct enough, is that we're still using a self-signed cert. That part of the validation will fail, and the part that checks the hostname should fail, but everything else should be fine
Verification error: self-signed certificate in certificate chain
Verify return code: 19 (self-signed certificate in certificate chain)
openssl x509
x509 provides diagnostic information about crt and other files. It's best used when you'd like to learn more about the service your connecting to, or extract more parameters about the CA that signed the certificate
$ openssl x509 -noout -text -in docker-data/ca/ca.crt > debug.log
-noout and -text together seem odd together, at least to me. Together, the options indicate to x509 there will be no output such as a crt or csr file and to print diagnostic information about the certification from the -in option to stdout
Certificate:
Data:
Version: 3 (0x2)
Serial Number:
<redacted>
Signature Algorithm: sha256WithRSAEncryption
Issuer: C=US, ST=None, L=None, O=Dev, CN=rootca
Validity
Not Before: Jan 4 09:00:33 2025 GMT
Not After : Feb 3 09:00:33 2025 GMT
Subject: C=US, ST=None, L=None, O=Dev, CN=rootca
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
<redacted>
Exponent: <redacted>
X509v3 extensions:
X509v3 Subject Key Identifier:
<redacted>
X509v3 Authority Key Identifier:
<redacted>
X509v3 Basic Constraints: critical
CA:TRUE
Signature Algorithm: sha256WithRSAEncryption
Signature Value:
<redacted>
There is quite a bit of information to look at, most notably is Validity and information on how the fingerprints and block ciphers are implemented for the Certificate. I've <redacted> anything that makes the output harder to read
Common Name(CN) and Subject Alt Name(SAN) extensions provided can be used to verify the hostname against the Server's Certificate to line up with DNS entry or an IPAddress. Traditionally, only Common Name would be used to line up to something like localhost to your server. Subject Alt Name provides the ability to set multiple aliases such localhost, 127.0.0.1, or even pc-name.local. Therefore you'll be able to have as many DNS or IP entries as you'd like