Connect to Amazon Aurora using SQLAlchemy

Question

I am trying to connect to Amazon Aurora using 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 Documents] ( http://docs.aws.amazon) ..com / AmazonRDS / latest / UserGuide / UsingWithRDS.IAMDBAuth.html # UsingWithRDS.IAMDBAuth.Connecting )

Here 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 have confirmed that I can connect via SSL using the mysql client.

But I want to connect using sqlalchemy and not mysql client. The following code has been compiled from a dozen tips found on the Internet, but only gives the following error.

'sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1045, "Access denied to 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)

Answer 1

My answer is triple:

IAM Roles

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

By "client" I mean any AWS resource that runs the code from this question. It can be a lambda function, or an instance of EC2, or something else. Whatever it is, he has an ARN, and he must be allowed action rds-db:connect.

URL quoted string

RDS. , URL-, , , URL-.

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

RDS

, , RDS . , :

# 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}

:

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)

user passwd connect_args. URL.

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