should the default jdbc mysql connection string be changed?

Development
,
1

Hi,

I updated the outdated mysql jdbc connector library (TRUNK-5356). I have successfully tested installation of OpenMRS with this update on Mysql versions 5.5, 5.6, 5.7.

I noticed that when connecting to Mysql 5.7+ the connection defaults are now changed when not explicitly stated whether ssl should or should not be used (useSSL=true/false) and the following warning is logged:

“Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.”

I would like to know if we should change our jdbc mysql connection string that users see when installing openmrs in the wizard to address the above warning.

There are various options

  • leave the connection string as is, so implementer have to decide for themselves and make a deliberate choice
  • disable ssl useSSL=false
  • enable ssl useSSL=true and verifyServerCertificate=false , which is what the connector defaults to when nothing is specified, the warning would disappear. However, I think this option is not recommended for production use.

Setting up SSL with certificates is not that trivial as discussed here Has anyone tried encrypting the application to database connection? SSL over JDBC to MySQL

looping in @cintiadr @maurya @lluismf for suggestions

2

Since we’re dealing with medical information, we should be encouraging use of SSL – i.e., security in transit. So, not a fan of useSSL=false.

I’m not sure it’s reasonable to expect our implementations to have public-facing database servers (for LetsEncrypt) or to purchase & install official certs for each of their servers.

Perhaps shipping with useSSL=true and verifyServerCertificate=false with some clear, step-by-step instructions on how to make this more secure for production. But ideally we’d provide an easy way to get this set up.

Is there any way we could automate or semi-automate the installation of self-signed certs?

3

Great suggestions @teleivo,

And I agree with @burke.

We should be able to automate it in a way. The following guide should help.

I have added this string verifyServerCertificate\=false&useSSL\=true&requireSSL\=true in openmrs-runtime.properties and was succesful in connecting,

The following SQL statement can be run to confirm the connection is secure -

SELECT processlist_id, processlist_user, processlist_host, connection_type, variable_value AS cipher FROM performance_schema.threads t JOIN performance_schema.status_by_thread sbt ON (t.thread_id = sbt.thread_id AND sbt.variable_name = 'Ssl_cipher_list' ) WHERE connection_type IS NOT NULL\G

You should see something similar to -

 processlist_id: 1620
processlist_user: openmrs_user
processlist_host: localhost
 connection_type: SSL/TLS
          cipher: DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:AES256-SHA:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:AES128-SHA:AES256-RMD