Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connecting to Amazon Aurora using SQLAlchemy

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)
like image 655
vrlkm17 Avatar asked Sep 20 '17 17:09

vrlkm17


People also ask

Does SQLAlchemy work with AWS?

SQLAlchemy is compatible with all these engines. For this post, we provision an Aurora PostgreSQL database. To deploy the database for our sample application, follow the instructions in Deploy an Amazon Aurora PostgreSQL DB cluster with recommended best practices using AWS CloudFormation.

How do I connect an Aurora database client?

You can connect to an Aurora DB cluster using the same tools that you use to connect to a MySQL or PostgreSQL database. You specify a connection string with any script, utility, or application that connects to a MySQL or PostgreSQL DB instance. You use the same public key for Secure Sockets Layer (SSL) connections.

How do I connect to Postgres Aurora?

Open the context (right-click) menu for Servers, and then choose Create, Server. Enter information in the Create - Server dialog box. On the Connection tab, add the Aurora PostgreSQL cluster address for Host and the PostgreSQL port number (by default, 5432) for Port. Provide authentication details, and choose Save.


Video Answer


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

like image 130
Freek Wiekmeijer Avatar answered Oct 13 '22 07:10

Freek Wiekmeijer