SSL integration with Cloud SQL instance based MySQL

Question

I enabled SSL in a MySQL Cloud SQL instance. In order to connect to the instance , I downloaded the necessary certficates and can connect fine using mysql command. The CloudSQL instance is running with Private IP on a sharedVPC network .

   $ mysql -h 192.168.0.3 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u testuser -p
    Enter password:

Now to test connectivity from a code to connect to SQL instance I deployed the following in Cloud Functions

  import pymysql
  from sqlalchemy import create_engine

  def sql_connect(request):
       engine = create_engine('mysql+pymysql://testuser:<password>@192.168.0.3/mysql',echo=True)
       tab = engine.execute('show databases;')
       return str([t[0] for t in tab])

It shows "Access Denied" error as shown below

  Error: function terminated. Recommended action: inspect logs for termination reason. Additional troubleshooting documentation can be found at https://cloud.google.com/functions/docs/troubleshooting#logging Details:
(pymysql.err.OperationalError) (1045, "Access denied for testuser'@'192.168.60.4' (using password: YES)")

When I disable SSL it works fine as shown below

  ['information_schema', 'mysql', 'performance_schema', 'sys', 'testdb']

A) To enable SSL in code I did the following

   ssl_args = {'sslrootcert':'server-ca.pem','sslcert':'client-cert.pem','sslkey':'client-key.pem'}  
   engine = create_engine('mysql+pymysql://testuser:<password>@192.168.0.3/mysql',echo=True,connect_args=ssl_args)

but it is failing with below error

 __init__() got an unexpected keyword argument 'sslrootcert'

B) Also tried disabling ssl=False in code but it is failing with below error

  Invalid argument(s) 'ssl' sent to create_engine(), using configuration MySQLDialect_pymysql/QueuePool/Engine

UPDATE:

Changed the code for SSL as follows:

ssl_args = {'ssl': {'ca':'./server-ca.pem', 'cert':'./client-cert.pem', 'key':'./client-key.pem'}}

Uploaded the certs to cloud function source
Added 0.0.0.0/0 as authorized networks in CloudSQL to allow connecting from Cloud functions

Now seeing the following error

   "Can't connect to MySQL server on 'X.X.X.181' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: IP address mismatch, certificate is not valid for 'X.X.X.181'. (_ssl.c:1091))") . However can connect using the same certificates using `mysql` command

Need help to resolve both A) fixing the error as observed so that the code is integrated with SSL and B) Modify code so that it does not uses SSL

Hello, So about the first error, I think the error is how are you referencing the certificates in the configuration, after looking at this two posts ( [post1](https://stackoverflow.com/questions/62685852/sqlalchemy-pymysql-error-while-connecting-to-rds-using-iam-token-init-go) and [post2](https://stackoverflow.com/questions/53989887/how-do-i-configure-pymysql-connect-for-ssl) ), this seems to be the problem to the first part of the post (you need to use a dict), although I could not find nothing from the second part, I think it could be a similar issue that is a reference issue. — Luis Manuel, Dec 22 '20 at 16:06

score 1 · Answer 1 · answered Dec 21 '20 at 19:40

1

Use ssl_ca for the root, ssl_cert for the cert and ssl_key for the key

   ssl_args = {'ssl_ca':'server-ca.pem','ssl_cert':'client-cert.pem','ssl_key':'client-key.pem'}  
   engine = create_engine('mysql+pymysql://testuser:<password>@192.168.0.3/mysql',echo=True,connect_args=ssl_args)

answered Dec 21 '20 at 19:40

guillaume blaquiere

66,369
2
47
76

Still the same error.. "__init__() got an unexpected keyword argument 'ssl_ca'" – Zama Ques Dec 22 '20 at 14:53

score 0 · Answer 2 · answered Jun 18 '21 at 11:52

Use SSL parameter in the form ssl = {"ssl":{"ca":"server-ca.pem"}} within the connect function

from pymysql import connect
from sqlalchemy import create_engine
import os

SQLALCHEMY_DATABASE_URI='mysql+pymysql://testuser:<password>@192.168.0.3/mysql?ssl_ca=server-ca.pem'

engine = create_engine(SQLALCHEMY_DATABASE_URI)

args, kwargs = engine.dialect.create_connect_args(engine.url)

# Create connection to the DB
db_conn = connect(kwargs["host"], kwargs["user"], kwargs["passwd"], kwargs["db"], ssl = {"ssl":{"ca":kwargs["ssl"]["ca"]}})

cursor = db_conn.cursor()

# Execute query
cursor.execute("show tables")

cursor.fetchall()

SSL integration with Cloud SQL instance based MySQL

2 Answers2