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="...."
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;
Just to update on All.
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-----
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With