Enable TLS (SSL) database connection and certificate-based authentication
This note aims to show step by step how to generate a self-signed SAN SSL/TLS certificate on macOS Monterey and then configure a secure connection and certificate-based authentication for PostgreSQL.
Check openssl version
To avoid using an outdated version of openssl, it might be a good idea to upgrade it first. The easiest way is to run in a terminal
brew install openssl
As a result instead of openssl you will need to use /usr/local/opt/openssl/bin/openssl.
Let’s check a newly installed version
/usr/local/opt/openssl/bin/openssl version
Output:
OpenSSL 3.0.5 5 Jul 2022 (Library: OpenSSL 3.0.5 5 Jul 2022)
Creating a Root Certificate Authority (CA)
The CA certificate is used to verify an authenticity of other certificates during secure communication.
Let’s create two directories named keys (to store cryptographic keys) and certs (to store certificates) with the appropriate permissions
mkdir keys certs && chmod og-rwx keys certs
Create a trusted root certificate authority (CA):
/usr/local/opt/openssl/bin/openssl req -new -days 365 -x509 -nodes \
-subj "/CN=Your Company Root CA" \
-keyout keys/ca.key \
-out certs/ca.crt
Notes
- The subject might be more detailed if you want
/C=CN/ST=GD/L=SZ/O=Your Company, Inc./CN=Your Company Root CA - You can also just skip the
-subjparameter within the command and pass all these arguments in the interactive mode.
Copy a certificate authority’s (CA) certificate (ca.crt) from the certs directory to a PostgreSQL configuration directory:
mkdir pgconf && cp certs/ca.crt pgconf/ca.crt
Generate the PostgreSQL server key and certificate
The following code generates a server key and a certificate signing request (CSR), has the CSR signed by the root CA, and adds the server key and certificate to the pgconf directory with the appropriate permissions:
/usr/local/opt/openssl/bin/openssl req -new -nodes -out server.csr \
-subj "/CN=postgres" \
-keyout pgconf/server.key
Notes
CN(Common Name) field should match the hostname of the server.- If you want to add multiple names in the certificate, you should use the Subject Alternative Name (SubjectAltName,
SAN) instead ofCN.
Now, you need to create server certificate with the appropriate EKU (Extended Key Usage) and SAN based on your pgconf/server.key file, e.g.:
/usr/local/opt/openssl/bin/openssl x509 -req -in server.csr -days 365 \
-CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
-out pgconf/server.crt \
-extfile <(cat /etc/ssl/openssl.cnf <(printf "\n[SAN]\nsubjectAltName=IP:127.0.0.1,DNS:localhost,DNS:postgres\nextendedKeyUsage=serverAuth")) \
-extensions SAN
Note that extendedKeyUsage says how a certificate can be used:
clientAuthmeans it can be used to authenticate a client, i.e. authentication by client certificate when doing mutual authentication.serverAuthmeans it can be used to authenticate a server.
Let’s remove currently unnecessary server.csr file
rm server.csr
Configure PostgreSQL to enable certificate-based authentication
Set the TLS specific settings in postgresql.conf:
ssl = on # this enables TLS
ssl_cert_file = '/pgconf/server.crt' # this specifies the server certificate
ssl_key_file = '/pgconf/server.key' # this specifies the server private key
ssl_ca_file = '/pgconf/ca.crt' # this specific which CA certificate to trust
Let’s explicitly lock down the configuration and TLS-related files:
chmod og-rwx pgconf/*
Generating the Client Key and Certificate
Now is the moment to configure a client, as failing to do so will prevent anyone from accessing this environment.
/usr/local/opt/openssl/bin/openssl req -new -nodes \
-subj "/CN=user1" \
-out client.csr \
-keyout keys/client.key
Note that the CN for the client certificate must match the username of the client in the database. For example, if I created a user named user1, then the CN value must also be user1.
Let’s lock down the keys files:
chmod og-rwx keys/*
Generate a certificate with an EKU clientAuth and SAN:
/usr/local/opt/openssl/bin/openssl x509 -req -in client.csr -days 365 \
-CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
-out certs/client.crt \
-extfile <(cat /etc/ssl/openssl.cnf <(printf "\n[SAN]\nkeyUsage=digitalSignature\nextendedKeyUsage=clientAuth\n")) \
-extensions SAN
rm client.csr
Connecting
You can use environment variables to specify the options:
# the first parameter specifies which TLS mode to use to connect
export PGSSLMODE="verify-full"
# the following two parameters point to the client key/certificate
export PGSSLCERT="`pwd`/certs/client.crt"
export PGSSLKEY="`pwd`/keys/client.key"
# this parameter points to the trusted root CA certificate
export PGSSLROOTCERT="`pwd`/certs/ca.crt"
See docs on environment variables for details.
Now, attempt a connection using only the certificate, without providing a password:
psql -h localhost -p 5432 -U user1 postgres
Throubleshooting
ca md too weak
Error: ca md too weak
Solution: Update your openssl, for ex., use brew install openssl on macOS and then /usr/local/opt/openssl/bin/openssl req -x509 -nodes....
To determine a path of the openssl executable on your system, run
$ which openssl
/usr/bin/openssl
Before (on macOS):
openssl version
Output: LibreSSL 2.8.3
After:
/usr/local/opt/openssl/bin/openssl version
Output: OpenSSL 3.0.5 5 Jul 2022 (Library: OpenSSL 3.0.5 5 Jul 2022)
Wrong keys permissions
Error: private key file "keys/client.key" has group or world access; file must have permissions u=rw (0600) or less if owned by the current user, or permissions u=rw,g=r (0640) or less if owned by root
Solution: Set an appropriate permission and ownership rights for your private key file.
Authentication failed for an user
Error: pq: certificate authentication failed for user "<user_name>"
Solution: If a certificate was generated with an EKU’s serverAuth, it should be assigned to a server certificate. If the certificate was generated with an EKU’s clientAuth, it should be assigned to a client certificate.
Leave a comment