Connect to MariaDB via SSL/TLS on Ubuntu

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

By default when you install MariaDB server, it will allow connections from any systems for users with the correct credentials.

If you want to add another layer of security, you can enable SSL/TLS settings and force all users to connect securely.

For this tutorials, we’re going to be using existing Let’s Encrypt SSL certificates to configure with MariaDB.

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

Setup Let’s Encrypt

Since we’re going to be using Let’s Encrypt SSL/TLS certificates, let’s install and configure it.

Before generating your free wildcard certificates, you’ll first want to make sure certbot is installed and running.

To install it, run the commands below:

sudo apt update
sudo apt-get install letsencrypt

The commands above will install certbot tool and all dependencies that will be allowed to make the tool function.

Now that the tool is installed, you can now proceed to generating certificates.

Let’s Encrypt provides many ways to challenge you to validate that you own the domain you want to provide SSL certificates for. You will not be able to generate certificates if you can’t prove that you own the domain you want certificates for.

However,  for wildcard certificates, the only challenge method Let’s Encrypt accepts is the DNS challenge, which we can invoke via the preferred-challenges=dns flag.

So, to generate a wildcard cert for domain *.example.com, you run the commands below. You should also include the bare domain with registering.

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

The command options above are explained below:

  • certonly:                                     Obtain or renew a certificate, but do not install
  • –manual:                                    Obtain certificates interactively
  • –preferred-challenges=dns:      Use dns to authenticate domain ownership
  • –server:                                      Specify the endpoint to use to generate
  • –agree-tos:                                 Agree to the ACME server’s subscriber terms
  • -d:                                               Domain name to provide certificates for

After executing the command above, Let’s Encrypt will provide a text string to add a text record to your DNS entry.

Example:

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:

x4MrZ6y-JqFJQRmq_lGi9ReRQHPa1aTC9J2O7wDKzq8

Before continuing, verify the record is deployed.

Go to your DNS provider portal and add a text record for the string above and save.

Let's Encrypt DNS

Wait a few mins before continuing from the prompt. Some DNS providers take a wile to propagate changes so it may depend on your provider’s platform.

After the changes above and Let’s encrypt is able to validate that you own the domain, you should see a successful message as 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.

That should do it!

The wildcard certificate is now generated and ready to be used.

To verify that the certificate is ready, run the commands below:

sudo certbot certificates

That should display similar screen as below:

Found the following certs:
  Certificate Name: example.com
    Domains: *.example.com
    Expiry Date: 2020-01-05 07:48:04+00:00 (VALID: 85 days)
    Certificate Path: /etc/letsencrypt/live/example.com/fullchain.pem
    Private Key Path: /etc/letsencrypt/live/example.com/privkey.pem

You’re all set!

Now, Let’s Encrypts certificates are valid for 90 days. You’ll want to setup a cron job to automate the renewal process. To do that, open crontab and add the entry below:

sudo crontab -e

Then add the line below and save.

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

Let’s encrypt is installed and configured.

Configure MariaDB SSL/TLS Connection

Now that Let’s encrypt is installed and configured, go to MariaDB and configure to connect over SSL/TLS.

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

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

Create a private key the the cert files above.

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

Then make mysql user ower of the directory above.

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

When you’re done, open 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/chain.pem
ssl-cert=/var/lib/mysql/pki/cert.pem
ssl-key=/var/lib/mysql/pki/privkey.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)

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 login.

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 error above, please use the form below to report.

You may also like the post below:

1 Comment

  1. could you please share linux connected example

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.