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

Create MariaDB SSL Certificate

You create the certificate in the terminal with OpenSSL. It produces the certificate and key files MariaDB needs, with no paid certificate authority involved. You need an Ubuntu 24.04 server with MariaDB already installed and a user that can run commands with sudo.

If you haven’t created a self-signed certificate yet, follow the post below first. It walks through creating one on Ubuntu with OpenSSL. Come back here once you have the certificate and key files.

Create a self-signed certificate on Ubuntu

Once the files exist, continue below to put them to use in MariaDB.

Configure MariaDB SSL Connection

To turn on SSL, MariaDB needs three things: the certificate files in a folder it can reach, file permissions that let the MariaDB account read them, and a few lines in its configuration file that point to them. This section covers all three. Run every command with sudo, because these folders belong to root.

Start by copying the certificate files from /etc/ssl/private to /etc/mysql/ssl. Keeping a separate copy in the MariaDB folder means you can give the database account access to it without loosening permissions on the system-wide private folder. Run the command below to copy the files.

Next, change the ownership and permissions so the MariaDB account can read the files. The private key is the sensitive one. Only the MariaDB account (and root) should be able to read it. If MariaDB can't read the key, the service will fail to start with SSL turned on or will start without SSL. Run the commands below.

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 with the command below. It opens in a terminal text editor, so use the keys that editor expects to save and quit.

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

Add the lines below to the file, then save your changes. They tell MariaDB where the certificate, key and CA file are. Check that each path matches where you copied the files. A typo here is the most common reason SSL stays off.

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

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

A restart is required, because MariaDB only loads SSL files when the service starts. Existing connections are dropped during the restart, so do this at a quiet time on a busy server.

sudo systemctl restart mariadb

If the service doesn't come back up, check its status and the MariaDB error log. The usual causes are a wrong file path in the configuration or a key file the MariaDB account can't read. To undo the change, remove the SSL lines you added, save the file and restart the service again.

Validate MariaDB SSL settings

Before you rely on the certificate, confirm that MariaDB actually loaded it. You do this from inside the database shell by checking the SSL variables. You can also check how long the certificate is valid and require SSL for your accounts.

First, log into the MariaDB database with the command below. On a default Ubuntu install, the root account signs in through the system when you use sudo. If your root account uses a password, enter it when asked.

sudo mariadb

Then run the SQL statement below to list the SSL variables.

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)

Check two things in the output. The SSL status variable should read YES. If it says DISABLED, MariaDB did not load the certificate. The certificate, key and CA entries should also show the paths you set in the configuration file. If they are empty, go back and check the configuration lines.

You can also check how long the certificate stays valid by running the command below in the regular terminal, not in the MariaDB shell.

show status like 'Ssl_server_not%';

They give the date the certificate became valid and the date it expires. Note the expiry date. MariaDB does not warn you when a certificate runs out, and clients that check it will start failing on that day.

Database tools need SSL enabled on the server to connect securely. To make sure new accounts must use it, create the user with SSL required. Log into the MariaDB shell again if you left it, then follow the next step.

Run the SQL statement below to create a new database user and set a password. The statement includes the SSL requirement, so this account can't connect without encryption.

CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl; 

Replace jdoe with the username you want to create. Change the password in the statement as well, and use a strong one.

Run the statement below to list 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. The new account should appear in the list.

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

Accounts that existed before you set up SSL are not forced to use it. To change that, run the following SQL statement. Be careful on a live server: once an account requires SSL, any application still connecting without encryption will be refused. Update and test your clients first, or change one account at a time.

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

To undo the requirement for an account, alter that account again and remove the SSL requirement.

Connect to MariaDB using SSL

When a client connects with SSL, MariaDB sets up an encrypted channel between the client application and the server, so queries and results can't be read on the network. A certificate you signed yourself isn't trusted by any public certificate authority. Clients that verify the server certificate will therefore reject it unless you give them the certificate file as a trusted CA. Otherwise, you can switch off certificate verification in the client. The traffic is still encrypted if you skip verification, but the client no longer checks that it is talking to the right server.

Database tools need SSL enabled in their connection settings to work with accounts that require it. Look for an SSL or encryption option in the tool's connection dialog and turn it on. Many tools also have a setting to skip server certificate verification, which is what a self-signed certificate usually needs.

Once every account requires SSL, the database server refuses unencrypted connections and accepts only encrypted ones.

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, including their expiry dates, 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.