SSL MySQL Connection Broke Upgrading 3.1.2 to 3.2

PrivacyIDEA 3.1.2 installed from ubuntu server 18.04 package with SSL enabled on MySQL. Using the following connection string in 3.1.2 mysql://pi:OUo0eMY5TYoc@localhost/pi?charset=utf8&ssl_key=/var/lib/mysql/client-key.pem&ssl_cert=/var/lib/mysql/client-cert.pem

After upgrading to PrivacyIDEA 3.2, it appears pi.cfg is touched and the connection string is changed to include pymysql+mysql at the beginning. This causes a 500 error when connecting to the webui

"code": -500, "message": "[Errno 13] Permission denied"

Previously, this was fixed by removing pymysql+ from the connection string. However, after the upgrade, I get the following error message when my connection string is only using mysql

"code": -500, "message": "No module named 'MySQLdb'"

To my knowledge the “MySQLdb” module is a C wrapper module. We do not use it, since it would require development tools to be installed. This is the reason we are using “mysql+pymysql”, which is a pure python implementation.

So you probably need to reinstall the mysql driver in the python virtualenv.

I would recommend to not use the ubuntu packages if you are changing too much.

Thanks @Cornelinux, any ideas on what the permission denial issue is when using mysql+pymysql then? Are the added arguments incorrect for the python implementation?

It seems that the mysql package installs the client key with different permissions. In my set-up it is only readable by the mysql user but not the apache2 user www-data. This leads to the Permission denied error.
But why do You need SSL-encrypted traffic on localhost anyway? Correct me if i am wrong but only root should be able to dump/redirect the traffic and if root is compromised, SSL won’t help You there.

This is a 2-node HA cluster where each node has a copy of the database in their own MySQL instances. Part of the replication configuration involves enabling and allowing only SSL connections.

Are you talking about the certificate(s) located in the /var/lib/mysql folder? How can I allow the apache2 user permission to these files?

Hi @wwalker,

The “mysql” driver is from 2014. This is another reason, we are not using it anymore.

I took the effort to look into the 3rd party code:

Both drivers seem to expect the same ssl argument option.
However, I do not find your initial post anymore (also spent enough time today here), but if the pymysql ssl is working at all, you might want to investigate the access rights further.

Another side note: We do not use SSL with MySQL. Why? When we started with MySQL ssl was only good for protecting the login, but not the data transfer. Also: A lot of installations do not monitor their certificates. Expired certificates make up for great error messages. :wink:
This is why we usually do encryption by setting up a VPN between the nodes, which seems more robust to me.

@plettich Funny thing

rewritng the SQL database URI happened on purpose! :wink:

@wwalker: Watch out, it may happen again! You might probably want to remove privacyidea-apache2 and run the database migration scripts manually in the future.

1 Like