Enabling SSL on a MariaDB Server Running on a Synology NAS

A good portion of this post leans heavily on work done by other folks on the web. However, whatever I could find on the web usually only got me 95% of the way there. I had to combine information from several sources to get something that worked properly on my NAS.

Environment Details

• DSM 6.2.2-24922 Update 4
• MariaDB 10.3.21-0063

Requirements

You must have setup SSH capabilities on your NAS to perform this work. Details about how to get that setup can be found here: How to SSH into a NAS.

You must be comfortable using a Unix style command prompt.

You must be proficient with a Unix style editor such as vi, vim, or Emacs.

You need to be the root user on the NAS to perform these steps. If you login with any other user id, then use the sudo command to change to the root user. (See example below.) When prompted for a password, use the same password you used to login. Note the login user must be part of the administrators group for this command to work.

User@NAS:~$ sudo -i
Password:
root@NAS:~# 

Sources

As I mentioned at the start, I’m borrowing heavily from another blog post and the MariaDB knowledge base for a good chunk of this write up. I found other sites that seemed to be outlining a similar process which I could not get to work.

Install MariaDB

Install MariaDB using the Package Center. As noted earlier, the version of MariaDB I used for this tutorial is 10.3.21-0063. Make sure you keep your database’s root password handy, as you will need it in later steps.

Configuring MariaDB

SSH to your NAS. You may want to have two windows open. One to connect to the database and one to handle the file system work.

Become the root user, if you are not already root.

Validating Current DB Configuration

Connect to the database using the mysql command as the root user. You will be prompted for the password.

root@NAS:~# /usr/local/mariadb10/bin/mysql -u root -p

If the login was successful, you should see the following prompt.

MariaDB [(none)]> 

Issue the SHOW VARIABLES command as displayed below to verify that your instance of MariaDB was compiled to use SSL, but is currently not configured. More details on the possible values of “have_ssl” can be found on the MariaDB site.

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.001 sec)

Assuming the query contained DISABLED, then the instance can be configured to use SSL. Exit out of the mysql interface by typing exit and pressing return.

MariaDB [(none)]> exit
Bye
root@NAS:~#

Generating Certificates

We need to first create a directory where the generated certificates will live.

root@NAS:~# mkdir /etc/mariadb/
root@NAS:~#

Navigate the the newly created directory.

root@NAS:~# cd /etc/mariadb/
root@NAS:/etc/mariadb# 

Creating the Certificate Authority (CA) Private Key

Generate a private key for the CA using the openssl genrsa command.

root@NAS:/etc/mariadb# openssl genrsa 2048 > ca-key.pem

Output

Generating RSA private key, 2048 bit long modulus
...............................................................................................................+++++
..........................................................................................................................+++++
e is 65537 (0x10001)
root@NAS:/etc/mariadb# 

Use the private key to generate the X509 certificate for the CA using the openssl req command. You will be prompted from some details. Several folks on the internet have indicated that you need to vary the answers when prompted for the same information during a later step. If you don’t, certificate validation may fail.

root@NAS:/etc/mariadb# openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

Output

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) [TW]:US
State or Province Name (full name) [Taiwan]:New York
Locality Name (eg, city) [Taipei]:N/A
Organization Name (eg, company) [Synology Inc.]:N/A
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address [product@synology.com]:N/A
root@NAS:/etc/mariadb#

Creating the Server Certificates

Generate a private key and create a certificate request using the openssl req command. Remember to change the responses.

root@NAS:/etc/mariadb# openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

Output

Generating a RSA private key
...................................+++++
.....................+++++
writing new private key to 'server-key.pem'
-----
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) [TW]:US
State or Province Name (full name) [Taiwan]:Maine
Locality Name (eg, city) [Taipei]:.
Organization Name (eg, company) [Synology Inc.]:.
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address [product@synology.com]:.

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Some secret password
An optional company name []:
root@NAS:/etc/mariadb# 

Remove the passphrase from the private key using the openssl rsa command.

root@NAS:/etc/mariadb# openssl rsa -in server-key.pem -out server-key.pem

Output

writing RSA key
root@NAS:/etc/mariadb#

Generate a self-signed X509 certificate from the certificate request using the openssl x509 command.

root@NAS:/etc/mariadb# openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Output

Signature ok
subject=/C=US/ST=Maine/L=/O=/emailAddress=
Getting CA Private Key
root@NAS:/etc/mariadb# 

Certificate Validation

Verify the server certificate by issuing the following command.

root@NAS:/etc/mariadb# openssl verify -CAfile ca-cert.pem server-cert.pem

Output

server-cert.pem: OK
root@NAS:/etc/mariadb#

Configuring MariaDB

According to Synology, the custom configuration file for the database should be located at /var/packages/MariaDB10/etc/my.cnf . The initial installation does not create an empty file. You must create it yourself. Use your favorite editor to create and add the following entries to the file.

[mysqld]
ssl-ca=/etc/mariadb/ca-cert.pem
ssl-cert=/etc/mariadb/server-cert.pem
ssl-key=/etc/mariadb/server-key.pem

Bounce the Database

Check the status of the database server by issuing the following command.

root@NAS:/etc/mariadb# /usr/syno/bin/synopkg is_onoff MariaDB10

The expected output is status on.

package MariaDB10 is turned on
root@NAS:/etc/mariadb# 

Stop the server if it is running.

root@NAS:/etc/mariadb# /usr/syno/bin/synopkg stop MariaDB10

The expected output is the server has been stopped.

package MariaDB10 stop successfully
root@NAS:/etc/mariadb# 

Start the server.

root@NAS:/etc/mariadb# /usr/syno/bin/synopkg start MariaDB10

The expected result is the server started.

package MariaDB10 start successfully
root@NAS:/etc/mariadb# 

Validate SSL is Enabled

Connect to the database.

root@NAS:~# /usr/local/mariadb10/bin/mysql -u root -p

Validate the has_ssl now has the value of YES.

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]>

You can also verify the location of your configuration files.

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mariadb/ca-cert.pem         |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mariadb/server-cert.pem     |
| ssl_cipher          |                                  |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mariadb/server-key.pem      |
| version_ssl_library | OpenSSL 1.0.2r-fips  26 Feb 2019 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)

Some Thoughts About Security

This tutorial is by no means using best practices in terms of security. For example, setting the number of days on certs to 365000 would be a pretty bad practice in the real world. If you are exposing your database to the world, you should most likely be using a certificate supplied by a vendor. If you need production level security, talk to your security folks.

Final Thoughts

I wrote this tutorial because I could not find any source that took the configuration from start to finish. Hopefully, this will help folks out.