https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Lukas Koszyk <lukasz.koszyk@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |lukasz.koszyk@kit.edu |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #1 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- Created attachment 178134 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=178134&action=edit Bug 38760: Add support for database connection encryption parameters in koha-mysql. Sponsored-by: Karlsruhe Institute of Technology (KIT) -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Magnus Enger <magnus@libriotech.no> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |magnus@libriotech.no --- Comment #2 from Magnus Enger <magnus@libriotech.no> --- Could someone add a test plan for this? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #4 from Magnus Enger <magnus@libriotech.no> --- I tried to follow the steps from https://wiki.koha-community.org/wiki/Securing_MariaDB_connections_in_Koha_us.... 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 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #6 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- (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). -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Jan Kissig <bibliothek@th-wildau.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #178134|0 |1 is obsolete| | --- Comment #8 from Jan Kissig <bibliothek@th-wildau.de> --- Created attachment 180739 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=180739&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- *** Bug 38759 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |domm@plix.at QA Contact|testopia@bugs.koha-communit |domm@plix.at |y.org | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #13 from Thomas Klausner <domm@plix.at> --- 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 -------------- -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #15 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Bug description and test plan should be in the commit message. I have amended the patch before pushing. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 --- Comment #16 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 25.05! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Needs documenting --- Comment #18 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11.x as it wasn't backported to 24.05.x -- You are receiving this mail because: You are watching all bug changes.