How to connect to a remote MySQL database via SSL using Play Framework?
I deploy Play applications in distributed environments, backed by a remote MySQL database. Specifically, the applications are hosted on heroku, and the database is on Amazon RDS (though this really applies to any remote database connection). Since the database isn't just on localhost, I'd prefer that the remote MySQL connection is made through SSL for security.
Given a CA certificate to trust, how can I configure a Play application to connect to the MySQL server through SSL, only if the host certificate can be verified?
Assume this as the current database configuration:
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://url.to.database/test_db"
db.default.user=root
db.default.password="...."
Answer
Assuming you already have the CA certificate setup for the MySQL server (which is the case when using Amazon RDS), there are a few steps to make this work.
First, the CA certificate should be imported into a Java KeyStore file using keytool, which comes with the JDK. The KeyStore in this case will contain all of the CA certificates we want to trust. For Amazon RDS, the CA cert can be found here. With mysql-ssl-ca-cert.pem in your working directory, you can run the following command:
keytool -import -alias mysqlServerCACert -file mysql-ssl-ca-cert.pem -keystore truststore.jks
Which will create a new Java KeyStore file called truststore.jks after prompting you to enter a KeyStore password and asking if you want to trust the certificate (yes, you do). If you already have a truststore file, you can run the same command, replacing truststore.jks with the path to your existing KeyStore (you'll then be prompted for the password of the existing KeyStore, instead). I usually place truststore.jks in my conf directory.
Second, in application.conf you need to add a few JDBC URL parameters to the database URL:
verifyServerCertificate=true - Refuse to connect if the host certificate cannot be verified.
useSSL=true - Connect using SSL.
requireSSL=true - Refuse to connect if the MySQL server does not support SSL.
For example, if your current database URL is:
db.default.url="jdbc:mysql://url.to.database/test_db"
Then it should now be:
db.default.url="jdbc:mysql://url.to.database/test_db?verifyServerCertificate=true&useSSL=true&requireSSL=true"
Lastly, there are a few command-line options that need to be passed when starting the Play server to configure the truststore MySQL-Connector/J will use. Assuming my truststore.jks file is located in the conf directory, and the password is password, I would start my server (in dev mode) like this:
activator run -Djavax.net.ssl.trustStore="conf/truststore.jks" -Djavax.net.ssl.trustStorePassword="password"
In addition to this, I also like to make sure that it's impossible to connect to the database without using SSL, just in case the options somehow get messed up at the application level. For example if db.default.user=root, then when logged in as root in the MySQL server, run the following queries:
GRANT USAGE ON *.* TO 'root'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;