Introduction
We’re running a Java application with a connection string that looks like this with AWS RDS MySQL version 5.7:
jdbc:mysql://${db.endpoint}/${db.schema}?useConfigs=maxPerformance&characterEncoding=utf8&useSSL=false
As you can see all database queries will be sent to the RDS instance in plaintext.
Goals
We want:
- transport encryption for database connections
- to validate that the server’s identity is valid based on a CA certificate bundle
We do not require clients to authenticate themselves via client certificates.
RDS TLS options
MySQL uses yaSSL for secure connections in the following versions:
- MySQL version 5.7.19 and earlier 5.7 versions
- MySQL version 5.6.37 and earlier 5.6 versions
MySQL uses OpenSSL for secure connections in the following versions:
- MySQL version 8.0
- MySQL version 5.7.21 and later 5.7 versions
- MySQL version 5.6.39 and later 5.6 versions
Amazon RDS for MySQL supports Transport Layer Security (TLS) versions 1.0, 1.1, and 1.2. The following table shows the TLS support for MySQL versions.
| MySQL version | TLS 1.0 | TLS 1.1 | TLS 1.2 |
|---|---|---|---|
| MySQL 8.0 | Supported | Supported | Supported |
| MySQL 5.7 | Supported | Supported | Supported for MySQL 5.7.21 and later |
| MySQL 5.6 | Supported | Supported for MySQL 5.6.46 and later | Supported for MySQL 5.6.46 and later |
Getting started
To validate the RDS server identity we need the RDS CA certificate chain that can be downloaded from here:
You can then test the CA chain with the mysql client:
$ wget https://truststore.pki.rds.amazonaws.com/eu-central-1/eu-central-1-bundle.pem
$ /usr/bin/mysql -P ${db_port} -h ${db_endpoint} \
-u ${db_username} -p${db_password} \
--ssl-ca=eu-central-1-bundle.pem --ssl-verify-server-cert contosodb
# newer mysql clients use another parameter instead:
$ /usr/bin/mysql -P ${db_port} -h ${db_endpoint} \
-u ${db_username} -p${db_password} \
--ssl-ca=eu-central-1-bundle.pem --ssl-mode=VERIFY_IDENTITY contosodb
You should always check certificate expiration dates:
$ openssl crl2pkcs7 -nocrl -certfile eu-central-1-bundle.pem \
| openssl pkcs7 -print_certs -noout -text | rg -B3 'Subject:'
Validity
Not Before: Aug 22 17:08:50 2019 GMT
Not After : Aug 22 17:08:50 2024 GMT
Subject: C=US, L=Seattle, ST=Washington, O=Amazon Web Services, Inc., OU=Amazon RDS, CN=Amazon RDS Root 2019 CA
--
Validity
Not Before: Sep 11 19:36:20 2019 GMT
Not After : Aug 22 17:08:50 2024 GMT
Subject: C=US, ST=Washington, L=Seattle, O=Amazon Web Services, Inc., OU=Amazon RDS, CN=Amazon RDS eu-central-1 2019 CA
--
Validity
Not Before: May 21 22:33:24 2021 GMT
Not After : May 21 23:33:24 2121 GMT
Subject: C=US, O=Amazon Web Services, Inc., OU=Amazon RDS, ST=WA, CN=Amazon RDS eu-central-1 Root CA ECC384 G1, L=Seattle
--
Validity
Not Before: May 21 22:23:47 2021 GMT
Not After : May 21 23:23:47 2061 GMT
Subject: C=US, O=Amazon Web Services, Inc., OU=Amazon RDS, ST=WA, CN=Amazon RDS eu-central-1 Root CA RSA2048 G1, L=Seattle
--
Validity
Not Before: May 21 22:28:26 2021 GMT
Not After : May 21 23:28:26 2121 GMT
Subject: C=US, O=Amazon Web Services, Inc., OU=Amazon RDS, ST=WA, CN=Amazon RDS eu-central-1 Root CA RSA4096 G1, L=Seattle
I’ve split up the CA bundle into separate files with their names based on a slugified version of their common name (CN=...):
Amazon-RDS-Root-2019-CA.pem
Amazon-RDS-eu-central-1-2019-CA.pem
Amazon-RDS-eu-central-1-Root-CA-ECC384-G1.pem
Amazon-RDS-eu-central-1-Root-CA-RSA2048-G1.pem
Amazon-RDS-eu-central-1-Root-CA-RSA4096-G1.pem
Import the certificates into a truststore
Next we need to import them into a Java truststore that can be used by our JDBC connection:
Note: You should be using the newer pkcs12 (aka pfx) format instead of the older jks format for truststores.
#!/bin/bash
TRUSTSTORE_NAME=rds-truststore.pkcs12
TRUSTSTORE_PASSWORD=supersecretpassword
# Add certs to truststore
for CA in *.pem; do
echo "Store: [${TRUSTSTORE_NAME}] - Importing [${CA}]"
keytool -import -noprompt -trustcacerts -file "${CA}" -alias "${CA%%.*}" -storetype PKCS12 -keystore "${TRUSTSTORE_NAME}" -storepass "${TRUSTSTORE_PASSWORD}"
done
This will import all available *.pem files into a newly created truststore called rds-truststore.pkcs12.
Update connection settings
Place the truststore in your application’s environment - I placed it in /srv/aws-rds/truststore.pkcs12. Update the connection string and restart/redeploy the application:
# https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html
# newer v8 format
#db.url=jdbc:mysql://${db.endpoint}/${db.schema}?useConfigs=maxPerformance&characterEncoding=utf8&sslMode=VERIFY_CA&trustCertificateKeyStoreType=PKCS12&trustCertificateKeyStoreUrl=file:///srv/aws-rds/truststore.pkcs12&trustCertificateKeyStorePassword=supersecretpassword
# older v5.1 format
db.url=jdbc:mysql://${db.endpoint}/${db.schema}?useConfigs=maxPerformance&characterEncoding=utf8&useSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreType=PKCS12&trustCertificateKeyStoreUrl=file:///srv/aws-rds/truststore.pkcs12&trustCertificateKeyStorePassword=supersecretpassword
Verify that TLS is being used
Everything should be starting up smoothly. You should see established DB connections in your RDS metrics. To verify that TLS is being used run the following query on the database server:
SELECT sbt.variable_value AS tls_version, t2.variable_value AS cipher,
processlist_user AS user, processlist_host AS host
FROM performance_schema.status_by_thread AS sbt
JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id
JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
WHERE sbt.variable_name = 'Ssl_version' AND t2.variable_name = 'Ssl_cipher'
ORDER BY tls_version;
Empty tls_version and cipher columns mean that no transport encryption is used:
+-------------+-----------------------------+--------------------------------+---------------+
| tls_version | cipher | user | host |
+-------------+-----------------------------+--------------------------------+---------------+
| | | ... | ... |
| | | foobar-dev-01-user-backup | 10.200.96.121 |
| | | foobar-dev-03-user | 10.200.96.34 |
| | | foobar-int-01-user | 10.200.98.126 |
| TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | myapp-laboratory-user | 10.200.96.116 |
| TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | myapp-laboratory-user | 10.200.96.116 |
| TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | myapp-laboratory-user | 10.200.96.116 |
+-------------+-----------------------------+--------------------------------+---------------+
Ref: