Knowledgebase

Introduction

Dokku is a popular container manager that runs multiple Heroku buildpacks or Docker containers in a single instance. This architecture has many benefits, like isolation between different services running under the same host. Running PostgreSQL under Dokku allows for easy upgrades of PostgreSQL and sharing with other containers in the same instance.

This guide explains how to use a free Let's Encrypt SSL certificate to protect a PostgreSQL server running under Dokku on Ubuntu 20.04.

Prerequisites

• Host your domain with Rcs DNS.
• Create an "A" recordthat points to your server's IP address.
• Create a wildcard certificate with Lego.
• Connect to your server as a non-root sudo user.

1. Install Dokku

Install the latest version of Dokku on your server.

$ wget https://raw.githubusercontent.com/dokku/dokku/v0.25.4/bootstrap.sh
$ sudo DOKKU_TAG=v0.25.4 bash bootstrap.sh

Set the global domain for the Dokku instance. Replace example.com with your domain name.

$ dokku domains:set-global example.com

2. Install the Dokku PostgreSQL Extension

Install the Dokku PostgreSQL extension.

$ sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

Create a pgdb service on your instance with your preferred version of PostgreSQL. For example, PostgreSQL 13.4.

$ dokku postgres:create pgdb --image postgres --image-version 13.4

Change the postgres user password.

$ dokku postgres:connect pgdb

pgdb=# ALTER USER postgres PASSWORD 'your-new-password';
pgdb=# \q

Expose the PostgreSQL port to allow connections outside the container.

$ dokku postgres:expose pgdb 5432

3. Copy SSL Certificate to PostgreSQL

Create a scheduled task to copy the Let's Encrypt SSL certificate to the PostgreSQL data directory.

Locate the PostgreSQL data directory.

$ dokku postgres:info pgdb | grep "Data dir:"

The default data directory is /var/lib/dokku/services/postgres/pgdb/data.

4. Create a Renewal Hook

Switch to the root user.

$ sudo su

Create a renewal hook script.

# nano /usr/local/sbin/renew-cert-hook.sh

Paste the following script. Change example.com to your domain.

#!/bin/bash

umask 0177
DOMAIN=example.com
SERVICE=pgdb
CERT_DIR=/etc/letsencrypt/$DOMAIN/certificates
DATA_DIR=/var/lib/dokku/services/postgres/$SERVICE/data
cp $CERT_DIR/_.$DOMAIN.crt $DATA_DIR/server.crt
cp $CERT_DIR/_.$DOMAIN.key $DATA_DIR/server.key
chmod 600 $DATA_DIR/server.crt $DATA_DIR/server.key

Edit the file /usr/local/sbin/renew-cert.sh that you created when following the "Install Wildcard SSL with Lego" instructions in the Prerequisites and add the --run-hook flag, pointing to the file you created above.

After editing, your file will look like:

#!/bin/sh

export VULTR_API_KEY=xxxx_EXAMPLE_API_KEY_xxxx

export VULTR_HTTP_TIMEOUT=60
export VULTR_POLLING_INTERVAL=60
export VULTR_PROPAGATION_TIMEOUT=300
export VULTR_TTL=300

lego --dns vultr \
    --domains *.example.com \
    --domains example.com \
    --email admin@example.com \
    --path="/etc/letsencrypt/example.com" \
    --run-hook="/usr/local/sbin/renew-cert-hook.sh" \
    --accept-tos renew

Test the script.

# bash /usr/local/sbin/renew-cert.sh

Restart the PostgreSQL instance.

# postgres:restart pgdb

Exit the root user account.

# exit

5. Test the Connection

Connect to the database from another machine with the PostgreSQL client installed. Replace example.com with your server's domain name.

$ psql -d "dbname=postgres sslmode=require" -h example.com -U postgres

You should see the PostgreSQL prompt.

Password for user postgres:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#

Type \Q to exit the PostgreSQL client.

postgres=# \q