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

Create MariaDB SSL Certificate

Generating a self-signed SSL MariaDB Ubuntu certificate encrypts your database traffic to stop third parties from eavesdropping on private networks. You can create this certificate directly in your terminal using OpenSSL, which sets up the necessary security files for your database server without needing a paid certificate authority.

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

Configuring your MariaDB SSL connection requires moving your security keys into a dedicated folder and updating the main configuration file so the database service can find them. You must place the certificate files in the correct directory and adjust file permissions so the database user account can read them properly.

Copy the certificate files from `/etc/ssl/private` to `/etc/mysql/ssl` to secure your MariaDB server. Next, adjust the permissions to grant the MariaDB account access. Open the MariaDB configuration file using the command below and add the following lines before saving your changes.

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

Validating your MariaDB SSL settings confirms that your self-signed certificate is active by checking the internal security variables directly inside the database. You can run a quick SQL command after logging into your database shell to verify that encrypted connections are enabled and working.

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:

Database tools require SSL to be enabled for connections to function correctly, ensuring secure data transfer. For new users, run the SQL statement below to create a new database user and set a password.

Run the SQL statement below to create a new database user 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

Connecting to MariaDB using SSL creates a secure, encrypted tunnel between your client application and the database server to protect your data during transfer. Database tools and remote connections require specific flags to use this encryption successfully and bypass security checks for self-signed certificates.

Database tools require SSL to be enabled for connections to function correctly, ensuring secure data transfer.

The database server now forces encrypted communication for all incoming connections.

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.