Hello, friends. In this post, we will help you to configure MariaDB with SSL to secure connections.
When we connect to a database, we have to do it in the most secure way there is. Everything helps but especially SSL certificates. These can be obtained from tools like Let’s Encrypt or created by the server itself. In any case, this guarantees that all the data that the applications obtain or send to the database are protected.
Let’s start
Note for this post we will use the root account. In case it is not available you can use these commands with sudo. The result will be the same.
Install MariaDB on Linux
Of course, the first step we have to do is to install MariaDB on a server. To do this, check some of our posts
How to install MariaDB on Debian 10?
How to install MariaDB on Ubuntu 20.04?
Or How to install MariaDB on CentOS 8?
After that, you can continue.
Configure MariaDB with SSL
The first step is to create the directory where we will store the certificates that we will create later,
cd /etc/mysql mkdir ssl cd ssl
Now create a new CA key:
openssl genrsa 4096 > ca-key.pem
Now create the certificate
openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:FL
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:City
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:osradar-common
Email Address []:
On the output screen, you will have to answer some questions. Reply according to your case.
Creating the SSL Certificates
Now we can create the certificate for the server, this can be done by running the following command:
openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
...............................................................................+++++
.....+++++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:city
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:osradar-server
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:angelo
An optional company name []:osradar-server
There you will have to fill in the data again as in the previous step. The important thing here is that the Common Name cannot be the same.
Now process the new certificate:
openssl rsa -in server-key.pem -out server-key.pem writing RSA key
Then sign the certificate:
openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem Signature ok subject=C = AU, ST = Some-State, L = city, O = Internet Widgits Pty Ltd, OU = IT, CN = osradar Getting CA Private Key
Create the Client Certificate
Now we have created the certificate for the server, but we have to do the same for the client.
To do this run:
openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Again, you will have to fill in some data but Common Name has to be different.
Process the key:
openssl rsa -in client-key.pem -out client-key.pem
And sign the certificate:
openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Adding the certificates to the MariaDB server
With the certificates already created, you need to configure MariaDB with them.
So, open the MariaDB configuration file
nano /etc/mysql/mariadb.conf.d/50-server.cnf
And add the following lines:
ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem
Save the changes and close the editor.
Then assign special permissions to the folder where the certificates are. This is so that no intruder can modify them or breach them.
chown -R mysql:root /etc/mysql/ssl/
Apply all changes by restarting the service.
sudo systemctl restart mariadb
Configuring the clients
Before doing any configuration you have to copy /etc/mysql/ssl/ca-cert.pem, /etc/mysql/ssl/client-cert.pem, and /etc/mysql/ssl/client-key.pem to each of the clients that are going to connect to MariaDB.
Once everyone has the certificate added, configure MariaDB to use them:
nano /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
And add the following lines:
ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem
Save the changes and close the editor.
Now restart the service:
sudo systemctl restart mysql
Creating a new user for MariaDB
The configuration is ready, but now you have to force the users to use SSL. To do this create a new user with the REQUIRE SSL clause.
GRANT ALL ON sampledatabase.* TO me@localhost IDENTIFIED BY 'password' REQUIRE SSL;
This way this user will have to use SSL.
Conclusion
The security in the transmission of data to MariaDB is something fundamental and that we should not neglect. That is why you have learned today how to do it and you have to implement it.
