Best Security Practices for Amazon RDS with Sequelize
About a year ago, our team at SolutoNashville started working with Amazon RDS. It allows you to use several popular relational database dialects without having to manage hardware, run a server, or perform manual backups, among many other things. Our application connects to RDS using Node.js on AWS Lambda.
As Soluto and the rest of Asurion greatly value the security of our customer, contractor, and employee data, we insist on using end-to-end SSL/TLS for all of our systems. This means that, as information travels from a user’s computer to our backend APIs all the way to the database that powers them, nothing else can read or tamper with it.
Some may question the necessity to use end-to-end encryption for databases that aren’t publicly accessible from the internet. Although putting your instance in a VPC is strongly recommended, adding encryption can thwart insider attacks and mitigate damages if another system in your network is compromised.
In an age where a malware infected HVAC system at Target’s corporate headquarters can steal the credit card numbers of millions of its customers, technology professionals should practice defense-in-depth and use all tools at their disposal to stay on their guard.
Our ORM of choice is Sequelize, a mature Node package that supports several dialects, including PostgreSQL and MySQL. Unfortunately, there’s not a lot of documentation for using it to connecting to databases securely, and RDS specifically poses additional challenges that required research from our team. In order to prevent you from going through the same troubles, here is how we established a secure connection to RDS.
Establishing an Insecure Connection
To start, let’s create an insecure connection to the database:
const Sequelize = require('sequelize');const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.xxxxxxxxxxxx.region.rds.amazonaws.com',
dialect: 'postgres'
});
This example uses PostgreSQL, but if you wanted to use MySQL, you could simply replace ‘postgres’ with ‘mysql’.
Once connected to the PostgreSQL database, if you have the sslinfo module installed, you can perform the following query to verify whether or not you are using SSL/TLS:
sequelize.query('select ssl_is_used()', { type: sequelize.QueryTypes.SELECT })
.then((result) => {
console.log(result[0].ssl_is_used);
});With our current configuration, this will print “false” to the console. This means that, if a malicious device was on our network when we established this connection, it could read all database traffic, including the username and password.
For MySQL, you can use the following query to test SSL/TLS usage:
sequelize.query("SHOW STATUS LIKE 'Ssl_cipher'", { type: sequelize.QueryTypes.SELECT })
.then((result) => {
console.log(result[0].Value);
});
}This will print nothing if SSL/TLS is not used and the cipher (Ex. DHE-RSA-AES256-SHA) otherwise. As expected, this prints nothing for the example above.
Establishing a Secure Connection Without Verifying the Certificate
Enabling SSL is as simple as setting a flag:
const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.xxxxxxxxxxxx.region.rds.amazonaws.com',
dialect: 'postgres',
dialectOptions: {
ssl: true
}
});If we execute select ssl_is_used() again, it will return true. However, this will not perform any validation of the server’s certificate. This means that, if the client’s DNS server was compromised, the hacker could direct the client to connect to its own SSL/TLS server, once again stealing the database credentials.
Establishing a Secure Connection and Verifying the Certificate
To reject invalid certificates, we simply need to set a dialect option like so:
const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.xxxxxxxxxxxx.region.rds.amazonaws.com',
dialect: 'postgres',
dialectOptions: {
ssl: {
rejectUnauthorized: true
}
}
});However, you should expect the following error:
Unhandled rejection SequelizeConnectionError: self signed certificate in certificate chainFor MySQL, you will get a similar error:
Unhandled rejection SequelizeConnectionError: unable to get local issuer certificateTo trust certificates that were signed by the AWS RDS certificate authority, first, download the RDS certificate bundle. Then, load the certificate like so:
const fs = require('fs');
const rdsCa = fs.readFileSync(__dirname + '/rds-combined-ca-bundle.pem');Finally, supply the certificate authority in your constructor:
const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.xxxxxxxxxxxx.region.rds.amazonaws.com',
dialect: 'postgres',
dialectOptions: {
ssl: {
rejectUnauthorized: true,
ca: [rdsCa]
}
}
});We are now back in business!
The MySQL module simplifies the process by providing an Amazon RDS profile. To use it, you simply have to specify it like so:
const sequelize = new Sequelize(dbname, username, password, {
host: 'mysqlssltest.xxxxxxxxxxxx.region.rds.amazonaws.com',
dialect: 'mysql',
dialectOptions: {
ssl: 'Amazon RDS'
}
});This will not work for PostgreSQL and will simply revert back to ignoring the validity of the certificate.
Establishing a Secured Connection, Verifying the Certificate, and Using a CNAME
We make it a practice to use create CNAMEs to point to our database instance URLs. This way, if we ever migrate environments or the database instance URL otherwise changes, we simply have to update the CNAME and can leave our clients alone.
If you are using the MySQL Amazon RDS profile, no modifications are necessary to use a CNAME. However, if you attempt to connect to a PostgreSQL database like so:
const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.mycname.com',
dialect: 'postgres',
dialectOptions: {
ssl: {
rejectUnauthorized: true,
ca: [rdsCa]
}
}
});You will get the following error:
Unhandled rejection SequelizeConnectionError: Hostname/IP does not match certificate's altnames: Host: pgssltest.mycname.com. is not cert's CN: pgssltest.xxxxxxxxxxxx.region.rds.amazonaws.comAs the error implies, because the certificate provided by RDS contains a different domain name than your CNAME, it is treated as invalid. However, if your node-postgres version 7.4.3 or higher, we can get around this issue like so:
const tls = require('tls');const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.mycname.com',
dialect: 'postgres',
dialectOptions: {
ssl: {
rejectUnauthorized: true,
ca: [rdsCa],
checkServerIdentity: (host, cert) => {
const error = tls.checkServerIdentity(host, cert); if (error && !cert.subject.CN.endsWith('.rds.amazonaws.com')) {
return error;
}
}
}
}
});
This will attempt to verify the certificate as usual, but if an error is thrown, and the certificate’s domain name ends in “.rds.amazonaws.com”, we will suppress the error and continue as normal.
Bonus: Disable Insecure Connections
Using SSL/TLS is great, but if other database clients don’t follow suit, you still have this security vulnerability. Fortunately, you can configure your database to reject insecure connections.
For PostgreSQL, we simply need to add a parameter group that has the setting rds.force_ssl set to 1. After application, if you attempt to connect to the database insecurely using Sequelize, you will get the following error:
Unhandled rejection SequelizeConnectionError: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "pguser", database "pgssltest", SSL offMySQL also has a similar parameter group setting called require_secure_transport, but unfortunately, it is not modifiable. Luckily, you can execute a query to have the same effect (See “Using SSL with a MySQL DB Instance” on this page). This will result in the following error for insecure connections:
Unhandled rejection SequelizeAccessDeniedError: Access denied for user 'mysqluser'@'xxx.xxx.xxx.xxx' (using password: YES)Bonus: Encrypt Your Database
At this point, all of your database traffic is encrypted in transit. Although this is an awesome step in the right direction, without encrypting your data at rest, it can still be compromised. You can follow these steps provided by AWS to ensure your resources are properly encrypted.
Bonus: Disable Operator Aliases
Although this isn’t specific to RDS, every Sequelize user should be familiar with the operatorAliases setting. To explain what it does, consider if we wanted to create a function that deletes a record with a specific ID:
const deleteRecordById = async (recordId) => {
return await models.Record.destroy({
id: recordId
});
}This works as expected. However, what if recordId is input from a user that wasn’t properly type-checked or otherwise validated? Imagine if recordId ended up being defined as:
{
$ne: 'fakeId'
}This would result in the destroy call being evaluated like so:
models.Record.destroy({
id: {
$ne: 'fakeId'
}
});With our current setup, this is interpreted as “delete every record that has an ID that is not equal to fakeId.” This would result in every record being deleted!
In this example, $ne is a string operator alias for [Op.ne]. Although we should have validated this input regardless, we can prevent strings from ever being interpreted as operator aliases by turning off the operatorAliases setting. That way, if we received the above input, the destroy call would be interpreted as “delete every record that has an ID that is equal to { $ne: 'fakeId' },” which obviously wouldn’t match any records. For more information, see this section of the Sequelize documentation.
With that change, our final Sequelize instantiation looks as follows:
const sequelize = new Sequelize(dbname, username, password, {
host: 'pgssltest.mycname.com',
dialect: 'postgres',
operatorsAliases: false,
dialectOptions: {
ssl: {
rejectUnauthorized: true,
ca: [rdsCa],
checkServerIdentity: (host, cert) => {
const error = tls.checkServerIdentity(host, cert);
if (error && !cert.subject.CN.endsWith('.rds.amazonaws.com')) {
return error;
}
}
}
}
});Congratulations! Assuming you are not improperly reusing your database credentials, you are following best practices!
Remember, though, that this is not a silver-bullet, and you must ensure that the rest of your network and application are properly secured. This is the price we pay to earn the trust of our users, so keep on fighting the good fight!
