MySQL-python: Adding caching_sha2_password and TLSv1.2 Support

PREVIOUS POST
NEXT POST

python not connecting to MySQLPython 2 reaches EOL on 2020-01-01 and one of its commonly used third-party packages is MySQL-python. If you have not yet migrated away from both of these, since MySQL-python does not support Python 3, then you may have come across some issues if you are using more recent versions of MySQL and are enforcing a secured installation. This post will look at two specific issues that you may come across (caching_sha2_password in MySQL 8.0 and TLSv1.2 with MySQL >=5.7.10 when using OpenSSL) that will prevent you from connecting to a MySQL database and buy you a little extra time to migrate code.

For CentOS 7, MySQL-python is built against the client library provided by the mysql-devel package, which does not support some of the newer features, such as caching_sha2_password (the new default authentication plugin as of MySQL 8.0.4) and TLSv1.2. This means that you cannot take advantage of these security features and therefore leave yourself with an increased level of vulnerability.

So, what can we do about this? Thankfully, it is very simple to resolve, so long as you don’t mind getting your hands dirty!

Help! MySQL-python won’t connect to my MySQL 8.0 instance

First of all, let’s take a look at the issues that the latest version of MySQL-python (MySQL-python-1.2.5-1.el7.rpm for CentOS 7) has when connecting to a MySQL 8.0 instance. We will use a Docker container to help test this out by installing MySQL-python along with the Percona Server 8.0 client so that we can compare the two.

Next we will create a client config to save some typing later on and then check that we can connect to the MySQL instance that is already running; if you don’t have MySQL running on your local machine then you can install it in the container.

Changing the user’s authentication plugin

We have hit the first issue, because MySQL 8.0 introduced the caching_sha2_password plugin and made it the default authentication plugin, so we can’t connect at all. However, we can gain access by changing the grants for the user to use the old default plugin and then test again.

Configuring SSL options

We still can’t connect, so what can be wrong? Well, we haven’t added any SSL details to the config other than specifying that we need to use SSL, so we will add the necessary options and make sure that we can connect.

Forcing TLSv1.2 or later to further secure connections

That looks much better now, but if you look closely then you will notice that the MySQLdb connection is using TLSv1, which will make your security team either sad, angry or perhaps both as the connection can be downgraded! We want to secure the installation and keep data over the wire safe from prying eyes, so we will remove TLSv1 and also TLSv1.1 from the list of versions accepted by MySQL and leave only TLSv1.2 (TLSv1.3 is not supported with our current MySQL 8.0 and OpenSSL versions). Any guesses what will happen now?

MySQLdb can no longer connect to MySQL, sadly with a rather cryptic message! However, as we made the change that triggered this we don’t need to decipher it and can now start looking to add TLSv1.2 support to MySQLdb, so roll up your sleeves!

Solution: Build a new RPM

In order to build a new RPM we will need to do a little extra work in the container first of all, but it doesn’t take long and is pretty simple to do. We are going to install the necessary packages to create a basic build environment and then rebuild the MySQL-python RPM from its current source RPM.

We are now ready to start making some changes to the source code and build specifications, but first of all we need to take note of another change that occurred in MySQL 8.0. Older code will reference my_config.h, which has since been removed and is no longer required for building; the fix for this will be shown below.

Now that we have adjusted the source code and specification we can start work on the new package so that we can once again connect to MySQL.

All went well and so we can now install the new package and see if it worked.

Almost there… now force user authentication with the caching_sha2_password plugin

Hurrah! We can once again connect to the database and this time we are using TLSv1.2 and have thus increased our security. There is one thing left to do now though. Earlier on we needed to change the authentication plugin, so we will now change it back for extra security and see if all is still well.

Mission successful! Hopefully, if you are finding yourself in need of a little extra time migrating away from MySQLdb then this will help.


Image modified from photo by David Clode on Unsplash

PREVIOUS POST
NEXT POST

Share this post

Comments (3)

  • lefred Reply

    Hi Ceri,

    I would recommend to use mysql-connector-python for MySQL 5.5, 5.6,5.7 & 8.0 with Python 2.7, 3.4, 3.5 & 3.7.

    It also supports the X Protocol and builtin support for Django too. See https://dev.mysql.com/downloads/connector/python/

    Cheers 😉

    April 18, 2019 at 2:35 pm
    • Ceri Williams Reply

      Thanks for the comment lefred.

      Sadly, MySQL Connector/Python is not a drop-in replacement and would require code changes to use it.

      This post is about buying some time ahead of migrating code, since TLSv1 and TLSv1.1 should be disabled where possible, thus adding support to MySQL-python in around 5 minutes and bouncing MySQL is much faster than code changes, QA and release. Historically, MySQL-python was tried-and-tested and performed much better than MySQL Connection/Python; it would be interesting to see how this compares with the latest version though.

      April 18, 2019 at 4:42 pm
    • Aleksandr Kuzminsky Reply

      Hi Lefred!

      Not in Pypi – didn’t happen 😉

      Same time pymysql both supports 3.x and is a drop-in replacement. Never used it with ssl though.

      April 18, 2019 at 5:34 pm

Leave a Reply