Migrating AWS RDS MySQL instances from non-encrypted to encrypted

We had to do this exercise recently due to a security audit requirement, so I thought I’d write about it. If you’ve got old AWS (Amazon Web Services) RDS (Relational Database Service) instances around since before encrypted databases were an option in RDS, or you just never encrypted your databases, and are now deciding to encrypt them, you’ve come to the right place. The steps below apply specifically to MySQL RDS instances, but the same guidelines can be used for other database server types as well.

In summary, RDS doesn’t give you an option to simply encrypt your database if it was created as non-encrypted. Furthermore, you cannot take a snapshot of your non-encrypted database and create an encrypted instance out of it. Essentially you have to manually export the data from the non-encrypted instance, import it into the new encrypted instance, and switch your applications over to use the new encrypted database instances. Then you can get rid of your old non-encrypted instances.

Note that RDS does not allow you to create a full blown replicated database (that is not a read-only replica tied to the existence of a master database). Ideally, this feature would exist in RDS, in which case you can use replication from one DB instance (the un-encrypted one) to another (the encrypted one) so that data is automatically replicated and synchronized between the two. This is essential if you have live applications using your databases, in which case you can have almost no downtime if you can replicate data from an old database to a new one, and simply switch the application to use the new database without doing any manual export/import of data.

So unfortunately if you have live applications using your non-encrypted AWS RDS databases and you need to migrate to encrypted databases, you’ll need to pick a time to do the migration, get prepared, let your users know about the maintenance downtime, and take your applications offline to make it happen. (I’m hoping the good folks at AWS one day soon add a feature for us to fully replicate independently standing databases within RDS).

Anyway, on to the steps. To start out, depending on the size of your databases and your connection speed, you’ll need to decide whether to export the data from your non-encrypted database onto a machine external to AWS (such as your own developer/administration machine wherever you are), OR export it onto an EC2 instance in AWS. If your databases are relatively small in size (and non-numerous) or you just have a ton of bandwidth, you can decide to download all the data onto your own machine. Otherwise I’d recommend you create an EC2 instance in AWS if you don’t have one already, and use that to temporarily act as the machine to export data to, and import data from.

We used a Linux EC2 machine so I’ll focus on that. First of all, you want to make sure that the EC2 machine has an encrypted volume attached to it. This ensures that the data you export doesn’t end up on a non-encrypted disk in AWS (so as not to violate any security rules or policies for your data). At the time of writing this blog entry, EC2 machine root volumes cannot be encrypted, but you can attach encrypted volumes to them. In summary, from the AWS web console, in the EC2 console, you can create an encrypted volume and attach it to your EC2 machine. Then log on to your machine, format the volume, and mount it. I’m sure there are various guides out there for this, so I won’t focus on the nitty gritty.

ssh into your Linux EC2 instance and ensure that mysql client is installed by typing in the “mysql” command. If not, try “yum install mysql” to install it. Next, if you have security group (firewall) rules applied to your RDS instances, make sure that the EC2 machine can connect to the databases (add the IP for the EC2 machine to your RDS security group(s)). Ensure you can connect to your database by typing in the following command: mysql -u (username) -p –host=(database hostname)

You will probably want to create the new encrypted databases in RDS ahead of time from the actual scheduled “maintenance” with your users, so that there is minimal downtime during the actual maintenance window. So assuming your encrypted databases are created and ready, you’re in the maintenance window and are ready to migrate, and have taken your live applications offline, you can begin exporting data from each database.

Now you’re finally ready to export the data from the database. Connect to the EC2 linux machine and cd to the directory the encrypted volume is mounted on. Type in the following command to dump the database from the old non-encrypted MySQL RDS instance:

mysqldump –opt –events –routines –triggers –user=(username)-p –host=(hostname) (database name) > (database name).sql

Of course you’ll want to replace the username, hostname, and database names (everything in parenthesis) with real values. You will be prompted for the password. This command includes everything you’ll need from your old database. More information, or if you want to include multiple databases from the same MySQL server, can be found here on the mysqldump command: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Then to import the data into your new encrypted database, use the following command:

mysql –user=(user) -p –host=(hostname) -e “drop database if exists (database name); create database (database name); use (database name); source (database name).sql;”

Note that the export is usually very fast, but the import is slower. Also note that if you changed the username from what it was in the old database, you’ll need to modify all instances of the username in the .sql file dumped from mysql dump. In order to accomplish that, try this sed command:

sed -i “s/\`(old username)\`@\`%\`/CURRENT_USER/g” (database name).sql

Lastly, after the export and import are finished, update the hostnames of the old databases with the new ones in all your applications. Try out your applications to ensure your new databases are being queried. And once everything checks out, at this point you are ready to update your live applications and put them back online!

MEAN stack: associating a socket with a user

I’m using the MEAN stack for an application I’m working on. The project was seeded using the Angular fullstack yeoman generator (https://github.com/DaftMonk/generator-angular-fullstack/).

Out of the box the project has support for websockets (using socket.io), and users (using passportjs). However, sockets on the server side in express running on node are not tied to users, out of the box.

For several reasons the application likely needs to know what user a socket belongs to. For example, if there’s a change made to a model that needs to be emitted, you may need to emit it to only users with a certain role.

To get around this, I made a bunch of modifications which I’ll detail below. Essentially, the user object will get saved within the socket object. So when a socket is being processed, say through a model level trigger (i.e. “save” or “delete”) using mongoose for example, the user object will be in the socket and can be used in whatever processing logic.

The MEAN project seeded from the angular fullstack generator uses a token generated through jwt, which is stored in a cookie, to authenticate a user. So when a user login occurs, an event can be emitted with the jwt token over the socket to register the user with the socket. Furthermore, in your socketio.on(‘connection’,…) function in express, you can read the cookie to get the jwt token, then get the user and put it in the socket. This is essential so that if a user is already logged in, and returns to your web application (or opens a new tab to your application) and a new websocket is created, the cookie can be used to associate the socket with the user, since a new login event will not be emitted at that point.

First, let’s define a function that can take a token either directly as a parameter, or read it from the cookie in a socket, and get the user. This same function can be called from a login emit event with a jwt token as the payload over the socket, or from socketio.on(‘connection’,…).

var auth = require('../auth/auth.service');
function setupUserInSocket(socket, inputToken)
{
  var tokenToCheck = inputToken;
  if(!tokenToCheck && socket && socket.handshake && socket.handshake.headers && socket.handshake.headers.cookie)
  {
    socket.handshake.headers.cookie.split(';').forEach(function(x) {
      var arr = x.split('=');
      if(arr[0] && arr[0].trim()=='token') {
        tokenToCheck = arr[1];
      }
    });
  }
  if(tokenToCheck)
  {
    auth.getUserFromToken(tokenToCheck, function (err, user) {
      if(user) {
        console.info('[%s] socket belongs to %s (%s)', socket.address, user.email, user._id);
        socket.user = user;
      }
    });
  }
}

Note that the cookie is in socket.handshake.headers.cookie. Also note that I call auth.getUserFromToken, which is another function I created that decrypts the user ID from the jwt token, queries the user from the model, and returns it. The function looks like this:

var User = require('../api/user/user.model');
var async = require('async');
var config = require('../config/environment');
function getUserFromToken(token, next)
{
  async.waterfall
  (
    [
      function(callback)
      {
        jwt.verify(token, config.secrets.session, function(err, decoded) {
          callback(err, decoded);
        });
      },
      function(decoded, callback)
      {
        if(!decoded || !decoded._id)
          callback(null, null);
        else {
          User.findById(decoded._id, function (err, user) {
            callback(null, user);
          });
        }
      },
    ],
    function(err, user)
    {
      next(err, user);
    }
  );
}

Next, let’s use socketio.on(‘connection’,…) to call the function with the socket. If the jwt token is already in the cookies, meaning the user already logged in previously, the user will be associated with the socket:

socketio.on('connection', function (socket) {
  setupUserInSocket(socket);
  //...
});

And that’s it for that particular scenario! Next, let’s worry about when a user actually logs in. Within socketio.on(‘connection’, …) we can listen for login emits from the client over the socket like so:

socket.on("login", function(token,next) {
  setupUserInSocket(socket,token);
  next({data: "registered"});
});

And on the client side, we emit the login event over the socket when a successful login occurs. This can be done in a number of ways, but I decided to do it in login.controller.js. After Auth.login() is called, I call socket.login():

angular.module('classActApp')
  .controller('LoginCtrl', function ($scope, Auth, socket, ...) {
    //...
        Auth.login({
          email: $scope.user.email,
          password: $scope.user.password
        })
        .then( function() {
                  //...
                  socket.login();
//...

And in the client side socket.service.js, the login() function does the following:

angular.module('classActApp')
  .factory('socket', function(socketFactory, $location, CONSTANTS, Auth) {
return {
login: function () {
  socket.emit("login", Auth.getToken(), function(data) {
  });
},

Note that you also need to worry about logouts. If the user logs out from your web application, but sticks around on your web application, the socket for that session will remain associated to the user they were logged in as previously. This could be undesirable for several reasons. So in your express side of things (server side), you want to listen for logout events and clear out the user from the socket like so (note, this is added within socketio.on(‘connection’,…)):

socket.on("logout", function(next) {
  if(socket && socket.user) {
    console.info('[%s] socket being disassociated from %s (%s)', socket.address, socket.user.email, socket.user._id);
    socket.user = null;
  }
  next({data: "un-registered"});
});

And in your angular side of things (client side), when the user logs out, you want to emit a logout event over the socket. In the angular fullstack seeded project I’m using, this happens in navbar.controller.js which has the logout function.

angular.module('classActApp')
  .controller('NavbarCtrl', function ($scope, $location, socket, ...) {
    //...
    $scope.logout = function() {
      //...
            socket.logout();

And in socket.service.js:

angular.module('classActApp')
  .factory('socket', function(socketFactory, $location, CONSTANTS, Auth) {
    //...
    return {
      //...
      logout: function () {
        socket.emit("logout", function(data) {
        });
      },

And that’s it! Now all your sockets have the user they belong to (if any) associated to them (accessible from socket.user on the server side). And when emitting events from the server side from the socket, or when reading events emitted from the client side over the socket, we can now know the user the socket belongs to!

Using Grunt to deploy to individual OpenShift Applications

I ran across this problem using the MEAN stack seed project generated through the angular fullstack generator (https://github.com/angular-fullstack/generator-angular-fullstack). The seed project’s developers made it easy to deploy an application to OpenShift, however they set it up so you can only deploy to a single OpenShift application instance.

One of the requirements for my current MEAN project is to deploy it to two environments: a staging environment and a full blown production environment. However with this seed project and respective Grunt buildcontrol directives, that wouldn’t be possible.

So I took it upon myself to edit the Grunt file and the yeoman javascript to deploy to OpenShift, to allow it to create multiple environments.

The first step is to edit generator-angular-fullstack\openshift\index.js file that ships with the yeoman generator for angular fullstack. (Note: this file is outside of your actual MEAN project seeded by the generator, typically in your home directory, so you’ll need to search for it on your filesystem). As writing of this blog post, openshift\index.js has hardcoded the application name to be ‘openshift’. Find all occurences of ‘openshift’ (single quotes included) and replace it with this.deployedName (no single quotes). This will allow the application name to be the actual name you’ll input when running this openshift deployment yeoman script.

Secondly, you’ll need to edit the Gruntfile.js in your project. Skip down to the buildcontrol. Under openshift, change the remote field from ‘openshift’ to grunt.option(‘openshift_target’). grunt.option() allows you to take an argument from the command line, so this way you can specify the application name you want to deploy to.

And voilà, that’s it. Now you have the capability to deploy (and update) your MEAN application to multiple OpenShift application environments.

As per the angular fullstack generator documentation, to deploy to OpenShift, you would run the following command:

yo angular-fullstack:openshift

In the yeoman script run in the command above, you’ll be asked for the application name. I named mine “staging” and “production” (two separate environments created by running the command twice). And then to deploy to your application initially, or deploying all code updates, you’d run the commands.

grunt build
grunt buildcontrol:openshift --openshift_target=staging
grunt buildcontrol:openshift --openshift_target=production