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.
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'
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'"
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.
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!
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")'
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.