Dolt is the world’s first version-controlled relational database. Dolt lets you branch, fork, clone, merge, and diff your relational data, in all the same ways Git lets you work with your files.

We previously announced support for mutual TLS authentication in Dolt. Mutual TLS enables both sides of a connection to verify the identity of the other side, through the use of certificates. In a TLS connection, the server always provides a certificate, and mutual TLS extends this, so that the client also provides a certificate for the server to verify. This results in an increased security posture and prevents man-in-the-middle attacks. Mutual TLS, and certificate verification more generally, is particularly important in zero trust architectures.

Dolt implements the same user management connection security features from MySQL for requiring a client certificate for a specific user. But what if you want to require ALL connections to present a client certificate, without having to make sure that new user accounts are always configured properly? To completely prevent any connections that don’t present a client certificate, Dolt now provides a listener.require_client_cert configuration setting that you can enable. This option can be used on its own, or can be combined with connection security settings on user accounts to ensure that no users can connect without a certificate, even if a user account doesn’t have that constraint configured.

Configuration#

To enable this new mode, you’ll need to set a few settings in your config.yaml file. The main setting is listener.require_client_cert, but you’ll also need to set a few other settings so that your server can establish TLS connections and can verify client certificates against a CA (Certificate Authority) cert.

Here’s an example of a minimal setup:

log_level: debug
listener:
  require_client_cert: true
  ca_cert:   /path/to/my/certs/ca.pem
  tls_cert:  /path/to/my/certs/server-cert.pem
  tls_key:   /path/to/my/certs/server-key.pem

Let’s break down each of the settings we need to configure:

• require_client_cert – setting this value to true will require all client connections to present a certificate. To use a certificate, the server must be able to establish TLS connections, so tls_cert and tls_key must be provided. You’ll also need to provide ca_cert, since a server can’t verify a client certificate without a CA cert being configured. Any client that tries to authenticate without presenting a client certificate will be rejected.
• ca_cert – set this value to the file system path to your Certificate Authority (CA) cert. This is the cert that is used to verify client certificates. When a client connects, it sends its cert, and the server verifies that the client’s cert was created from the server’s configured CA cert. If the cert’s chain of trust does not terminate with the server’s CA cert, then the connection is rejected.
• tls_cert – this is the certificate the server uses when establishing a TLS connection with a client. It is a best practice to also configure the client with a CA cert, just like we did for the server, so that the client can also verify the server’s identity.
• tls_key – this is the private key associated with the tls_cert described above. This private key is only used briefly during the connection phase, but is very important. It allows the client to prove to the server that the public certificate it sent really belongs to the client.

Dolt Demo#

Let’s walk through a complete example to see how we configure a Dolt SQL server to always require a client certificate and what happens when a client tries to connect to a Dolt SQL server without presenting a certificate.

Certificate and Key Generation#

The first thing we need to do is get some certificates and private keys that we can use to configure our server and our client. If you read our blog post on Mutual TLS authentication, these are the same steps, since we need the same certs and keys. We’re creating self-signed certificates for this demo using openssl, but in a production setup, you’ll want to work with your security team or network administrator to find the right way your organization handles certificates and private keys. We need to create a CA cert that is shared across the client and server, a cert and private key for the server, and a cert and private key for the client.

First we create the CA key and certificate:

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 365 \
    -key ca-key.pem -out ca.pem \
    -subj "/C=US/ST=Washington/L=Seattle/O=Mutual TLS Demo/CN=Demo CA"

Next we use the CA cert to create a server key and cert:

openssl genrsa 2048 > server-key.pem

openssl req -new -key server-key.pem -out server-req.pem \
    -subj "/C=US/ST=Washington/L=Seattle/O=Mutual TLS Demo/CN=Server"

openssl x509 -req -in server-req.pem -days 365 \
    -CA ca.pem -CAkey ca-key.pem -set_serial 01 \
    -out server-cert.pem

And last, but not least, we use the CA cert to create a client key and cert:

openssl genrsa 2048 > client-key.pem

openssl req -new -key client-key.pem -out client-req.pem \
    -subj "/C=US/ST=Washington/L=Seattle/O=Mutual TLS Demo/CN=Client1"

openssl x509 -req -in client-req.pem -days 365 \
    -CA ca.pem -CAkey ca-key.pem -set_serial 02 \
    -out client-cert.pem

Once you’ve created all the certs and private keys, copy the following files to a known location on your computer where you can reference them later. I’m moving them to /opt/certs on my computer, but you can use any location you want.

cp ca.pem server-cert.pem server-key.pem client-cert.pem client-key.pem /opt/certs/

Server Configuration#

Now that we’ve got our certs and private keys ready, we can configure our Dolt SQL server to always require clients present a certificate. The first certificate is the CA certificate, or Certificate Authority certificate. This certificate is used to verify all client certificates. If a client certificate’s chain of trust does not terminate with this CA cert, then the connection is rejected. The second certificate and the private key are used to enable TLS on our server. Last, but not least, we also need to set the require_client_cert property to true.

Here’s the config.yaml file I created:

log_level: info
listener:
  require_client_cert: true
  ca_cert:   /opt/certs/ca.pem
  tls_cert:  /opt/certs/server-cert.pem
  tls_key:   /opt/certs/server-key.pem

Next, we start up the SQL server with this configuration by passing a path to our config.yaml file through the --config parameter:

dolt sql-server --config config.yaml

If the server starts up correctly, you should see output like this:

Starting server with Config HP="0.0.0.0:3306"|T="28800000"|R="false"|L="info"
INFO[0000] Creating root@localhost superuser            
INFO[0000] Server ready. Accepting connections.         
WARN[0000] secure_file_priv is set to "", which is insecure. 
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read. 
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory. 

Client Usage#

Now that our server is configured to require all clients to present a certificate when connecting, let’s try connecting with a client and see how it works.

First, let’s see what happens when we try to connect to the server without specifying a client certificate:

mysql -uroot --protocol TCP -e "SELECT USER();" 

When we run this, the connection is rejected and the mysql client returns a rather cryptic error showing us that authentication failed:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 100

Authentication error messages from servers generally tend to be opaque to avoid leaking information about user accounts, since any details could potentially be used by an attacker trying to gain access to the system.

Here’s what the command looks like when we supply a client certificate:

mysql -uroot --protocol TCP -e "SELECT USER();" \
    --ssl-mode=VERIFY_CA \
    --ssl-ca=/opt/certs/ca.pem \
    --ssl-cert=/opt/certs/client-cert.pem \
    --ssl-key=/opt/certs/client-key.pem

When we run this, the mysql client is now able to connect and execute the SELECT USER() statement and we see these results returned:

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

Let’s walk through each of the options we provide to mysql and explain what each one does and why we need to supply it:

• ssl-mode – The mysql client supports several SSL/TLS modes. By default (PREFERRED), the client will attempt an encrypted connection but fall back to an unencrypted one if TLS is not available. Specifying VERIFY_CA requires that the connection use TLS and that the server’s certificate be verified against the CA provided via ssl-ca. If the server cannot negotiate TLS or the certificate cannot be verified by the client, the connection fails. It’s critical to specify VERIFY_CA as the SSL mode so that the client verifies the server’s certificate and ensures that it has been issued from the CA cert the client specified. Without this, the client cannot truly trust the server’s identity.
• ssl-ca – This is the CA certificate used by the client to validate the server’s certificate during the handshake, as described above. The client never sends this certificate over the network. Clients can connect without verifying the server’s identity, but without this step the client has no proof of who it is talking to and therefore no real mutual trust.
• ssl-cert – This is the client’s certificate. It is transmitted to the server as part of the mutual TLS handshake. The server validates that this certificate chains to a CA it trusts (configured in the server’s own ssl_ca setting). The client typically sends its full certificate chain.
• ssl-key – This is the client’s private key. It never leaves the client machine. During the handshake, it is used to sign specific TLS messages so the server can verify that the client possesses the private key that corresponds to the certificate it presented. After this proof-of-possession step, the private key is not used for encrypting data; all subsequent data encryption uses symmetric session keys negotiated by the TLS protocol.

A Note on dolt sql Usage#

Normally, when you run dolt sql, the dolt CLI will interact directly with the database files on disk. However, when a Dolt SQL server is running, using dolt sql in the same database directory causes Dolt to connect to the running SQL server over a network connection. This is done because it isn’t safe for multiple processes to work directly with the same database files on disk. It’s important to note that when you have listener.require_client_cert enabled, dolt sql won’t work because it doesn’t have a valid client certificate to use when connecting to your running Dolt SQL server that requires client certificates.

Summary#

Dolt supports a new configuration option, listener.require_client_cert, that requires all connections to present a client certificate, otherwise the connection will be rejected. This compliments Dolt’s support for mutual TLS authentication by ensuring no client can connect to the server without supplying a client certificate. This new option, and the related support for mutual TLS, allow you to further increase the security posture of your system by requiring clients to present certificates and verifying those certificates against the server’s CA cert.

If you’re curious about Dolt and want to talk more about version-controlled databases or authentication security, come by our Discord to talk to our engineering team and meet other Dolt users.