Tech Blog

Using TLS with our analytics database (part 2): secure communication with Exasol

We covered the basics of Transport Layer Security (TLS) in the first part of this article. We’ll now look at how you can use TLS with our Loading...analytics database and will focus on two areas:

  1. Incoming TLS connections to Exasol services like JDBC, BucketFS or EXAOperation
  2. Outgoing connections where Exasol consumes services provided by a third party

Incoming TLS connections

EXAOperation and BucketFS are probably the first touchpoints users have with Exasol. EXAOperation runs on HTTPS (i.e. HTTP + TLS) by default and BucketFS supports both HTTP and HTTPS. And, of course, HTTPS is the only suitable choice unless you’re running development tests.

This diagram shows an incoming TLS connection with EXAOperation as the service and the user’s web browser as the consumer.

The default TLS certificate and why you should replace it

In order to allow incoming connections from the start, Exasol comes pre-loaded with a default server certificate. Once you open a browser connection to that machine, your browser will display a warning that the connection isn’t secure. There are two reasons for this:

  1. The certificate is signed by an Exasol Certification Agency (CA) and your machine doesn’t have that CA’s certificate pre-installed
  2. Exasol can’t predict where the certificate is going to be hosted, so the hostname is left blank in the certificate

The chicken-or-the-egg problem of secure connections

For a truly secure connection, you need to put in place a custom certificate immediately after installing EXAOperation. Until then you’re facing a classic chicken-or-the-egg problem. On the one hand, you need to install your own certificate to establish a trusted TLS connection. On the other, you need to connect to Exasol first in order to do this.

This graphic shows an example of a simple on-premise installation in a single network. The organization’s CA issues a root certificate. IT administrators then place this certificate on all users’ machines in that organization. The same administrators also install the server certificate via EXAOperation.

Exasol TLS certificate installation (on-premise)

You can be reasonably sure with on-premise that it takes a matter of minutes to replace the certificate – and that no one will interfere.

However, if you plan to expose one or more of Exasol’s services to the internet, you need to install a proper certificate first and then open your firewall.

Another way to circumvent the problem is to use Secure Shell (SSH) to add the server certificates. Cloud providers allow you to install your own SSH keys directly with the installation of Loading...virtual machines. This gives you a secure starting point for the TLS certificate setup.

Outgoing connections

Depending on the certificate issuer outgoing TLS connections can either be as easy as pie or a right royal pain.

Services with certificates issued by a popular CA

If an application on your Exasol cluster wants to connect to the public application program interface (API) of a service with a certificate signed by a popular CA – like one of those that come pre-loaded in most truststores – there isn’t much to do. The TLS connection should work out of the box. The TLS client can verify the server certificate by following the certificate chain down to that known root CA.

Services with certificates issued by a custom CA

This is where you need to work a bit harder to get TLS running. There are multiple reasons:

  • If you run an Exasol cluster, all data nodes must know the custom root CA – the certificate must be installed on all nodes
  • User Defined Functions (Loading...UDFs) can’t access the standard truststore as they run in an environment that only gives them a very restricted view of the host’s file system
  • The truststores the UDF container sees are installed with the language containers, so any updates of the language container overwrite any potential truststore changes

Different clients, different truststores

To complicate things further, different clients tend to have their own truststores.

A Linux machine has a central truststore in the /etc directory, typically in form of text files. Loading...Java has its own truststore and file format. So does Loading...Python. At least web browsers now use the truststore that comes with the operating system.

Installing CA certificates on an Exasol cluster

The previous graphic shows the effort needed to install CA certificates for all languages on all cluster nodes. On the plus side you only really need to place the CA certificate in the Java truststore under: /etc/pki

/etc/pki

Installation in other truststores is optional and rarely needed. You’re most likely going to use the outgoing connection from the EXALoader to run IMPORT statements. The Loader is written in Java, so extending the central Java truststore is sufficient to get IMPORT working with TLS.

Installing new TLS certificates on an Exasol cluster

Before you start installing TLS certificates, you need to ask yourself:

  • Do I need TLS connections from UDFs or only from the EXALoader (i.e. for the IMPORT command)?
  • If I need UDFs, what programming languages do I need to support?

Installing certificates so that the EXALoader can use them for imports is fairly straightforward. If the external source should be accessed via JDBC, you need to add your certificates via Java’s keytool on all data nodes in the cluster. The Java truststore is located under 

/etc/pki/ca-trust/extracted/java/cacerts

The trouble starts when you need TLS in a UDF. The UDF truststores currently form an integral part of the language containers. They can and should be modified in situ. That way client applications work without modification or special configuration because the truststore is in its default location inside the running container. But there are also downsides. Firstly, this only applies until a language container update. And secondly, you have to locate the truststore files first – and they have variable path elements unlike the central one.

To find the Java truststore in the language container, run the following command:

updatedblocate --regex 'ScriptLanguages.*/java/cacerts'

Remember, you have to install the certificates on all data nodes.

Reinstalling the certificates after a language container update

Updates of a language container currently overwrite the corresponding truststores. So you need to remember to reinstall all custom certificates after any update.

The future of TLS in UDF containers: shared truststores

To improve handling of TLS certificates in UDF containers, we’re planning to share truststores from the base system with the UDFs. This means you can install certificates in the central truststore and they’ll be available to UDFs too – but not the other way round to prevent security issues.

Summary

We now know how and where TLS comes into play when using Exasol’s analytics database – creating secure TLS connections and how to install the right certificates in the various truststores.

Check the first part of this blog series for the basics of TLS, how it works, how to use it to our advantage and what its limitations and pitfalls are.