Securing MariaDB connections in Koha using a separate database server

This guide outlines the steps to add database certificates and enable secure connections in Koha.
The process includes:
#Enabling TLS
#Enabling TLS with server certificate verification
#Enabling TLS with both server and client certificate verification
Assumptions:
*   The external database server is already installed, and Koha is running without secure connections.
*   The following steps are based on Koha 24.11, Ubuntu 24.04 and MariaDB Server 10.11.
== Enabling TLS ==
=== Steps on the Database Server ===
1. Create certificates and keys:
mkdir -p /etc/mysql/ssl/{certs,private}
openssl genrsa 4096 > /etc/mysql/ssl/private/ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key /etc/mysql/ssl/private/ca-key.pem \
 -out /etc/mysql/ssl/certs/ca-cert.pem \
 -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=MariaDB_CA"
openssl req -newkey rsa:4096 -days 3650 -nodes -keyout /etc/mysql/ssl/private/server-key.pem \
 -out /etc/mysql/ssl/certs/server-req.pem \
 -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=db"
openssl rsa -in /etc/mysql/ssl/private/server-key.pem -out /etc/mysql/ssl/private/server-key.pem
openssl x509 -req -in /etc/mysql/ssl/certs/server-req.pem -days 3650 \
 -CA /etc/mysql/ssl/certs/ca-cert.pem -CAkey /etc/mysql/ssl/private/ca-key.pem \
 -set_serial 01 -out /etc/mysql/ssl/certs/server-cert.pem
chown -Rv mysql:root /etc/mysql/ssl/
2. Configure MariaDB to require TLS: Add the following to /etc/mysql/mariadb.conf.d/50-ssl.cnf:
[mariadb]
ssl_ca=/etc/mysql/ssl/certs/ca-cert.pem
ssl_cert=/etc/mysql/ssl/certs/server-cert.pem
ssl_key=/etc/mysql/ssl/private/server-key.pem
require_secure_transport = on
3. Restart MariaDB:
systemctl restart mariadb.service
Verification:
* From the bash shell:
mysql
Then, run:
SHOW VARIABLES LIKE '%ssl%';
STATUS;
You should see that "open_ssl" and "have_ssl" are active and SSL shows the cipher in use.
=== Steps on the Koha Application Server ===
1. Enable TLS in the Koha configuration: Update <config> in koha-conf.xml:
<tls>yes</tls>
2. Restart Koha:
systemctl restart koha-common.service
Testing:
* Log in to the Koha staff interface.
* Verify the secure connection via bash:
mariadb -u <database_username> -h <database_server> -p <database_name>
Then, run:
SHOW VARIABLES LIKE '%ssl%';
STATUS;
== Enabling TLS with server certificate verification ==
1. Complete Step 1: Enabling TLS.
2. On the Koha Application Server:
* Create a directory for certificates:
mkdir -p /etc/mysql/ssl
3. On the Database Server:
* Copy the MariaDB server certificate to the Koha server:
scp /etc/mysql/ssl/certs/ca-cert.pem root@<koha_application_server>:/etc/mysql/ssl/
4. On the Koha Application Server:
* Update <config> in koha-conf.xml:
<ca>/etc/mysql/ssl/ca-cert.pem</ca>
* Restart Koha:
systemctl restart koha-common.service
Testing:
* Log in to the Koha staff interface.
* Verify the secure connection with the server certificate:
mariadb -u <database_username> -h <database_server> -p <database_name> --ssl-ca=/etc/mysql/ssl/ca-cert.pem
Then, run:
SHOW VARIABLES LIKE '%ssl%';
STATUS;
You should see that "open_ssl" and "have_ssl" are active and SSL shows the cipher in use.
== Enabling TLS with both server and client certificate verification ==
1. Complete Step 2: Enabling TLS with server certificate verification.
2. On the Database Server:
* Create the client certificate:
openssl req -newkey rsa:4096 -days 3650 -nodes -keyout /etc/mysql/ssl/private/client-key.pem \
 -out /etc/mysql/ssl/certs/client-req.pem \
 -subj "/C=US/ST=State/L=City/O=Organization/OU=Unit/CN=MariaDB_User"
openssl rsa -in /etc/mysql/ssl/private/client-key.pem -out /etc/mysql/ssl/private/client-key.pem
openssl x509 -req -in /etc/mysql/ssl/certs/client-req.pem -days 3650 \
 -CA /etc/mysql/ssl/certs/ca-cert.pem -CAkey /etc/mysql/ssl/private/ca-key.pem \
 -set_serial 01 -out /etc/mysql/ssl/certs/client-cert.pem
* Copy the client certificate and key to the Koha server:
scp /etc/mysql/ssl/certs/client-cert.pem root@<koha_application_server>:/etc/mysql/ssl/
scp /etc/mysql/ssl/private/client-key.pem root@<koha_application_server>:/etc/mysql/ssl/
3. On the Koha Application Server:
* Update permissions:
chmod 640 /etc/mysql/ssl/client-key.pem
chown root:<koha_group> /etc/mysql/ssl/client-key.pem
* Update <config> in koha-conf.xml:
<cert>/etc/mysql/ssl/client-cert.pem</cert>
<key>/etc/mysql/ssl/client-key.pem</key>
* Restart Koha:
systemctl restart koha-common.service
Testing:
*    Log in to the Koha staff interface.
*    Verify the secure connection with both server and client certificates:
mariadb -u <database_username> -h <database_server> -p <database_name> \
 --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
 --ssl-cert=/etc/mysql/ssl/client-cert.pem \
 --ssl-key=/etc/mysql/ssl/client-key.pem
Then, run:
SHOW VARIABLES LIKE '%ssl%';
STATUS;
You should see that "open_ssl" and "have_ssl" are active and SSL shows the cipher in use.
Category:Documentation
Category:Installation