From 326360703b2144ab3094db795043115490a616aa Mon Sep 17 00:00:00 2001 From: Lukas Koszyk Date: Thu, 10 Oct 2024 13:35:54 +0000 Subject: [PATCH] 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 "yes" in the 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 --- Koha/Database.pm | 67 +++++++++++++++++++++++++--------- t/db_dependent/Koha/Database.t | 58 ++++++++++++++++++++++++++++- 2 files changed, 107 insertions(+), 18 deletions(-) diff --git a/Koha/Database.pm b/Koha/Database.pm index 8457c18e80..bae5d7633a 100644 --- a/Koha/Database.pm +++ b/Koha/Database.pm @@ -61,13 +61,7 @@ sub dbh { my $driver = db_scheme2dbi($config->get('db_scheme')); my $user = $config->get("user"), my $pass = $config->get("pass"), - my $dsn = sprintf( - 'dbi:%s:database=%s;host=%s;port=%s', - $driver, - $config->get("database_test") || $config->get("database"), - $config->get("hostname"), - $config->get("port") || '', - ); + my $dsn = generate_dsn($config); my $attr = { RaiseError => 1, @@ -75,16 +69,6 @@ sub dbh { }; if ($driver eq 'mysql') { - 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'), - ); - } - $attr->{mysql_enable_utf8} = 1; } @@ -197,6 +181,55 @@ sub db_scheme2dbi { return $scheme eq 'Pg' ? $scheme : 'mysql'; } +=head2 generate_dsn + + my $dsn = Koha::Database::generate_dsn($config); + +Returns a data source name (DSN) for a database connection +from the config instance. + +=cut + +sub generate_dsn { + my ($config) = @_; + my $driver = db_scheme2dbi( $config->get('db_scheme') ); + + my $dsn = sprintf( + 'dbi:%s:database=%s;host=%s;port=%s', + $driver, + $config->get("database_test") || $config->get("database"), + $config->get("hostname"), + $config->get("port") || '', + ); + + if ( $driver eq 'mysql' ) { + my $tls = $config->get("tls"); + if ( $tls && $tls eq 'yes' ) { + + $dsn .= ';mysql_ssl=1'; + + my $mysql_ssl_client_key = $config->get('key'); + my $mysql_ssl_client_cert = $config->get('cert'); + my $mysql_ssl_ca_file = $config->get('ca'); + + if ( $mysql_ssl_client_key && $mysql_ssl_client_key ne '__DB_TLS_CLIENT_KEY__' ) { + $dsn .= sprintf( ';mysql_ssl_client_key=%s', $mysql_ssl_client_key ); + } + + if ( $mysql_ssl_client_cert && $mysql_ssl_client_cert ne '__DB_TLS_CLIENT_CERTIFICATE__' ) { + $dsn .= sprintf( ';mysql_ssl_client_cert=%s', $mysql_ssl_client_cert ); + } + + if ( $mysql_ssl_ca_file && $mysql_ssl_ca_file ne '__DB_TLS_CA_CERTIFICATE__' ) { + $dsn .= sprintf( ';mysql_ssl_ca_file=%s', $mysql_ssl_ca_file ); + } + } + + } + + return $dsn; +} + =head2 EXPORT None by default. diff --git a/t/db_dependent/Koha/Database.t b/t/db_dependent/Koha/Database.t index 8f8f124065..3543992e71 100755 --- a/t/db_dependent/Koha/Database.t +++ b/t/db_dependent/Koha/Database.t @@ -16,8 +16,9 @@ # along with Koha; if not, see . use Modern::Perl; -use Test::More tests => 3; +use Test::More tests => 4; use C4::Context; +use t::lib::Mocks; my $dbh = C4::Context->dbh; my $sql_mode = $dbh->selectrow_array(q|SELECT @@SQL_MODE|); @@ -33,3 +34,58 @@ subtest 'db_scheme2dbi' => sub { is(Koha::Database::db_scheme2dbi('xxx'), 'mysql', 'ask for unsupported DBMS, get mysql'); is(Koha::Database::db_scheme2dbi(), 'mysql', 'ask for nothing, get mysql'); }; + +subtest 'generate_dsn' => sub { + plan tests => 6; + + my $config = Koha::Config->get_instance; + + t::lib::Mocks::mock_config( 'database', 'koha' ); + t::lib::Mocks::mock_config( 'hostname', 'localhost' ); + t::lib::Mocks::mock_config( 'port', '3306' ); + t::lib::Mocks::mock_config( 'db_scheme', 'mysql' ); + t::lib::Mocks::mock_config( 'tls', 'no' ); + + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306', + 'DSN string for MySQL configuration without TLS.' + ); + + t::lib::Mocks::mock_config( 'tls', 'yes' ); + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306;mysql_ssl=1', + 'DSN string for MySQL configuration with TLS without client key, client cert and ca file.' + ); + + t::lib::Mocks::mock_config( 'key', '/path/to/client-key.pem' ); + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306;mysql_ssl=1;mysql_ssl_client_key=/path/to/client-key.pem', + 'DSN string for MySQL configuration with TLS and client key.' + ); + + t::lib::Mocks::mock_config( 'cert', '/path/to/client-cert.pem' ); + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306;mysql_ssl=1;mysql_ssl_client_key=/path/to/client-key.pem;mysql_ssl_client_cert=/path/to/client-cert.pem', + 'DSN string for MySQL configuration with TLS, client key and client cert.' + ); + + t::lib::Mocks::mock_config( 'ca', '/path/to/ca.pem' ); + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306;mysql_ssl=1;mysql_ssl_client_key=/path/to/client-key.pem;mysql_ssl_client_cert=/path/to/client-cert.pem;mysql_ssl_ca_file=/path/to/ca.pem', + 'DSN string for MySQL configuration with TLS with client key, client cert and ca file.' + ); + + t::lib::Mocks::mock_config( 'key', '__DB_TLS_CLIENT_KEY__' ); + t::lib::Mocks::mock_config( 'cert', '__DB_TLS_CLIENT_CERTIFICATE__' ); + + is( + Koha::Database::generate_dsn($config), + 'dbi:mysql:database=koha;host=localhost;port=3306;mysql_ssl=1;mysql_ssl_ca_file=/path/to/ca.pem', + 'DSN string for MySQL configuration with TLS with ca file.' + ); +}; -- 2.39.5