Description
Katrin Fischer
2024-12-20 10:33:46 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) Could someone add a test plan for this? 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. 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 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 (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). 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. 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> *** Bug 38759 has been marked as a duplicate of this bug. *** 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. (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 | +-----------------------+ (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! 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 -------------- 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> Bug description and test plan should be in the commit message. I have amended the patch before pushing. Pushed for 25.05! Well done everyone, thank you! |