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)
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.
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.
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.
My answer is threefold:
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
.
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.
passwd = urllib.parse.quote(rds_password)
passwd = urllib.quote(rds_password)
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}
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With