Configure MariaDB for SSL/TLS

This tutorial will demonstrate how to set up a MariaDB server to serve over SSL/TLS, and how to configure both the MariaDB command-line client and a PHP PDO application to connect over SSL/TLS. Watch the video explanation here.

Prerequisites: This tutorial assumes you have two GNU/Linux servers, one acting as the SQL server, and the other acting as an SQL client. The example servers use Debian 10. If you don't have any servers yet, you can create some at linode.nots.co or digitalocean.nots.co. This tutorial also assumes the servers have subdomains; you can register a domain name at hover.nots.co, although it's not strictly necessary when acting as your own certificate authority.


Step 1: Connect to the SQL server, install updates, reboot (if necessary), and install the MariaDB server:

Code

apt update
apt full-upgrade
systemctl reboot
apt install mariadb-server
mysql_secure_installation

Step 2: Access MariaDB and create a test database & user:

Code

mariadb
CREATE DATABASE test_database;
GRANT ALL PRIVILEGES ON test_database.* TO 'test_user'@'client.ip.address.here' IDENTIFIED BY 'P@ssw0rd';
FLUSH PRIVILEGES;
quit;

Step 3: Attempt to connect to the MariaDB server (from the SQL server) and observe that access is denied:

Code

mariadb -u test_user -p

Step 4: Edit the MariaDB server configuration to listen for remote connections:

Code

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

Change the "bind-address" option to "0.0.0.0" to listen on all network interfaces, then save the configuration file and restart MariaDB:

Code

systemctl restart mariadb

Step 5: Connect to the SQL client, install updates, reboot (if necessary), and install the MariaDB client:

Code

apt update
apt full-upgrade
systemctl reboot
apt install mariadb-client

Step 6: Connect to the MariaDB server from the MariaDB client, create some test records, and view the current SSL configuration and status:

Code

mariadb -u test_user -p --host=sqlserver.example.com
SHOW DATABASES;
USE test_database;
SHOW TABLES;
CREATE TABLE test_table (RecordID int, RecordText longtext);
INSERT INTO test_table VALUES (0, "This is my first record.");
INSERT INTO test_table VALUES (1, "This is my second record.");
SELECT * FROM test_table;
SHOW VARIABLES LIKE '%ssl%';
status;
quit;

Step 7: Install PHP and the PHP MySQL module:

Code

apt install php php-mysl

Step 8: Create a text file called "read-records.php" and insert the following PHP code:

Code

try {
$conn = new PDO("mysql:host=sqlserver.example.com;dbname=test_database", "test_user", "P@ssw0rd");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected to SQL.\n";
echo "SSL Cipher Info:\n";
var_dump($conn->query("SHOW STATUS LIKE 'ssl_cipher';")->fetchAll());
echo "Database records:\n";
$stmt = $conn->query("SELECT * FROM test_table");
$stmt->execute();
while ($row = $stmt->fetch()) {
echo $row['RecordID'] . " ";
echo $row['RecordText'] . "\n";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage() . "\n";
}
?>

Step 9: Run the PHP file and observe that the records are read, but no SSL cipher is in use:

Code

php ./read-records.php

Step 10: On the SQL server, generate SSL certificates and keys for the certificate authority, server, and client:

Code

mkdir /etc/mysql/ssl
cd /etc/mysql/ssl
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:4096 > ./ca-key.pem
openssl req -new -x509 -nodes -days 365 -key ./ca-key.pem -out ./ca-cert.pem -subj "/C=YourCountry/ST=YourProvince/L=YourLocality/O=Your Organization/CN=A Unique Common Name"
openssl req -newkey rsa:4096 -nodes -keyout ./server-key.pem -out ./server-req.pem -subj "/C=YourCountry/ST=YourProvince/L=YourLocality/O=Your Organization/CN=sqlserver.example.com"
openssl x509 -req -in ./server-req.pem -days 365 -CA ./ca-cert.pem -CAkey ./ca-key.pem -CAcreateserial -out ./server-cert.pem
openssl req -newkey rsa:4096 -nodes -keyout ./client-key.pem -out ./client-req.pem -subj "/C=YourCountry/ST=YourProvince/L=YourLocality/O=Your Organization/CN=sqlclient.example.com"
openssl x509 -req -in ./client-req.pem -days 365 -CA ./ca-cert.pem -CAkey ./ca-key.pem -CAcreateserial -out ./client-cert.pem

Step 11: Check the expiration date of all three certificates that were generated:

Code

openssl x509 -enddate -noout -in ./ca-cert.pem
openssl x509 -enddate -noout -in ./server-cert.pem
openssl x509 -enddate -noout -in ./client-cert.pem

Step 12: Convert the server's private key to plain RSA format and change its owner to MariaDB's user:

Code

openssl rsa -in ./server-key.pem -out ./server-key-rsa.pem
chown mysql: ./server-key-rsa.pem

Step 13: Edit the MariaDB server configuration to serve over SSL/TLS:

Code

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

Set "ssl-ca", "ssl-cert", and "ssl-key" to point to the appropriate files, uncomment "ssl = on" and change "on" to "true", then restart MariaDB:

Code

systemctl restart mariadb

Step 14: Connect to MariaDB locally and confirm that SSL is configured:

Code

mariadb -u root -p
SHOW VARIABLES LIKE '%ssl%';
quit;

Step 15: Copy ca-cert.pem, client-cert.pem, and client-key.pem to the SQL client, then configure the MariaDB client to access SQL over SSL/TLS:

Code

mkdir /etc/mysql/ssl
cd /etc/mysql/ssl
openssl rsa -in ./client-key.pem -out ./client-key-rsa.pem
nano /etc/mysql/mariadb.conf.d/50-client.cnf

Add an "ssl-ca" option before "ssl-cert" and "ssl-key". Set "ssl-ca", "ssl-cert", and "ssl-key" to point to the appropriate files, and uncomment "ssl-verify-server-cert=on".

Step 16: Connect to MariaDB remotely and confirm that SSL is in use:

Code

mariadb -u test_user -p --host=sqlserver.example.com
status;
quit;

Step 17: On the SQL server, log into MariaDB as the root user and configure the test user to require SSL:

Code

mariadb -u root -p
SHOW GRANTS FOR 'test_user'@client.ip.address.here;
GRANT USAGE ON *.* TO `test_user`@`client.ip.address.here` REQUIRE SSL;
SHOW GRANTS FOR 'test_user'@client.ip.address.here;
quit;

Step 18: On the SQL client, modify the PHP application to connect using SSL:

Code

$pdo_options = [
PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ssl/ca-cert.pem',
PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/ssl/client-cert.pem',
PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/ssl/client-key.pem',
];

Add "$pdo_options" to the end of the "$conn" object (after the SQL username and password), then run the PHP file and confirm that an SSL cipher was used:

Code

php ./read-records.php

There are no pages beneath this page

Expand: Discussion Discussion (0 posts)