4 minute read

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


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


  • 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 -subj parameter 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


  • 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 of CN.

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:,DNS:localhost,DNS:postgres\nextendedKeyUsage=serverAuth")) \
  -extensions SAN

Note that extendedKeyUsage says how a certificate can be used:

  • clientAuth means it can be used to authenticate a client, i.e. authentication by client certificate when doing mutual authentication.
  • serverAuth means 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


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


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

Before (on macOS):

openssl version

Output: LibreSSL 2.8.3


/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