MySQL over SSL not implemented

[christer@…]

I am not able to connect to a remote MySQL server with a user that requires SSL. When looking into db/mysql_backend.py I do not see a way to make any information about SSL show up in the MySQLdb.connect() call.

How to reproduce:

All of these were performed on December 27, 2010:

1. Install Trac dependencies: yum install python-genshi python-setuptools MySQL-python swig mod_wsgi python-docutils python-pygments
2. Install Trac: easy_install Trac==0.12
   • Installed: /usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg
3. Set up for wsgi over Apache
4. Create a user in MySQL that has REQUIRE SSL set: GRANT ALL ON trac.* TO 'tracuser'@'tracserver-IP' IDENTIFIED BY 'password' REQUIRE SSL;
5. Setup trac.ini to connect: database = mysql://tracuser:password@remotemysql:3306/trac
6. Connect to ​https://myserver/trac

System Information

• uname -a: Linux admin 2.6.35.4-rscloud #8 SMP Mon Sep 20 15:54:33 UTC 2010 x86_64 x86_64 x86_64 GNU/Linux
• cat /etc/issue: Fedora release 14 (Laughlin)
• python —version: Python 2.7 (installed using yum)
• mysql —version: mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 (installed using yum on CentOS5)
• yum info MySQL-python: Arch: x86_64, Version: 1.2.3, Release: 0.5.c1.fc14 (installed using yum)

Backtrace

Note that the line numbers for mysql_backend.py don't seem to correspond to what is currently tip of the 0.12 branch. Perhaps there is something lacking in the easy_install script?

  File "/usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/mysql_backend.py", line 85, in get_connection
    cnx = MySQLConnection(path, log, user, password, host, port, params)
  File "/usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/mysql_backend.py", line 225, in __init__
    host=host, port=port, charset='utf8')
  File "/usr/lib64/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (1045, "Access denied for user 'tracuser'@'remotemysql' (using password: YES)")

Workaround

In order to get this to work for me, I modified the two main files in Trac so that the SSL information can be passed in using trac.ini configuration. I realize that this is not a good final solution (e.g., it doesn't allow for only specifying CA cert).

• diff /usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/api.py.orig /usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/api.py

  270a271
  >     ssl = None
  277c278,283
  <             value = urllib.unquote(value)
  ---
  >             if ';' in value :
  >                 # trac.ini: database = mysql://tracuser:password@remotemysql:3306/trac?ssl=ca_cert;client_cert;client_key
  >                 ca,cert,key = value.split(';',2)
  >                 value = {'ca':ca,'cert':cert,'key':key}
  >             else:
  >                 value = urllib.unquote(value)
  

• diff /usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/mysql_backend.py.orig /usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/mysql_backend.py

  224c224,228
  <         cnx = MySQLdb.connect(db=path, user=user, passwd=password,
  ---
  >         if(params['ssl'] != None):
  >             cnx = MySQLdb.connect(db=path, user=user, passwd=password,
  >                               host=host, port=port, charset='utf8', ssl=params['ssl'])
  >         else:
  >             cnx = MySQLdb.connect(db=path, user=user, passwd=password,
  

[Remy Blank]

Related to #5120, and could be solved at the same time. Thijs, want to give it a try?

[Thijs Triemstra]

yup.

[Thijs Triemstra]

According to the ​MySQL docs these are valid attributes for the ssl dict: key, cert, ca, capath, and cipher.

What about adding them to the connection string like:

mysql://admin:admin@localhost/trac?unix_socket=/tmp/mysql.sock&key=/path/to/key&
cert=/path/to/cert&ca=/path/to/cert&capath=/path/to/cadir&cipher=list of ciphers

[Thijs Triemstra]

Another option would be defining those ssl params in a custom-mysql.cnf file, instead of trying to parse all those params from the connection string. Not 100% sure if it's possible to define those ssl params there, but that would be much simpler to implement (and maintain).

[Thijs Triemstra]

Replying to thijstriemstra:

Another option would be defining those ssl params in a custom-mysql.cnf file, instead of trying to parse all those params from the connection string. Not 100% sure if it's possible to define those ssl params there, but that would be much simpler to implement (and maintain).

Turns out it's ​possible for MySQL, this script creates a my.cnf file that you can use to test run MySQL over SSL:

#
# Create a my.cnf file that you can use to test certificates
#
DIR=`pwd`/ssl

cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/ca-cert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/ca-cert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf

so +1 for implementing this feature using ​option files instead of parsing ssl params in the connection string.

[Remy Blank]

Replying to thijstriemstra:

so +1 for implementing this feature using ​option files instead of parsing ssl params in the connection string.

+1 here, too. So you would have to allow for a read_default_file argument in the database connection string, to be passed to connect().

[Remy Blank]

With [10407] it's now possible to specify a client configuration file with the read_default_file connection string parameter, so the SSL configuration can be placed there.

[Christian Boos]

Lower the severity of these MySQL enhancements as other major enhancements are really the highlights of this release.