In this article, we’ll discuss how to configure and setup MySQL database server and client to use SSL TLS server and client certificates so that the communication between them is end-to-end encrypted and secured using Mutual TLS(mTLS) authentication.
Also, we’ll show you how to turn ON both server certificate identity verification and client certificate identity verification in the MySQL configuration, so that you could enforce Zero Trust Security over all DB connection requests originating from end users.
You can use the same procedure to create SSL TLS X.509 server certificates and client certificates to setting up Mutual TLS authentication for any database (Postgresql, MariaDB, MongoDB etc.) and even webservers(Nginx,Apache), workloads(Docker, Kubernetes pods,Service Mesh), web apps etc.
We will use BastionXP CA to issue TLS X.509 server and client certificates to the MySQL server and MySQL client, respectively. BastionXP is a web based PKI/CA that issues certificates over HTTPS requests.
BastionXP CA is very simple and easy to setup. BastionXP is a open source and open standards based CA which is free to download.
Prerequisites:
You’ll need the following artifacts for this tutorial
- MySQL server
- MySQL client
- BastionXP CA
MySQL Database Server and Client Setup:
Let’s assume you already have a MySQL database server installed and running in a server with a domain name db.example.com.
Note: You could use any domain name for your db, including internal domains such as mysqldb.internal.example.com or even localhost for this tutorial.
If you don’t have a MySQL installed already, visit the MySQL official website and follow the instructions to download, install and setup a MySQL database in your server.
Now let’s try connecting to this MySQL database server using a local mysql client on the server.
$ mysql -h db.example.com -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. mysql>
We have successfully connected to the MySQL DB.
Next, let’s create SSL certificates and configure the server and client to use SSL certificates and operate in SSL mode.
Create SSL TLS X.509 Certificates for the MySQL Server and Client
Before you can turn ON the SSL mode or Mutual TLS authentication for your MySQL server and client, you need to create self-signed SSL/TLS X.509 certificates for the MySQL server and MySQL client. For this, please refer to the following tutorial:
BastionXP is a free open-source based SSL TLS X.509 certificate management software to automatically generate, renew and manage SSL X.509 certificates for various applications(web server, web clients, database, web apps, workloads, devices) in your orgnaization.
Once you have created the server and client SSL certificates and the keys, you can move on to the next section.
Configure SSL Certificate for MySQL Server
To configure the MySQL server to start accepting only SSL connections and discard all non-SSL connections, we need to set the require_secure_transport option in the my.cnf file to ON.
We also need to provide the location of the SSL server certificate, SSL private key and the SSL CA certificate files in the server.
The default directory where the MySQL server looks for the certificates and keys is /var/lib/mysql. The default names for the certificates are: server-cert.pem, server-key.pem and ca.pem.
First, copy the SSL TLS certificates generated by BastionXP CA to the default directory and change their ownership to mysql.
$ sudo cp ~/.bsh/tls_server.crt /var/lib/mysql/server-cert.pem
$ sudo cp ~/.bsh/tls_server.key /var/lib/mysql/server-key.pem
$ sudo cp ~/.bsh/tls_root_ca.crt /var/lib/mysql/ca.pem
$ sudo chown msyql:mysql /var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem /var/lib/mysql/ca.pem
Next, add the following configuration to the MySQL server’s configuration file: /etc/my.cnf
... [mysqld] ssl_ca=/var/lib/mysql/ca.pem ssl_cert=/var/lib/mysql/server-key.pem ssl_key=/var/lib/mysql/server-key.pem require_secure_transport=ON ...
Now, restart the mysql server service to pick up the configuration changes.
$ sudo systemctl restart mysql
$ sudo systemctl status mysql
Configure SSL Certificate for MySQL Client
You need to install the MySQL client on your laptop or local machine from which you want to connect to the MySQL database server.
Note: BastionXP CA issues short-lived SSL TLS X.509 client certificates [valid for 8 hours only] by default to the end users or mysql clients. Users must renew their client certificates after the expiry period. BastionXP CA provides granular control over who can access what resource and for how long.
Add the following configuration to the MySQL configuration file in the client machine: /etc/my.cnf
... [client] ssl_mode=VERIFY_IDENTITY ssl_ca=/home/bob/.bsh/tls_root_ca.crt ssl_cert=/home/bob/.bsh/tls_client.crt ssl_key=/home/bob/.bsh/tls_client.key ...
When the ssl_mode is set to VERIFY_IDENTITY, the client will verify if the server certificate was issued by the trusted CA at /home/bob/.bsh/tls_root_ca.crt. The client will also verify the identify of the server certificate by checking if the CN is set to the DB server name db.example.com in the server’s certificate.
This is to ensure the client is indeed connecting to the right MySQL server it intends to connect to.
Now open a new terminal window in your laptop.
Using the MySQL client, you can connect to your MySQL database server as shown below:
$ mysql -h db.example.com -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
We are able to successfully connect to our MySQL database using a client SSL TLS certificate issued by our BastionXP Private CA.
Now, let’s try if we can connect to the MySQL DB in non-SSL mode by setting the flag --ssl-mode=DISABLED as shown below:
$ mysql -h db.example.com -uroot -p --ssl-mode=DISABLED Enter password: ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
The MySQL DB throws an error saying: “Authentication requires secure connection”, meaning the client must connect to the DB using an SSL connection.
Configure MySQL server to require clients to authenticate using a certificate issued by our CA
To configure the MySQL server to use client TLS authentication (mutual TLS and not just one-way TLS), we must instruct it to mandate client certificate authentication to ensure clients present a valid client certificate issued by our CA when they connect.
MySQL requires client certificate based authentication on a per-user basis, meaning each MySQL user needs to be configured to provide a valid client certificate.
The requirement can be configured using CREATE USER or ALTER USER statements. When set, MySQL will reject connections from these users if they don’t present a valid certificate signed by our CA.
mysql> CREATE USER 'bob' REQUIRE SUBJECT 'CN=bob' ISSUER '/CN=ca.example.com'; mysql> ALTER USER 'dave' REQUIRE SUBJECT 'CN=dave' ISSUER '/CN=ca.example.com'; mysql> ALTER USER 'root' REQUIRE SUBJECT 'CN=root' ISSUER '/CN=ca.example.com';
You can use the BastionXP Role Based Access Control feature to define roles and logins for each user, so that the subject, SAN and CN fields of the client certificate are set automatically by the CA.
Conclusion:
Securely connecting to a database is crucial. SSL TLS certificate based Mutual TLS(mTLS) authentication provides two-way authentication between the database server and the client.
In this article, you have learnt how to setup and use BastionXP PKI/CA to issue SSL TLS X.509 CA, server and client certificates.
You also learnt how to securely connect to a MySQL database over an SSL connection by creating and installing SSL server certificate and SSL client certificate in the MySQL server and client.
Finally, you learnt how to configure the MySQL to verify the identity of the server and client specified in the certificate and also the identity of the certificate issuer(CA).
You can use the same procedure to create and configure SSL certificate based mTLS authentication for any database - MySQL, Mariadb, Postgresql, SQLite etc..
If you have any questions, please write to us at: [email protected]