MySQL servers operating on Stable-v4 2.0.123, Stable-v5 3.0.30, or any versions of Stable-v6 and Stable-v7 stacks can support SSL based connections to the database. This document describes how to verify the server is SSL ready, as well as the steps to prepare, use, and enforce this feature. Topics include:
- Verify SSL is Enabled
- Verify SSL Connections
- Enforce SSL Connections
- Keys for Named Users
- Key Maintenance
MySQL 5.7 hosts that pre-date this feature will have generated their own set of keys by default. When the stack is updated these pre-existing keys will conflict with the new keys requiring a restart of the database. To upgrade:
- click the "Upgrade" button on the environment dashboard
- when the run fails restart MySQL with the command
/etc/init.d/mysql restart, in this case with the master before any attached replicas
- when the restart has been completed on all your database instances, click the "Apply" button on the environment dashboard to restart the chef run.
MySQL 5.5 versions 5.5.40 and below and 5.6 versions 5.6.22 and below are built with an older version of SSL and do not properly support the SSL feature; Stable-v4-2.0.124 disables the SSL feature for these versions. If you are currently using one of these versions we recommend performing a minor version upgrade to the latest supported MySQL version in that series.
SSL connections are designed to encrypt client/server traffic to and from your database for increased security. Your database components exist with your application within a virtual network, and the database communication ports by default are not externally accessible. The SSL feature is useful for customers that have specific compliance concerns for network traffic, or for cases where a security exception has been used to expose the database port in the AWS firewall.
When you upgrade to a stack that includes this feature the cookbooks run performed during the upgrade will generate self-signed SSL keys on the database master and distribute these keys automatically to the other databases within the environment. The database master will then generate a key for our administrative users as well as for your main application user; the admin keys will be distributed to all database instances in the environment, and the application keys will be distributed as needed to the home directory of your application user.
Note: The database service must be restarted before MySQL will be able to validate your SSL keys.
Verify SSL is Enabled
- Connect via SSH to the db_master instance
- Assume the role of the administrative user
sudo su -
- Check the ca file that is in place
mysql -e "show global variables like 'have_ssl'"
- If the value is set to
YESyou are now running with SSL enabled, you can type
exitand move on to Verifying SSL Connectivity.
- If the value is set to
- Verify the configuration file for MySQL has the ca file configured
cat /etc/mysql/my.cnf | grep 'ssl-ca'.
- If the configuration file shows SSL is
onand the server indicated it was off you'll need to Restart MySQL. This will result in a brief downtime for your application so you'll probably want to do this during a maintenance window.
- If the configuration file shows SSL is
- Since SSL is not enabled in the configuration file, you'll need to upgrade your environment stack to at least Stable-v4 2.0.123 or Stable-v5 3.0.30.
Verify SSL Connections
Use the user, host, and database name from your database.yml to connect to the database:
mysql -u <user> -h <hostname> <dbname>
Enter the command
\s; if SSL is enabled and your key has been authenticated you will see a line like the following in the output:
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
If you do not see this line make sure you are not using
localhost as the hostname in the
Enforce SSL Connections
To enforce that MySQL uses SSL authentication instead of only Password Authentication you'll need to modify each user you want this setting to apply to. MySQL offers several options (see "Other Account Characteristics") to enforce various aspects of certificate authentication, but
REQUIRE X509 is usually going to meet the needs of most configurations.
To enforce x509 for the deploy user:
sudo su - mysql -e "GRANT USAGE ON *.* TO deploy@'%' REQUIRE X509;" exit
Modify each host of the database.yml either by overriding our template (Stable-v5) or using the database_yml_custom cookbook (Stable-v4) to add:
To remove this setting you would rerun the same command using
REQUIRE NONE. The database.yml customizations are expected to be included by default in a future release of the stack.
Keys for Named Users
If you have created additional OS level (Linux) named users on your instances you can generate and distribute database SSL keys for these users. In order to use this approach you'll also need to first add a MySQL user with a matching username and set its privileges as appropriate. Once the users are in place you can:
- Generate a new key with the command `sudo -i /engineyard/bin/db_user_ssl_keys.sh [expire_in_days]
- [expire_in_days] will default to 5 years (1825) unless otherwise specified
- the password is actually only a temporary value and is removed from the key for ease of use
- Create a custom chef cookbook that runs the following command for any users you have added
When booting an environment from snapshots it may be required to generate new server and user keys for your environment, due to a mismatch between old and new keys. This process can also be followed should you wish to regenerate the keys for any other reason. This will require a maintenance downtime, since once you distribute the new keys a database restart is required. Use the following steps:
- Connect via SSH to your database master and assume the role of the super user.
- If the environment is running the stable-v6 stack there is an Ubuntu bug that requires
RANDFILE = $ENV::HOME/.rndto be commented out, via
vim /etc/ssl/openssl.cnf, press 'i' to enter insert mode, add a
#to comment out the line, then press Escape to leave insert mode and finally type
:wqto save and quit
- Run the following shell commands
mkdir -p /db/old_keys/server mkdir -p /db/old_keys/user mv /db/mysql/*/data/root.crt /db/old_keys/server/ mv /db/mysql/*/data/server.crt /db/old_keys/server/ mv /db/mysql/*/data/server.key /db/old_keys/server/ mv /db/mysql/keygen/mysql /db/old_keys/user/ mv /db/mysql/keygen/deploy /db/old_keys/user/
- Run /engineyard/bin/db_server_ssl_keys.sh
- Restart MySQL
- Click "Apply" on the cloud dashboard