How to Connect to MariaDB via SSL/TLS on Ubuntu

This post is going to shows that How to Connect to MariaDB via SSL/TLS on Ubuntu.

Let’s Encrypt is a certificate authority that is provides Free SSL/Transport Layer Security encryption(TLS) certificate. This authority run and managed by Internet Security Research Group

By default MariaDB server will allow connections from any systems for users with the correct credentials.

If you want to add a another layer of security for MariaDB server then follow the below steps to enable SSL/TLS settings and force all users to connect securely:

Step 1: Install and setup Let’s Encrypt Certbot Tool

For generate certificate, you need to make sure that Certbot is installed and running. If not then run below commands to install it :

sudo apt update
sudo apt-get install letsencrypt

Step 2: Generate Let’s Encrypt Wildcard SSL Certificate

Run the below command to generate a wildcard cert for domain  example.com. You should change example.com with your registered domain.

sudo certbot certonly --manual --preferred-challenges=dns --email admin@example.com --server https://acme-v02.api.letsencrypt.org/directory --agree-tos -d example.com -d *.example.com

After run the above command, Let’s Encrypt provide a text string to add as a text record to your DNS Record.

Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator manual, Installer None

-------------------------------------------------------------------------------
Would you be willing to share your email address with the Electronic Frontier
Foundation, a founding partner of the Let's Encrypt project and the non-profit
organization that develops Certbot? We'd like to send you email about EFF and
our work to encrypt the web, protect its users and defend digital rights.
-------------------------------------------------------------------------------
(Y)es/(N)o: y
Obtaining a new certificate
Performing the following challenges:
dns-01 challenge for example.com

-------------------------------------------------------------------------------
NOTE: The IP of this machine will be publicly logged as having requested this
certificate. If you're running certbot in manual mode on a machine that is not
your server, please ensure you're okay with that.

Are you OK with your IP being logged?
-------------------------------------------------------------------------------
(Y)es/(N)o: y

-------------------------------------------------------------------------------
Please deploy a DNS TXT record under the name
_acme-challenge.example.com with the following value:

z5MrZ6d-aqFJQRmp_lGi9RTQHPa1aTC9J2O7wDKzp9

Before continuing, verify the record is deployed.

Now Go to your DNS provider dashboard and open DNS zone file to add a text record for the above provided text string.

After the above steps and validate that you own the provided domain, you will see a successful message like below :

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/example.com/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/example.com/privkey.pem
   Your cert will expire on 2020-01-09. To obtain a new or tweaked
   version of this certificate in the future, simply run certbot
   again. To non-interactively renew *all* of your certificates, run
   "certbot renew"
 - Your account credentials have been saved in your Certbot
   configuration directory at /etc/letsencrypt. You should make a
   secure backup of this folder now. This configuration directory will
   also contain certificates and private keys obtained by Certbot so
   making regular backups of this folder is ideal.
 - If you like Certbot, please consider supporting our work by:

   Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
   Donating to EFF:                    https://eff.org/donate-le

Now the wildcard certificate is generated and ready for use. So run the below command to verify that the certificate is ready to use.

sudo certbot certificates

When you run above command it will show similar screen like below:

Found the following certs:
  Certificate Name: example.com
    Domains: *.example.com
    Expiry Date: 2020-03-03 10:21:04+00:00 (VALID: 90 days)
    Certificate Path: /etc/letsencrypt/live/example.com/fullchain.pem
    Private Key Path: /etc/letsencrypt/live/example.com/privkey.pem

If you want automatic renewal process then you will need to setup cron job for automatic renewal process.

Run the below command to open cron tab :

sudo crontab -e

Now add the below line and save

0 1 * * * /usr/bin/certbot renew >> /var/log/letsencrypt/renew.log

Save and you are done.

Configure MariaDB SSL/TLS Connection

In above steps, Let’s encrypt is successfully installed and configured. Now configure MariaDB to connect over SSL/TLS.

So create a new cert directory and copy the existing Lets encrypt certificate files to the directory created.

sudo mkdir /var/lib/mysql/newdirname
sudo cp /etc/letsencrypt/live/example.com/* /var/lib/mysql/newdirname/

Create a private key:

sudo openssl rsa -in /var/lib/mysql/newdirname/privkey.pem -out /var/lib/mysql/newdirname/private.key

Now run the below command to change owner of the directory above:

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

Above command, make mysql user owner of the above directory.

Now open MariaDB configuration file, simply run below command:

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

After open configuration file, add the below 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/newdirname/chain.pem
ssl-cert=/var/lib/mysql/newdirname/cert.pem
ssl-key=/var/lib/mysql/newdirname/privkey.pem

Save the file and restart MariaDB:

sudo systemctl restart mariadb

Now, connect to MariaDB and verify SSL/TLS using below command :

sudo mysql -u root

Then run the below query :

show variables like '%ssl%'; 

It display output similar 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/newdirname/chain.pem   |
| ssl_capath          |                                       |
| ssl_cert            | /var/lib/mysql/newdirname/cert.pem    |
| ssl_cipher          |                                       |
| ssl_crl             |                                       |
| ssl_crlpath         |                                       |
| ssl_key             | /var/lib/mysql/newdirname/privkey.pem |
| version_ssl_library | YaSSL 2.4.4                           |
+---------------------+---------------------------------------+
10 rows in set (0.001 sec)

Run the below commands to connect via the client over SSL/TLS :

sudo mysql --ssl

Now show the cipher that is being used:

show status like 'ssl_cipher';

When you run above command, it display output similar as below:

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

After enabling SSL/TLS, now you need to creating users and requiring SSL/TLS to login.

create user dbuser identified by 'password_here' require ssl; 

Run the below command for each to force existing users to use SSL/TLS :

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

Exit and you’re done to Learn Connect to MariaDB via SSL/TLS on Ubuntu.

That’s all

If you find any error and issue in above steps , please use comment box below to report.