Server — MariaDB — TLS

Most MariaDB TLS articles assume you are running a database server that accepts remote connections. That is the scenario where TLS is genuinely critical: credentials and query results travelling across a network in plaintext is an obvious problem that encryption solves directly.

February is not that scenario. MariaDB on February is bound to 127.0.0.1, accepts no connections from outside the machine, and most applications connect via the Unix socket rather than the TCP port at all. Traffic on the loopback interface and the local Unix socket does not leave the machine and cannot be intercepted by anything on the network.

So why configure TLS at all? Two reasons. First, the network binding is a configuration setting that could change, accidentally or deliberately, and TLS provides a second layer of protection that holds regardless. Second, if any future service needs to connect to MariaDB over the network — even the WireGuard VPN or a container with bridge networking — TLS should already be in place rather than retrofitted under pressure.

This article covers generating a self-signed certificate for MariaDB, configuring the server to offer TLS, and enforcing it on a per-user basis for any user whose connection should be encrypted. Unix socket connections are exempt from TLS requirements and remain unaffected throughout.

How TLS works in MariaDB 10.11

MariaDB 10.11, which is what Ubuntu 24.04 ships, does not enable TLS automatically. The server needs a certificate and key configured before it will offer TLS connections at all. Once configured, TLS is available but not mandatory by default: applications can choose to use it or not. Mandatory TLS can be enforced globally with require_secure_transport or per user with REQUIRE SSL on the user account.

It is worth knowing that this changed in MariaDB 11.4, which enables TLS automatically for non-local connections. If February is ever upgraded to 11.4 or later, the auto-TLS behaviour kicks in and some of the manual configuration here becomes redundant. For 10.11, everything needs to be done explicitly.

Generating the certificate

MariaDB needs a CA certificate, a server certificate signed by that CA, and a server private key. For a localhost-only server with no external certificate authority in play, generating all three locally is the right approach.

Create a directory to hold the certificates:

sudo mkdir -p /etc/mysql/tls
sudo chown mysql:mysql /etc/mysql/tls
sudo chmod 750 /etc/mysql/tls

Generate the CA key and self-signed certificate:

cd /etc/mysql/tls

sudo openssl genrsa -out ca-key.pem 4096
sudo openssl req -new -x509 -days 3650   -key ca-key.pem   -out ca-cert.pem   -subj "/CN=MariaDB-CA/O=February"

Generate the server key and a certificate signing request:

sudo openssl genrsa -out server-key.pem 4096
sudo openssl req -new   -key server-key.pem   -out server-req.pem   -subj "/CN=february/O=February"

Sign the server certificate with the CA:

sudo openssl x509 -req -days 3650   -in server-req.pem   -CA ca-cert.pem   -CAkey ca-key.pem   -CAcreateserial   -out server-cert.pem

Set ownership so MariaDB can read the key files:

sudo chown mysql:mysql /etc/mysql/tls/*.pem
sudo chmod 640 /etc/mysql/tls/*.pem
sudo chmod 644 /etc/mysql/tls/ca-cert.pem
sudo chmod 644 /etc/mysql/tls/server-cert.pem

The CA certificate and server certificate are not secret and can be world-readable. The server key and CA key are private and should be readable only by the mysql user.

Configuring MariaDB to use the certificates

Add the TLS configuration to /etc/mysql/mariadb.conf.d/50-server.cnf under [mysqld]:

# TLS
ssl-ca   = /etc/mysql/tls/ca-cert.pem
ssl-cert = /etc/mysql/tls/server-cert.pem
ssl-key  = /etc/mysql/tls/server-key.pem
tls_version = TLSv1.2,TLSv1.3

tls_version explicitly restricts the server to TLS 1.2 and 1.3, disabling older protocol versions. TLS 1.0 and 1.1 are deprecated and should not be offered.

Restart MariaDB:

sudo systemctl restart mariadb

Confirm TLS is available:

sudo mariadb -e "SHOW VARIABLES LIKE 'have_ssl';"

The output should show have_ssl = YES. If it shows DISABLED, MariaDB could not load the certificates. Check the error log at /var/log/mysql/error.log for the reason, which is usually a file permission or path problem.

Confirm the TLS configuration is correct:

sudo mariadb -e "SHOW VARIABLES LIKE 'ssl_%';"

This should show the paths to the CA certificate, server certificate, and server key, and the TLS version restriction.

Testing a TLS connection

Connect explicitly over TLS to confirm it works:

sudo mariadb --ssl-ca=/etc/mysql/tls/ca-cert.pem   --ssl-verify-server-cert   -e "\s" | grep SSL

The output should include a line showing the TLS cipher in use, something like:

SSL: Cipher in use is TLS_AES_256_GCM_SHA384

If it shows SSL: Not in use, the client is connecting via Unix socket rather than TCP. Add -h 127.0.0.1 to force a TCP connection:

sudo mariadb -h 127.0.0.1   --ssl-ca=/etc/mysql/tls/ca-cert.pem   --ssl-verify-server-cert   -e "\s" | grep SSL

Enforcing TLS per user

With TLS available, enforce it for any application user that connects over TCP rather than the Unix socket. This is done by altering the user account to require SSL:

sudo mariadb -e "ALTER USER 'powerdns'@'localhost' REQUIRE SSL;"
sudo mariadb -e "ALTER USER 'pdnsadmin'@'localhost' REQUIRE SSL;"
sudo mariadb -e "FLUSH PRIVILEGES;"

After this change, those users can only connect over an encrypted connection. A connection attempt without TLS will be refused with an error. Connections via the Unix socket are considered secure transport and satisfy the REQUIRE SSL requirement without needing a certificate.

To confirm a user’s requirements:

sudo mariadb -e "SELECT user, host, ssl_type FROM mysql.user WHERE user IN ('powerdns', 'pdnsadmin');"

The ssl_type column should show ANY for users with REQUIRE SSL in effect.

Do not apply REQUIRE SSL to the root user. Root connects via unix_socket authentication, which is already secure and does not use the TCP path. Adding a TLS requirement to a unix_socket user creates confusion without adding security.

Distributing the CA certificate to clients

Applications connecting to MariaDB over TCP need the CA certificate to verify the server. Where to provide it depends on the application and its connector.

For Python applications using PyMySQL or mysqlclient:

connection = pymysql.connect(
    host='127.0.0.1',
    user='myapp',
    password='password',
    database='mydb',
    ssl={'ca': '/etc/mysql/tls/ca-cert.pem'}
)

For PHP applications:

$pdo = new PDO('mysql:host=127.0.0.1;dbname=mydb', 'myapp', 'password', [
    PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/tls/ca-cert.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
]);

For applications that read MariaDB configuration from ~/.my.cnf or /etc/mysql/mariadb.conf.d/, add a [client] section:

[client]
ssl-ca = /etc/mysql/tls/ca-cert.pem
ssl-verify-server-cert

Each application’s documentation will specify how to pass SSL options. The CA certificate path is the consistent requirement across all of them.

Certificate expiry

The certificates generated here have a ten-year lifetime, set by -days 3650. For a homelab server this is a pragmatic choice; renewing MariaDB TLS certificates annually adds maintenance overhead with limited security benefit for a server that only accepts localhost connections.

If February is ever extended to accept connections from outside the machine, the certificate lifetime should be reconsidered and a renewal reminder should be set. A MariaDB server offering expired certificates will generate connection errors that are easy to misdiagnose.

Add a note to the password manager entry for the MariaDB TLS certificates with the expiry date. The certificates themselves can be inspected at any time:

openssl x509 -in /etc/mysql/tls/server-cert.pem -noout -dates

The honest summary

TLS on a localhost-only MariaDB server is a belt-and-suspenders measure. The primary security comes from the network binding and user authentication. TLS adds encryption for TCP connections that would otherwise be unencrypted on the loopback interface, and it enforces an additional authentication layer for users configured with REQUIRE SSL.

For February as currently configured, the practical impact is modest. For February if it ever grows to accept connections from other machines, even over WireGuard, TLS goes from a nice-to-have to a requirement. Configuring it now costs an hour. Retrofitting it under pressure when a new service needs remote database access costs considerably more.