Amazon Web Services RDS (Relational Database Service) hosts MySQL databases in the AWS Cloud for you. The hosted MySQLs are mostly like any standard MySQL that you would install on an EC2 instance with a couple of tricks down their sleeves. One of the said tricks is letting you authenticate to the database with IAM credentials (the Access Keys and Secret Keys that you use to authenticate to the APIs) instead of using MySQLs traditional users and passwords.
Why use IAM credentials for databases
RDS databases start out with one initial user and password that you have configured. That user is an administrative “root” user that has lots of power. You shouldn’t be using those credentials in your applications!
- Using IAM, the database users are defined outside of your databases, so you can define from IAM to what RDS instances
- You can use IAM roles to connect to your RDS instances so that you don’t need to manage long-term Access and Secret Keys
I want to do that too
If you like the idea of using IAM credentials to connect to your RDS instance, and you read the RDS manual about this feature, you might think “this isn’t for me” since the manual only explains how to connect with the MySQL command line client and Java.
Tell me how it works
Basically, you need to create an RDS with the “IAM database authentication” feature enabled, and an IAM user that lets you
AWS_ACCOUNT_ID is your 12 digit AWS account identifier, which you can find in the upper right part of the AWS console (or in any ARN for your resources. See below in the example how you can obtain it).
RDS_INSTANCE_ID is the RDS “Resource Id” (watch out! It’s not the RDS instance name!) of an RDS instance, or * to grant permission to authenticate to an RDS instance.
DATABASEUSER is the name of a user created in the MySQL database with a CREATE USER statement, and later a GRANT, so it can access some database.
You also have to appropriately set up a database user to be able to authenticate with the new scheme (note that you can still create and use non-IAM-authenticated MySQL users since the IAM authenticated ones have to be explicitly enabled).
You may have thought that you were going connect to the RDS instance with the Access Key as the user and the Secret Key as the password to your database. One little-known thing about how AWS authenticates API requests is that the Secret Key never leaves the client machine (it never gets transmitted to the servers). Instead, the Secret Key is used to sign a request, so that AWS can verify that it’s been signed with the Secret Key. This means that requests can get intercepted, and that isn’t enough for the interceptor to generate new requests. So the RDS IAM authentication uses the same signing scheme that the APIs use.
Since the authentication scheme has to transmit a signature, we have to use a little-known capability of the MySQL client to transmit passwords in clear. The default protocol of MySQL sends the password that you specify to the MySQL client hashed to the server. Since we have a signature that has to arrive intact to the MySQL server, it shouldn’t get manipulated (if it gets hashed, then the server cannot make sense of it), and that’s why we use MySQL clients capability of sending whatever you specify as a password over the wire.
For added protection, you have to encrypt the MySQL connection to use the IAM authentication scheme (or else the database will not authenticate the user).
Show me how to do it!
These instructions are made so you can copy and paste them into your console session. You need the aws command line client installed and configured.
First we create an RDS instance with MySQL
Open the RDS instance so you can connect to it
Create an IAM user with a policy that will let us connect to the database
Now we’re going to create a MySQL user that lets you connect to the database with dbiamuser. Wait for the RDS instance to be completely created before executing the following commands.
And now “la piece de résistance“! We’re going to connect to the RDS instance!
I’ve chosen to do this with Perl, since it has standard libraries to connect to MySQL, and I want to feel the experience of getting something not documented to work. There is a script in this repository that will connect you to the RDS instance, calculating the signature beforehand, and setting the appropriate client options to connect. Other languages libraries will certainly have similar options, but I’m sure that seeing how it’s done in Perl will help you.
Note: if you get an error talking about “can’t use DBI” or “can’t find the MySQL driver”, you may need to install Perl’s DataBase Interface. apt-get install -y libdbd-mysql-perl will do all the magic for you if you’re on Debian/Ubuntu.
- The first time you connect, there will be a small delay, but don’t despair. Subsequent connections are immediate.
- The fact that you have to connect to the database to enable a user to connect with IAM credentials can be a bit frustrating if you’re creating new RDS instances, via CloudFormation, for example. A workaround can be creating the RDS instances from database snapshots that already have the users created.
- If the instance that connects to your RDS is running in EC2 or ECS don’t generate an IAM user, and instead, use an IAM Role so that you don’t have to manage the Access and Secret keys!
- Administrative IAM users (all actions on all resources) will also be able to authenticate to your RDS instance with any IAM-enabled user. This may not be apparent when you enable the IAM authentication feature.
- The fact that an IAM user can authenticate on an instance doesn’t mean he can do so from any place. Remember that Security Groups have to be properly open to the origin of the connection
- When creating the IAM policy, the IAM console warns about the policy “not granting any permissions”, and the service “rds-db” not being recognized. Don’t worry about it (just check that your policy is correct), as the policy validators don’t seem to know about these policies yet.
- The first time I built a restrictive IAM policy for connecting to an RDS instance I put the RDS instance name in the place of the RDS_RESOURCE_ID field arn:aws:rds-db:eu-west-1:$AWS_ACCOUNT_ID:dbuser:$RDS_RESOURCE_ID/$DB_CONNECT_USER. They are not the same (and thus the policy won’t work). To find the correct RDS_RESOURCE_ID, you have to look in the Details section of your RDS instance.
- How to generate auth tokens
- Notes on how to build the IAM policy
- DBD::MySQL options
- MySQL client cleartext password documentation
The canonical, up-to-date source of this post is on GitHub. Feel free to contribute back.