SSL MySQL Connection Broke Upgrading 3.1.2 to 3.2

wwalker · December 2, 2019, 5:28pm

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'"

cornelinux · December 2, 2019, 10:31pm

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.

wwalker · December 2, 2019, 11:00pm

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?

plettich · December 3, 2019, 2:02pm

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.

wwalker · December 3, 2019, 4:01pm

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?

cornelinux · December 3, 2019, 4:56pm

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:

github.com

PyMySQL/PyMySQL/blob/2330bf798894b35f3fcc796e9c5df5bac44105ab/pymysql/connections.py#L153


    This is used to provide custom marshalling and unmarshalling of types.
    See converters.
:param use_unicode:
    Whether or not to default to unicode strings.
    This option defaults to true for Py3k.
:param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
:param cursorclass: Custom cursor class to use.
:param init_command: Initial SQL statement to run when connection is established.
:param connect_timeout: Timeout before throwing an exception when connecting.
    (default: 10, min: 1, max: 31536000)
:param ssl:
    A dict of arguments similar to mysql_ssl_set()'s parameters.
:param read_default_group: Group to read from in the configuration file.
:param compress: Not supported
:param named_pipe: Not supported
:param autocommit: Autocommit mode. None means use server default. (default: False)
:param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
:param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
    Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
:param defer_connect: Don't explicitly connect on construction - wait for connect call.
    (default: False)

github.com

farcepest/MySQLdb1/blob/master/MySQLdb/connections.py#L133




sql_mode
  If supplied, the session SQL mode will be changed to this
  setting (MySQL-4.1 and newer). For more details and legal
  values, see the MySQL documentation.


client_flag
  integer, flags to use or 0
  (see MySQL docs or constants/CLIENTS.py)


ssl
  dictionary or mapping, contains SSL connection parameters;
  see the MySQL documentation for more details
  (mysql_ssl_set()).  If this is set, and the client does not
  support SSL, NotSupportedError will be raised.


local_infile
  integer, non-zero enables LOAD LOCAL INFILE; zero disables


autocommit
  If False (default), autocommit is disabled.

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.
This is why we usually do encryption by setting up a VPN between the nodes, which seems more robust to me.

@plettich Funny thing

github.com

NetKnights-GmbH/ubuntu/blob/master/debian_server/privacyidea-nginx.postinst#L88


	if [ !$(grep "^PI_PEPPER" /etc/privacyidea/pi.cfg) ]; then
	    # PEPPER does not exist, yet
	    PEPPER="$(tr -dc A-Za-z0-9_ </dev/urandom | head -c24)"
	    echo "PI_PEPPER = '$PEPPER'" >> /etc/privacyidea/pi.cfg
	fi
	if [ !$(grep "^SECRET_KEY" /etc/privacyidea/pi.cfg || true) ]; then
	    # SECRET_KEY does not exist, yet
	    SECRET="$(tr -dc A-Za-z0-9_ </dev/urandom | head -c24)"
	    echo "SECRET_KEY = '$SECRET'" >> /etc/privacyidea/pi.cfg
	fi
        if [ -n "$(grep '^SQLALCHEMY_DATABASE_URI\s*=\s*.mysql:.*$' /etc/privacyidea/pi.cfg)" ]; then
	    #  We found an old mysql config file
	    sed -i -e s/"\(^SQLALCHEMY_DATABASE_URI\s*=\s*.\)mysql:\(.*\)$"/"\1mysql+pymysql:\2"/g /etc/privacyidea/pi.cfg
	    echo "# The SQLALCHEMY_DATABASE_URI was updated during the update to privacyIDEA 3.2" >> /etc/privacyidea/pi.cfg
	fi
}


create_database() {
        # Create the file with the MySQL Barracuda fix, if it does not exist
        if [ ! -f $MYSQLFIX ]; then
                printf "[mariadb]\ninnodb_large_prefix=on\ninnodb_file_format=Barracuda\n" > $MYSQLFIX

rewritng the SQL database URI happened on purpose!

@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.