Reducing your attack surface is an important part of network security. If you are running a service that only needs to be accessed from localhost, you should not allow connections from the outside world. On my web servers, only 3 ports are open:
- 22 for SSH
- 80 for http
- 442 for https
All other ports are filtered. This significantly reduces the number of possible attack vectors. Other services such as MySQL (port 3306) are only accessible from the private network. To access MySQL from a remote machine you can SSH tunnel to the database server and then connect to the database on localhost. Most database clients like MySQL Workbench support SSH Tunneled Connections.
However, I came across a problem when I wanted to run a python script locally to update my remote database server. There is a library for connecting directly to a MySQL database with Python, but not for connecting through an SSH Tunnel. Another option would be to run the python script on the remote server, but in my case I did not want to do that.
To solve this, we will have to manage the SSH Tunnel ourselves. After building the SSH Tunnel, you will be able to connect to a port on your local machine and the connection will be sent to a different port on the remote machine’s loopback interface.
If you use key authentication to SSH, run this command:
ssh -nNT -L 1234:localhost:3306 REMOTE_SERVER
If you are using password Authentication, run this command:
ssh -fNT -L 1234:localhost:3306 REMOTE_SERVER
That’s it. The command will go to the background and you can connect to localhost:1234 as if it were locahost:3306 on the remote server.
Final Notes
Test the SSH Tunnel
There are a few final notes when using this command. First, to test that your tunnel is working, try to connect to MySQL on the remote server by running this command on the local machine:
mysql -u REMOTE_DB_USER -h 127.0.0.1 -P 1234 -p
You must to set the host (using the -h flag) to 127.0.0.1. If you leave the host blank or set it to localhost, MySQL uses sockets and thus ignores the port flag (-P).
Close the SSH Tunnel
How do we close the SSH Tunnel? When you want to destroy the SSH Tunnel you can run the following command. It will kill a process that is listening to port 1234:
fuser -k 1234/tcp
Keep SSH Tunnel Alive
Finally, when using SSH Tunnels you may want to configure SSH to keep the session alive so your connection does not drop unexpectedly. SSH does not close connections after any length of time, but router’s remove inactive connections from the NAT tables periodically. To prevent this from happening, add the following code to the top of your SSH configuration file:
Host *
# Send keep-alive packet every 60 seconds
ServerAliveInterval 60
# Send keep-alive packet only 60 times (1 hour)
ServerAliveCountMax 60
Adjust ServerAliveInerval and ServerAliveMaxCount as desired. SSH configuration file is found in ~/.ssh/config