How to connect to a remote MySQL database via SSL using Play Framework?

Question

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="...."

Solution 1

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;

Solution 2

Just to update on All.

1. You can download the bundle certificate which contains many certificates of Amazon from here https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem.

2. If you see the content of that pem it contains many certificates. Split it to multiple PEM files where each file will contain like this

   -----BEGIN CERTIFICATE-----
       [main content]
   -----END CERTIFICATE-----
   

3. Then run this command for every cert file that you created

   keytool -import \
   -keystore  $JAVA_HOME/jre/lib/security/cacerts \
   -storepass changeit -noprompt \
   -alias $ALIAS -file $YOUR_INDIVIDUAL_PEM_FILE
   

Just for making your life easy somebody has just created bash script for this : https://gist.github.com/shareefhiasat/dabe5e96dbd7123c7b101aac1c0eca8a

Comments

[Stu Thompson]

Note: this is not specific to Play Framework, more of a general Java / MySQL thing

[Daniël van Eeden]

No need to FLUSH PRIVILEGES after GRANT…. You only need to do that if you directly modified the mysql.* tables.