How to connect SQLAlchemy to a GCP CLoud SQL instance using SSL?

Question

I am trying to connect to a GCP Cloud SQL Instance using Python 3 and do not want to use the cloud proxy, I just want to connect directly using SSL certs so I followed the GCP guide here to connect from a public IP secured with ssl keys.

Using this works for the mysql client:

mysql -uroot -pMyPassWord -h 1.2.3.4 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

But when I do what I believe is the same in Python I get an error:

from sqlalchemy import create_engine

db_connect_string='mysql+mysqldb://root:[email protected]:3306/mydb'
ssl_args = {'ssl': {'ssl_cert':'./client-cert.pem', 'ssl_key':'./client-key.pem', 'ssl_ca':'./server-ca.pem'}}
engine = create_engine(db_connect_string, connect_args=ssl_args)

print(engine.table_names())

The error is:

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1045, "Access denied for user 'root'@'1.2.3.4' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)

Which is straight forward enough if it wasn't for the fact that I:

Have already added my public IP address 1.2.3.4/32 as an authorised network.
I can access via mysql client so why does the restriction not apply there?

What am I missing?

Johnny John Boy Johnny John Boy · Accepted Answer · 2020-04-19T18:40:24

OK finally got this working. The error message is misleading as you'd expect it to be related to whitelisting my IP but it's not. Here is the working code:

from sqlalchemy import create_engine

db_connect_string='mysql+mysqldb://root:[email protected]:3306/mydb'
ssl_args = {'ssl': {'cert':'./client-cert.pem', 'key':'./client-key.pem', 'ca':'./server-ca.pem'}}
engine = create_engine(db_connect_string, connect_args=ssl_args)

print(engine.table_names())

The mysqlclient needs a dictionary called ssl with key pairs but all other answers I could find on stack had either the wrong ones or maybe they've been changed.

Here's the link to the dictionary required to pass as an argument:

https://mysqlclient.readthedocs.io/user_guide.html#installation

Here is the MySQL documentation which explains the arguments:

https://dev.mysql.com/doc/refman/8.0/en/mysql-ssl-set.html

Full list of arguments here:

mysql: The connection handler returned from mysql_init().
key: The path name of the client private key file.
cert: The path name of the client public key certificate file.
ca: The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server.
capath: The path name of the directory that contains trusted SSL CA certificate files.
cipher: The list of permissible ciphers for SSL encryption.

How to connect SQLAlchemy to a GCP CLoud SQL instance using SSL?

1 Answers