Re: Replication Security

[Darryl Miles]

The native MySQL protocol is not encrypted and I believe the login sequence to be plain text (or rather obfuscated text) where an attacker sniffing the connection could copy to again valid authentication details. He however would still have to pass the Host column GRANT checks in various priviledge tables to get connected.

All the 3 methods I detail below use secure authentication sequences that do not suffer from reply attacks, this techology is largely based on public key cryptography and secure key exchanges, there is also the option to make use of mutual authentication (not just the client proving he is the client to the server, but also the server proving to the client he really is the server and not a fake server trying to solicit your data). Obviously you will need to examine and check you have configured things up correctly.

So to answer your question the SSH method does allow itself to be setup so there is no way a 3rd party can hijack the connection and play man-in-the-middle. For this garuntee you will need to enforce mutual authentication part of the public key technologies in use. This is available in most detault configurations of OpenSSH.


There are three general methods to replicate securely over an untrusted network with MySQL :

1) Use SSL support within MySQL at both ends to allow the slave to connect to the master using SSL secured connection. MySQL itself takes on the responsbility of providing its own application <> application security, this is the most secure approach to use and once setup and working the most robust.

It does however mean you have to expose the listening port of the MySQL master over the untrusted network. The easiest soltuion to take care of that is to use IP address based firewalling to only allow the slave IPs to connect.

There is also the risk (under slave misconfiguration) that you may expose the username and password accidentlly if you omit the SSL options to the connection and your slave client connects to the MySQL master without using SSL. Yes, you can configure the MySQL master to not allow authentication without SSL. But its now too late you have already exposed your password on the untrusted network.

As far as I know MySQL does not implement a challenge reponse type of authentication, there are good reasons for this as it increases server login time by the RTT time of the connection. Recommendations to the MySQL team would be to add an option to each server listening socket to be able to use the existing plain text or insist on challenge / response login. Then change the initial greeting banner string to indicate that the client must use this method to authenticate itself, this would mean being able to have a subsection in my.cnf to configure options for each socket adaptor instance.

Versions 4.0.x and 4.1.x have supported (when compiled with openssl support) SSL server connections for a while now. The mysqlclient library has also supported creating client SSL connection for a while too, however SSL client support in for the internal replication client has been lacking and may not even be available at this time. However the feature is on its way.


2) Use SSH (secure socket tunneling). This is where on Unix with OpenSSH or on Win32 with CYGWIN+OpenSSH is configured to make a secure tunnel connection between the two hosts and wait patiently to relay a local connection across the secure tunnel connection. Maybe PUTTY.EXE can be used for connection forwarding, this is a SSH client implemention for Win32.

So once setup you may configure OpenSSH at the slave end to listen on 127.0.0.1:12345 then when it gets a connection it relays the data (through the secure socket tunnel) to the remote host. When the data is at the remote host its configure to connect to 192.168.1.1:3306 (or whatever your MySQL tcp socket is).

This is fairly secure providing you always listen on 127.x.x.x but any local user of the machine will be able to get access to the remote MySQL server (even if the master TCP port is firewalled or not available to the public) they would still need username / password to complete login. This may also present you with the option to be able to make your master listen on 127.0.0.1:3306 instead of a network facing IP. So no one on the public network can even attempt to make a login directly.


3) Use a VPN. This is where IP level data is encrpted between two endpoints usualy on a private network and usualy by an external VPN router. This situation IMHO is far more complex than the other two, but once setup would allow any services to be used securely between two sites. This has more implications as you need to setup your public network facing firewals to make sure that any data that somehow missed the tunnel doesn't propagate anywhere else. You need to setup your IP routing to be able to route between the address spaces (over the VPN). You need to address how your network differenciates between what data / servies is allowed to and from the untrusted network and what data is allowed to and from the VPN network (guarding your endpoint). This seems overkill if MySQL replication is the only service you want to secure. But Win32 Routing & Remote Access simplifies matters as it can be handy to setup a direct point to point VPN.


My advise would be:

* Use SSL client replication support (when it becomes available for general release).
* If your MySQL hosts are Unix boxes use OpenSSH solution, its readily available on that platform.
* If your MySQL hosts are Win32 Server boxes and you have Routing & Remote Access service then investigate setting up private address space for a point to point VPN between to two boxes. Make the master end connect to the slave end with a persistent connection, where the master is in charge of assigning IPs, so way the master always gets a predictable IP address for itself over reboots. You then configure the slave to connect to this known address.
* If your MySQL hosts are Win32 w/o R&RA look to installing CYGWIN+OpenSSH.