Bug 15427 - Allow db connections using TLS
Summary: Allow db connections using TLS
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Chris Cormack
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: 18215
  Show dependency treegraph
 
Reported: 2015-12-27 02:56 UTC by Chris Cormack
Modified: 2017-06-14 22:18 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 15427 : Enable TLS support for MySQL (9.57 KB, patch)
2015-12-27 03:13 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 15427 : Enable TLS support for MySQL (9.38 KB, patch)
2017-01-20 17:10 UTC, Mirko Tietgen
Details | Diff | Splinter Review
Bug 15427 : Enable TLS support for MySQL (9.45 KB, patch)
2017-02-20 14:25 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2015-12-27 02:56:22 UTC
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.
Comment 1 Chris Cormack 2015-12-27 03:13:19 UTC
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
Comment 2 Mirko Tietgen 2016-01-28 12:50:57 UTC
Can this be tested locally or do I need to set up an external MySQL DB?
Comment 3 Chris Cormack 2016-01-28 19:23:53 UTC
(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.
Comment 4 Mirko Tietgen 2016-01-28 22:43:38 UTC
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.
Comment 5 Srdjan Jankovic 2016-03-20 23:34:13 UTC
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
Comment 6 M. Tompsett 2017-01-03 05:17:30 UTC
Bug 16690 would make testing this easier, I think.
Comment 7 Mirko Tietgen 2017-01-20 15:50:29 UTC
This needs a rebase.
Comment 8 Mirko Tietgen 2017-01-20 15:55:04 UTC
Caused by changes in bug 13669.
Comment 9 Mirko Tietgen 2017-01-20 17:10:08 UTC
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>
Comment 10 Mirko Tietgen 2017-01-20 17:11:25 UTC
I rebased it and tested using tcpdump.
Comment 11 Mirko Tietgen 2017-02-14 14:45:49 UTC
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.
Comment 12 Jonathan Druart 2017-02-16 15:01:51 UTC
(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?
Comment 13 Mirko Tietgen 2017-02-16 15:44:21 UTC
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".
Comment 14 Jonathan Druart 2017-02-20 14:25:40 UTC
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>
Comment 15 Jonathan Druart 2017-02-20 14:28:16 UTC
(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!
Comment 16 Kyle M Hall 2017-03-03 18:33:25 UTC
Pushed to master for 17.05, thanks Dimitris!
Comment 17 Marcel de Rooy 2017-03-10 08:09:08 UTC
Note: tls is not found in debian/templates/koha-conf-site.xml.in
Comment 18 M. Tompsett 2017-03-10 20:00:14 UTC
(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. :)
Comment 19 Katrin Fischer 2017-03-12 23:05:26 UTC
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.