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 and post2 ), 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
– Luis Manuel, Commented Dec 22, 2020 at 16:06

guillaume blaquiere · Accepted Answer · 2020-12-21 19:40:10Z

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, 2020 at 19:40

guillaume blaquiere

76.6k3 gold badges65 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zama Ques Over a year ago

Still the same error.. "__init__() got an unexpected keyword argument 'ssl_ca'"

Abhishek J · Accepted Answer · 2021-06-18 11:52:34Z

0

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()

answered Jun 18, 2021 at 11:52

Abhishek J

1019 bronze badges

Collectives™ on Stack Overflow

SSL integration with Cloud SQL instance based MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related