Summary

To use PostgreSQL as external database servers, it's better to use TLS/SSL connection. This post shows how to generate certificates, configure servers and verify them.

There are just 3 steps.

1. Prepare for server certificates
   • Generate self-signed certificates
   • (Optional) Generate CA-signed certificates for clients to verify
2. Edit server config files
   • postgresql.conf : Edit options, listen_address / ssl
   • pg_hba.conf : Add hostssl definition
3. Verify in a client machine
   • Use psql with sslmode

Environment

• OpenBSD 7.1
• PostgreSQL 14.2

Reference

• Official docs on "ssl-tcp": 14, current

Tutorial

1. Prepare for server certificates

Generate self-signed certificates

Create server certificates in the PostgreSQL data directory as _postgresql user.

$ doas su - _postgresql
$ whoami
_postgresql

$ cd /var/postgresql/data

Create a self-signed certificate with openssl command line tool.
Of course, -days 36500 below can be modified, which means it will be valid within 36500 days = almost 100 years.

$ # ksh
$ DB_HOST_DOMAIN="(...)"
$ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"

The output was:

Generating a 2048 bit RSA private key
..............................................................................+++++
................+++++
writing new private key to 'server.key'
-----

You will see:

$ ls -l server\.*
-rw-r--r--  1 _postgresql  _postgresql  3660 Apr 24 13:17 server.crt
-rw-r--r--  1 _postgresql  _postgresql  1704 Apr 24 13:17 server.key

Modify permission of the key:

$ chmod 400 server.key

Stay in /var/postgresql/data as _postgresql.

(Optional) Generate CA-signed certificates for clients to verify

If you don't hesitate to edit /etc/ssl/openssl.cnf to use v3_ca extensions, it is able to create a server certificate whose identity can be validated by clients. It creates root and intermediate certificates. The detail about editing is in the official docs:

$ # create a certificate signing request (CSR) and a public/private key file
$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=$ROOT_CA_DOMAIN"
$ chmod 400 root.key

$ # create a root certificate authority
$ openssl x509 -req -in root.csr -text -days 36500 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
$ # ... might be end with "Error Loading extension section v3_ca"

$ # create a server certificate signed by the new root certificate authority
$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
$ chmod 400 server.key

$ openssl x509 -req -in server.csr -text -days 36500 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

Here, you might meet "Error Loading extension section v3_ca". In this case, try to modify /etc/ssl/openssl.cnf following this post.

$ ls {root,server}*
root.crt   root.csr   root.key   root.srl   server.crt server.csr server.key

2. Edit server config files

You are in /var/postgresql/data as _postgres. Right?

$ whoami
_postgresql
$ pwd  
/var/postgresql/data

Edit postgresql.conf:

$ nvim postgresql.conf

so as to enable requests from remote clients and also ssl connection:

  #listen_addresses = 'localhost' ...
+ listen_addresses = '*'
  ...
  #port = 5432
+ port = {$DB_PORT} # (optional) for security
  ...
  #ssl = off
+ ssl = on
  #ssl_cert_file = 'server.crt'
  #ssl_ca_file = ''
  #ssl_crl_file = ''
  #ssl_key_file = 'server.key'

Besides, when you use not-self-signed certificates and have root.crt, ssl_ca_file must be filled.

Next, edit pg_hba.conf:

$ nvim pg_hba.conf

to add a line on hostssl to the bottom so as to allow ssl connection from clients:

+ hostssl all             all             0.0.0.0/0               md5

As of 14, you don't have to set clientcert to 0 when you use self-signed certificates. It is one of "auth-options", which can be set to verify-ca or verify-full when you have valid client certificates. }}">In 13 and smaller, it can be set to 1 (defalut, then)/0/no-verify.

Besides, when something fails on the way, /var/postgresql/logfile is surely useful to get the detail.

Let's come back to your user:

$ # end of behavior as _postgresql
$ exit

Restart the database server:

$ doas rcctl restart postgresql

Done.

3. Verify in a client machine

In a client machine, use psql with "sslmode=require":

$ psql "sslmode=require host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME"
Password for user ...: 

Enter db user's password.

psql (14.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Here, TLS connection based on "protocol: TLSv1.3" etc. is acquired :)