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 [email protected] --server --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.
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/private.key
..
..
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.
