1. Overview
An unencrypted connection between a MySQL server and a client can expose data in transit over the network. For a production-ready application, we should move all communication to a secure connection via TLS (Transport Layer Security) protocol.
In this tutorial, we’ll learn how to enable a secure connection on a MySQL server. Also, we’ll configure the Spring Boot application to use this secure connection.
2. Why Use TLS on MySQL?
First, let’s understand some basics of TLS.
The TLS protocol uses an encryption algorithm to ensure that data received over the network can be trusted and not tampered with or inspected. It has mechanisms to detect data change, loss, or replay attacks. TLS also incorporates algorithms that provide identity verification using the X.509 standard.
An encrypted connection adds a layer of security and makes the data unreadable over the network traffic.
Configuring a secure connection between the MySQL server and client enables better authentication, data integrity, and trustworthiness. Additionally, the MySQL server can perform additional checks on the client’s identity.
However, such a secure connection comes with a performance penalty due to encryption. The severity of the performance cost depends on various factors like query size, data load, server hardware, network bandwidth, and other factors.
MySQL server performs encryption on a per-connection basis, and this can be made mandatory or optional for the given user. MySQL supports SSL encryption-related operations at runtime with the installed OpenSSL library.
We can use the JDBC Driver Connector/J to encrypt the data between the client and server after the initial handshake.
MySQL server v8.0.28 or above supports only TLS v1.2 and TLS v1.3. It no longer supports the earlier versions of TLS (v1 and v1.1).
Server authentication can be enabled using either a certificate signed by a trusted root certificate authority or a self-signed certificate. Also, it’s common practice to build our own root CA file for MySQL, even in production.
Additionally, the server can authenticate and verify the client’s SSL certificate and perform additional checks on the client’s identity.
We’ll enable secure transport on the MySQL server using the property require_secure_transport and the default-generated certificates.
Let’s quickly bootstrap the MySQL Server by implementing the settings in a docker-compose.yml:
version: '3.8'
services:
mysql-service:
image: "mysql/mysql-server:8.0.30"
container_name: mysql-db
command: [ "mysqld",
"--require_secure_transport=ON",
"--default_authentication_plugin=mysql_native_password",
"--general_log=ON" ]
ports:
- "3306:3306"
volumes:
- type: bind
source: ./data
target: /var/lib/mysql
restart: always
environment:
MYSQL_ROOT_HOST: "%"
MYSQL_ROOT_PASSWORD: "Password2022"
MYSQL_DATABASE: test_db
We should note that the above MySQL Server uses the default certificates located in path /var/lib/mysql.
Alternatively, we can override the default certificates by including a few mysqld configs in docker-compose.yml:
command: [ "mysqld",
"--require_secure_transport=ON",
"--ssl-ca=/etc/certs/ca.pem",
"--ssl-cert=/etc/certs/server-cert.pem",
"--ssl-key=/etc/certs/server-key.pem",
....]
Now, let’s start the mysql-service using the docker-compose command:
$ docker-compose -p mysql-server up
3.2. Create a User with X509
Optionally, we can configure the MySQL server with client identification using the X.509 standard. With X509, a valid client certificate is required. This enables the two-way mutual TLS or mTLS.
Let’s create a user with X509 and grant permission on the test_db database:
mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'Password2022' require X509;
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'%';
We can set up a TLS connection without any user certificate identification:
mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'Password2022' require SSL;
We should note that the client is required to provide a truststore if SSL is used.
Spring Boot applications can configure TLS over the JDBC connection by setting the JDBC URL with a few properties.
There are various ways of configuring Spring Boot Application to use TLS with MySQL.
Before that, we’ll need to convert the truststore and client certificates into JKS format.
Let’s convert the MySQL server-generated ca.pem and client-cert.pem files to JKS format:
keytool -importcert -alias MySQLCACert.jks -file ./data/ca.pem \
-keystore ./certs/truststore.jks -storepass mypassword
openssl pkcs12 -export -in ./data/client-cert.pem -inkey ./data/client-key.pem \
-out ./certs/certificate.p12 -name "certificate"
keytool -importkeystore -srckeystore ./certs/certificate.p12 -srcstoretype pkcs12 -destkeystore ./certs/client-cert.jks
We should note that, as of Java 9, the default keystore format is PKCS12.
TLS can be enabled with the sslMode set as PREFERRED, REQUIRED, VERIFY_CA, or VERIFY_IDENTITY.
The PREFERRED mode either uses the secure connection, if the server supports it, or otherwise falls back to an unencrypted connection.
With REQUIRED mode, the client can only use an encrypted connection. Like REQUIRED, VERIFY_CA mode uses a secure connection but additionally validates the server certificates against the configured Certificate Authority (CA) certificates.
The VERIFY_IDENTITY mode does one additional check on the hostname along with certificate validation.
Also, a few Connector/J properties need to be added to the JDBC URL, such as trustCertufucateKeyStoreUrl, trustCertificateKeyStorePassword, clientCertificateKeyStoreUrl, and clientCertificateKeyStorePassword.
Let’s configure the JDBC URL in the application.yml with sslMode set to VERIFY_CA:
spring:
profiles: "dev2"
datasource:
url: >-
jdbc:mysql://localhost:3306/test_db?
sslMode=VERIFY_CA&
trustCertificateKeyStoreUrl=file:/<project-path>/mysql-server/certs/truststore.jks&
trustCertificateKeyStorePassword=mypassword&
clientCertificateKeyStoreUrl=file:/<project-path>/mysql-server/certs/client-cert.jks&
clientCertificateKeyStorePassword=mypassword
username: test_user
password: Password2022
We should note that the deprecated properties equivalent to VERIFY_CA are a combination of useSSL=true and verifyServerCertificate=true.
If the trust certificate files are not provided, we’ll get an error to that effect:
Caused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
at java.base/sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:157) ~[na:na]
at java.base/sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:83) ~[na:na]
at java.base/java.security.cert.CertPathValidator.validate(CertPathValidator.java:309) ~[na:na]
at com.mysql.cj.protocol.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:402) ~[mysql-connector-java-8.0.29.jar:8.0.29]
In case the client certificate is missing, we’ll get a different error:
Caused by: java.sql.SQLException: Access denied for user 'test_user'@'172.20.0.1'
Alternatively, we can set the above configuration as environment variables and include the SSL-related configs as JVM parameters.
Let’s add the TLS and Spring-related configs as environment variables:
export TRUSTSTORE=./mysql-server/certs/truststore.jks
export TRUSTSTORE_PASSWORD=mypassword
export KEYSTORE=./mysql-server/certs/client-cert.jks
export KEYSTORE_PASSWORD=mypassword
export SPRING_DATASOURCE_URL=jdbc:mysql://localhost:3306/test_db?sslMode=VERIFY_CA
export SPRING_DATASOURCE_USERNAME=test_user
export SPRING_DATASOURCE_PASSWORD=Password2022
Then, let’s run the application with the above SSL configurations:
$java -Djavax.net.ssl.keyStore=$KEYSTORE \
-Djavax.net.ssl.keyStorePassword=$KEYSTORE_PASSWORD \
-Djavax.net.ssl.trustStore=$TRUSTSTORE \
-Djavax.net.ssl.trustStorePassword=$TRUSTSTORE_PASSWORD \
-jar ./target/spring-boot-mysql-0.1.0.jar
5. Verify the TLS Connection
Let’s now run the application using any of the above methods and verify the TLS connection.
The TLS connection can be verified using the MySQL server general log or by querying the process and sys admin tables.
Let’s verify the connections using the log file in its default path /var/lib/mysql/:
$ cat /var/lib/mysql/7f44397082d7.log
2022-09-17T13:58:25.887830Z 19 Connect [email protected] on test_db using SSL/TLS
Alternatively, let’s verify the connections used by test_user:
mysql> SELECT process.thd_id,user,db,ssl_version,ssl_cipher FROM sys.processlist process, sys.session_ssl_status session
where process.user='[email protected]'and process.thd_id=session.thread_id;+--------+----------------------+---------+-------------+------------------------+
| thd_id | user | db | ssl_version | ssl_cipher |
+--------+----------------------+---------+-------------+------------------------+
| 167 | [email protected] | test_db | TLSv1.3 | TLS_AES_256_GCM_SHA384 |
| 168 | [email protected] | test_db | TLSv1.3 | TLS_AES_256_GCM_SHA384 |
| 169 | [email protected] | test_db | TLSv1.3 | TLS_AES_256_GCM_SHA384 |
6. Conclusion
In this article, we’ve learned how a TLS connection to MySQL makes the data secure over the network. Also, we’ve seen how to configure the TLS connection on MySQL Server in a Spring Boot Application.
As always, the example code can be found over on GitHub.
