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

This article explains creating a MariaDB self-signed SSL certificate on Ubuntu 24.04.

By default, when you install MariaDB server, it will only allow connections from the local system for users with the correct credentials, regardless of the transport protocol. Creating a self-signed certificate for MariaDB can enhance security by enabling encrypted connections between the server and clients.

By generating and using a self-signed certificate, you can secure your MariaDB connections and mitigate various security vulnerabilities while maintaining control over your database environment.

The steps below walk you through creating and configuring the MariaDB database server to use a self-signed SSL certificate for connection.

Create MariaDB SSL Certificate

Unlike MySQL, when you install MariaDB on Ubuntu, it doesn’t automatically create a self-signed certificate. You must create a self-signed certificate to use in your setup.

If you haven’t already created a self-signed certificate, you can use 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

With the self-signed certificates created, run the command below to create a location to store the certificates for MariaDB.

sudo mkdir /var/lib/mysql/pki

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

Then, the permissions will be adjusted to allow access to the MariaDB account.

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 by running the command below.

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

Then, add these lines to the file and save.

# * 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

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

Exit and restart MariaDB.

sudo systemctl restart mariadb

Validate MariaDB SSL settings

Once you finish the configuration above, validate whether MariaDB can see the installed SSL certificates.

First, log on to the MariaDB database.

sudo mariadb

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

show variables like '%ssl%'; 

The result should be similar to the one below.

+---------------------+-------------------------------+
| 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 see how long the certificates are valid by running the command below.

show status like 'Ssl_server_not%';

It should output similar lines as below.

+-----------------------+--------------------------+
| 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

Now we know SSL is configured; you can force users to always use SSL when connecting to the database.

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

CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl; 

Replace jdoe with the name of the account you want to create.

By running the statement below, you can validate all the database accounts that must use SSL when connecting.

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

Your output should look similar to the one below.

+-------------+-----------+----------+-----------------------+
| 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 |
+-------------+-----------+----------+-----------------------+

Execute the SQL statement below to force existing database accounts to use SSL.

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

Connect to MariaDB using SSL

Now that users must use SSL to connect to MariaDB, they must run the command below to access the database from the local host.

mariadb -u jdoe -p --protocol=tcp

If they’re using a database tool, they must enable SSL for the connection to succeed.

That should do 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.