Setup issues SSL/TLS to MariaDB

Troubleshooting
1

Hi everyone,
I set up a LAMP Environment on my Raspi 3. As I would like to make the MariaDB available from clients in the Internet, I was looking for a solution to use SSL for encryption. I used this manual:
https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/

Everything works fine until I set ssl_cert = /etc/mysql/ssl/server-cert.pem. In this case the variable have_ssl changes to DISABLED.

MariaDB [(none)]> SHOW VARIABLES LIKE ‘%ssl%’;

+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| have_openssl        | NO                         |
| have_ssl            | YES                        |
| ssl_ca              | /etc/mysql/ssl/ca-cert.pem |
| ssl_capath          | /etc/mysql/capath          |
| ssl_cert            |                            |
| ssl_cipher          |                            |
| ssl_crl             |                            |
| ssl_crlpath         |                            |
| ssl_key             |                            |
| version_ssl_library | YaSSL 2.4.2                |
+---------------------+----------------------------+




MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | NO                             |
| have_ssl            | DISABLED                       |
| ssl_ca              | /etc/mysql/ssl/ca-cert.pem     |
| ssl_capath          | /etc/mysql/capath              |
| ssl_cert            | /etc/mysql/ssl/server-cert.pem |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             |                                |
| version_ssl_library | YaSSL 2.4.2                    |
+---------------------+--------------------------------+

I checked the mysql fault log, but no mesages at all. The path seems right.
Is there a way to create error messages to see why have_ssl changes? I am wondering that usually there are huge logs to investigate for error reasons, and for SSL connection there is not a single entry?
How can I determine, why have_ssl changed to DISABLED? Which requirements have to be fulfilled to change to yes? I couldn’t find information on this in the maria reference or in forums.

Thank you very much in advance.

Cheers,
Markus

2

Generally, better try the official instructions first. In case of MariaDB, the documentation is quite complete, no need to follow any 3rd party guide. They can be great, but I have seen so many simply being outdated, unusual or even breaking the system.
https://mariadb.com/kb/en/library/secure-connections/

But your guide mostly follows official instructions, so all fine. You missed to set the ssl_key variable on server and client and ssl-cert on client as well. Recheck:
On server:
nano /etc/mysql/mariadb.conf.d/50-server.cnf

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

On client:
nano /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

[mysql]
...
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

Cross check: https://mariadb.com/kb/en/library/securing-connections-for-client-and-server/

And of course the files need to be in place, recheck this as well. And what both guides are missing, is to verify/set read permissions for the mysql user. Usually this should be already the case, but just to be sure and further increase security, do the following on both, server and client. Note that after this, only root user has full read/write permissions on the whole ssl dir and mysql user read-only permissions, which should be sufficient:

chown -R mysql:mysql /etc/mysql/ssl
chmod -R 400 /etc/mysql/ssl

Did you actively set ssl_capath to /etc/mysql/capath? Not sure if this is correct, but should be empty by default. Check if the directory exists, otherwise comment this setting. If it’s for trusted CA verification, then there is a system dir for this, which should be used by MariaDB, AFAIK.
Furthermore, if this leads to only trusted CAs are accepted, then it could beak your SSL, since you created self-signed certificates and are no trusted CA :wink:.

3

Hi Micha,

Thanks. I tried the official instructions :slight_smile: The link is provided in the “See Also” section.
I followed both guidelines with the same result: Diabled :frowning:
Currently I am only working on one Raspi, the server, to get it SSL ready. I assume that have_ssl = YES is required to be able to connect from a client.
My configuration is 1:1 identical as in https://mariadb.com/kb/en/library/securing-connections-for-client-and-server/. The kb states: check that have_ssl is enabled. Unfortunately it does not give any further information what to do if it is not yes :-/
I also executed chmod and chown. It is was 0755, now they are 0400.
ssl_capath to /etc/mysql/capath: This was from the documentation of the clientsoftware I intend to use. There the client certificate of my notebook should be stored. However, I removed the line. My variables now look like this:

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | NO                             |
| have_ssl            | DISABLED                       |
| ssl_ca              | /etc/mysql/ssl/ca-cert.pem     |
| ssl_capath          |                                |
| ssl_cert            | /etc/mysql/ssl/server-cert.pem |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /etc/mysql/ssl/server-key.pem  |
| version_ssl_library | YaSSL 2.4.2                    |
+---------------------+--------------------------------+

I would expect that there should be some error messages, when the expected *.pem are not in place or something like that.
Isn’t this the case for SSL? Or can I enable a DEBUG mode? Just something were I can see, whats going on, not just guessing :wink: