5.3 C
Amsterdam

Configure MariaDB with SSL to secure connections

angeloma
angeloma
Senior Writer and partner

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.

- Advertisement -

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Donate to Osradar

Latest article

x