[Bug 36026] New: Need TLS MySQL Connection Without Mutual Authentication
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Bug ID: 36026 Summary: Need TLS MySQL Connection Without Mutual Authentication Change sponsored?: --- Product: Koha Version: 22.11 Hardware: All OS: Linux Status: NEW Severity: enhancement Priority: P5 - low Component: Authentication Assignee: koha-bugs@lists.koha-community.org Reporter: rschet@sandia.gov QA Contact: testopia@bugs.koha-community.org CC: dpavlin@rot13.org We’ve successfully connected our Koha site (version 22.11.12.000) to an Azure Database for MySQL flexible server without TLS encryption, but we’ve been unable to connect to the Azure MySQL database with TLS encryption enabled and required. The reason for this appears to be that Koha seems to require mutual TLS, which is not supported by the Azure MySQL database. When connecting to MySQL on Azure, TLS clients use a public SSL CA certificate to allow for encrypted communication, and clients are authenticated at the server by usernames and passwords. But in Koha, the koha-conf.xml configuration file calls for a CA certificate and also for client certificate and client key for client authentication with mutual TLS. This works for a local MySQL database but not for a remote Azure MySQL database because the Azure MySQL database does not provide a way to configure the CA certificate, server public key certificate, and server private key, which must be configured correctly for mutual TLS to work. We need a way to connect to a remote MySQL database with TLS through the use of a CA certificate for encryption and username and password for authentication and without mutual TLS (that is, without the use of certificates and keys for authentication). -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #1 from David Cook <dcook@prosentient.com.au> --- I just took a look at Koha/Database.pm and I see what you mean: my $tls = $config->get("tls"); if ($tls && $tls eq 'yes') { $dsn .= sprintf( ';mysql_ssl=1;mysql_ssl_client_key=%s;mysql_ssl_client_cert=%s;mysql_ssl_ca_file=%s', $config->get('key'), $config->get('cert'), $config->get('ca'), ); } It would take a bit of digging to figure out why it was set up this way originally, but it wouldn't be too hard to change this, so that the client authentication was optional/separate. My plate is a bit full at the moment, but if someone wanted to take this on... they'd need to make a Koha::Database::generate_dsn type function and unit test it with no tls, tls with no client auth, tls with client auth. If they make the $config object a parameter of the function, then it would be really easy to unit test. Ideally, someone would also do an integration test with a MySQL using SSL. It would be interesting to add that to the Jenkins CI actually. If no else does a patch, I could come back to it, but it might be a few weeks or longer. I think this is certainly a worthwhile one to do though. -- 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=36026 --- Comment #2 from rschet@sandia.gov --- Great, thank you! -R -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Lukas Koszyk <lukasz.koszyk@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lukasz.koszyk@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Need TLS MySQL Connection |Add TLS MySQL connection |Without Mutual |without mutual |Authentication |authentication CC| |cornelius.amzar@bsz-bw.de, | |michaela.sieber@kit.edu Version|22.11 |Main -- 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=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |markus.john@bsz-bw.de -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #3 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- Created attachment 172587 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172587&action=edit Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. To test: 1) Configure the database to support TLS connections. 2) Set "<tls>yes</tls>" in the config section in koha-conf.xml. 3) Run "koha-plack --reload <koha_instance>". 4) Open Koha's staff interface in the browser. 5) Observe an internal server error. 6) Apply patch. 7) Repeat step 3 and 4. 8) Observe the error is gone. 9) Sign off. Sponsored-by: Karlsruhe Institute of Technology (KIT) -- 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=36026 Lukas Koszyk <lukasz.koszyk@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Change sponsored?|--- |Sponsored Patch complexity|--- |Small patch Status|NEW |Needs Signoff -- 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=36026 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com, | |jonathan.druart@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> 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=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172587|0 |1 is obsolete| | --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 172822 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172822&action=edit Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. To test: 1) Configure the database to support TLS connections. 2) Set "<tls>yes</tls>" in the config section in koha-conf.xml. 3) Run "koha-plack --reload <koha_instance>". 4) Open Koha's staff interface in the browser. 5) Observe an internal server error. 6) Apply patch. 7) Repeat step 3 and 4. 8) Observe the error is gone. 9) Sign off. Sponsored-by: Karlsruhe Institute of Technology (KIT) Signed-off-by: Cornelius Amzar <cornelius.amzar@bsz-bw.de> Signed-off-by: Markus John <markus.john@bsz-bw.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #5 from cornelius.amzar@bsz-bw.de --- Tested connection, it works! Before, we were unable to connect to a remote mariadb server who requires TLS without having a key and cert pair for the client. With the patch we were able to connect to the server using a CA cert only but key and cert left blank. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Michaela Sieber <michaela.sieber@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |clemens.tubach@kit.edu, | |raphael.straub@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |domm@plix.at --- Comment #6 from Thomas Klausner <domm@plix.at> --- I tried to QA this patch according to the test plan, but 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 ``` But I assume this failure is caused by the fact that KTD mysql does not have SSL configured. At least now the three SSL opts are not included in DSN with empty values: `mysql_ssl_client_key=;mysql_ssl_client_cert=;mysql_ssl_ca_file=` So I assume this works, but cannot properly test it without a TSL-enabled MySQL server. Is there a way to set this up in KTD? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=36026 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I asked Tomas and there seems to be no easy or ready-set-up way to test this with ktd. I know that KIT runs this in production and we also have tested the change on our servers thoroughly, but not in a development environment. Maybe we or KIT could share some more information about setting up the encrypted database connection in general? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Maybe this could be helpful? https://peterkellner.net/2023/08/02/Enforcing-SSL-Connections-in-MySql-Using... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #9 from Michaela Sieber <michaela.sieber@kit.edu> --- As proposed by David we will make the Koha::Database::generate_dsn type function and the unit test with no tls, tls with no client auth, tls with client auth. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Lukas Koszyk <lukasz.koszyk@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172822|0 |1 is obsolete| | --- Comment #10 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- Created attachment 173381 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=173381&action=edit Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. To test: 1) Apply patch. 2) Run "prove -v t/db_dependent/Koha/Database.t" to check if the new function generate_dsn returns correct DSNs. 3) Use KTD to verify that the TLS DB connection actually works: a) Open shell in database container "docker exec -it kohadev-db-1 /bin/bash" b) 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/ c) Configure the MariaDB server to require TLS: echo " [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 " > /etc/mysql/mariadb.conf.d/50-ssl.cnf d) Restart MariaDB: kill 1 e) Switch to Koha container: ktd --shell f) Set "<tls>yes</tls>" in the <config> section in koha-conf.xml. g) In Koha/Database.pm: replace ";mysql_ssl=1" by ";mysql_ssl=1;mysql_ssl_optional=1" 4) Run restart_all. 5) Check that Koha's staff interface works. 6) Sign off. 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=36026 Lukas Koszyk <lukasz.koszyk@kit.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #173381|0 |1 is obsolete| | --- Comment #11 from David Nind <david@davidnind.com> --- Created attachment 173426 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=173426&action=edit Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. To test: 1) Apply patch. 2) Run "prove -v t/db_dependent/Koha/Database.t" to check if the new function generate_dsn returns correct DSNs. 3) Use KTD to verify that the TLS DB connection actually works: a) Open shell in database container "docker exec -it kohadev-db-1 /bin/bash" b) 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/ c) Configure the MariaDB server to require TLS: echo " [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 " > /etc/mysql/mariadb.conf.d/50-ssl.cnf d) Restart MariaDB: kill 1 e) Switch to Koha container: ktd --shell f) Set "<tls>yes</tls>" in the <config> section in koha-conf.xml. g) In Koha/Database.pm: replace ";mysql_ssl=1" by ";mysql_ssl=1;mysql_ssl_optional=1" 4) Run restart_all. 5) Check that Koha's staff interface works. 6) Sign off. Sponsored-by: Karlsruhe Institute of Technology (KIT) Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #12 from David Nind <david@davidnind.com> --- Thanks for providing the test plan! (This made it easy to sign off.) Could you also please add a release note? In addition, (once it passes QA) could you add a comment with instructions on how to set this up/what this means. Once it is pushed, I will add the instructions to either the Wiki or manual (probably the Wiki). Testing notes (using KTD): 1. Commands on one line to avoid any confusion for test plan step 3(b): 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. For test plan step 3(f) - this is around line 265 (there are other <config> sections in the file). 3. For test plan step 3(g) - this is at line 209. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |RM_priority -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_24_11_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl --- Comment #13 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Lukas Koszyk from comment #10)
f) Set "<tls>yes</tls>" in the <config> section in koha-conf.xml.
g) In Koha/Database.pm: replace ";mysql_ssl=1" by ";mysql_ssl=1;mysql_ssl_optional=1"
4) Run restart_all.
5) Check that Koha's staff interface works.
6) Sign off.
Could you please clarify this part of the test plan? We only set tls to yes, but do not fill the other stuff in koha-conf. We switch to ssl optional by changing the code we should be testing.. So we are actually testing something else? And signing off the original? And note: Setting mysql_ssl_optional to true disables strict SSL enforcement and makes SSL connection optional. This option opens security hole for man-in-the-middle attacks. => Not a good idea? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #14 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- (In reply to Marcel de Rooy from comment #13)
(In reply to Lukas Koszyk from comment #10)
f) Set "<tls>yes</tls>" in the <config> section in koha-conf.xml.
g) In Koha/Database.pm: replace ";mysql_ssl=1" by ";mysql_ssl=1;mysql_ssl_optional=1"
4) Run restart_all.
5) Check that Koha's staff interface works.
6) Sign off.
Could you please clarify this part of the test plan?
We only set tls to yes, but do not fill the other stuff in koha-conf. We switch to ssl optional by changing the code we should be testing.. So we are actually testing something else? And signing off the original?
And note: Setting mysql_ssl_optional to true disables strict SSL enforcement and makes SSL connection optional. This option opens security hole for man-in-the-middle attacks. => Not a good idea?
Yes, the mysql_ssl_optional parameter is not part of the patch to sign off. The additional parameter "mysql_ssl_optional=1" is added only to test in Koha-testing-docker. Without it we couldn't test in docker, probably there is a difference in the packages installed in docker. On our test system with MariaDB 10.6 on Ubuntu 22.04, the TLS connection works without this parameter. Also, I think mysql_ssl_optional should not be used on a production system. But even if this option is enabled for the client and if require_secure_transport is enabled on the MariaDB server then all connections attempted using insecure transport will be rejected. This test only checks if the TLS connection works. Additional parameters like ca certificate can be used (you need to copy the certificate first) but we didn't want to create too complicated configuration. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #173426|0 |1 is obsolete| | --- Comment #15 from Thomas Klausner <domm@plix.at> --- Created attachment 174249 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=174249&action=edit Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. To test: 1) Apply patch. 2) Run "prove -v t/db_dependent/Koha/Database.t" to check if the new function generate_dsn returns correct DSNs. 3) Use KTD to verify that the TLS DB connection actually works: a) Open shell in database container "docker exec -it kohadev-db-1 /bin/bash" b) 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/ c) Configure the MariaDB server to require TLS: echo " [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 " > /etc/mysql/mariadb.conf.d/50-ssl.cnf d) Restart MariaDB: kill 1 e) Switch to Koha container: ktd --shell f) Set "<tls>yes</tls>" in the <config> section in koha-conf.xml. g) In Koha/Database.pm: replace ";mysql_ssl=1" by ";mysql_ssl=1;mysql_ssl_optional=1" 4) Run restart_all. 5) Check that Koha's staff interface works. 6) Sign off. Sponsored-by: Karlsruhe Institute of Technology (KIT) Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Thomas Klausner <domm@plix.at> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Thomas Klausner <domm@plix.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Passed QA --- Comment #16 from Thomas Klausner <domm@plix.at> --- Thanks to the excellent test plan I could now easily generate certs/keys for MySQL and thus verify that everything works! The qa-script also worked, but it could not run the tests because either it failed due to the needed addition of `mysql_ssl_optional=1` or because the certs/keys where missing. But as the tests work when NOT including the workaround to actually try out the certs/keys; and as I manually checked that the DB connection works when using certs/keys, I declare this feature "Passed QA" :-) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #17 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Thomas Klausner from comment #16)
Thanks to the excellent test plan I could now easily generate certs/keys for MySQL and thus verify that everything works!
The qa-script also worked, but it could not run the tests because either it failed due to the needed addition of `mysql_ssl_optional=1` or because the certs/keys where missing. But as the tests work when NOT including the workaround to actually try out the certs/keys; and as I manually checked that the DB connection works when using certs/keys, I declare this feature "Passed QA" :-)
Did you see my comment actually before your declaration? :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #18 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Marcel de Rooy from comment #17)
(In reply to Thomas Klausner from comment #16)
Thanks to the excellent test plan I could now easily generate certs/keys for MySQL and thus verify that everything works!
The qa-script also worked, but it could not run the tests because either it failed due to the needed addition of `mysql_ssl_optional=1` or because the certs/keys where missing. But as the tests work when NOT including the workaround to actually try out the certs/keys; and as I manually checked that the DB connection works when using certs/keys, I declare this feature "Passed QA" :-)
Did you see my comment actually before your declaration? :)
Did you check on Lukas reply? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #19 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Katrin Fischer from comment #18)
(In reply to Marcel de Rooy from comment #17)
(In reply to Thomas Klausner from comment #16)
Thanks to the excellent test plan I could now easily generate certs/keys for MySQL and thus verify that everything works!
The qa-script also worked, but it could not run the tests because either it failed due to the needed addition of `mysql_ssl_optional=1` or because the certs/keys where missing. But as the tests work when NOT including the workaround to actually try out the certs/keys; and as I manually checked that the DB connection works when using certs/keys, I declare this feature "Passed QA" :-)
Did you see my comment actually before your declaration? :)
Did you check on Lukas reply?
Sure. I understand the reasoning behind it, but it does not change my former comment actually. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #20 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Marcel, you pointed out some concerns with the patch and you say that Lukas' comment didn't solve the problem for you. What do you suggest to change? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #21 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Katrin Fischer from comment #20)
Marcel, you pointed out some concerns with the patch and you say that Lukas' comment didn't solve the problem for you. What do you suggest to change?
Hi Katrin. No thats not what I said. Please see comment13. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #22 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This looks like a communication problem to me at this point and I try to resolve. Re-reading your comment doesn't give me any new clue. Could you try to re-phrase? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #23 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Katrin Fischer from comment #22)
This looks like a communication problem to me at this point and I try to resolve. Re-reading your comment doesn't give me any new clue. Could you try to re-phrase?
We are not testing the actual code in the test plan. We are testing the optional flag which is actually not safe. The test plan should focus on testing the proposed changes, fully using TLS. Code looks good to me btw. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #24 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Marcel de Rooy from comment #23)
(In reply to Katrin Fischer from comment #22)
This looks like a communication problem to me at this point and I try to resolve. Re-reading your comment doesn't give me any new clue. Could you try to re-phrase?
We are not testing the actual code in the test plan. We are testing the optional flag which is actually not safe. The test plan should focus on testing the proposed changes, fully using TLS.
Code looks good to me btw.
So the issue with the test plan might be due to problems in making things work with koha-testing-docker. :( We have been testing this patch on a package installation both at KIT and BSZ for a while now. Could the other concern be moved to a separate bug since this patch didn't introduce the flag? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #25 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Katrin Fischer from comment #24)
(In reply to Marcel de Rooy from comment #23)
(In reply to Katrin Fischer from comment #22)
This looks like a communication problem to me at this point and I try to resolve. Re-reading your comment doesn't give me any new clue. Could you try to re-phrase?
We are not testing the actual code in the test plan. We are testing the optional flag which is actually not safe. The test plan should focus on testing the proposed changes, fully using TLS.
Code looks good to me btw.
So the issue with the test plan might be due to problems in making things work with koha-testing-docker. :( We have been testing this patch on a package installation both at KIT and BSZ for a while now.
Could the other concern be moved to a separate bug since this patch didn't introduce the flag?
If this is thoroughly tested, I would recommend to record that on the commit message and remove/edit the current test plan which makes not much sense. Put some sign-off lines on it that really count. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #26 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- SUGGESTION * Re-added the BSZ sign-off lines since these got lost at some step. Code wasn't changed, so they are still valid. * Reverted to initial test plan that was not targetted at ktd testing Is this acceptable? ---- Bug 36026: Use only configured TLS options for database connection Database connections with TLS require client private keys and certificates for authentication but MariaDB also supports authentication by user and password. This patch allows omitting the TLS options for certificate based client authentication. This is not easily testable with a development installation. But the patch has been verified by different parties on production and testing systems installed with Debian packages. 0) Apply patch. 1) Configure the database to support TLS connections. 2) Set "<tls>yes</tls>" in the config section in koha-conf.xml. 3) Run "koha-plack --reload <koha_instance>". 4) Open Koha's staff interface in the browser. 5) Observe an internal server error. 6) Apply patch. 7) Repeat step 3 and 4. 8) Observe the error is gone. 9) Run "prove -v t/db_dependent/Koha/Database.t" Sponsored-by: Karlsruhe Institute of Technology (KIT) Signed-off-by: Cornelius Amzar <cornelius.amzar@bsz-bw.de> Signed-off-by: Markus John <markus.john@bsz-bw.de> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Thomas Klausner <domm@plix.at> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #27 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Go for it -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #28 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Marcel de Rooy from comment #27)
Go for it
Thanks! Will fix commit message as suggested once I get there. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |24.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #29 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 24.11! 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=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_24_11_candidate, |release-notes-needed |RM_priority | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|release-notes-needed | Text to go in the| |Database connections with release notes| |TLS require client private | |keys | |and certificates for | |authentication but MariaDB | |also supports | |authentication by user and | |password. | |This enhancement | |allows omitting the TLS | |options for certificate | |based client | |authentication. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com Status|Pushed to main |Needs documenting --- Comment #30 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Enhancement, no 24.05.x backport. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=38760 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #31 from Lukas Koszyk <lukasz.koszyk@kit.edu> --- I have added a manual to the Koha Wiki on how to configure MariaDB and Koha on a separate database server to use secure connections: https://wiki.koha-community.org/wiki/Securing_MariaDB_connections_in_Koha_us... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #32 from David Nind <david@davidnind.com> --- (In reply to Lukas Koszyk from comment #31)
I have added a manual to the Koha Wiki on how to configure MariaDB and Koha on a separate database server to use secure connections:
https://wiki.koha-community.org/wiki/ Securing_MariaDB_connections_in_Koha_using_a_separate_database_server
Thanks Lukas! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #33 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to David Nind from comment #32)
(In reply to Lukas Koszyk from comment #31)
I have added a manual to the Koha Wiki on how to configure MariaDB and Koha on a separate database server to use secure connections:
https://wiki.koha-community.org/wiki/ Securing_MariaDB_connections_in_Koha_using_a_separate_database_server
Thanks Lukas!
Very nice, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the|Database connections with |Database connections with release notes|TLS require client private |TLS require client private |keys |keys and certificates for |and certificates for |authentication, but MariaDB |authentication but MariaDB |also supports |also supports |authentication by user and |authentication by user and |password. |password. | |This enhancement |This enhancement |allows omitting the TLS |allows omitting the TLS |options for certificate |options for certificate |based client |based client |authentication. |authentication. | | | |Information on configuring: | |https://wiki.koha-community | |.org/wiki/Securing_MariaDB_ | |connections_in_Koha_using_a | |_separate_database_server -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=39591 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=39602 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 --- Comment #34 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- We just figured out that koha-create doesn't work still. So you will need to set up the encryption **after** setting up the instance. I've filed 2 bugs: - bug 38760 - the koha-create command has no options for setting up an encrypted connection from the start - Bug 39602 - Installer doesn't work with TLS MySQL connection without mutual authentication -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |39591 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39591 [Bug 39591] koha-create needs options to set up encrypted database connections -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |41862 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41862 [Bug 41862] Encrypted database connection breaks with update to Debian 12 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org