In this section we will show how to set up TLS with an external Certificate Authority (CA).
A Certificate Authority will only issue you a certificate for a domain it is convinced that you are the rightful owner of that domain. How to go about proving this varies between CA's. In the current example we will use Let's Encrypt, a non-profit CA that provides server certificates free of charge. Certificates signed by Let's Encrypt are automatically trusted by all major operating system and thus require no further configuration on the client side.
Let's Encrypt offers more than one way of proving ownership:
Put a CA-provided challenge at a specific place on the web server.
Put a CA-provided challenge in a DNS record corresponding to the target domain.
Receive a CA-provided challenge at (hopefully) an administrator-controlled email address corresponding to the domain, and then respond to it on the CA's web page.
Option 1. is the most often used and easy to explain option so we will go with that. This requires us to run a web server on the MonetDB server host. If that is a problem, refer to Let's Encrypt's documentation for information on the other methods.
We'll assume we have separate client and server hosts, otherwise it does not
make sense to configure TLS. We will 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. 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'"
Certbot is a tool provided by Let's Encrypt. It takes care of requesting the certificate and the proof-of-ownership mentioned above. It will also automatically renew the certificates when their expiration date approaches.
First install Certbot and — if you don't have one yet — a web server. On Debian/Ubuntu that would look like this:
$ sudo apt install certbot nginx
On Red Hat/Fedora it would look like
$ sudo yum install certbot nginx
$ sudo dnf install certbot nginx
Now, before you move on, first verify that port 80 (http) of the server is reachable from
outside. Open dbhost.mymonetdb.org in your web browser and see it render a dummy page.
The next step is to request the certificate. As part of this process, Certbot will place
a special file on our dummy website, so it needs to know where the web server stores its
files. On Debian that would be /var/www/html/, on Red Hat /usr/share/nginx/html.
$ sudo certbot certonly --webroot --webroot-path=/var/www/html -d dbhost.mymonetdb.org
This command will ask some easy questions and then contact Let's Encrypt to request the certificate. If all goes well, it will say something like
$ sudo certbot certonly --webroot --webroot-path=/var/www/html -d dbhost.mymonetdb.org
.. .. ..
Successfully received certificate.
Certificate is saved at: /etc/letsencrypt/live/dbhost.mymonetdb.org/fullchain.pem
Key is saved at: /etc/letsencrypt/live/dbhost.mymonetdb.org/privkey.pem
This certificate expires on 2024-04-15.
These files will be updated when the certificate renews.
Certbot has set up a scheduled task to automatically renew this certificate in the background.
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 sudo yum install stunnel or sudo dnf install stunnel.
Then write the following config file somewhere, for example in
/etc/stunnel-monetdb.conf. Make sure the cert= and key= settings include
the right host name.
# /etc/stunnel-monetdb.conf
foreground = yes
# comment this out once you have everything working
debug = debug
cert=/etc/letsencrypt/live/dbhost.mymonetdb.org/fullchain.pem
key=/etc/letsencrypt/live/dbhost.mymonetdb.org/privkey.pem
[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'
+------+
| %2 |
+======+
| 42 |
+------+
1 tuple
This should just work.
We will now test on the client host 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 identical to the test we ran on the server:
$ mclient -d monetdbs://dbhost.mymonetdb.org/demo -s 'select 42'
+------+
| %2 |
+======+
| 42 |
+------+
1 tuple
or, with Python:
$ python3 -c 'import pymonetdb; pymonetdb.connect("monetdbs://dbhost.mymonetdb.org/demo")'
$
If this hangs, it's probably a network issue. If the command immediately returned to the command line without an error message, it worked!
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
When Certbot automatically renews the certificate, stunnel needs to be notified
so it can pick up the renewed certificate. We can arrange this using a
post-renewal hook. Create the file /etc/letsencrypt/renewal-hooks/post/100-reload-stunnel
with the following contents:
#!/bin/sh
systemctl reload stunnel-monetdb.service
Make it executable by running chmod +x /etc/letsencrypt/renewal-hooks/post/100-reload-stunnel.
When certbot renews the certificate it will now run this script and the script
will notify stunnel that the certificate has changed
It's a good idea to test this, but keep in mind that Let's Encrypt will only allow you to force-renew your certificate about five times per 24 hours. This means you should double-check everything and then run
$ sudo certbot renew --force-renewal
This should succeed without error messages. Check the stunnel log to see that it reloaded:
$ journalctl -u stunnel-monetdb.service -g reload
-- Boot 416f34f28c7143ea9fc6f3d2af76edbc --
.. .. ..
Jan 16 11:06:56 ip-172-31-23-6 systemd[1]: Reloading stunnel-monetdb.service - TLS termination for monetdbd...
Jan 16 11:06:56 ip-172-31-23-6 stunnel[412]: 2024.01.16 11:06:56 LOG7[ui]: Processing SIGNAL_RELOAD_CONFIG
Jan 16 11:06:56 ip-172-31-23-6 stunnel[412]: LOG7[ui]: Processing SIGNAL_RELOAD_CONFIG
Jan 16 11:06:56 ip-172-31-23-6 systemd[1]: Reloaded stunnel-monetdb.service - TLS termination for monetdbd.
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.