8.3.16. Configure SSL for PostgreSQL

TLS/SSL encryption is used to secure data transmitted between the client and the server. Follow these instructions to configure encryption using your own SSL certificate with hostname validation.

1. Generate the certificate and key files (in the command, replace example.psql.tools in two lines with the host address of your instance):

   openssl req -x509 -newkey rsa:2048 \
     -sha256 -days 365 \
     -nodes \
     -keyout server-key.pem \
     -out server-cert.pem \
     -subj "/CN=example.psql.tools" \
     -addext "subjectAltName=DNS:example.psql.tools" \
     -addext "keyUsage=digitalSignature,keyEncipherment" \
     -addext "extendedKeyUsage=serverAuth"

2. Install the certificate in the "PostgreSQL" section on the "Settings → SSL" tab (leave the private key password field blank if you did not specify one when generating the certificate and key):
3. Check the host address in the installed certificate (use your connection credentials in the command: example.psql.tools — host, 12345 — port):

   openssl s_client -connect example.psql.tools:12345 -starttls postgres -showcerts </dev/null 2>/dev/null | openssl x509 -noout -subject -issuer -ext subjectAltName

   Without a certificate installed, the output looks like this:

   subject -issuer -ext subjectAltName
   subject=CN = adm.tools
   issuer=CN = adm.tools
   No extensions in certificate

   After installing the certificate, the output will show the host address of your instance:

   subject=CN = example.psql.tools
   issuer=CN = example.psql.tools
   X509v3 Subject Alternative Name:
       DNS:example.psql.tools

4. Connect to the instance using a certificate and make sure the connection is encrypted (use your connection credentials in the command: example.psql.tools — host, 12345 — port, user — username):

   psql "host=example.psql.tools port=59264 dbname=db1 user=user sslmode=verify-full sslrootcert=server-cert.pem"

   Execute the SQL query:

   SELECT ssl, version, cipher, bits, client_dn FROM pg_stat_ssl WHERE pid = pg_backend_pid();

   If everything is configured correctly, the connection is established successfully, and the output looks like this:

    ssl | version |         cipher         | bits | client_dn
   -----+---------+------------------------+------+-----------
    t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 |