By default, the communication between MonetDB client and server is unencrypted. This is not a problem if they are running on the same host or in a trusted private network, but it is unsafe if the communication takes places across an untrusted network such as the open Internet. In recent versions of MonetDB and some of its client libraries it is possible to encrypt connections using the TLS (Transport Layer Security) protocol, formerly known as SSL. This is the same mechanism that is used to secure web connections (HTTPS).
The use of TLS prevents (a) eavesdropping, where an attacker captures confidential information as it crosses the network, and (b) tampering, where the attacker alters data or commands, or pretends to be a legitimate server or client.
On the server side, MonetDB itself does not implement TLS. Instead, a TLS termination proxy must be used. In other words: the client, extended with TLS support, makes a TLS connection to the proxy which authenticates and decrypts it to be forwarded to the unmodified MonetDB server process.
The primary reason for this arrangement is that it is best practice to make
TLS secret keys only readable by root, and monetdbd
and mserver5
are not suitable to
run as root. It is better to move the responsibilities of dealing with root privileges
and secret keys to a separate process and it turns out there are several
off-the-shelve packages that do exactly that. In the examples we will use stunnel but
any TLS termination proxy will suffice.
On the client side, mclient and libmapi have been extended to allow connecting to a TLS protected server, as have several other client libraries. See the table below for version information.
Client library | TLS supported since |
---|---|
mclient / libmapi | Dec2023 |
pymonetdb | 1.8.0 |
monetdb-java | 3.4 (soon to be released) |
On the current page, we will first describe the client-side perspective: how to get mclient, pymonetdb, monetdb-java JDBC, etc to establish a TLS connection once the server has been set up. Then we'll very briefly recap the fundamentals of Transport Layer Security to establish the terminology used when describing the concrete examples. Finally, on separate pages we'll give step by step examples how to set up a TLS-protected MonetDB server.
With mclient, making a secure connection then looks like this:
$ mclient -d monetdbs://dbhost.mymonetdb.org:50000/demo
Note that instead of just a database name, we pass a monetdbs://
URL after the -d
.
With pymonetdb, it looks like this:
import pymonetdb
conn = pymonetdb.connect('monetdbs://dbhost.mymonetdb.org/demo')
# alternatively,
conn = pymonetdb.connect('demo', host='dbhost.mymonetdb.org', use_tls=True)
With JDBC,
public static void main(String[] args) throws SQLException {
String url = "jdbc:monetdbs://dbhost.mymonetdb.org/demo";
String user = "monetdb";
String password = "monetdb";
Connection conn = DriverManager.getConnection(url, user, password);
}
If the server uses a self-signed certificate (see below), the certificate must be copied to the client host and its path must be passed to the MonetDB driver. This can be done in the URL as follows:
monetdbs://dbhost.mymonetdb.org/demo?cert=/path/to/certificate.crt
With pymonetdb, the path to the certificate can also be passed as a separate parameter:
pymonetdb.connect('demo', server_cert='/path/to')
With JDBC, it can be passed through the Properties parameter
to DriverManager#getConnection
:
public static void main(String[] args) throws SQLException {
Properties props = new Properties();
props.put("user", "monetdb");
props.put("password", "monetdb");
props.put("cert", "/path/to/certificate.crt");
String url = "jdbc:monetdbs://dbhost.mymonetdb.org/demo";
Connection connection = DriverManager.getConnection(url, props);
}
Note: mclient and pymonetdb allow backslashes in the path to the certificate:
cert=C:\path\to\server.crt
, but JDBC does not. With JDBC, either
percent-escape them or replace them with slashes:
cert=C:%5Cpath%5Cto%5Cserver.crt
cert=C:/path/to/server.crt
When setting up a TLS-protected server we need two things. First, we need a private key, also called secret key, which exists on our server alone. Because our server is the sole owner, the private key can be used to identify the server. Second, if the server is running on for example dbhost.mymonetdb.org, we need to obtain a certificate that states that the holder of our private key is authorized to act on behalve of dbhost.mymonetdb.org.
When a client makes a TLS connection, the first thing the server does is to send the client proof of the private key it owns. TLS is designed in such a way that it can do this without sending the private key itself. It also sends a certificate that links the private key to the host name. The client has to examine the proof and the technical details of the certificate. When these have been verified, the client has to decide whether or not to believe the certificate. This is an important step because as we will see, everybody can generate a private key and a certificate that links this private key to dbhost.mymonetdb.org.
There are two ways for the client to trust the certificate presented by the server: (a) the client has a copy of the certificate and is configured to trust it; (b) a third party called a Certificate Authority (CA) has provided a second certificate which says the first certificate is valid, and the client somehow trusts that CA. The latter gives rise to chain of certificates, each certifying the previous one:
Certificate | Signed By Private Key | States That |
---|---|---|
Cert1, the certificate we got from CA1 | CA1's private key | Our private key is valid for dbhost.mymonetdb.org |
Cert2, the certificate CA1 once got from CA2 | CA2's private key | CA1's private key is valid to sign such certificates |
Cert3, the certificate CA2 once got from CA3 | CA3's private key | CA2's private key is valid to sign such certificates |
SelfCert3, a certificate CA3 once signed for itself | CA3's private key (!) | According to CA3 itself, CA3 is very trustworthy |
All operating systems and web browsers have a built-in list of 100–200 trust anchors, which are self-signed certificates such as SelfCert3 above which the manufacturer deems sufficiently trustworthy. If the client trusts SelfCert3, it can trust Cert3 so it can trust Cert2 so it can trust Cert1 so it can trust that our private key belongs to dbhost.mymonetdb.org.
Large organizations often have their own internal CA, whose root certificate is added to the trust store of all computers running inside that organization.
It's also possible to skip the CA's altogether and simply create a self-signed certificate that links the server's private key to the server's host name. In that case, all clients must be manually configured to trust this self-signed certificate.
Setting up TLS-protected communications with MonetDB consists of four, sometimes five steps:
Set up MonetDB as usual, making sure it's only accessible from localhost.
Create a private key which will never leave the server.
Obtain a certificate linking the private key to the server's host name. Either by self-signing or by involving a Certificate Authority (CA).
Install and configure the TLS proxy to accept incoming TLS connections, decrypt them and forward them to the MonetDB instance.
If the certificate from Step 3 is not backed by a well known CA, manually configure all clients to trust it.
We provide two worked explanations of how to go about this. Both use stunnel as the TLS proxy, they differ only in how they obtain and manage the certificates.
In the first example we self-sign our certificate.
In the second example we obtain our certificate from Let's Encrypt, a non-profit CA that provides this service for free.