Jesse's Software Engineering Blog
A common web application server architecture is to only allow public access to specific servers. This allows all internal servers to be closed to internet traffic and only accessible from other servers in the network. I outlined this technique in an article for setting up an internal MySQL database server.
Often times access is still needed to internal servers, such as connecting to an internal database, and can be facilitated by a SSH tunnel. A SSH tunnel forwards a local port to a remote port via another server. For example, if you have two servers: web and database, and database is only accessible from web, you can set up a local port to forward to a database port by tunneling through web via SSH.
SSH Tunnel Connection
In order to tunnel through an external server, SSH access must be available to the server in which you are tunneling through. For more info see Setting up SSH access.
To set up the tunnel:
ssh -f email@example.com -L 3306:dbserver:3306 -N
The first parameter is the SSH user and host of the server which you are tunneling through. The second parameter is a combination of ports and server in which you are trying to access: <local_port>:<remote_server>:<remote_port>. The remote_server value is the IP address, domain name, or a server alias, of the internal server you are trying to access.
The -L option is required for setting up the tunnel. -f runs the command in the background, and -N prevents the command from being executed remotely. For more info: man ssh
NOTE: For performance reasons you may need to tune the encryption protocols: -XC -c blowfish-cbc,arcfour
After running the tunnel command you will have a local connection to a remote internal server via port 3306. In this example I am accessing an internal database and can test the connection:
mysql -u user -p -h 127.0.0.1 -P 3306
To terminate the tunnel, determine the process id by searching for the port and kill it:
netstat -tulpn | grep 3306 # sample output tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2697/ssh tcp6 0 0 ::1:3306 :::* LISTEN 2697/ssh # kill process kill -9 2697
SSH Tunnel Considerations
When running SSH tunneling you have to make sure that the port being forwarded is not currently in use. It is possible to have both a local instance of MySQL running and a remote connection, but they have to be running on different ports. So if you wanted to have MySQL running locally on 3306 then you would specify a different port to forward:
ssh -f firstname.lastname@example.org -L 3308:dbserver:3306 -N
Now connections to the remote database would be made locally through 3308 and mapped to dbserver port 3306. Or alternatively if you wanted the remote connection to be through 3306 then change the local port by editing the my.cnf file (/etc/mysql/my.cnf on Ubuntu) and tunneling:
vi /etc/mysql/my.cnf [mysqld] port = 3308 ssh -f email@example.com -L 3306:dbserver:3306 -N
Now remote connections can be made through port 3306 and remote connections can be made through 3308.
Programming languages allow for control over which ports the code base is accessing MySQL through. In PHP for example, the various connections use port 3306 by default:
mysql_connect('127.0.0.1, 'user', 'passwd'); new mysqli('127.0.0.1', 'user', 'passwd'); new PDO('mysql:host=127.0.0.1, 'user', 'passwd');
But if you wanted code to access a remote server via a tunnel on a different port:
mysql_connect('127.0.0.1:3308', 'user', 'passwd'); new mysqli('127.0.0.1', 'user', 'passwd', 'db', 3308); new PDO('mysql:host=127.0.0.1;port=3308', 'user', 'passwd');
This isn’t practical in the sense of having to change the code base, so a better approach is to change the php.ini file (PDO doesn’t support this):
vi /etc/php5/apache2/php.ini mysql.default_port = 3308 mysqli.default_port = 3308
This allows for quick control over which data source the code base is reading from. Note this is not limited to MySQL you can access any service on a remote server via the appropriate port.
Being able to connect to internal servers locally via SSH tunnel port forwarding is a huge asset during development. Being able to quickly change the data source to a local code base offers huge advantages for both development and debugging, while allowing a secure server architecture by closing internal servers to outside connections. The above outlined techniques can be applied to any service/port.