Drop connection if SSL is not available.

[Teodor Moroz]

I am using sqlalchemy to connect to various DB's. When MySQL is in use, I want to force TLS connection. Meaning that if a secure connection was not obtained, we should not connect at all. So I added:

skip_ssl

to my.cnf and restarted MySQL server.

When I try to connect to the DB via command line:

mysql -h $host -u $user -p --ssl-ca=$ssl_ca --ssl-cert=$cert --ssl-key=$key

everything works as expected. I am not connected and the following error is shown:

ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it

But when I specify the same values to the create_engine method, via connect_args:

connect_args = {'ssl' : { 'ca': ca, 'key': client_key, 'cert': client_cert, }}

It still connects just fine. How can I force the same behavior from within the sqlalchemy? So I am kicked off when SSL is disabled and I explicitly want to use SSL?

[Mike Bayer]

hey there -

this has to do with the MySQL driver you're using.  Can you please provide information on that ?   Usually you'd want to send an issue to the github tracker of that driver directly as I'm not familiar with this option.

Things I can note are:

1. skip-ssl is deprecated in MySQL: https://dev.mysql.com/doc/refman/5.7/en/connection-options.html#option_general_ssl

2. skip-ssl is a client side variable.  the MySQL DBAPI you are using to connect must support being able to locate and honor this file (e.g. mysqlclient, pymysql, mysql-connector, etc).  PyMySQL for example may need you to specify read_default_file: https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L139

3. the DBAPI would have to know how to honor this specific variable.    Grepping through pymysql I don't see any mention of this variable so it's likely not supported.   If you were using mysqlclient, that would be using MySQL native client libraries which are more likely to be behaving the same way as your command line client.

hope this can help you get started.

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-devel/fcd4d49d-613b-44c9-a934-3688eb62ba7cn%40googlegroups.com.

[Teodor Moroz]

Hi Mike,

1) I am using version 5.7, so this arg is fine

2) It works as a server-side var as well. I tested it with the usual MySQL client, and if this option is set on the server, MySQL client throws me away, claiming that SSL is not supported. Which is exactly what I need in my application as well.

3) I am using the standard mysqldb module.  mysldb does not throw an exception when SSL dict is specified. Meaning when I create a simple script that just tries to connect using mysqldb directly. The connection is still established. But when I pass ssl_mode='REQUIRED', then everything works as expected. So I simply patched sqlalchemy with this option and now everything within sqlalchemy works fine as well.

So my question is, should I create a PR for sqlalchemy? Because obviously always falling back to an unencrypted connection is a serious security caveat. 

Thanks,

Teodor.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-devel" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-devel/mpyw1r-xvi0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-devel/8a72a035-8cbf-42bc-ba72-5aeed01df48d%40www.fastmail.com.

[Mike Bayer]

On Wed, Nov 4, 2020, at 2:37 PM, Teodor Moroz wrote:

Hi Mike,

1) I am using version 5.7, so this arg is fine

2) It works as a server-side var as well. I tested it with the usual MySQL client, and if this option is set on the server, MySQL client throws me away, claiming that SSL is not supported. Which is exactly what I need in my application as well.

3) I am using the standard mysqldb module.  mysldb does not throw an exception when SSL dict is specified. Meaning when I create a simple script that just tries to connect using mysqldb directly. The connection is still established. But when I pass ssl_mode='REQUIRED', then everything works as expected. So I simply patched sqlalchemy with this option and now everything within sqlalchemy works fine as well.

So my question is, should I create a PR for sqlalchemy? Because obviously always falling back to an unencrypted connection is a serious security caveat. 

you mean you'd like to add it to

        keys = ["ssl_ca", "ssl_key", "ssl_cert", "ssl_capath", "ssl_cipher"]

right?    Sure, make sure you include it in test/dialect/mysql/test_dialect -> _test_ssl_arguments as well.

note you can send this ssl dict explicitly also:

create_engine("mysql://user:pass@host/dbname", connect_args={"ssl": {"ssl_mode": "...", "ssl_...": ..."}})

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-devel/CALW2zk5NpBS_Vgz64GgtxHceLtFovXui9jUBHcSSG%3D81pE%3DXYg%40mail.gmail.com.