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:

1. Enabling TLS
2. Enabling TLS with server certificate verification
3. 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.