Setting up RDS (MySQL) database access using IAM to generate access tokens

user_518703 0
Viewed 2135

I have followed the instructions to set up AWS and MySQL such that I should be able to sign in to mysql using mysql-client and a user (named aws_iam) without a password, but with a token generated by awscli with the role attached to my EC2 instance.

The instructions are here

So what I have is:

  • An EC2 instance with a role which allows me to generate RDS credentials
  • An RDS instance running MySQL, with a user aws_iam which is identified by AWSAuthenticationPlugin
  • When signed in to the EC2 instance via SSH I can run mysql -h mydb.randomstring.region.rds.amazonaws.com -u root -p and enter the master password from the RDS set up to get a mysql shell.
  • Also when signed in to the EC2 instance via SSH I can run aws rds generate-db-auth-token --hostname mydb.randomstring.region.rds.amazonaws.com --port 3306 --username aws_iam and when running this I get a token that looks like this:

mydb.randomstring.region.rds.amazon.aws.com:port-number/?Action=connect&DBUser=aws_iam&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Expires=900&X-Amz-Date=current_time&X-Amz-SignedHeaders=host&X-Amz-Security-Token=really-long-url-encoded-string&X-Amz-Credential=string/region/rds-db/aws4_request&X-Amz-Signature=long-hash

Then I run a connection command:

mysql -h mydb.randomstring.region.rds.amazonaws.com --ssl-ca=rds-ca-2015-eu-west-1.pem --ssl-mode=VERIFY_IDENTITY -u aws_iam --enable-cleartext-plugin --password=TOKEN

But then I just get

ERROR 1045 (28000): Access denied for user 'aws_iam'@IP (using password: YES)

A few things I have noticed:

  • the "token" is originally url encoded; however decoding it doesn't work either
  • the token has lots of parameters in a url format; it is possible only one of these parameters is the actual token but this isn't mentioned in the documentation
  • if your EC2 role doesn't have the policy for "rds-db" you can still generate a token. This likely means the token generation doesn't prove that your policy works; but there's no way to debug it other than that

So has anybody enabled this and is there something I have missed?

amazon-iam amazon-rds amazon-web-services
2 Answers

It turned out that my role didn't have the correct permissions. Unfortunately what those wrong permissions were is lost in time, but I seem to recall the "Resource" key is wrong.

Here is the policy where you'll need to substitute the following variables:

  • AWS_ROLE_NAME: the name of your role assigned to the instance
  • AWS_ACCOUNT_ID: your overall account ID (see from the account or billing pages)
  • RDS_REGION: the region your instance is in, e.g. "us-east-1" (doesn't need to specify the AZ for single AZ)
  • RDS_RESOURCE_ID: The code under the title "Resource ID" in the RDS web console for the instance you wish to access (should be of the form db-([A-Z0-9]+))

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "rds-db:connect" ], "Resource": [ "arn:aws:rds-db:RDS_REGION:AWS_ACCOUNT_ID:dbuser:RDS_RESOURCE_ID/AWS_ROLE_NAME" ] } ] }

I've given the correct answer to sqlbot because they answered a lot sooner with useful info, but this policy should help some people out.

user_5187030
Related