Connecting to Amazon Aurora using SQLAlchemy

user_8637322 0
Viewed 8273

I am trying to connect to Amazon Aurora with SQLAlchemy using an SSL connection, specifying the IAM role as the database user account and the authentication token as the password, as described here in [AWS docs] (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html#UsingWithRDS.IAMDBAuth.Connecting)

These are the steps I followed.

wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

aws rds generate-db-auth-token --hostname 'datadbcluster-1.cluster-xxxxxxxxxxxx.us-west-2.rds.amazonaws.com' --port 3306 --username dt_analyst --region us-west-2 > /home/ubuntu/dt_analyst.pem

mysql -h datadbinstance2nd. xxxxxxxxxxxx.us-west-2.rds.amazonaws.com--ssl-ca /home/ubuntu/rds-combined-ca-bundle.pem -u dt_analyst --ssl-verify-server-cert --enable-cleartext-plugin -p'<token>'

I verified that I can connect over SSL using the mysql client.

But I want to connect using sqlalchemy and not the mysql client.  The following code is compiled from a dozen pieces of advice found on the internet but only yields the following error.

‘sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1045, "Access denied for user…”)’

My code is as follows.

import boto3

client = boto3.client('rds', region_name='us-west-2')

dialect='mysql'
user = ‘dt_analyst’
host = 'datadbcluster-1.cluster-xxxxxxxxxxxx.us-west-2.rds.amazonaws.com'
port = 3306
data = ‘datadb’
region='us-west-2'

token = client.generate_db_auth_token(host,port,user,region)
host1 = 'datadbinstance2nd. xxxxxxxxxxxx.us-west-2.rds.amazonaws.com'
conn_str = '%s://%s:%s@%s:%d/%s'%(dialect,user,token,host1,port,data)
conn_str += '?ssl_key=%s'%token
conn_str += '&ssl_cert=’/home/ubuntu/rds-combined-ca-bundle.pem'

ssl_args = {
    'ssl': {
        'ca_cert': '/home/ubuntu/rds-combined-ca-bundle.pem',
        'sslmode': 'require',
        'verify_ssl_cert': True
    }
}

engine = create_engine(conn_str,connect_args=ssl_args, echo=True)
amazon-aurora sqlalchemy python
1 Answers

My answer is threefold:

IAM Roles

There is one step that was not mentioned here: you need to assign an IAM role which gives your client the right to connect to RDS.

With "client" I mean whatever AWS resource is running the code from this question. Could be a lambda function, or an EC2 instance, or something else. Whatever it is, it has an ARN and it must be allowed the action rds-db:connect.

URL quoting connection string

Then there may be an issue with RDS credentials. You may have to URL quote the password if it contains characters which are not allowed in a URL.

  • Python 3: passwd = urllib.parse.quote(rds_password)
  • Python 2: passwd = urllib.quote(rds_password)

RDS temporary credentials

Finally, it could be that your RDS database is requiring temp credentials. This code sample shows how to obtain these:

# get RDS config, often done through environment variables
import os

rds_host = os.environ.get("RDS_HOST")  
rds_port = os.environ.get("RDS_PORT")
rds_username = os.environ.get("RDS_USER")   

# get temp credential
import boto3

temp_passwd = boto3.client('rds').generate_db_auth_token(
    DBHostname=rds_host,
    Port=rds_port,
    DBUsername=rds_username 
)

rds_credentials = {'user': rds_username, 'passwd': temp_passwd}

Bringing it together:

def create_sqlalchemy_engine(self):
    conn_str = f"{protocol}://{rds_host}:{rds_port}/{database}"

    kw = dict()
    kw.update(rds_credentials)
    # kw.update({'ssl': {'ca': /path/to/pem-file.pem}})  # MySQL
    # kw.update({'sslmode': 'verify-full', 'sslrootcert /path/to/pem-file.pem})  # PostgreSQL

    return create_engine(conn_str, connect_args=kw)

I moved the user and passwd parameters from the conection string to connect_args. This takes away the need for the URL quoting.

See also: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html

user_11582410
Related