Bug 38760

Summary: koha-mysql doesn't work with encrypted database connection
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Command-line UtilitiesAssignee: Lukas Koszyk <lukasz.koszyk>
Status: Pushed to main --- QA Contact: Thomas Klausner <domm>
Severity: normal    
Priority: P5 - low CC: bibliothek, clemens.tubach, cornelius.amzar, dcook, domm, lukasz.koszyk, magnus, markus.john, michaela.sieber, raphael.straub, robin
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38759
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39591
GIT URL: Change sponsored?: Sponsored
Patch complexity: Small patch Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
25.05.00
Circulation function:
Attachments: Bug 38760: Add support for database connection encryption parameters in koha-mysql.
Bug 38760: Add support for database connection encryption parameters in koha-mysql.
Bug 38760: Add support for database connection encryption parameters in koha-mysql.

Description Katrin Fischer 2024-12-20 10:33:46 UTC
We noticed that the koha-mysql command doesn't send the right parameters when using an encrypted database connection. 

We are using the solution as outlined in bug 36026.

If you add a warn to see the connection command as created by koha-mysql, you will see something like:

mysql --host=<host> --user=XXX --password=XXX koha_XXX

The parameters required for the encrypted connection (--ssl and --ssl-ca) are missing/unsupported.
Comment 1 Lukas Koszyk 2025-02-17 08:45:45 UTC
Created attachment 178134 [details] [review]
Bug 38760: Add support for database connection encryption parameters in koha-mysql.

Sponsored-by: Karlsruhe Institute of Technology (KIT)
Comment 2 Magnus Enger 2025-03-12 07:38:23 UTC
Could someone add a test plan for this?
Comment 3 Lukas Koszyk 2025-03-26 13:18:00 UTC
Here's how to test the patch:

Testing Database Connection Encryption:
1. Apply the patch.
2. Configure koha-conf.xml to use a TLS connection
(see https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026)
3. Run:
koha-mysql <koha_instance>
4. Verify that the MySQL console opens successfully.
5. Check the encryption settings in the MySQL console:
- run:
SHOW VARIABLES LIKE '%ssl%';
- run:
STATUS;
Ensure that the following conditions are met:
- have_openssl and have_ssl are set to "YES".
- The SSL cipher in use is displayed.

Testing MySQL Timezone Configuration:
1. Apply the patch.
2. Configure the timezone in koha-conf.xml, for example:
<timezone>Antarctica/South_Pole</timezone>
3. Run:
koha-mysql <koha_instance>
4. Verify that the MySQL console opens successfully.
5. Check the timezone setting in the MySQL console by running:
SELECT @@session.time_zone;
6. Confirm that the output matches the timezone configured in koha-conf.xml.
Comment 4 Magnus Enger 2025-04-02 09:32:09 UTC
I tried to follow the steps from https://wiki.koha-community.org/wiki/Securing_MariaDB_connections_in_Koha_using_a_separate_database_server. Should the steps under "Enabling TLS" be enough, or do we have to do all of them ("Enabling TLS with server certificate verification" and "Enabling TLS with both server and client certificate verification")? 

I created the certs* and added the config** in /etc/mysql/mariadb.conf.d/50-ssl.cnf, then restarted the db container:

$ docker restart kohadev-db-1

When I log into the db I see this:

$ ktd --dbshell

MariaDB [koha_kohadev]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| have_openssl        | YES                                   |
| have_ssl            | YES                                   |
| ssl_ca              | /etc/mysql/ssl/certs/ca-cert.pem      |
| ssl_capath          |                                       |
| ssl_cert            | /etc/mysql/ssl/certs/server-cert.pem  |
| ssl_cipher          |                                       |
| ssl_crl             |                                       |
| ssl_crlpath         |                                       |
| ssl_key             | /etc/mysql/ssl/private/server-key.pem |
| version_ssl_library | OpenSSL 1.1.1f  31 Mar 2020           |
+---------------------+---------------------------------------+
10 rows in set (0.000 sec)

MariaDB [koha_kohadev]> STATUS;
--------------
mysql  Ver 15.1 Distrib 10.5.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		10
Current database:	koha_kohadev
Current user:		koha_kohadev@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.5.28-MariaDB-ubu2004 mariadb.org binary distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/run/mysqld/mysqld.sock
Uptime:			1 min 3 sec

Threads: 2  Questions: 284  Slow queries: 0  Opens: 294  Open tables: 287  Queries per second avg: 4.507


This looks OK I think? 

| have_openssl        | YES 
| have_ssl            | YES  

But this looks wrong: 

SSL:			Not in use

When i run "sudo koha-mysql kohadev" in the Koha container it connects without problems. 

Any idea what I am doing wrong? 

------------------------------------------------------------------------------------

* = Adding the certs

root@8aa3a057cfea:/# mkdir -p /etc/mysql/ssl/{certs,private}
root@8aa3a057cfea:/# openssl genrsa 4096 > /etc/mysql/ssl/private/ca-key.pem
Generating RSA private key, 4096 bit long modulus (2 primes)
............................................................................................++++
.....................................................................................................................................................................++++
e is 65537 (0x010001)
root@8aa3a057cfea:/# 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"
root@8aa3a057cfea:/# 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"
Ignoring -days; not generating a certificate
Generating a RSA private key
..................................................................................................................................................++++
.................................................................++++
writing new private key to '/etc/mysql/ssl/private/server-key.pem'
-----
root@8aa3a057cfea:/# openssl rsa -in /etc/mysql/ssl/private/server-key.pem -out /etc/mysql/ssl/private/server-key.pem
writing RSA key
root@8aa3a057cfea:/# 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
Signature ok
subject=C = US, ST = State, L = City, O = Organization, OU = Unit, CN = db
Getting CA Private Key
root@8aa3a057cfea:/# chown -Rv mysql:root /etc/mysql/ssl/
changed ownership of '/etc/mysql/ssl/private/ca-key.pem' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/private/server-key.pem' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/private' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/certs/server-req.pem' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/certs/ca-cert.pem' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/certs/server-cert.pem' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/certs' from root:root to mysql:root
changed ownership of '/etc/mysql/ssl/' from root:root to mysql:root

** Config in /etc/mysql/mariadb.conf.d/50-ssl.cnf

root@8aa3a057cfea:/# cat /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
Comment 5 Magnus Enger 2025-04-02 09:40:16 UTC
Oops. While koha-mysql is working, the Web interface is not:

==> /var/log/koha/kohadev/plack-error.log <==
DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: DBI connect('database=koha_kohadev;host=db;port=3306;mysql_ssl=1','koha_kohadev',...) failed: SSL connection error: Enforcing SSL encryption is not supported at /kohadevbox/koha/Koha/Database.pm line 75. at /kohadevbox/koha/Koha/Database.pm line 125
Comment 6 Lukas Koszyk 2025-04-02 14:32:03 UTC
(In reply to Magnus Enger from comment #5)
> Oops. While koha-mysql is working, the Web interface is not:
> 
> ==> /var/log/koha/kohadev/plack-error.log <==
> DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: DBI
> connect('database=koha_kohadev;host=db;port=3306;mysql_ssl=1','koha_kohadev',
> ...) failed: SSL connection error: Enforcing SSL encryption is not supported
> at /kohadevbox/koha/Koha/Database.pm line 75. at
> /kohadevbox/koha/Koha/Database.pm line 125

Unfortunately, there are some issues with the database TLS configuration in koha-testing-docker. I have described this in https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026#c14

As a workaround, you can add "mysql_ssl_optional=1" in Koha/Database.pm. See https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026#c15, point (g).
Comment 7 Michaela Sieber 2025-04-09 08:19:15 UTC
I try to rephrase and make things a little bit clearer...

Please note: this testplan is for ktd, not for a production environment!

Unfortunately, there are some issues with the database TLS configuration in koha-testing-docker. 
This is described comment 14 of bug 36026 (https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026#c14)

As a workaround, you can add "mysql_ssl_optional=1" in Koha/Database.pm. 
See point (g) in comment 15 of bug 36026 (https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026#c15 ).

Here's how to test the patch attached to this bug 38760:

Testing Database Connection Encryption:
1. Apply the patch.
2. Configure koha-conf.xml to use a TLS connection
(see comment 10 https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026#c10, follow steps 3a-g + 4 )
3. Run:
koha-mysql <koha_instance>
4. Verify that the MySQL console opens successfully.
5. Check the encryption settings in the MySQL console:
- run:
SHOW VARIABLES LIKE '%ssl%';
- run:
STATUS;
Ensure that the following conditions are met:
- have_openssl and have_ssl are set to "YES".
- The SSL cipher in use is displayed.

Testing MySQL Timezone Configuration:
1. Apply the patch.
2. Configure the timezone in koha-conf.xml, for example:
<timezone>Antarctica/South_Pole</timezone>
3. Run:
koha-mysql <koha_instance>
4. Verify that the MySQL console opens successfully.
5. Check the timezone setting in the MySQL console by running:
SELECT @@session.time_zone;
6. Confirm that the output matches the timezone configured in koha-conf.xml.
Comment 8 Jan Kissig 2025-04-09 10:04:13 UTC
Created attachment 180739 [details] [review]
Bug 38760: Add support for database connection encryption parameters in koha-mysql.

Sponsored-by: Karlsruhe Institute of Technology (KIT)
Signed-off-by: Jan Kissig <bibliothek@th-wildau.de>
Comment 9 Katrin Fischer 2025-04-09 10:50:14 UTC
*** Bug 38759 has been marked as a duplicate of this bug. ***
Comment 10 Katrin Fischer 2025-04-09 10:53:13 UTC
Thanks a ton for the sign-off, Jan!
I wonder if you have the setup still and could also test if it works if you have a timezone configured in your koha-conf.xml? (we need this because of the Goethe libraries). See bug 38759.
Comment 11 Jan Kissig 2025-04-09 11:05:11 UTC
(In reply to Katrin Fischer from comment #10)
> Thanks a ton for the sign-off, Jan!
> I wonder if you have the setup still and could also test if it works if you
> have a timezone configured in your koha-conf.xml? (we need this because of
> the Goethe libraries). See bug 38759.

Tested also for timezone, which was set to 

 <timezone>Antarctica/South_Pole</timezone>

in koha-conf.xml.

Then running

koha-mysql kohadev 
...

MariaDB [koha_kohadev]> SELECT @@session.time_zone;
+-----------------------+
| @@session.time_zone   |
+-----------------------+
| Antarctica/South_Pole |
+-----------------------+
Comment 12 Katrin Fischer 2025-04-09 11:36:35 UTC
(In reply to Jan Kissig from comment #11)
> (In reply to Katrin Fischer from comment #10)
> > Thanks a ton for the sign-off, Jan!
> > I wonder if you have the setup still and could also test if it works if you
> > have a timezone configured in your koha-conf.xml? (we need this because of
> > the Goethe libraries). See bug 38759.
> 
> Tested also for timezone, which was set to 
> 
>  <timezone>Antarctica/South_Pole</timezone>
> 
> in koha-conf.xml.
> 
> Then running
> 
> koha-mysql kohadev 
> ...
> 
> MariaDB [koha_kohadev]> SELECT @@session.time_zone;
> +-----------------------+
> | @@session.time_zone   |
> +-----------------------+
> | Antarctica/South_Pole |
> +-----------------------+

Awesome!
Comment 13 Thomas Klausner 2025-04-13 19:44:04 UTC
I followed the test plan and it worked!

* Timezone settings work, I also see the correct timezone in the ktd-staff interface (if I patch Koha::Database to ignore the cert)
* SHOW VARIABLES LIKE '%ssl%':
  * have_openssl and have_ssl are set to "YES"
  * The SSL cipher in use is NOT displayed (but I think the test plan is a bit unclear here)
* STATUS:
  * Here I see the cipher in use:  
  * SSL: Cipher in use is TLS_AES_256_GCM_SHA384

Yay!

Here's the output of the two SQL commands:


MariaDB [koha_kohadev]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| have_openssl        | YES                                   |
| have_ssl            | YES                                   |
| ssl_ca              | /etc/mysql/ssl/certs/ca-cert.pem      |
| ssl_capath          |                                       |
| ssl_cert            | /etc/mysql/ssl/certs/server-cert.pem  |
| ssl_cipher          |                                       |
| ssl_crl             |                                       |
| ssl_crlpath         |                                       |
| ssl_key             | /etc/mysql/ssl/private/server-key.pem |
| version_ssl_library | OpenSSL 1.1.1f  31 Mar 2020           |
+---------------------+---------------------------------------+


MariaDB [koha_kohadev]> status;
--------------
mysql  Ver 15.1 Distrib 10.5.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		73
Current database:	koha_kohadev
Current user:		koha_kohadev@localhost
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.5.28-MariaDB-ubu2004 mariadb.org binary distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/run/mysqld/mysqld.sock
Uptime:			28 min 2 sec

Threads: 9  Questions: 2963  Slow queries: 0  Opens: 304  Open tables: 297  Queries per second avg: 1.761
--------------
Comment 14 Thomas Klausner 2025-04-13 19:47:58 UTC
Created attachment 180892 [details] [review]
Bug 38760: Add support for database connection encryption parameters in koha-mysql.

Sponsored-by: Karlsruhe Institute of Technology (KIT)
Signed-off-by: Jan Kissig <bibliothek@th-wildau.de>
Signed-off-by: Thomas Klausner <domm@plix.at>
Comment 15 Katrin Fischer 2025-04-22 06:39:27 UTC
Bug description and test plan should be in the commit message. I have amended the patch before pushing.
Comment 16 Katrin Fischer 2025-04-22 06:49:40 UTC
Pushed for 25.05!

Well done everyone, thank you!