Thursday, January 24, 2008

Setting up an SSH tunnel to talk to a remote database

On the nbusers list, Gary Greenberg asked

I have a database (Postgresql) running on the server with the SSL access only.

I.e. to run the query against it I need to login in via PUTTY or other SSL client.

Does anyone know about SSL proxy or whatever that will allow me to create a database connection in the NB6 Services tab?

I gave a pretty lame answer, because I've actually never had to do this, but Wade Chandler had an excellent description of what to do, which I will share here

It sounds like you are saying you have to use SSH. Do you mean the only way to access the server is with SSH? If so then you can use SSH tunnels. I do this often.

You will use which ever SSH client you have. Putty in this case. You then setup a tunnel which in Putty I believe should be under SSH tunnels or tunnels in the configuration section where you setup your connections or connect. You'll have a local port you will later connect to using regular TCP/IP and the server or remote port which the end SSH connection hooks to your local port and makes a tunnel to. Be sure and save your connection once you make your changes so you don't lose them for the next run.

Once you make your SSH connection on your local machine to the SSH server then your DB client server address will actually point to 127.0.0.1 and the local port number you setup in your SSH tunnel. SSH will then link your local port ${whatever_it_is} to the other port on the other end of your SSH connection. So, you actually connect with regular TCP/IP to your local machine on a port which is tunneled over SSH to a TCP/IP port on the other end of your SSH connection. This is why you connect to your local box/machine as the server with the database running on it though it is not...it is tunneled to the real connection and machine.

I have heard about SSH tunneling in the past, and never understood clearly it worked, never having the need. But this description was so clear, I just Got It. Thanks, Wade!

No comments: