TLS setup in MySQL and Spring Boot application

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 must move all communications over a secure connection via the TLS (Transport Layer Security) protocol.

In this tutorial, we will learn how to enable secure connection on MySQL server. Also, we will configure the Spring Boot application to use this secure connection.

2. Why use TLS over MySQL?

First, let’s understand some of the basics of TLS.

The TLS protocol uses an encryption algorithm to ensure that data received over the network can be trusted and cannot be tampered with or tampered with. It has mechanisms to detect data changes, loss, or replay attacks. TLS also includes algorithms that provide identity verification using the X.509 standard.

An encrypted connection adds a layer of security and makes data unreadable on network traffic.

Configuring a secure connection between the MySQL server and the client enables better authentication, data integrity, and trustworthiness. Additionally, the MySQL server may perform additional checks on the identity of the client.

However, such a secure connection comes with a performance penalty due to the encryption. The severity of performance cost depends on various factors such as query size, data load, server hardware, network bandwidth and other factors.

3. Configure TLS Connection to MySQL Server

The MySQL server performs encryption on a per-connection basis, and can be made mandatory or optional for a given user. MySQL supports SSL encryption-related operations at runtime with the OpenSSL library installed.

We can use JDBC Driver Connector /J to encrypt data between 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 older versions of TLS (v1 and v1.1).

Server authentication can be enabled Using a certificate signed by a trusted root certificate authority or a self-signed certificate, Besides, it is common Create Your Own Root CA File for MySQLin production as well.

Additionally, the server can authenticate and verify the client’s SSL certificate and perform additional checks on the client’s identity.

3.1. Configure MySQL Server with TLS Certificates

We will enable secure transport on the MySQL server Property require_secure_transport and default-generated certificates,

Let’s quickly bootstrap the MySQL server by applying the settings 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 uses the default certificates located in the server path /var/lib/mysql,

Alternatively, we can override the default certificates by including some mysqld configuration 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 begin mysql-service using the docker-compose command:

$ docker-compose -p mysql-server up

3.2. create a user with X509

Alternatively, we can configure the MySQL server with the client identity using the X.509 standard. with X509, A valid client certificate is required. It enables two-way mutual TLS or MTLS,

Let’s create a user with X509 and allow 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 identity:

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'Password2022' require SSL;

we should note that Client needs to provide a truststore if SSL is used,

4. Configure TLS on Spring Boot Application

Spring Boot applications can configure TLS over JDBC connections by setting the JDBC URL with certain properties.

There are several ways to configure a Spring Boot application to use TLS. MySQL,

Before that, we need to change the truststore and client certificates to JKS draft.

4.1. Convert PEM file to JKS format

Let’s convert the MySQL server-generated as a pem And client-certificate.pem files to JKS draft:

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,

4.2. configure using application.yml

TLS can be enabled with ssl mode set as favorite, Necessary, VERIFY_CAeither VERIFY_IDENTITY,

favorite The mode either uses a secure connection, if the server supports it, or otherwise falls back to an unencrypted connection.

with required mode, the client can only use encrypted connections. to like Necessary, VERIFY_CA The mode uses a secure connection but additionally validates server certificates against configured Certificate Authority (CA) certificates.

VERIFY_IDENTITY The mode performs an additional check on the hostname with certificate verification.

In addition, some connector/J The properties need to be added to the JDBC URL, such as TrustCertufucatekeystoreurl, trustcertificatekeystorepassword, ClientCertificateKeyStoreUrlAnd clientcertificatekeystorepassword,

Let’s configure the JDBC URL in application.yml with ssl mode 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 are equal to VERIFY_CA are a combination of use ssl=true And VerifyServerCertificate=true,

If the trust certificate files are not provided, we will receive an error to the effect of:

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]

If the client certificate is missing, we will get a different error:

Caused by: java.sql.SQLException: Access denied for user 'test_user'@'172.20.0.1'

4.3. Configure TLS using environment variables

Alternatively, we can set the above configuration as an environment variable and include the SSL-related configuration as a JVM parameter.

Let’s add the configuration related to TLS and Spring 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, run the application with the above SSL configuration:

$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 TLS Connection

Let us 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 Common Log or by querying process And sys admin tables.

Let’s verify the connection in its default path using the log file /var/lib/mysql/,

$ cat /var/lib/mysql/7f44397082d7.log
2022-09-17T13:58:25.887830Z        19 Connect   test_user@172.22.0.1 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='test_user@172.20.0.1'and process.thd_id=session.thread_id;+--------+----------------------+---------+-------------+------------------------+
| thd_id | user                 | db      | ssl_version | ssl_cipher             |
+--------+----------------------+---------+-------------+------------------------+
|    167 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |
|    168 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |
|    169 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |

6. conclusion

In this article, we have learned how a TLS connection to MySQL makes data secure over the network. Also, we saw how to configure TLS connection to MySQL server in Spring Boot application.

As always, example code can be found on GitHub.

       

Leave a Comment