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:
What am I missing?
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: