Navigation

esmiz · #1 1st December 2010, 01:45

Hi there

On last days I've been trying to learn something about mysql over ssl connections.
In an 'ispconfig 3' multiserver setup, communication between servers is done through unencrypted mysql connections.
I thought It would be great to have slaves communicating over SSL with the master, and this is what I figured out:

Environment is a multiserver with a master and 3 slaves, all of them running Debian Lenny.

First, in one of the servers I made server and client certificates for every machine. All certificates are signed with the same CA, and the only question I answered was common-name where I wrote server's hostname.

Then on every server /etc/mysql/my.cnf I added the path to client certificates within the [client] section, and the path to server certificates within the [mysqld] section. Something like this:

Code:

[client]
ssl-ca		= /etc/mysql/ssl-certs/ca-cert.pem
ssl-cert	= /etc/mysql/ssl-certs/ks1-client-cert.pem
ssl-key		= /etc/mysql/ssl-certs/ks1-client-key.pem

[mysqld]
ssl
ssl-ca		= /etc/mysql/ssl-certs/ca-cert.pem
ssl_capath	= /etc/mysql/ssl-certs/
ssl-cert	= /etc/mysql/ssl-certs/ks1-server-cert.pem
ssl-key		= /etc/mysql/ssl-certs/ks1-server-key.pem

After doing this, all the connections done by mysql seem to be forced to be encrypted. It looked promising, I could connect with root and ispcsrv* users from a server to the others in the usual way: mysql -h -u -p

Checking the connection with commands like: SHOW VARIABLES LIKE '%SSL%'; or SHOW STATUS LIKE 'Ssl_cipher'; showed that SSL was being used.

Well this seems to work when connection is initialized from shell but not when connection is initialized from a php script, so slaves and master were not communicating properly.

I thought that perhaps ispconfig user needs its own my.cnf, and added the file /usr/local/ispconfig/.my.cnf with just this content:

Code:

[client]
ssl-ca		= /etc/mysql/ssl-certs/ca-cert.pem
ssl-cert	= /etc/mysql/ssl-certs/ks1-client-cert.pem
ssl-key		= /etc/mysql/ssl-certs/ks1-client-key.pem

No joy, but some more searching drove me to http://php.net/manual/en/function.mysql-connect.php and that gave me the clue I needed.
Php uses mysql_connect function to connect to mysql servers, the flag 'MYSQL_CLIENT_SSL' is used to achieve ssl connections.

Then I used grep to look for 'mysql_connect' string, in ispconfig /home directory files:

Code:

grep -R 'mysql_connect' /usr/local/ispconfig/

Fortunately, It seems that there are just 6 files where this function is used:

/usr/local/ispconfig/interface/lib/classes/db_firebird.inc.php
/usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php
/usr/local/ispconfig/interface/lib/classes/simplepie.inc.php
/usr/local/ispconfig/server/lib/classes/db_mysql.inc.php
/usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php
/usr/local/ispconfig/server/plugins-available/software_update_plugin.inc.php

So I backed them up, and add the required flags to every instance the function is invoked. As an example, line 72 in the file /usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php looks like:

Code:

$this->linkId = mysql_connect($this->dbHost, $this->dbUser, $this->dbPass);

And with the flags, it becomes:

Code:

$this->linkId = mysql_connect($this->dbHost, $this->dbUser, $this->dbPass, false, MYSQL_CLIENT_SSL);

The result of all this is that communication between ispconfig slaves and the master is back now and is encrypted.

The questions I have are:

Is this is a proper way of doing things?, Is there something that I'm missing or is not needed?

I'm almost sure that there are many wrong things in these steps, so thanks in advance for all your corrections.

Regards.

xmz

esmiz · #2 1st December 2010, 04:47

Hi

I answer to myself. This is not a good method.
I realized that changes are not reflected in the job queue and not propagated to the slave.

Regards

esmiz · #3 15th December 2010, 23:45

Please don't follow any advice from the previous post, my apologies. I gave up that idea.
A good solutions should not be that difficult neither involve modifying core files.

I realized that we have a wonderful tool easier to setup that could do the job, openvpn.

Since 2 weeks I'm playing with openvpn within the multiserver environment and it seems to be the perfect solution to encrypt all the communication between servers (ispconfig internal jobs, mysql replication, rsync, etc...)

It was as easy as change /etc/hosts in all servers and add in there the other server names with their tun IP's, then change ispcsrv users in master mysql database to reflect the new IP's.

What do you guys think about this solution? Is any of you already running openvpn within ispconfig?

Regards