Example with Self-Signed Certificate

In this section we will show how you can set up TLS with a self-signed certificate. Because it's self-signed, the client side cannot use its default root certificates to verify the authenticity of the server. We need to configure every client individually to trust our self-signed certificate.

We'll assume we have separate client and server hosts, otherwise it does not make sense to configure TLS. We also assume that the server host is running Linux and that it is reachable as dbhost.mymonetdb.org.

Client side

We'll demonstrate the client side both with mclient and, because MonetDB might not always be installed on the client side, with Python.

For mclient, install MonetDB Dec2023 or later as usual. In particular, install the client package, which is MonetDB-client on Red Hat/Fedora and monetdb-client with Debian/Ubuntu.

For Python, run pip install pymonetdb to install the Python client library. If pymonetdb is already installed, run Python interactively to verify that the installed version is at least version 1.8.0:

$ python3
Python 3.11.4 (main, Aug 25 2023, 14:04:46) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymonetdb
>>> pymonetdb.__version__
'1.8.0'

Configure MonetDB on the server

First configure MonetDB as usual as explained in the general installation instructions. From here on we'll assume monetdbd is running in farm directory /var/monetdb5/dbfarm.

Create a database demo and verify that you can succesfully connect to it with the following command on the server:

mclient -d demo -s "SELECT 'hello, world'"

By default, MonetDB listens on port 50000. However, we are going to use port 50000 for the TLS connections so we'll move the plain listen port to 49999. You may of course also choose any other available port. The port can be changed using monetdbd set:

$ sudo monetdbd set listenaddr=localhost /var/monetdb5/dbfarm
$ sudo monetdbd set port=49999 /var/monetdb5/dbfarm
$ sudo systemctl restart monetdbd.service

To test this, try

$ mclient -h localhost -p 49999 -d demo -s "select 'hello, world'"

Create a self-signed certificate

On Linux we typically use the OpenSSL command line utilities to manage keys. Be careful. In the name of backward compatibility, OpenSSL tends to use non-obvious and horrifically inappropriate default values and it will allow you to do very dangerous things without as much as a warning. That said, you can use something like the following shell script to generate and sign keys.

The script generates a key for domain (host name) $DOMAIN, valid for $EXPIRATION_DAYS days. Of course you can also change the file names.

#!/bin/bash -e -x

DOMAIN=dbhost.mymonetdb.org
EXPIRATION_DAYS=90

KEY_FILE="selfsigned.key"
CSR_FILE="selfsigned.csr"
CRT_FILE="selfsigned.crt"

openssl req -newkey rsa:2048 -nodes -keyout "$KEY_FILE" -out "$CSR_FILE" -subj "/CN=$DOMAIN/subjectAltName=$DOMAIN" --verbose -batch
openssl x509 -signkey "$KEY_FILE" -in "$CSR_FILE" -req -days "$EXPIRATION_DAYS" -out "$CRT_FILE"
rm "$CSR_FILE"

# Explained later:
systemctl reload stunnel-monetdb.service

The script should be run as root in a directory only accessible by root. For example, /etc/ssl/private. Running it will leave the files selfsigned.key and selfsigned.crt. The .key file is secret and should never leave the server or be readable by anyone but root.

The .crt file is not secret at all and will be copied to all systems that need to connect to this server. It's a plain text file so you can simply cat selfsigned.crt and copy-paste the contents.

The key will expire in 90 days so make sure to periodically rerun the script before that happens, perhaps using a cron job.

Configure stunnel

The next step is to set up the TLS termination proxy. We will use stunnel.

First we need to install it. On Debian/Ubuntu, run sudo apt install stunnel4. On Red Hat/Fedora, run yum install stunnel or dnf install stunnel.

Then write the following config file somewhere, for example in /etc/stunnel-monetdb.conf. Make sure the cert= and key= settings are correct for your system.

# /etc/stunnel-monetdb.conf

foreground = yes

# comment this out once you have everything working
debug = debug

cert=/etc/ssl/private/selfsigned.crt
key=/etc/ssl/private/selfsigned.key

[monetdbd]
accept = 50000
connect = localhost:49999

With this file in place, we can manually test it. Still root, run the following command on the server:

# stunnel /etc/stunnel-monetdb.conf
2024.01.11 09:18:19 LOG5[ui]: stunnel 5.68 on x86_64-pc-linux-gnu platform
2024.01.11 09:18:19 LOG5[ui]: Compiled with OpenSSL 3.0.9 30 May 2023
2024.01.11 09:18:19 LOG5[ui]: Running  with OpenSSL 3.0.11 19 Sep 2023
2024.01.11 09:18:19 LOG5[ui]: Threading:PTHREAD Sockets:POLL,IPv6,SYSTEMD TLS:ENGINE,OCSP,PSK,SNI Auth:LIBWRAP
2024.01.11 09:18:19 LOG5[ui]: Reading configuration from file /etc/stunnel-monetdb.conf
2024.01.11 09:18:19 LOG5[ui]: UTF-8 byte order mark not detected
2024.01.11 09:18:19 LOG5[ui]: FIPS mode disabled
2024.01.11 09:18:19 LOG3[ui]: No trusted certificates found
2024.01.11 09:18:19 LOG5[ui]: Configuration successful
2024.01.11 09:18:19 LOG5[ui]: Binding service [monetdbd] to :::50000: Address already in use (98)

The warning :::50000: Address already in use is harmless, ignore it. If you get this far, stunnel is running and happy. Now, open another shell on the server and run

$ mclient -d monetdbs://dbhost.mymonetdb.org/demo -s 'select 42'
TLS error: SSL_connect handshake: certificate verify failed

If you see this, mclient was able to connect to stunnel but it rejected the certificate stunnel offered because the certificate is self-signed. This behavior is correct,

Now copy the file selfsigned.crt somewhere on the system where mclient has permission to read it. For example, $HOME/selfsigned.crt. Try again:

$ sudo cat /etc/ssl/private/selfsigned.crt >$HOME/selfsigned.crt
$ mclient -d "monetdbs://dbhost.mymonetdb.org/demo?cert=$HOME/selfsigned.crt" -s 'select 42'
+------+
| %2   |
+======+
|   42 |
+------+
1 tuple

It works!

Test on the client side

We run these client tests while we're still running stunnel in a shell session on the server, as in the previous section.

Before continuing, make sure port 50000 on the server is actually reachable from the client. For example, if the server is running in AWS EC2, the port is blocked by the built-in firewall unless you explicitly open it. Apart from that, testing on the client side is similar to the test we ran on the server:

$ mclient -d monetdbs://dbhost.mymonetdb.org/demo

or, with Python:

$ python3 -c 'import pymonetdb; pymonetdb.connect("monetdbs://dbhost.mymonetdb.org/demo")'

If this hangs, it's probably a network issue. If it says it could not verify the certificate, it worked. Note that with mclient, a succesful connection means you get an interactive sql> prompt, but with the Python command success means the command exited silently, without printing any error messages.

Then, make sure the certificate (NOT the key!) is available on the client, for example by copy-pasting its contents to a file on the client. Try again, the following three should all work:

$ mclient -d 'monetdbs://dbhost.mymonetdb.org/demo?cert=/tmp/selfsigned.crt'
$ python3 -c 'import pymonetdb; pymonetdb.connect("monetdbs://dbhost.mymonetdb.org/demo?cert=/tmp/selfsigned.crt")'
$ python3 -c 'import pymonetdb; pymonetdb.connect("monetdbs://dbhost.mymonetdb.org/demo", server_cert="/tmp/selfsigned.crt")'

Run stunnel as a daemon

We're still running stunnel in a shell session on the server. We need to run it as a daemon. On Linux with systemd, that can be done as follows. First, stop the current stunnel by pressing Control-C. Then create a file /etc/systemd/system/stunnel-monetdb.service with the following contents:

# /etc/systemd/system/stunnel-monetdb.service

[Unit]
Description=TLS termination for monetdbd
Documentation=man:stunnel4(8)

[Service]
ExecStart=/usr/bin/stunnel /etc/stunnel-monetdb.conf
ExecReload=kill -HUP $MAINPID
PrivateTmp=yes

[Install]
WantedBy=multi-user.target

Then start the daemon and arrange for it to start automatically from now on:

# systemctl enable --now stunnel-monetdb.service

At the end of Section 'Create a self-signed certificate we mentioned creating a cron job or other mechanism to renew the certificate every month or so. The same mechanism must also notify the running stunnel daemon by running systemctl reload stunnel-monetdb.service. This will make stunnel pick up the new certificate without interrupting existing connections, see the ExecReload= line in the config file.

Finally, when everything works correctly it may be a good idea to reduce the amount of logging stunnel does by reducing debug = debug to for example debug = notice or debug = warning.