Bug 39591

Summary: koha-create needs options to set up encrypted database connections
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Command-line UtilitiesAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: cornelius.amzar, dcook, markus.john, robin
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=38760
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36026
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 36026    
Bug Blocks:    

Description Katrin Fischer 2025-04-09 11:40:14 UTC
When using an external database server with encrypted connection, koha-create doesn't work.

This is similar to bug 38760 which also includes a test plan for testing the encryption under ktd conditions. Additional information can be found on bug 36026.
Comment 1 cornelius.amzar 2025-04-09 12:50:04 UTC
More Details:
If the DB and user does not yet exist, the script would need root privileges to create DB. Remote root eccess is not secure and I think its disabled by default in Debian. 

When trying to obtain a DB configuration file
koha-create --request-db dhmd
User dhmd-koha already exists.

Not sure what this means, I cant delete this user because it's already used by processes. 

When trying to populate a DB
koha-create --populate-db dhmd
Koha instance is empty, no staff user created.

When trying to use a given DB
koha-create --use-db dhmd
--use-db must have a database name. It can be specified in a readable
password file (/etc/koha/passwd). Using --passwdfile overrides the default
/usr/koha/passwd file. Each line of a passwd file should be in the format of:
    instance:username:password:dbname:dbhost
A database name can also be specified using '--database dbname'.

Then there are no options to encrypt the connection. 

So, the only solutions seems to be installing MariaDB locally, running Koha-create and then copy a dump to the remote DB server.
Comment 2 cornelius.amzar 2025-04-09 12:51:30 UTC
P.S. In our case ALL required information is already in koha-conf.xml, generated by Ansible. So, the script could simple ready anything out of this file...
Comment 3 cornelius.amzar 2025-04-09 13:48:29 UTC
It's not clear why an existing UNIX user prevents using a DB. 

koha-create --use-db dhmd --database koha_dhmd
User dhmd-koha already exists.
Comment 4 Katrin Fischer 2025-04-09 16:37:31 UTC
So we have identified 2 separate problems:

1) missing root privileges for creating the db on the remote server
2) missing configuration options for database encryption

For 1) I think pre-creating the database on the remote server could be the solution.
For 2) I wonder if koha-create really needs database access after that, as all the "content creation" is then left to the web installer.

I wonder if something like this would work, inspired by: bug 14533 comment 4

koha-create --request-db instance

Modify the generated file:

database name: $mysqldb
database user: $mysqluser
     password: $mysqlpwd

On the remote database server, create the db using the same credentials:

CREATE DATABASE \`$mysqldb\`;
CREATE USER \`$mysqluser\`@'$mysql_hostname' IDENTIFIED BY '$mysqlpwd';
GRANT ALL PRIVILEGES ON \`$mysqldb\`.* TO \`$mysqluser\`@'$mysql_hostname';
FLUSH PRIVILEGES;

Create a passwdfile in the format:
instancename:username:passwd:database:dbhost

koha-create --populate-db --dbhost host instance
koha-create --use-db --passwdfile ./file

Adjust koha-conf.xml to use the encrypted database connection.

Run web installer.

Questions:
- In the snippet taken from bug 14533 it uses both --populate-db and --use-db. Both require a file with the db credentials in different formats to be set up. Is this still correct or can it be avoided?
Comment 5 David Cook 2025-04-10 00:18:57 UTC
I haven't really followed this at all, I haven't seen any specific error messages yet, and I don't really use koha-create much (other staff take care of creating Koha instances).

That said, technically you don't need to use the root user for koha-create. On the database server, you can create a custom user with appropriate permissions, and then place that in your /etc/mysql/debian.cnf on your Koha server.

It's been a while since I used "use-db" or "request-db" myself, but I imagine those should work?

--

What's the mysql client reporting when "koha-create doesn't work"?
Comment 6 Katrin Fischer 2025-04-10 07:56:52 UTC
So, --request-db and --populate-db seems to have worked. But --use-db gives an error as the user for the instance already exists.

koha-create --populate-db --dbhost external_host foobar4
Koha instance is empty, no staff user created.
Starting Koha worker daemon for foobar4 (default):.
Starting Koha indexing daemon for foobar4:.

koha-create --use-db --passwdfile ./passwd foobar4
User foobar4-koha already exists

Maybe someone could help clarify what each task is supposed to do (populate-db vs. use-db)?