Like many people, you store sensitive information in your database. And like some, your database server is on a different host from one or more of your webservers. How do you secure the necessary network connections?
The obvious answer is SSL/TLS. But that requires you to a) use a custom MySQL binary, and b) manage *yet another* set of certificates. And it requires setting extra parameters when connecting to the database in your code.
A less obvious answer is to use the ssh's built-in port forwarding to tunnel the connection. Here's how.
Special thanks to Jon Baer for this post to NYPHP Talk!
Note that the commands and scripts in this document assume that you are connecting to a MySQL database named "db" on remotehost.com, port 3306, using username "dbuser" and password "PASS". You will need to change these values to match your own setup.
ssh -fNg -L 3307:127.0.0.1:3306 email@example.comThe first command tells ssh to log in to remotehost.com as myuser, go into the background (-f) and not execute any remote command (-N), and set up port-forwarding (-L localport:localhost:remoteport ). In this case, we forward port 3307 on localhost to port 3306 on remotehost.com.
mysql -h 127.0.0.1 -P 3307 -u dbuser -p db
The second command tells the local MySQL client to connect to localhost port 3307 (which is forwarded via ssh to remotehost.com:3306). The exchange of data between client and server is now sent over the encrypted ssh connection.
To make the same connection using PHP's built-in MySQL client:
$smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "PASS" );
mysql_select_db( "db", $smysql );
On the database server, as root, create a user to handle the server side of all tunnels. This user will have no valid shell (might not work on all operating systems?)
useradd -s /bin/false myuserConnections via the tunnel will look like they are coming from 127.0.0.1, so you need to update the GRANT tables in the database:
chown -R myuser:myuser /home/myuser/.ssh
chmod 755 /home/myuser/.ssh
chmod 600 /home/myuser/.ssh/authorized_keys
USE mysql;Congrats, your server is ready to accept tunnels.
GRANT ALL ON db.* TO firstname.lastname@example.org IDENTIFIED BY 'PASS';
details here ).
The first step is to set up an RSA key pair as root on each client, leaving all questions blank:
root@local# ssh-keygen -t rsaThe next step is to append the rsa public key file to myuser's .ssh/authorized_keys on the server. First we copy the key, then we shell in and append it to the authoized list:
Generating public/private rsa key pair.
Enter file in which to save the key (/var/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/root/.ssh/id_rsa.
Your public key has been saved in /var/root/.ssh/id_rsa.pub.
The key fingerprint is:
root@local# scp /var/root/.ssh/id_rsa.pub email@example.com:/tmp/myhost.local_rsa.pubNow you can set up the connection on the client, without typing myuser's password:
root@local# ssh remotehost.com
root@remote# cat /tmp/myhost.local_rsa.pub >> /home/myuser/.ssh/authorized_keys
root@local# ssh -fNg -L 3307:127.0.0.1:3306 firstname.lastname@example.org
Add the following to /etc/inittab on each client:
sm:345:respawn:/usr/bin/ssh -Ng -L 3307:127.0.0.1:3306 email@example.comAnd that should be all you need to do. Send init the HUP signal ( kill -HUP 1 ) to make it reload the configuration. To turn it off, comment out the line and HUP init again.
By Chris Snyder on May 11, 2004 at 2:50pm