CODEWITHSUNDEEP
mysqlsqlalchemygoogle-cloud-functionsgoogle-cloud-sql
Zama Ques
Zama Ques

Reputation: 1544

SSL integration with Cloud SQL instance based MySQL

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:

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

  2. Uploaded the certs to cloud function source

  3. 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

Upvotes: 0

Views: 298

Answers (2)

Abhishek J
Abhishek J

Reputation: 111

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

Upvotes: 0

guillaume blaquiere
guillaume blaquiere

Reputation: 76000

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)

Upvotes: 1

Related Questions