The Grumpy Troll

Ramblings of a grumpy troll.

MySQL, SSL/TLS and Ubuntu

Some notes, from having set up a MySQL server on Ubuntu and worked to make sure it offered SSL (TLS) for the connections. In this case, Ubuntu 12.04.2 LTS running inside a VMWare Fusion 5 virtual machine. (I write TLS for the generic protocol support, SSL as it pertains to MySQL in particular because that's the term the MySQL documentation uses). I installed MySQL 5.5.

This is purely for testing purposes, so there was no MySQL performance tuning done for the VM; the only relevant tuning was existing tuning, just making sure that the Linux kernel did not try and treat the virtual disk, provided from a backing store as a file in the outer filesystem, as a spinning disk but instead as just a dumb backend (even more appropriate since the laptop has an SSD):

# /etc/rc.local
echo 0 > /sys/block/sda/queue/rotational
echo deadline > /sys/block/sda/queue/scheduler
$ sudo apt-get install mysql-server mysql-client
[ supply a MySQL root password when prompted ]

Ubuntu uses “/etc/mysql/my.cnf” for global MySQL configuration.

First gotcha: the need to remember that AppArmor is filtering access to files by MySQL. So to install a certificate, there's an apparently undocumented requirement to install the key and cert in a particular location with particular names: they must match /etc/mysql/*.pem. Sod that. If you have TLS set up for various services, with keys and certs sensibly managed instead of scattered about, and want to actually enable certificate verification, then your first step is to edit “/etc/apparmor.d/usr.sbin.mysqld” to grant read-access as appropriate. Note: most configuration options for MySQL's SSL support do not result in meaningful error messages when access to a file is blocked. It took setting ssl-ca to get a hint appear in “/var/log/mysql/error.log”.

Useful points to bear in mind: use \s in the mysql client to check connection status and use:

mysql> show variables like '%ssl%';

to get variables and settings. Note that a value of DISABLED means that the server has been built with support for SSL but the run-time configuration has not yet been sufficiently blindly poked with goat-blood infused wands.

Put this into “/etc/mysql/my.cnf”:

ssl-ca          = /path/to/certauthority.pem
#tempdisable# ssl-verify-server-cert


Note that by default, MySQL does not verify the server certificate identity, as hinted at by the “[client]” section above. Alas, there is also the problem that the client code appears to ignore the X.509v3 subjectAltNames extension, so the only identity from the certificate used for checking is that of the CN field in the certificate Subject.

Further, the default rules only allow the MySQL root user to connect from localhost, even after granting access to other IPs. So enabling certificate validation means that you need a certificate with CN=localhost in the Subject.

So first leave [client]ssl-verify-server-cert commented out. Then run this, where you should replace foobar.local with the hostname corresponding to the public IP of the machine; this allows you to connect from the local machine to the local machine, via the machine's hostname.

$ mysql -u root -p
[ supply the root password you used at install time ]
mysql> GRANT USAGE ON *.* TO ''@'foobar.local';

If you enable validation, a connection to localhost will yield:

ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure

After all this, I can't believe that folks still perpetuate the myth that PostgreSQL is harder to set up than MySQL. PostgreSQL actually documents the settings accurately (instead of telling you about directives that apparently have broken configuration for several releases now but none of the maintainers appear to care, ssl-capath), provides reasonable diagnostics and lets you get up and running with TLS enabled connections in a matter of minutes, the only potential issue being how you configure pg_hba.conf. I've lost interest in making sure that MySQL can use multiple ciphers for compatibility with various clients, as is default behaviour in every other application.

You should now be able to do something like:

  TO 'fooadmin'@'adminhost.tld'
  IDENTIFIED BY 'raw_password_here'

-A grumpy troll



MySQL is easy to set up if you want a default insecure configuration. Why do you have to go rocking the boat like this?

(Slightly more seriously, I'm so sorry you're stuck having to run MySQL. I haven't yet found anything it does that isn't done better by either SQLite or PostgreSQL, often both)

Categories: TLS AppArmor mysql OpenSSL SQL Ubuntu