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).
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.
Great, thank you! -R
Created attachment 172587 [details] [review] 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)
Created attachment 172822 [details] [review] 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>
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.
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?
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?
Maybe this could be helpful? https://peterkellner.net/2023/08/02/Enforcing-SSL-Connections-in-MySql-Using-Docker/
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.
Created attachment 173381 [details] [review] 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)
Created attachment 173426 [details] [review] 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>
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.
(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?