PostgreSQL 12: TLS Connection
Tags: postgres, database, tls, security
Published: 2020-06-02 14:12:42


\* The cover image is originally by [TheDigitalArtist](https://pixabay.com/users/thedigitalartist-202249/) and edited with great appreciation.

---

## Introduction

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

### References

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

---

## 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.

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

Use [openssl](https://man.openbsd.org/openssl.1) command line tool.

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

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

{% post nabbisen/libressl-on-openbsd-error-loading-extension-section-v3ca-3k2m %}

---

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

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

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

```diff
+ 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.

```console
$ exit
```

Finally, restart the database server.

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

### 3. Verify in client machine

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

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

Enter db user's password.

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