How to Create a Self-Signed SSL Certificate for MariaDB on Ubuntu 24.04

Create MariaDB SSL Certificate

MariaDB on Ubuntu doesn’t automatically make a self-signed SSL certificate like MySQL does, so you’ll need to create one yourself to secure your database connections.

If you haven’t already created a self-signed certificate, you can follow the post below to create one on Ubuntu.

Create a self-signed certificate on Ubuntu

Once created, continue below to use the certificates in MariaDB.

Configure MariaDB SSL Connection

Once you’ve created your self-signed SSL certificate, you need to set up MariaDB to use it by making a special folder and copying the certificate files into it.

sudo mkdir /var/lib/mysql/pki

Next, copy the certificate files from the [/etc/ssl/private] directory you created earlier to the new folder.

Then, adjust the permissions to allow the MariaDB account access.

sudo cp /etc/ssl/private/{server.crt,server.key} /var/lib/mysql/pki/
sudo chown -R mysql:mysql /var/lib/mysql/pki

After that, open the MariaDB configuration file using the command below.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add these lines to the file and save your changes.

# * SSL/TLS

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on

ssl-cert = /var/lib/mysql/pki/server.crt
ssl-key = /var/lib/mysql/pki/server.key

..............
..............

Now, exit and restart MariaDB.

sudo systemctl restart mariadb

Validate MariaDB SSL settings

After setting up your MariaDB SSL certificate, you can check if the database recognizes it by logging in and running a simple command to see the SSL variables.

First, log into the MariaDB database.

sudo mariadb

Then, run the SQL statement to list the SSL tables.

show variables like '%ssl%'; 

You should see a result similar to this:

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| have_openssl        | YES                           |
| have_ssl            | YES                           |
| ssl_ca              |                               |
| ssl_capath          |                               |
| ssl_cert            | /var/lib/mysql/pki/server.crt |
| ssl_cipher          |                               |
| ssl_crl             |                               |
| ssl_crlpath         |                               |
| ssl_key             | /var/lib/mysql/pki/server.key |
| version_ssl_library | OpenSSL 3.0.13 30 Jan 2024    |
+---------------------+-------------------------------+
10 rows in set (0.001 sec)

You can also check how long the certificates are valid by running the command below.

show status like 'Ssl_server_not%';

It should show output lines like these:

+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Feb 19 17:20:43 2035 GMT |
| Ssl_server_not_before | Feb 21 17:20:43 2025 GMT |
+-----------------------+--------------------------+

Force users to connect with SSL

To make sure everyone connects securely, you can tell MariaDB to require SSL for specific users when they log in, which adds an extra layer of protection.

For new users, run the SQL statement below to create a user named jdoe and set a password.

CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl; 

Remember to replace jdoe with the username you want to create.

Run the statement below to verify all database accounts that must use SSL for connections.

select user,host,ssl_type,plugin from mysql.user;

Your output should appear similar to this:

+-------------+-----------+----------+-----------------------+
| User        | Host      | ssl_type | plugin                |
+-------------+-----------+----------+-----------------------+
| mariadb.sys | localhost |          | mysql_native_password |
| root        | localhost |          | mysql_native_password |
| mysql       | localhost |          | mysql_native_password |
| jdoe        | %         | ANY      | mysql_native_password |
+-------------+-----------+----------+-----------------------+

To make existing database accounts use SSL, run the following SQL statement:

alter user 'root'@'localhost' require ssl;

Connect to MariaDB using SSL

When users need to connect to MariaDB using SSL, especially if it’s required, they’ll use a specific command to establish a secure connection from their computer.

mariadb -u jdoe -p --protocol=tcp

If they’re using a database tool, they’ll need to enable SSL for the connection to work.

And that’s it!

Conclusion:

• Implementing a self-signed SSL certificate for MariaDB strengthens security by encrypting data transmitted between the server and clients.
• Following the steps outlined, you can successfully create, configure, and validate SSL connections for your MariaDB database.
• Enforcing SSL for all user connections ensures that sensitive information remains secure from potential eavesdropping.
• Regularly check and manage SSL certificates to maintain a secure database environment and avoid potential disruptions.
• Consider moving to a trusted certificate authority (CA) for production environments to enhance security further.