How to Setup Self Signed SSL/TLS with MariaDB on Ubuntu Linux

|

|

The tutorial provides a step-by-step guide on how to secure a MariaDB server by connecting it via SSL/TLS on Ubuntu 20.04 | 18.04. The process requires creating a self-signed certificate, configuring the MariaDB server for the secure connection, verifying the installation, and enforcing users to use SSL/TLS for log-ins.

This brief tutorial shows students and new users how to connect to MariaDB via SSL/TLS on Ubuntu 20.04 | 18.04.

Installing the MariaDB server will default allow connections from any system for users with the correct credentials.

Setting up a self-signed SSL/TLS connection with MariaDB on Ubuntu Linux is a great way to add an extra layer of security to your database. SSL/TLS certificates allow for secure communication between your database and other systems, ensuring that sensitive data remains private and secure.

By creating a self-signed certificate, you can configure MariaDB to use SSL/TLS without purchasing a certificate from a third-party provider, making it a cost-effective option for small businesses and personal projects.

To get started with configuring MariaDB with SSL/TLS certificates, follow the steps below:

Create SSL Cert

Since we’re creating self-signed certificates, simply run the commands below to create a directory where the cert files will be created.

After creating the directory, change it and begin creating your self-signed certificates.

sudo mkdir /var/lib/mysql/pki
cd /var/lib/mysql/pki

Now that the directory is created and you have changed into it, run the commands below to create the CA certificate and private key.

Create a CA key and CA cert.

sudo openssl genrsa -out ca-key.pem 2048
sudo openssl req -new -x509 -nodes -days 365 -key ca-key.pem -out ca-cert.pem

The commands above generate a 2048-bit key length and create a new 1-year (365 days) private key.

You may increase the key length and expiration date for the private key.

While creating the private key, you’ll be prompted for details of the key you’re generating.

Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:MN
Locality Name (eg, city) []:BP
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

Next, create a private key for the server. You’ll be prompted as above. Type the details that you want to include with the cert.

Create a private server key

sudo openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem

When you’re done above, export the server’s private key to an RSA-type key using the commands below:

sudo sudo openssl rsa -in server-key.pem -out server-key.pem

After all the above, run the commands below to generate an SSL cert using the commands below:

Generate SSL/TLS cert

sudo openssl x509 -req -in server-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

In the directory, you should have these files:

  • ca-cert.pem
  • ca-key.pem
  • server-cert.pem
  • server-key.pem
  • server-req.pem

Configure MariaDB SSL/TLS Connection

After creating a self-signed certificate, go to MariaDB and configure it to connect over SSL/TLS.

Then, make MySQL the user owner of the directory above.

sudo chown -R mysql. /var/lib/mysql/pki

When you’re done, open the MariaDB configuration file.

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

Then, add the highlighted lines to enable SSL/TLS

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
#port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking
ssl-ca=/var/lib/mysql/pki/ca-cert.pem
ssl-cert=/var/lib/mysql/pki/server-cert.pem
ssl-key=/var/lib/mysql/pki/server-key.pem
..
..

After adding the lines above, restart MariaDB.

sudo systemctl restart mariadb

Next, connect to MariaDB and verify SSL/TLS are loaded by running the statement below:

sudo mysql -u root

Then run the query below:

show variables like '%ssl%'; 

It should show similar lines as below:

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like '%ssl%'; 
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | NO                             |
| have_ssl            | YES                            |
| ssl_ca              | /var/lig/mysql/pki/chain.pem   |
| ssl_capath          |                                |
| ssl_cert            | /var/lib/mysql/pki/cert.pem    |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /var/lib/mysql/pki/privkey.pem |
| version_ssl_library | YaSSL 2.4.4                    |
+---------------------+--------------------------------+
10 rows in set (0.001 sec)

The “have_ssl” in MariaDB says whether SSL support is available, while “have_openssl” says whether OpenSSL is compiled.

So, if you have MariaDB built with YaSSL, have_ssl will be YES, while have_openssl will be NO.

To connect via the client over SSL/TLS, run the commands below:

sudo mysql --ssl

Then, show the cipher being used:

show status like 'ssl_cipher';

It should display similar lines as below:

+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.000 sec)

After enabling SSL/TLS, you should begin creating users and requiring SSL/TLS to log in.

create user dbuser identified by 'password_here' require ssl; 

To force existing users to use SSL/TLS, run the query below for each.

grant usage on *.* to 'dbuser_here'@'%' require ssl;

Exit, and you’re done.

Conclusion:

This post showed you how to configure MariaDB to connect over SSL/TLS. If you find an error above, please use the form below to report it.

Like this:



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.