Encrypted Connections

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.

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.

Client perspective

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

Keys and Certificates

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:

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 MonetDB for TLS

Setting up TLS-protected communications with MonetDB consists of four, sometimes five steps:

1. Set up MonetDB as usual, making sure it's only accessible from localhost.

2. Create a private key which will never leave the server.

3. Obtain a certificate linking the private key to the server's host name. Either by self-signing or by involving a Certificate Authority (CA).

4. Install and configure the TLS proxy to accept incoming TLS connections, decrypt them and forward them to the MonetDB instance.

5. 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.