4

I am connecting to a remote mysql database (on my local network) from a flask app using ssl connection.

app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://user:password@192.168.1.24/AwesomeDB?ssl_ca=client-ssl/ca.pem&ssl_cert=client-ssl/client-cert.pem&ssl_key=client-ssl/client-key.pem"

Any call to the database throws this mysterious error.

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '192.168.1.24' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: IP address mismatch, certificate is not valid for '192.168.1.24'. (_ssl.c:1122))")

I can log into the mysql monitor from terminal just fine.

mysql -u user -p -h 192.168.1.24 --ssl-ca=client-ssl/ca.pem --ssl-cert=client-ssl/client-cert.pem --ssl-key=client-ssl/client-key.pem

I am on macos 11.0.1 and python 3.9.0. I have tried installing and linking certifi as described here. Everything works fine without ssl. Please help.

Edit: the problem is not specific to macos, I tried connecting from ubuntu with the same result.

lead-free
  • 128
  • 2
  • 11

2 Answers2

1

I was facing a similar issue when trying to connect to CLoudSQL(GCP)using Flask SQL alchemy over SSL. Looks like CLoudSQL somehow prefers mysqlconnector over pymysql driver when used with Flask SQL alchemy. So replacing the pymysql driver with mysqlconnector in SQLALCHEMY_DATABASE_URI did the trick.

Make sure you have mysqlconnector package installed

pip install mysql-connector-python via pip

Here's a small snippet of the working code

>>> from flask import Flask
>>> from flask_sqlalchemy import SQLAlchemy
>>>
>>> app = Flask(__name__)
>>>
>>> app.config["SQLALCHEMY_TRACK_MODIFICATIONS"]=False 
>>>
>>> app.config["SQLALCHEMY_DATABASE_URI"] = 'mysql+mysqlconnector://{username}:{password}@{db_hostname}/{database}?ssl_ca=server-ca.pem'
>>>
>>> db = SQLAlchemy(app)
>>>
>>> class User(db.Model):
...   id = db.Column(db.Integer, primary_key=True)
...   username = db.Column(db.String(80), unique=True, nullable=False)
...   email = db.Column(db.String(120), unique=True, nullable=False)
...   def __repr__(self):
...     return '<User %r>' % self.username
...
>>> db.create_all()
>>>
>>> admin = User(username='admin', email='admin@example.com')
>>> guest = User(username='guest', email='guest@example.com')
>>>
>>> db.session.add(admin)
>>> db.session.add(guest)
>>> db.session.commit()
>>>
>>> User.query.all()
[<User 'Flask'>, <User 'admin'>, <User 'guest'>]

Note: In cases wherever possible, it is preferred to use pymysql adapter as it's more performant even though mysqlconnector is officially supported by Oracle. Both are implemented in pure python though.

Abhishek J
  • 101
  • 7
  • related: https://stackoverflow.com/a/66403127/2144390 – Gord Thompson Jun 18 '21 at 18:34
  • @snakecharmerb - Thanks for pointing it out. updated the link: here's a nice document that compares different MySQL DB drivers: https://wiki.openstack.org/wiki/PyMySQL_evaluation#MySQL_DB_Drivers_Comparison – Abhishek J Jun 19 '21 at 07:14
0

As stated in the docs, "If the server uses an automatically-generated certificate that is self-signed or does not match the host name (as seen from the client), it may also be necessary to indicate ssl_check_hostname=false"

https://docs.sqlalchemy.org/en/14/dialects/mysql.html#ssl-connections

So you could try :

app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://user:password@192.168.1.24/AwesomeDB?ssl_ca=client-ssl/ca.pem&ssl_cert=client-ssl/client-cert.pem&ssl_key=client-ssl/client-key.pem&ssl_check_hostname=false"

and see how it goes. This did the trick for me.

vittorio
  • 138
  • 2
  • 11
  • I get an error with the above config ```__init__() got an unexpected keyword argument 'ssl_check_hostname'``` Am using the same mysql+pymysql. How did you fix this? – Sowmea shree Nov 01 '22 at 13:39