How to authenticate in MySQL RDS using IAM DB Authentication and Python

Question

I have an AWS RDS DB running MySQL 5.6.39, with IAM DB Authentication Enabled.

First of all, I completed with success the Tutorial: Configuring a Lambda Function to Access Amazon RDS in an Amazon VPC and this was my starting point for the next steps.

I want to log in with IAM credentials and so, following this and this tutorials, I did:

1. When I created the RDS MySQL instance, I selected Enabling IAM database authentication.

2. Created a user named lambda:

   CREATE USER 'lambda' IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';
   GRANT ALL PRIVILEGES ON test_db.* TO 'lambda'@'%';
   FLUSH PRIVILEGES;
   

3. Created an IAM policy, and attached it to the role I was using as an Execution role for my lambda function:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [
           "rds-db:connect"
         ],
         "Resource": [
           "<DB-ARN>/lambda"
         ]
       }
     ]
   }
   

4. Created a lambda function:

   import sys
   import boto3
   import logging
   import pymysql
   
   #rds settings
   rds_host  = "<RDS-ENDPOINT>"
   username = "lambda"
   db_name = "test_db"
   
   logger = logging.getLogger()
   logger.setLevel(logging.INFO)
   
   client = boto3.client('rds',region_name='eu-west-2')
   token = client.generate_db_auth_token(rds_host,3306, name)
   ssl = {'ca': 'rds-combined-ca-bundle.pem'} 
   logger.info("token: "+ token)
   
   conn = pymysql.connect(rds_host, user=username, passwd=token, db=db_name, connect_timeout=5, ssl=ssl)
   
   logger.info("SUCCESS: Connection to RDS mysql instance succeeded")
   def handler(event, context):
     ...
   

5. I got the following error:

   error: (1045, "Access denied for user 'lambda'@'<LAMBDA_IP>' (using password: YES)")
   

In an attempt to find If it was a python error I used AWS CLI, from an EC2 instance with the policy attached.

1. Get the token:

   aws rds generate-db-auth-token --hostname <RDS-ENDPOINT> --port 3306 --username lambda
   

2. Connect to the DB, using the token I got in the last step:

   mysql -h <RDS-ENDPOINT> -u lambda --enable-cleartext-plugin --password='<TOKEN>'
   

3. I got the same error:

   mysql: [Warning] Using a password on the command line interface can be insecure.
   ERROR 1045 (28000): Access denied for user 'lambda'@'<EC2_IP>' (using password: YES)
   

Answer 1

I was seeing this error as well after triple-checking the configuration of the database as well as IAM and role attachments. It turned out to be my connector inside my Python lambda function.

My goal was to connect to my RDS MySQL database using sqlalchemy + pymysql. I first figured out the necessary parameters for pymysql and then converted them over to use in sqlalchemy.

Here is my pymysql solution:

conn =  pymysql.connect(
# Maybe this was the parameter I was missing, to tell it to connect without a standard password
   auth_plugin_map={'mysql_clear_password':None}, 
   host=ENDPOINT, 
   user=USER, 
   password=token, 
   port=PORT, 
   database=DBNAME, 
   ssl_ca='RDS_Certificate.pem', 
   ssl_verify_identity=False)

and the sqlalchemy solution:

DBHostname = DBCLUSTER_HOSTNAME
DBPort = DBCLUSTER_PORT
DBUsername = DBCLUSTER_USER
DBName = DBCLUSTER_NAME

engine = sqlalchemy.create_engine(
    "mysql+pymysql:///"
)  # connection params will be set by the event callback

@sqlalchemy.event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
    client = boto3.client("rds")
    token = client.generate_db_auth_token(
        DBHostname=DBHostname,
        Port=int(DBPort),
        DBUsername=DBUsername,
        Region=REGION,
    )

    # set up db connection parameters, alternatively we can get these from boto3 describe_db_instances
    cparams["host"] = DBHostname
    cparams["port"] = int(DBPort)
    cparams["user"] = DBUsername
    cparams["password"] = token
    cparams["database"] = DBName
    cparams["ssl"] = {
        "ca": "ssl-ca-bundle.pem",
        "verify_identity": False,
    }
    cparams["auth_plugin_map"] = {
        "mysql_clear_password": None,
    }

Answer 2

The policy is not correct!

The Resource is not the DB ARN, but "arn:aws:rds-db:<AWS_REGION>:<AWS_ACCOUNT_ID>:dbuser:<AWS_DB_RESOURCE_ID>/<DB_USERNAME>"

To get this information from the management console, you can go to:

• AWS_REGION - The region code, for example eu-west-2 or any other from here.
• AWS_ACCOUNT_ID - Get it from the Account Settings.
• AWS_DB_RESOURCE_ID - Find it in Details\Configuration\Resource ID in the DB page and it starts with db-.
• DB_USERNAME - Is lambda because it was the one created in step 2.

By the way, and as Michael - sqlbot pointed out here and in this answer, the generation of the token is local so getting it should not be interpreted as getting a correct password.