Securing the MySQL Connection
  • 15 Mar 2022
  • 5 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Securing the MySQL Connection

  • Dark
    Light
  • PDF

Article Summary

Enabling TLS with MySQL

Modern SQL databases, such as MySQL, its variants, and PostgreSQL, can protect communication between clients, such as Kea, and servers using SSL/TLS. In MySQL, the TLS configuration is split between clients and servers, so to enable TLS, you need to update both the Kea and MySQL server configuration.

Checking the TLS support status of your MySQL installation

Most MySQL installations have TLS enabled during compilation time. However, some deployments may have it disabled. If your MySQL was compiled without TLS support, recompilation or migration to a more modern MySQL installation is necessary. An excellent resource is Secure Connections Overview by MariaDB, which explains how to check if the installation has TLS support. You can check the status with the following query:

SHOW GLOBAL VARIABLES LIKE 'have_ssl';

The interpretation of the result is:

  • If the result is NO, then the server was not compiled with TLS support. You need to either recompile or install a different version.
  • If the result is DISABLED, then the server was compiled with TLS support, but TLS is not configured yet. You need to tweak your MySQL configuration.
  • If the result is YES, then the server was compiled with TLS support, and TLS is enabled. Your MySQL should be ready to accept TLS connections from Kea.

There is good documentation about enabling TLS for MariaDB. Note that TLS support depends on the crypto library (OpenSSL in most cases) capabilities. It may be necessary to update the OpenSSL library.

Enable TLS support in MariaDB

To enable TLS support in MariaDB, you need a server certificate, a private key, and a certificate from the Certificate Authority (CA) that issued them. How you get them is very deployment-specific. Larger organizations may have their own CA. A good example is described in Certificate Creation with OpenSSL; it explains how to generate everything on your own if you don't have a 'real' CA to issue the certificates for you.

Next, you should update the MySQL configuration. Again, this is very deployment-specific, but the configuration files are typically stored in /etc/mysql/. Add the following entries there:

[mysqld]
ssl_cert = /etc/mysql/tls/server-cert.pem
ssl_key = /etc/mysql/tls/server-key.pem
ssl_ca = /etc/mysql/tls/ca.pem

Care should be taken so the MySQL process is able to read the files. Note that server-key.pem (and all other private keys) are generated by OpenSSL with 600 permissions, meaning only the owner of the file is able to read them. Usually, the following command will solve the problem:

sudo chown mysql.mysql /etc/mysql/tls/*.pem

Enabling TLS support on Kea

To enable TLS support in Kea, you need a CA certificate (which in most cases will be the same used for the server), Kea's secret key, and Kea's certificate. One way to self-generate a CSR (Certificate Signing Request) is via this command:

openssl req -newkey rsa:2048 -days 365000 -nodes -keyout kea-key.pem -out kea-req.pem

And then use the CA's certificate and the CA's secret key to generate a certificate for Kea:

openssl x509 -req -in kea-req.pem -days 365000 -CA /etc/mysql/tls/ca.pem -CAkey /etc/mysql/tls/ca-key.pem -set_serial 01 -out kea-cert.pem

As a result, you should have kea-cert.pem (Kea certificate), kea-key.pem (Kea secret key), and kea-req.pem (Kea signing request, no longer needed). You can update your Kea configuration file to point to those files. You typically want to update the lease-database, hosts-database, and possibly config-databases. An example lease database configuration could look like this:

  "lease-database": {
    "type": "mysql",
    "name": "keatest",
    "user": "keatest",
    "password": "secret1",
    "host": "localhost",
    "port": 3306,
    "trust-anchor": "/etc/mysql/tls/ca.pem",
    "cert-file": "/etc/kea/tls/kea-cert.pem",
    "key-file": "/etc/kea/tls/kea-key.pem"
  },

When you start Kea, you should see logs similar to this:

$ kea-dhcp4 -c mysql-ssl.json 
2022-03-14 19:28:53.787 INFO  [kea-dhcp4.dhcp4/527862.140606264556544] DHCP4_STARTING Kea DHCPv4 server version 2.1.4-git (development) starting
...
2022-03-14 19:28:53.790 INFO  [kea-dhcp4.dhcpsrv/527862.140606264556544] DHCPSRV_MYSQL_DB opening MySQL lease database: cert-file=kea-cert.pem host=localhost key-file=kea-key.pem max-reconnect-tries=3 name=keatest password=***** port=3306 reconnect-wait-time=3000 trust-anchor=/etc/mysql/tls/ca.pem type=mysql universe=4 user=keatest
...

An example configuration file for Kea DHCPv4 is provided here in .txt and here in JSON format. The DHCPv6 database configuration is identical.

Optional hardening

The TLS protocol allows several modes of operation. Server authentication is a model that is frequently used by bank websites. The client (web browser) checks whether the server (the bank) is legitimate, but the server won't verify the client on the TLS level. Typically that verification is done with other means, such as user credentials and 2FA. This is less secure.

The more secure, but harder to deploy, alternative is to have mutual authentication, or Client Certificate Verification as it's called by the MariaDB documentation. We recommend mutual authentication, so clients validate the server certificate and the server validates client certificates. You can go further, to check the client identity to protect against an authorized client faking another one.

Server authentication alone does not protect against MITM attacks

If you use server authentication, your traffic is encrypted and therefore protected against eavesdropping. This is adequate protection against offline attacks, but without client certificate verification, this does not protect against inline attacks (MITM or Man-in-the-Middle attacks).

Certification Revocation Lists

Certificate Revocation Lists (CRLs) and similar features are complex to administer without a clear benefit. However, you can still use them, but only in the MySQL configuration.

The optional peer certificate validation is the only point where you can get differences between MySQL variants. If you want to enable this, please consult your MySQL or MariaDB documentation.

It should be possible to designate which certificates should be used to access specific databases, although this has not been tested with Kea.

MySQL user configuration

When creating or altering MySQL users, you should restrain or allow users from connecting using clauses such as "REQUIRE SUBJECT", which correspond to TLS certificate fields.


Example Configuration

This is an example configuration file for the DHCPv4 server in Kea. It contains a configuration for MySQL lease and host backends with TLS enabled.

{ "Dhcp4":

{
  // Kea is told to listen on eth0 interface only.
  "interfaces-config": {
    "interfaces": [ "eno2" ]
  },

  // This specifies how to access the lease backend. Please use more
  // meaningful credentials in your actual deployments.
  "lease-database": {
    "type": "mysql",
    "name": "keatest",
    "user": "keatest",
    "password": "keatest",
    "host": "localhost",
    "port": 3306,
    "trust-anchor": "/etc/mysql/tls/ca.pem",
    "cert-file": "kea-cert.pem",
    "key-file": "kea-key.pem"
  },


// Specify connection to the host backend that holds the host reservations.
// Note that Kea configures each backend separately, so in principle it's
// possible to store leases and hosts (and config data using config backend)
// in different databases. However, typical deployments use the same backend
// for everything.
  "hosts-database": {
    "type": "mysql",
    "name": "keatest",
    "user": "keatest",
    "password": "keatest",
    "host": "localhost",
    "port": 3306,
    "trust-anchor": "/etc/mysql/tls/ca.pem",
    "cert-file": "kea-cert.pem",
    "key-file": "kea-key.pem"
  },

// This is a just a basic configuration example.
  "subnet4": [
    {
       "pools": [ { "pool":  "192.0.2.10 - 192.0.2.200" } ],
       "subnet": "192.0.2.0/24",
       "interface": "eno2",
       "id": 1
    }
  ],

// The following configures logging. It assumes that messages with at
// least informational level (info, warn, error and fatal) should be
// logged to stdout.
    "loggers": [
        {
            "name": "kea-dhcp4",
            "output_options": [
                {
                    "output": "/var/log/kea-dhcp4.log"
                }
            ],
            "severity": "INFO"
        }
    ]
}

}