python MySQLdb: use ssl without certificate

Question

I have a mysql server running on AWS which requires to authenticate via SSL (but does not require a user certificate).

What I tried:

Connecting without a cert works fine via SequelPro (GUI):

It also works when using mysqlconnector with an empty ssl param: 'ssl_ca': ''. But unfortunately not with MySQLdb.

I tried (using a connection string)

1.

conn = sqlalchemy.create_engine(
    con_str,
    connect_args={'ssl':{'ca': ''}})
pd.read_sql_query('select id from mytable limit 1', conn)

2.

conn = sqlalchemy.create_engine(
    # the following is used to enforce mysqlconnector usage
    con_str.replace("mysql:", "mysql+mysqlconnector:"),
    connect_args={'ssl_ca':''})
pd.read_sql_query('select id from mytable limit 1', conn)

The second works fine, the first not. Of course I also tried around with the bare connectors (MySQLdb.connect() and mysql.connector.connect()) and experienced the same behaviour and couldn't bring MySQLdb to work.

Question:

Can you give me some hints on how to use SSL in MySQLdb without a cert (and key)?

Further Background:

We switched to AWS from another provider so unfortunately no more ssh like before and now SSL. And I'm not administrating the DB so I can't make it using user certificates, am only being forced to use ssl without any certs.

A colleague explained that this is okay from a security viewpoint, because the server sends a cert. We trust him that he is the one belonging to the corresponding URL because we trust the CA.

Are you connecting to it from the same box? Is the MySQL instance publicly accessible? It's a bad idea to not use a cert in either of those cases. — Bailey Parker
– Bailey Parker, Commented Jan 25, 2018 at 11:49
No, I try accessing it from my local machine. The instance is not public accessible. I ran in this issue when moving from old hoster to AWS and now being "forced" to use ssl — Nico Albers
– Nico Albers, Commented Jan 25, 2018 at 11:51
If you are accessing it from your local machine then there is a chance that the machine you are talking to is not your mysql database. The purpose of the SSL cert is to ensure that you are in fact talking to your DB. Without it communication between you and the DB is not secure. — Bailey Parker
– Bailey Parker, Commented Jan 25, 2018 at 11:52
Yeah, I know. I'd prefer to use ssh like before, but this is not an option any more. And I'm not administrating the DB so I can't use user and server certificates, only being enforced to use ssl without cert. :-( Not happy with that too — Nico Albers
– Nico Albers, Commented Jan 25, 2018 at 12:12

identitycrisis · Accepted Answer · 2020-01-09 05:21:59Z

6

Might not work in your case, but I was also looking to do this in pymysql, and it seems like passing a dict with any key works:

connect_args={'ssl': {'key': 'whatever'}}

But then I found out mysql-connector-python tries ssl even without any parameters. I switched because of another reason.

answered Jan 9, 2020 at 5:21

identitycrisis

611 silver badge3 bronze badges

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

1 Comment

Holonet Over a year ago

Underrated answer, it took me days to find this, thanks!

Collectives™ on Stack Overflow

python MySQLdb: use ssl without certificate

What I tried:

Question:

Further Background:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I tried:

Question:

Further Background:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related