Fri, 27 Feb 2009
/Admin/databases/MySQL:
Setup MySQL to use SSL for Remote Connections
Some good advice in an age of people, companies, and governments
avaricious to acquire / store / use / sell your personal information: use
encryption wherever possible when communicating over networks.
Here[1] is a nice concise guide to the basics of getting SSL working on
MySQL[2].
First login to MySQL and check for SSL support:
# mysql -p
Enter password:
mysql> show variables like 'have_ssl';
You should see "DISABLED" at this point, since you have not set it up
yet. (If the response says anything other then "DISABLED" or "YES", then
your MySQL server has probably been compiled without SSL support. Not a
problem on Debian....)
Then Enable SSL Support in the Server:
To avoid a rather dauntingly complex series of openssl command lines, I
opted to use tinyca ("apt-get install tinyca") to provide a GUI front-end to
openssl on my desktop. Basically, with tinyca the process of generating the
files required for SSL is a rather short and simple point-n-click process,
and seems to work just fine if you fill in the absolute minimum information
in the forms. For the record, I also chose a 1024 bit key and made the
"common names" on the CA and the certificate different.
After creating CA, certificate, and key, export them all. Do export the
key without a password. (I did verify that a key exported with a password
causes SSL support in MySQL to silently fail, and log the failure to
/var/log/syslog.) scp the three files to your server, copy them to
/etc/mysql, change their ownership to the mysql user and "chmod 600", then
in /etc/mysql/my.cnf uncomment and add them to the appropriate lines:
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/my-new-server-cert.pem
ssl-key=/etc/mysql/my-new-server-key.pem
After restarting MySQL,
mysql> show variables like 'have_ssl';
should result in a "YES".
Now Get MySQL clients Working:
Test a client using SSL on the MySQL server. Create a temporary user for
the test:
mysql> GRANT ALL on databasename.* TO 'ssluser'@'localhost' IDENTIFIED BY
'thispassword' REQUIRE SSL;
From a terminal on the MySQL server, try logging in with this user:
mysql -ussluser -p --ssl-ca=/etc/mysql/cacert.pem
No errors means SSL is working! Delete the test user:
mysql> DELETE FROM mysql.user WHERE user='ssluser' and host='localhost';
And still on the MySQL server, create a user for remote access, from a
specific IP address only:
mysql> GRANT ALL on databasename.* TO 'SSLremote'@'153.129.49.127'
IDENTIFIED BY 'thispassword' REQUIRE SSL;
On the remote client (IP address 153.129.49.127) presumably your desktop,
try to login over SSL:
mysql -uSSLremote -pthispassword -hwww.mysqlserverhost.com
--ssl-ca=/home/user/cacert.pem
If it works, mission accomplished!
Just in case anyone else might share my bright idea that it should be
possible to coax phpmyadmin into using this MySQL SSL connection, it would
appear to be not easy. This post[3] suggests a patch is necessary. And in
fact, I grepped the phpmyadmin source currently in my /usr/share/phpmyadmin
for "mysqli_ssl_set" and turned up no hits. So the command line it is, or
maybe a little custom Python web app....
[1] http://blog.aisleten.com/2008/05/25/connecting-to-mysql-using-ssl-encryption-in-ruby-on-rails/
[2] http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html
[3] http://sourceforge.net/tracker/index.php?func=detail&aid=1746131&group_id=23067&atid=377411
posted at: 04:35 | path: /Admin/databases/MySQL | permanent link to this entry
