As per an earlier article on tunneling, I had setup some tunnels to allow secure access to my database behind the firewall. I haven’t however done much development locally in awhile, so I hadn’t used this tunnel. Just recently, I discovered that it wasn’t working anymore. Digging deeper I found that my public-key authentication was breaking somehow for a particular user.
Trying to debug things with “-v” wasn’t giving me useful information, so I thought I’m sure there must be something in the logs and luckily came across this post:
The long and short of it, was just like Artem, my issue was permissions of the home directory of the authenticating user. A quick fix to that and we were all set.
Thinking about it, now I remember I’d been having permissions issues within my virtual hosts, so I’d “chown -R”‘d them to the apache user. This ended up preventing me from being able to read the home directory(which happened to be the webroot) of the special user I’d created for tunneling and managing webfiles. Silly me.
The big lesson here: like most things on Unix – permissions should always be the first thing to check.
I’ve got a few cloud servers each one of which has both an internal and external ip address. One server has a webserver, the other has a mysql server.
The webserver connects to the mysql server on it’s internal ip, and in my.cnf the mysql server is bound to it’s internal ip accordingly. The separation is done for two reasons, performance and cost. The MySQL database is located on a different node, so if there are issues with the webserver or some kind of DDOS attack against it, the database server is unaffected and vice-versa. I get all internal bandwidth free but I have to pay for all external bandwidth, so if I were to expose the server on a remote ip, then I’d be paying for all data-transfer between the webserver and mysql server. Before I started this project, I was doing just that, and I realized how wasteful it was.
Furthermore, using this approach, I am able to better lock-down my servers, effectively creating a local network of my servers behind a firewall. My website ports such as port 80 or 443 can be allowed to be accessed by outside the firewall, but all other traffic would require an SSH tunnel via a specific host. This is basically like creating a proxy server, but even more secure as I only allow the intended access at the exact moment that i need it.
I’ve already implemented ssh keys so the only way to access this on-demand proxy server would be to do so on authorized machine, which adds yet another layer of security.
I want to be able to remotely administrate the mysql server without having to rely on ssh’ing directly to the box and running command line tools. For instance, I’d like to use MySQL Administrator on a Mac or PC.
Because the mysql server is bound to it’s internal ip address and not it’s external (specifically because internal bandwidth is free), I can only access it by creating an SSH tunnel.
Also, I run my SSH on a custom port for added security.
On the server hosting the mysql server, I have a rule like this:
Now that I have the firewall setup, I can create my ssh tunnel. I’m doing this using a command line script for simplicity, but on OS X it would be very easy to create a simple “script” that would operate much like a “connect VPN” process.
After researching online a bit, I discovered that to tunnel via SSH, you used the following command (the second part of this command just tests the db connection to make sure it worked):
What this does is tell ssh to create a remote SSH session to MY_EXTERNAL_IP on MY_SSH_PORT and then forward all requests to REMOTE_PORT to MY_INTERNAL_IP and create a binding to the LOCAL_IP.
What’s happening behind the scenes is that you are routing all requests to LOCAL_IP on LOCAL_PORT to REMOTE_PORT on MY_EXTERNAL_IP which in turn forwards all traffic to REMOTE_PORT on MY_INTERNAL_IP. Going the reverse direction any communication from MY_INTERNAL_IP gets routed straight back to the LOCAL_IP. This is a very simplified description of an SSH Tunnel. In essence you’ve created a tunnel through which all requets are able to be securely encrypted and sent to a specific server that is sitting behind a firewall.
Note, not just anyone can create such a tunnel. You have to be able to access the box via SSH. As I mentioned before, I’ve locked-down the system using public/private keys. If you have not done that, then anyone that has a vaild account on the server and can connect via ssh can actually create an ssh tunnel like this.
i.e.
# ssh -f -p12345 -L 127.0.0.1:3306:10.0.0.2:3306 dba@176.148.12.20 sleep 60 && mysql -h 127.0.0.1
For simplicity, I choose 127.0.0.1 but that has the unintended side-effect of making MySQL Administrator (the gui tool) think I’m connecting to a local box and it confuses it sees that the server is off. I’d prefer not to risk some fringe use case like this causing disasterous bugs when I update a table, etc.
So, I manually added a network interface. This was easy to accomplish in OS X by accessing the Network Settings and then adding a new ethernet service. I named it “SSH Tunnel” and gave it an internal ip of 192.168.0.10 which was a valid but unused address on my subnet (but not one that would have any conflicts with the DHCP server on my router).
New IP Setup for SSH Tunneling
I actually didn’t give any DNS or GATEWAY information, as it was not relevant.
So now I have a new ip available on my local subnet, and I used that as my LOCAL_IP. Despite the fact that the ip is bound to a fictional ethernet interface on my local computer, MySQL Administrator thinks it’s a remote server, so the issue is solved.
All in all, it took me about a days worth of research but less than an hour to implement everything. Now i have secure access remotely to administrate my database behind a firewall. To the outside world, my database server is entirely invisible. Behind that firewall, I can access my database without risk of being charged for bandwidth.
Geek 'N Me 