Connecting to MariaDB through an SSH Tunnel

When you want to connect a client to a database server through an insecure network, there are two main choices: use SSL or use an SSH tunnel. Although SSL often may seem to be the best option, SSH tunnels are in fact easier to implement and can be very effective. Traffic through an SSH tunnel is encrypted with all of the security of the SSH protocol, which has a strong track record against attacks.

There are various ways to implement an SSH tunnel. This article suggests a simple approach which is adequate in many situations. For the examples here, let’s assume that there is a database server running on a host named, server.example.com, with an IP address of 1.2.3.4. Suppose further that the client is on a host named, client.example.com, with an IP address of 5.6.7.8. We’ll also suppose that there are tightly configured iptables firewalls on both systems.

Dealing with Firewalls

The first step is to open the firewall for SSH communications between the systems. Let’s use the standard port for SSH (i.e., port 22). The tunnel will be instigated by the client. So the iptables script on the server might contain something like this:

IP_CLIENT=5.6.7.8
IPTABLES=/sbin/iptables
# Accept inbound packets that are 
# part of previously-OK’ed sessions

$IPTABLES -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
...
$IPTABLES -A INPUT -s $IP_CLIENT -p tcp -j ACCEPT --dport 22 -m state --state NEW

On the client side, the iptables script might include the following entries:

IP_SERVER=1.2.3.4
IPTABLES=/sbin/iptables
# Accept inbound packets that are part of previously-OK’ed sessions
$IPTABLES -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
…
$IPTABLES -A OUTPUT -d $IP_SERVER -p tcp --dport 22 -m state --state NEW -j ACCEPT

It’s useful to establish dedicated users on each machine. We’ll assume we’ve done that and called them simply, tunnel on both. They shouldn’t have any special privileges, but they need to have a home directory (assumed to be /home/tunnel) and the ability to run a command shell.

Preparing SSH Keys

The user on the client side will need to create SSH keys. This can be done by executing the ssh-keygen utility while logged in as the tunnel user like so:

ssh-keygen -t DSA -b 1024 -C [email protected]

This example uses DSA, although you could use RSA. The last parameter, indicated by -C is purely a comment and doesn’t affect the use of the keys. The comment will be added to the end of the public key, and is useful for keeping track of what key belongs to what system.

When you execute the ssh-keygen command, it will offer to create the keys in the /home/tunnel/.ssh directory. This is fine; just accept this choice. It will also ask for a password, but this isn’t needed. So we’ll ignore it and press return. The result will be two files in the /home/tunnel/.ssh directory called id_dsa and id_dsa.pub. The first is the secret key, which should be kept secure. The second file is the public key, which can be distributed freely.

Now we need to place a copy of the public key on to the server system. It needs to go into the file called, /home/tunnel/.ssh/authorized_keys. Assuming this is the first key to be used on the server system for the tunnel user, the id_dsa.pub file can be copied into the server directory /home/tunnel/.ssh and renamed to authorized_keys. If not, you can append it to the end of the file by executing something like this at the command-line from the directory where you’ve uploaded the id_dsa.pub file:

cat id_dsa.pub >> /home/tunnel/.ssh/authorized_keys

You could also use a simple text editor to copy the contents of the id_dsa.pub file to the end of the authorized_keys file. Just put what you paste on a separate line in that file.

Testing the SSH Connection

Once the keys have been created and put where they belong, we should be able to log into the server with the tunnel user from the client, without having to enter a password. We would do that by executing this from the command-line:

ssh [email protected]

The first time you do this, there should be a message that says that it is an unknown server. Just confirm that you want to go ahead with the connection. After this first time, you won’t get this message. If it connects successfully, you have proved that the tunnel user can make a connection to the server.

To make the SSH tunnel robust, it’s helpful to run a utility called autossh. This monitors an SSH tunnel and re-establishes it if it fails. You can find it in the standard repositories for Debian and Ubuntu or may need to add one of the well known additional repositories for other distributions. Once you’ve done that, autossh can be installed using the standard package management tools for the distribution (e.g., aptitude or yum).

Establishing an SSH Tunnel

We’re now ready to establish the SSH tunnel. In a Debian based installation, probably the best place to put the command to establish the tunnel is the directory, /etc/network/if-up.d. For Centos/Red Hat, it could go in the /etc/rc.local directory.

You would execute something like this from the command-line:

su - tunnel -c ‘autossh -M 0 -q -f -N -o “ServerAliveInterval 60” -o  
“ServerAliveCountMax 3” -L 4002:localhost:3306 [email protected]’

Once we’ve executed that, we will have established port 4002 on the client and it will be connected to port 3306 on the server. If the command is run manually, the software invoked will run in the background and the terminal can be closed. The command can be placed in a script, though, that will run automatically at startup.

Connecting to MariaDB

Assuming the server has a MariaDB running on the default port and we have the MariaDB client installed on the client machine, we can now connect to MariaDB on the server. We would enter something like the first line below at the command-line on the client, and should see a message in response similiar to the one that followings:

mysql -u root -p –host=‘127.0.0.1’ –port=4002

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 164575
Server version: 10.1.1-MariaDB-1~wheezy-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4123

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

MariaDB [(none)]> 

Conclusion

Generating SSH keys is a simpler process than the creation of SSL certificates, and the deployment is easier too. From my experience, there have also been fewer vulnerabilities with SSH than SSL. There is obviously some overhead in using an SSH tunnel, compared with an unencrypted connection. However, the overhead seems to be about the same as that imposed by SSL. The gain in security, though, is considerable.