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
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:
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:
References
- Data-in-Transit Encryption MariaDB documentation
- Securing Connections for Client and Server
- How to set up MariaDB SSL and secure connections from clients
- OpenSSL commands
- CertificateTools.com X509 Certificate Generator
