Data in transit encryption with MariaDB 10.6 (WolfSSL)

Posted on , updated on by Yannick Jaquier
MariaDB

Table of contents

Preamble

How to implement data in transit encryption between your MariaDB server and your clients ? I thought it would be easy to implement but I have discovered that there is a difference whether you use statically or dynamically linked MariaDB version. Dynamically linked MariaDB version (RPM version) mainly use openSSL library coming directly from the OS.

This allow to quickly patch your system without touching MariaDB when you have a big threat like the Heartbleed Bug.

I’m not a fan of those RPM/Deb dynamically linked version because you cannot choose in which directory the binaries will land. And if you want to have multiple (minor) version running on the same server this is a main issue !

On top of this the community edition of MariaDB only come in tarball and so are statically linked with a SSL library.

In itself this is not an issue and starting with MariaDB 10.4.6 WolfSSL library is used and when using TLS v1.2 or higher you need a v3 certificate as explained in this MariaDB note. Unfortunately the how-to has not yet been written at the time of writing this blog post and we have opened a call to MariaDB to get the steps on how to do it.

I have decided to create this post as I’m using different commands than what MariaDB shared with us. Mainly because the OpenSSL commands can do multiple steps in one and few options are completely changing what the command is doing…

My test server is a bare metal server with 12 cores and 64 GB of memory and MariaDB Community 10.6.7.

Data in transit encryption certificates generation

Root Certificate Authority

Generate a RSA root Certificate Authority (CA) private key. The numbits (4096) is totally up to you, default is 2048:

In the home of my Linux mariadb account I have decided to create a dedicated certs directory and generate everything inside:

[mariadb@server01 ~]$ mkdir certs
[mariadb@server01 ~]$ cd certs
[mariadb@server01 certs]$ openssl genrsa -out ca.key 4096
Generating RSA private key, 4096 bit long modulus
....................................................................++
........................................................++
e is 65537 (0x10001)

Create a self-signed certificate to use as a root CA:

[mariadb@server01 certs]$ openssl req -x509 -new -nodes -key ca.key -days 365 -out ca.cert
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:Geneva
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:DIT
Common Name (eg, your name or your server's hostname) []:Root CA
Email Address []:

Server v3 certificate generation

To generate server and client v3 certificates create first this option file (v3.ext), documentation can be found with man x509v3_config:

[mariadb@server01 certs]$ cat v3.ext
basicConstraints=CA:FALSE
keyUsage=digitalSignature
extendedKeyUsage=serverAuth,clientAuth

From official documentation:

  • Basic Constraints: This is a multi-valued extension which indicates whether a certificate is a CA certificate
  • Key Usage: Key usage is a multi-valued extension consisting of a list of names of the permitted key usages
  • Extended Key Usage: This extension consists of a list of values indicating purposes for which the certificate public key can be used

Generate the server RSA private key:

[mariadb@server01 certs]$ openssl genrsa -out server.key 4096
Generating RSA private key, 4096 bit long modulus
.........................++
...................................................++
e is 65537 (0x10001)

Generate the server certificate signing request:

[mariadb@server01 certs]$ openssl req -new -nodes -key server.key -days 365 -out server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:Geneva
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:DIT
Common Name (eg, your name or your server's hostname) []:Server Certificate
Email Address []:
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Remark:
In few Internet posts they say that Common Name (eg, your name or your server’s hostname) must be different for each certificate signing request to avoid failed certification verification. I have tried using three times the same name and it worked…

Generate and sign a server v3 certificate:

[mariadb@server01 certs]$ openssl x509 -req -in server.csr -days 365 -CA ca.cert -CAkey ca.key -set_serial '01' -extfile v3.ext -out server.cert
Signature ok
subject=/C=CH/ST=Geneva/L=Geneva/O=MyCompany/OU=DIT/CN=Server Certificate
Getting CA Private Key

Optionaly verify the server certificate with:

[mariadb@server01 certs]$ openssl verify -CAfile ca.cert server.cert
server.cert: OK

Client v3 certificate generation

This x509 certificate and private key generation is not required to activate one-way TLS but you must have it for two-way TLS. Generate a RSA client private key:

[mariadb@server01 certs]$ openssl genrsa -out client.key 4096
Generating RSA private key, 4096 bit long modulus
...............................++
.++
e is 65537 (0x10001)

Generate the client certificate signing request:

[mariadb@server01 certs]$ openssl req -new -nodes -key client.key -days 365 -out client.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:Geneva
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:DIT
Common Name (eg, your name or your server's hostname) []:Client Certificate
Email Address []:
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Generate and sign a client v3 certificate:

[mariadb@server01 certs]$ openssl x509 -req -in client.csr -days 365 -CA ca.cert -CAkey ca.key -set_serial '01' -extfile v3.ext -out client.cert
Signature ok
subject=/C=CH/ST=Geneva/L=Geneva/O=MyCompany/OU=DIT/CN=Client Certificate
Getting CA Private Key

Optionaly verify the client certificate with:

[mariadb@server01 certs]$ openssl verify -CAfile ca.cert client.cert
client.cert: OK

Data in transit encryption MariaDB configuration and testing

In my MariaDB configuration file (my.cnf) I have added the below parameters (check the error log to ensure all went well):

# SSL
ssl_ca = /home/mariadb/certs/ca.cert
ssl_cert = /home/mariadb/certs/server.cert
ssl_key = /home/mariadb/certs/server.key

Once you have restarted your MariaDB instance you can check that SSL is enabled with:

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| have_openssl        | NO                              |
| have_ssl            | YES                             |
| ssl_ca              | /home/mariadb/certs/ca.cert     |
| ssl_capath          |                                 |
| ssl_cert            | /home/mariadb/certs/server.cert |
| ssl_cipher          |                                 |
| ssl_crl             |                                 |
| ssl_crlpath         |                                 |
| ssl_key             | /home/mariadb/certs/server.key  |
| version_ssl_library | WolfSSL 5.1.1                   |
+---------------------+---------------------------------+
10 rows in set (0.001 sec)
 
MariaDB [(none)]> show variables like '%tls%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| tls_version   | TLSv1.1,TLSv1.2,TLSv1.3 |
+---------------+-------------------------+
1 row in set (0.001 sec)

But connecting with no special client parameter does NOT transparently encrypt data in transit, you can verify it with:

MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.000 sec)

One-way TLS for client

The clients I plan to use is obviously the local client of my MariaDB server installation as well as SQLYog for which we have a valid license.

I my server configuration file I have added:

[client-server]
port = 3316
socket = /mariadb/software/mariadb01/conf/mariadb01.sock
ssl_ca = /home/mariadb/certs/ca.cert
#ssl-verify-server-cert

Remark:
The ssl-verify-server-cert parameter suggested in official documentation is not working in MariaDB 10.6.7…

When I try to connect I now get, which means that transit between client and server is encrypted:

[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.6.7-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show session status like 'ssl_cipher';
 
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| Ssl_cipher    | TLS13-AES128-GCM-SHA256 |
+---------------+-------------------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> status
--------------
/mariadb/software/mariadb01/bin/mariadb  Ver 15.1 Distrib 10.6.7-MariaDB, for linux-systemd (x86_64) using readline 5.1
 
Connection id:          12
Current database:
Current user:           yjaquier@10.75.43.64
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.7-MariaDB-log MariaDB Server
Protocol version:       10
Connection:             server01.domain.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
TCP port:               3316
Uptime:                 17 hours 54 min 25 sec
 
Threads: 3  Questions: 690  Slow queries: 2  Opens: 131  Open tables: 124  Queries per second avg: 0.010
--------------

With SQLYog it is even simpler as I have only activated below option:

data_in-transit-encryption01
data_in-transit-encryption01

And above “show session status like ‘ssl_cipher'” command is displaying same result…

What is nice is that it is very simple to activate this one-way TLS but non rigorous uses can still not activate the option in their tool or even use –skip_ssl option of server client binaries:

[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com --skip_ssl
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.6.7-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.000 sec)

To solve this you have have all the REQUIRE tls_option of the CREATE USER command. For example if I activate the minimum one-way TLS for my account:

MariaDB [(none)]> alter user yjaquier require ssl;
Query OK, 0 rows affected (0.011 sec)

Then bypassing SSL is forbidden:

[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com --skip_ssl
Enter password:
ERROR 1045 (28000): Access denied for user 'yjaquier'@'10.75.43.64' (using password: YES)
[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 10.6.7-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| Ssl_cipher    | TLS13-AES128-GCM-SHA256 |
+---------------+-------------------------+
1 row in set (0.001 sec)

Two-way TLS for client

In two-way TLS both server and client are authenticated. Both server and client provide a x509 certificate and a private key.

[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com  --ssl-ca=/home/mariadb/certs/ca.cert --ssl-cert=/home/mariadb/certs/client.cert --ssl_key=/home/mariadb/certs/client.key
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.6.7-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| Ssl_cipher    | TLS13-AES128-GCM-SHA256 |
+---------------+-------------------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> status;
--------------
/mariadb/software/mariadb01/bin/mariadb  Ver 15.1 Distrib 10.6.7-MariaDB, for linux-systemd (x86_64) using readline 5.1
 
Connection id:          27
Current database:
Current user:           yjaquier@10.75.43.64
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.7-MariaDB-log MariaDB Server
Protocol version:       10
Connection:             server01.domain.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
TCP port:               3316
Uptime:                 18 hours 49 min 12 sec
 
Threads: 7  Questions: 711  Slow queries: 2  Opens: 131  Open tables: 124  Queries per second avg: 0.010
--------------

Same as for one-way TLS you can enforce this two-way TLS connection with similar REQUIRE tls_option of CREATE USER command using one of this (refer to official documentation for more info):

  • REQUIRE X509
  • REQUIRE ISSUER ‘issuer’
  • REQUIRE SUBJECT ‘subject’
  • REQUIRE CIPHER ‘cipher’

To keep it simple I enforce my account with:

MariaDB [(none)]> alter user yjaquier require x509;
Query OK, 0 rows affected (0.001 sec)

And my yjaquier account can be used only in two-way TLS connections:

[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com
Enter password:
ERROR 1045 (28000): Access denied for user 'yjaquier'@'10.75.43.64' (using password: YES)
[mariadb@server01 ~]$ /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=yjaquier -p --host=server01.domain.com  --ssl-ca=/home/mariadb/certs/ca.cert --ssl-cert=/home/mariadb/certs/client.cert --ssl_key=/home/mariadb/certs/client.key
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.6.7-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| Ssl_cipher    | TLS13-AES128-GCM-SHA256 |
+---------------+-------------------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]>

For SQLYog I had to transfert my client certificate on my Windows desktop and browse for them in the SSL options:

data_in-transit-encryption02
data_in-transit-encryption02

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

This entry was posted in MariaDB and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>