AWS RDS MySQL Token Access In Python

We use AWS' RDS to manage our relational database needs.  One quirk in this service is that, in addition to all the cloud setup stuff, you need to log in to the database with a traditional driver and an old-school username/password pair.  Given pretty much every other cloud service uses public key cryptography with timestamps to keep things secure this feels a bit odd.

AWS introduced an IAM-based scheme involving tokens quite a while ago.  We were quite excited by this.  And spent a lot of time -- on and off -- trying to make it work.  Sadly the documentation leaves a lot to be desired and there are a few landmines to sidestep.  So here, step-by-step, is how to make it work.

Setup

We are going to set up IAM-based authentication for a single MySQL instance using the PyMySQL driver in python 3.  Anyone who can make this work with a different setup please please please comment below or email us.  We assume you have full admin access to RDS, IAM and your database instance.

Steps

RDS Instance Setup

1 - Get MySQL running on RDS and get working admin access via MySQL Workbench or something similar.  Call the RDS "Resource ID" rds_id.  It should look like db-ABCDEFGH...  This is not the name or endpoint or arn.

2 - Enable "IAM DB Authentication" per this link.

3 - Create a user in the MySQL instance per this link.  Remember this username -- it is used only for logging in to the database but you'll still need it later.  We call this "db_user" here.

4 - Permission this user to access your schema and do useful things to it.  In MySQL Workbench execute:

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.* TO db_user

Adjust permissions as needed.  The graphical controls in Workbench do not work (see below).

5 - Create or choose an IAM user/role that will access the RDS instance.  This name can be different than "db_user."

6 - Ensure your user/role has access to the RDS instance.  For a user this is straightforward.  For a role you'll need to configure whatever service you are using to have access.

7 - Attach the required policy to the IAM user/role.  This is sort-of explained at this link.  Let's take it step-by-step.

Go in to IAM and select your role or user.  Click "Permissions" and then "Add inline policy" down at the bottom.

Edit the JSON directly and create a policy that looks like:

{ "Version": "2012-10-17",
  "Statement": [ { "Effect": "Allow",
                   "Action": [ "rds-db:connect" ],
  "Resource": [ "arn:aws:rds-db:rds_region:aws_account_num:dbuser:rds_id/db_user" ]
  }]
}

Substitute rds_region, rds_id, aws_account_num, and db_user for your setup.  When you try to save/attach the policy AWS will warn you that it doesn't recognise the service rds-db, doesn't recognise these rds-db actions and that the resource referenced by the ARN may not be supported.  It will also warn you that the policy doesn't do anything.  Ignore all of those warnings and attach/save the policy anyway.

8 - Now we move to python.  The code snippet you need is:

rds = boto3.client('rds')
token = rds.generate_db_auth_token(rds_hostname,
                                   rds_post,
                                   db_user,
                                   rds_region)
dbConnection = pymysql.connect(auth_plugin_map = {'mysql_cleartext_password':None},
                               user = db_user,
                               host = rds_hostname,
                               password = token,
                               ssl={'ca': SSLCAFile},
                               database = schema)

SSLCAFile is the local location for the AWS .pem file.

You'll need to have a recent version of pymysql and it's dependencies installed for that to work.

9 - Debugging

You can use that code snippet to generate a token and then log in via Workbench to your database with db_user as the username and the token as the password.  This requires enabling SSL and setting the CA file in Workbench's connection setup.  That's some kind of weird 2 factor authentication scheme as running the python script requires an AWS secret key.

Code Samples

Here is a sample pymysql token generator you can use to log in via Workbench: https://github.com/DataFinnovation/blog-content/blob/master/aws-rds-mysql-token-access-in-python/pymysql_token_gen.py

A Note on mysql-connector-python

This driver doesn't seem to work for AWS tokens.  The token is quite long and generates an error message like:

auth_response = struct.pack('<B', resplen) + plugin_auth_response
struct.error: ubyte format requires 0 <= number <= 255

If you truncate the token down to under 255 characters the error message changes to a more traditional bad-password complaint.  Given MySQL's password limit is much lower than that this isn't quite a bug.

Once you have pymysql working here is a python snippet using mysql-connector that exposes this problem:  https://github.com/DataFinnovation/blog-content/blob/master/aws-rds-mysql-token-access-in-python/mysql_token_gen.py.  It's possible that works on some future date -- if you are reading this and it does please contact me to update the post.

AWS Issues

Given AWS' own stated password length limits here all of this should never work.  That document should be modified and/or generate_db_auth_token should return something under the stated limit.  boto3 knows your instance details so that's not a big ask.

IAM should understand these rds-db arns and stop with those erroneous warning and error messages.  This should also exist in some form as a standard policy.

And lastly MySQLWorkbench doesn't work well with the AWSAuthenticationPlugin setting.  Trying to add those permissions with the panels/buttons just generates more error messages.  That's why you need to type in the "grant select ..." sql statement and execute it.  Maybe this should get rolled into the IAM policy setup/attaching/etc process?  At a minimum the error messages should get cleaned up (Workbench is open source).