PostgreSQL 12: TLS Connection

* The cover image is originally by TheDigitalArtist and edited with great appreciation.

Introduction

To use remote PostgreSQL server as database server, it's better to use TLS/SSL connection.
This post shows a simple and easy way with self-signed certificate.

Environment

• OpenBSD 6.7
• PostgreSQL 12.2

References

• Official docs about "ssl-tcp": 12, current

Summary

There are just 3 steps.

1. Prepare for server certificates
   • generate self-signed certificate
2. Configure server to edit files
   • postgresql.conf : edit options, listen_address / ssl
   • pg_hba.conf : add hostssl definition, and include "verify-ca=0" as one of auth-options in it if your clients don't have valid certificates
3. Verify in client machine
   • $ psql "sslmode=require host=$DB_HOST user=$DB_USER dbname=$DB_NAME"

Tutorial

1. Prepare for server certificates

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

$ doas su _postgresql
$ cd /var/postgresql/data

Use openssl command line tool.

$ # create a self-signed certificate
$ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
$ chmod 400 server.key

Of course, "36500" which means 36500 days = almost 100 years as valid term can be modified.

Optionally, 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 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

$ # 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 the error about v3_ca extension. In this case, try to modify /etc/ssl/openssl.cnf following the below post:

LibreSSL on OpenBSD: Error due to missing v3_ca in extension

nabbisen ・ Jul 8 '20

#libressl #openssl #openbsd #troubleshooting

After running one of the commands sets above, some files are created. server.crt and server.key are important.

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

Stay in /var/postgresql/data as _postgresql.

2. Configure server to edit files

First, edit postgresql.conf so as to enable requests from remote clients and also ssl connection:

  #listen_addresses = 'localhost'
  ...
+ listen_addresses = '*'
  ...
  #ssl = off
+ ssl = on
  #ssl_ca_file = ''
  #ssl_cert_file = 'server.crt'
  #ssl_crl_file = ''
  #ssl_key_file = 'server.key'

ssl_ca_file may be filled when it was created as root.crt or something else.

Next, edit pg_hba.conf and add a hostssl line at the bottom so as to allow ssl connection from clients:

+ hostssl all             all             0.0.0.0/0               md5 clientcert=0

clientcert is one of auth-options. Set it to "0" if you clients don't have valid certificates. When they have valid ones, use "verify-ca" or "verify-full" instead. (Update: Be careful "0" is no longer supported as of 14.)

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

After all done, say thank you and goodbye to _postgresql.

$ exit

Finally, restart the database server.

$ doas rcctl restart postgresql

3. Verify in client machine

In the client machine, use psql with "sslmode=require" statement like this:

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

Enter db user's password.

psql (12.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

{$DB_NAME}=> 

Here, TLS connection is acquired :)