Securing MySQL Databases with SSL/TLS.

Many databases were born over 25 years ago, back in the unadulterated times of LAN parties and IRC. SSL was just for banks and sending unencrypted database traffic accounts was just how you did things. When databases use unencrypted connections, it means someone with access to the network could watch all and inspect all database traffic.

Turning on encryption makes the data unreadable, strengthening the connection, as in a zero trust network we assume it’s already been compromised. By default, MySQL is configured to only allow connections from the local system. If you want to connect to a MySQL server from a remote system, it is recommended to secure it with SSL/TLS. You should even assume the possibility that the database host could be compromised and always encrypt all database traffic.

Securing a database is relatively easy, and requires just a few extra steps that we’ve outlined below. For this blog post, I’ve focused on MySQL Community Server , which is using the MySQL Enterprise Server, I would recommend checking out this great guide on secure deployment for MySQL.

There a lot of tutorial available online, but many of them are outdated and don't cover the secure defaults in MySQL 8. This post will cover the basics of securing a MySQL database with TLS using MySql 8 and Ubuntu 22.04.

This blog post has been split into two parts. Part One, this post will dive into TLS for MySQL and Part Two will introduce the concepts of using VERIFY_IDENTITY for clients to secure production certificates using Teleport MachineID.

How to Setup MySQL TLS?

For the first part of this post, we’ll need to install the MySQL Community Server and setup self-signed certificates for TLS.

1. Install MySQL 8 Server on Ubuntu 22.04

We’ll start by installing MySQL, while it’s available via https://dev.mysql.com/downloads/mysql/, but we’ll use apt to install mysql.

# Upgrade packages
sudo apt update
# Install mysql
sudo apt install mysql-server
# Check that it's running
systemctl status mysql.service
# When installing from a package you might get an error when initially setting up the user. 
# to reset the password login as sudo and reset the root password. 
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

2. Configuring MySQL

Before we can start securing the database, we'll use mysql_secure_installation to configure the database with the best practices for securing a MySQL database.

mysql_secure_installation

Note: We don't recommend using the VALIDATE PASSWORD COMPONENT as later on we'll be using Certificates based authentication later and this component doesn't take this into consideration. We highly recommend using a long random password for the root user.

3. Creating a dedicated MySQL User and Grant Privileges

Next we'll create a new dedicated user. The example below creates a new super-user with a password and access to all tables. This isn't recommended, instead it's best to create a limited accounts with the principle of least privilege. The MySQL Documentation provides good outlines for creating accounts.

>  mysql -u root -p
CREATE USER 'admin'@'localhost'
  IDENTIFIED BY 'password';
  GRANT RELOAD,PROCESS
  ON *.*
  TO 'admin'@'localhost';

4. Check MySQL status and SSL/TLS Configuration

At this point MySQL is up and running. The next step is to review the current SSL/TLS Setup. Using SHOW VARIABLES LIKE '%ssl%'; highlights that the current ssl certificate configuration is using three self signed certificates ca.pem, server-cert.pem, server-key.pem.

mysql> SHOW VARIABLES LIKE '%ssl%';
#+-------------------------------------+-----------------+
#| Variable_name                       | Value           |
#+-------------------------------------+-----------------+
#| admin_ssl_ca                        |                 |
#| admin_ssl_capath                    |                 |
#| admin_ssl_cert                      |                 |
#| admin_ssl_cipher                    |                 |
#| admin_ssl_crl                       |                 |
#| admin_ssl_crlpath                   |                 |
#| admin_ssl_key                       |                 |
#| have_openssl                        | YES             |
#| have_ssl                            | YES             |
#| mysqlx_ssl_ca                       |                 |
#| mysqlx_ssl_capath                   |                 |
#| mysqlx_ssl_cert                     |                 |
#| mysqlx_ssl_cipher                   |                 |
#| mysqlx_ssl_crl                      |                 |
#| mysqlx_ssl_crlpath                  |                 |
#| mysqlx_ssl_key                      |                 |
#| performance_schema_show_processlist | OFF             |
#| ssl_ca                              | ca.pem          |
#| ssl_capath                          |                 |
#| ssl_cert                            | server-cert.pem |
#| ssl_cipher                          |                 |
#| ssl_crl                             |                 |
#| ssl_crlpath                         |                 |
#| ssl_fips_mode                       | OFF             |
#| ssl_key                             | server-key.pem  |
#| ssl_session_cache_mode              | ON              |
#| ssl_session_cache_timeout           | 300             |
#+-------------------------------------+-----------------+

MySQLs will use self-signed certificate when starting. You can use OpenSSL to view the details, note that it's valid for 10 years and the Common Name (CN) is MySQL_Server_8.0.29_Auto_Generated_CA_Certificate

openssl x509 -in /var/lib/mysql/server-cert.pem -text -nooutCertificate:
#    Data:
#        Version: 3 (0x2)
#        Serial Number: 2 (0x2)
#        Signature Algorithm: sha256WithRSAEncryption
#        Issuer: CN = MySQL_Server_8.0.29_Auto_Generated_CA_Certificate
#        Validity
#            Not Before: Jul  5 23:31:22 2022 GMT
#            Not After : Jul  2 23:31:22 2032 GMT
#        Subject: CN = MySQL_Server_8.0.29_Auto_Generated_Server_Certificate

Even with the self-signed certificates the default MySQL connect isn't secure. To connect using SSL/TLS you'll need to add --ssl-mode to the MySQL Login mysql -u root -p --ssl-mode=required.

To confirm that the connection is using SSL/TLS verify with \s.

mysql> \s
--------------
mysql  Ver 8.0.29-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:		8
Current database:
Current user:		root@localhost
+ SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
- SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.29-0ubuntu0.22.04.2 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			58 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 134  Flush tables: 3  Open tables: 53  Queries per second avg: 0.103
--------------

5. Optional: Create SSL/TLS Certificates with mysql_ssl_rsa_setup

MySQL 8 will automatically create self-signed certificates for you. If using an earlier version of MySQL you might need to create your own certificate. This can be done using mysql_ssl_rsa_setup

# Creates new self-signed certificates 
mysql_ssl_rsa_setup --uid=mysql --datadir=/var/lib/mysql/

6. Enable Remote SSL Connections on MySQL

By default, MySQL does not allow login from a remote host and allows an unsecured connection. t configure MySQL to allow connection and accept only secure connections.

6.1 Create new remote user.

> mysql
CREATE USER 'developer'@'192.168.0.100/255.255.255.0' IDENTIFIED BY 'yourpassword' REQUIRE SSL;

As of MySQL 8.0.23, a host value specified as an IPv4 address can be written using CIDR notation, such as 198.51.100.44/24.

6.2 Allow remote connections

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Edit and Add the following lines:

[mysqld]
..
require_secure_transport = ON
+ bind-address = 0.0.0.0
- bind-address = 127.0.0.1

Then restart MySQL.

6.3 Restrict remote connections

Using firewalls If you wish to allow remote access to port 3306, make sure to restrict source IP (IP or host from which clients connect) as much as possible. Below is an example of allowing TCP network access to port 3306 only if the connection originates from source CLIENT_IP.

# using iptables
iptables -A INPUT -i eth0 -s CLIENT_IP -p tcp --destination-port 3306 -j ACCEPT

# using ufw
ufw allow from CLIENT_IP to any port 3306
# Using host control feature in database You can also restrict remote connection sources by supplying specific hosts while creating database users.

6.4 Test remote SSL connections

Next, we'll export the self-signed CA to the client. Export the ca.pem from /var/lib/mysql/ca.pem.

MySQL client encrypted connection configuration is similar to server configuration. It requires a CA cert (Certificate authority used to issue server certificate and client certificate) and client certificate-private key pair. You can use --ssl-mode={option} with option being

• VERIFY_CA: Verify Certificate Authority.
• VERIFY_IDENTITY: Verify server hostname. (not possible if using mysql_ssl_rsa_setup) to enable mandatory encrypted connection with additional verification of certificate authority or hostname.

Connect using exported CA.

mysql -h mysql-server-ip -u developer -p --ssl-mode=VERIFY_CA --ssl-ca=/var/lib/mysql/ca.pem

NOTE: Host name identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically by the server or manually using mysql_ssl_rsa_setup.

7. Upgrading SSL Mode to VERIFY_IDENTITY

One problem of using mysql_ssl_rsa_setup and mysql default self-signed certificate is that the server hostname is not verified. This means while the connection is encrypted, the server hostname is not verified. Opening the possibility for MITM attacks for another database using self-signed certs.

To fix this issue on you'll need to issue certificates from a central certificate authority (CA). Depending on where you're hosted you have a few options. For AWS users,

1. AWS Certificate Manager Private Certificate Authority.
   • Pro: A completely managed CA allowing for different CA hierarchies.
   • Con: Start at $400 per month.
2. Leverage Teleport Certificate Authority with MachineID
   • Pro: Open-source Certificate Authority designed for infrastructure access.
   • Con: Expanding Database support, might not support obscure Database.

In Part Two of this blog post we'll explain how Teleport can be used to secure databases using TLS using Teleport Machine ID to auto-renew certificates.