Currently if the database server is a different machine to another we connect to it without using an encrypted connection. This will allow it to use TLS instead.
Created attachment 45992 [details] [review] Bug 15427 : Enable TLS support for MySQL In summary, changes are: 1) If you have chosen MySQL, Makefile.PL will ask you if you want TLS (default: "no"), and then the locations for CA cert, client cert and client key (reasonable defaults are provided). Settings <tls>, <ca>, <cert> and <key> are added in koha-conf.xml 2) If <tls>yes</tls> in koha-conf.xml, the installer and database connection scripts add the TLS options in both DBI connection strings and mysql command line To test 1/ Apply patch 2/ Check everything still works and db connections are the same as before 3/ Either run Makefile.PL and step through the options or edit your koha-conf.xml to enable TLS 4/ Check db connections are still working Patch provided to me by Dimitris Kamenopoulos and I reformatted it into a git patch, any errors are probably mine
Can this be tested locally or do I need to set up an external MySQL DB?
(In reply to Mirko Tietgen from comment #2) > Can this be tested locally or do I need to set up an external MySQL DB? YOu should be able to test it locally, just make sure MySQL is running on a port not a socket.
When I set <tls> to yes and add nothing in the cert fields, it seems to work as before. I don't think it should? I also wonder how to verify that it actually uses TLS.
You can a) install mysql 5.7, that one fails to connect if you ask for ssl and cannot do it, or b) use tcpdump, it will be fairly obvious whether the connection is encrypted
Bug 16690 would make testing this easier, I think.
This needs a rebase.
Caused by changes in bug 13669.
Created attachment 59383 [details] [review] Bug 15427 : Enable TLS support for MySQL In summary, changes are: 1) If you have chosen MySQL, Makefile.PL will ask you if you want TLS (default: "no"), and then the locations for CA cert, client cert and client key (reasonable defaults are provided). Settings <tls>, <ca>, <cert> and <key> are added in koha-conf.xml 2) If <tls>yes</tls> in koha-conf.xml, the installer and database connection scripts add the TLS options in both DBI connection strings and mysql command line To test 1/ Apply patch 2/ Check everything still works and db connections are the same as before 3/ Either run Makefile.PL and step through the options or edit your koha-conf.xml to enable TLS 4/ Check db connections are still working Patch provided to me by Dimitris Kamenopoulos and I reformatted it into a git patch, any errors are probably mine Signed-off-by: Mirko Tietgen <mirko@abunchofthings.net>
I rebased it and tested using tcpdump.
FYI what remember from testing, I did the following: - created certificates. This might be helpful https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html#creating-ssl-files-using-openssl-unix-command-line - edited koha-conf.xml like this (add <tls>, <ca>, <cert>, <key>) > <config> > <db_scheme>mysql</db_scheme> > <database>koha_koha</database> > <hostname>127.0.0.1</hostname> > <port>3306</port> > <tls>yes</tls> > <ca>/home/mirko/newcerts/ca.pem</ca> > <cert>/home/mirko/newcerts/client-cert.pem</cert> > <key>/home/mirko/newcerts/client-key.pem</key> > <user>koha_koha</user> > … - logged out of the staff client - ran the following command to output to a text file > sudo tcpdump -i lo port 3306 -s 65535 -n -q -A > login.yestls3.txt - logged into the staff client - stopped tcpdump after a bit - logged out - changed config to <tls>no</tls> - started tcpdump again, output to another file > sudo tcpdump -i lo port 3306 -s 65535 -n -q -A > login.notls3.txt - logged into the staff client - stopped tcpdump after a bit and then compared the output files, which were obviously different. I hope I did not forget anything in between, was a while ago.
(In reply to Mirko Tietgen from comment #11) > and then compared the output files, which were obviously different. Yes they are different, but I do not find anything obvious that is telling me the connection is encrypted. What should I search for?
In the non-TLS file I see a lot of MySQL commands. Like SELECTs. In the TLS file I don't (actually I see one, not sure why), but mostly "garbage".
Created attachment 60467 [details] [review] Bug 15427 : Enable TLS support for MySQL In summary, changes are: 1) If you have chosen MySQL, Makefile.PL will ask you if you want TLS (default: "no"), and then the locations for CA cert, client cert and client key (reasonable defaults are provided). Settings <tls>, <ca>, <cert> and <key> are added in koha-conf.xml 2) If <tls>yes</tls> in koha-conf.xml, the installer and database connection scripts add the TLS options in both DBI connection strings and mysql command line To test 1/ Apply patch 2/ Check everything still works and db connections are the same as before 3/ Either run Makefile.PL and step through the options or edit your koha-conf.xml to enable TLS 4/ Check db connections are still working Patch provided to me by Dimitris Kamenopoulos and I reformatted it into a git patch, any errors are probably mine Signed-off-by: Mirko Tietgen <mirko@abunchofthings.net> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
(In reply to Mirko Tietgen from comment #13) > In the non-TLS file I see a lot of MySQL commands. Like SELECTs. In the TLS > file I don't (actually I see one, not sure why), but mostly "garbage". Yes indeed, it works as intended!
Pushed to master for 17.05, thanks Dimitris!
Note: tls is not found in debian/templates/koha-conf-site.xml.in
(In reply to Marcel de Rooy from comment #17) > Note: tls is not found in debian/templates/koha-conf-site.xml.in See bug 18245. :)
This won't get ported back to 16.11.x as it is an enhancement. Not changing status to Resolved Fixed as there is an unpushed follow-up on bug 18215.