PostgreSQL 14: TLS Connection
Tags: postgres, database, tls, security
Published: 2022-04-24 05:04:51

## Summary

To use [PostgreSQL](http://postgresql.org/) 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](https://www.openbsd.org) 7.1
- PostgreSQL 14.2

### Reference

- Official docs on "ssl-tcp": [14](https://www.postgresql.org/docs/14/ssl-tcp.html), [current](https://www.postgresql.org/docs/current/ssl-tcp.html)

## Tutorial

### 1. Prepare for server certificates

#### Generate self-signed certificates

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

```console
$ doas su - _postgresql
$ whoami
_postgresql

$ cd /var/postgresql/data
```

Create a self-signed certificate with [openssl](https://man.openbsd.org/openssl.1) command line tool.
Of course, `-days 36500` below can be modified, which means it will be valid within 36500 days = almost 100 years.

```console
$ # 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:

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

You will see:

```console
$ 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:

```console
$ 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](https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES):

```console
$ # 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](https://obsd.solutions/en/blog/2020/06/08/libressl-error-due-to-missing-v3_ca-in-extension/).

```console
$ 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?

```console
$ whoami
_postgresql
$ pwd  
/var/postgresql/data
```

Edit postgresql.conf:

```console
$ nvim postgresql.conf
```

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

```diff
  #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:

```console
$ nvim pg_hba.conf
```

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

```diff
+ 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"](https://www.postgresql.org/docs/14/auth-pg-hba-conf.html), which can be set to `verify-ca` or `verify-full` when you have valid client certificates. [In 13 and smaller]({{< relref "postgresql-12-tls-connection" >}}), 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:

```console
$ # end of behavior as _postgresql
$ exit
```

Restart the database server:

```console
$ doas rcctl restart postgresql
```

Done.

### 3. Verify in a client machine

In a client machine, use [`psql`](https://www.postgresql.org/docs/current/app-psql.html) with "sslmode=require":

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

Enter db user's password.

```console
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 :)