Remote Connection Fun with Tunneling!

 

pclark's picture

Oftentimes, we find ourselves not able to be On-Site or otherwise physically On-Console of the server we need to work on.  If time is of the essence the administrator with command line savvy can conquer just about any remote administration or troubleshooting need. How does one acheive this level of connectivity?  Where can I get some of that? Well, look no further than 'terminal' and 'ssh'.

To the seasoned admin terminal and ssh are ubiquitous and necessary daily tools that can ease the administration of local or remote systems.  Most of the GUI programs available are nothing more than fancy wrappers for the command line but often with a reduced subset of functionality.  This post will attempt to get you reasonably acquainted with creating an SSH tunnel with the available tools on a Linux or OSX system. For those of us on a Windows platform, you too should be able to take advantage of the instructions, only you will need the free program- PuTTY, available for download from: http://www.chiark.greenend.org.uk/~sgtatham/putty/

When getting started sometimes the biggest hurdle is actually finding where 'terminal' is in the filesystem.  For OSX users, typing in 'terminal' in spotlight should find it quickly, if looking in finder, navigate to Applications -> Utilities -> terminal. On Linux, there is typically a menu item for terminal under 'System Tools'.

As with just about anything allowing remote connections, the firewall at both ends needs to allow traffic to and from the specific ports.  99% of the time, all that is required is the SSH port to be forwarded, which by default is port 22.  However with the prevalence of scripted attacks probing wide swaths of the internet for the default port 22, admins often move the SSH service to a differnt port i.e. 2222, or whatever they desire as long as it's not conflicting with some other applications ports.

SSH in itself is a very powerful program with very deep abilities - it has been called a 'Swiss Army Knife'.  For my purposes, I'm only going to cover the more common scenarios as they relate to creating a tunnel to a remote server and port.

Lets get started:

Scenario 1:

Suppose you need to connect to a database named 'pilot' which is at erp.xyz.com, and they have setup admin/password for you to login.  PostgreSQL is running on port 5432, but is not configured to outside access.  Their SSH service is running on port 2222.  Concidently, you are running your own postgres locally on your PC you're using to connect, so you cannot use 5432 as your local port, so you elect to use port 5433 on your local machine.

From terminal, type...

ssh -L5433:localhost:5432 -p 2222 admin@erp.xyz.com 
password: password

Once at the prompt type 'top', which is a resource/process monitor commonly available on most unix platforms.  We do this to keep the connection active, otherwise ssh will disconnect an inactive connection. Now, you can minimize your terminal session.  Fire up your local pgAdmin in the regular way and add a host.

For this connection, you want:

Name: XYZ Company DB 
Host: localhost (or 127.0.0.1) 
Port: 5433 
Username: postgres (or admin, mfgadmin, even a regular xtuple user) 
For the password, enter the password
If the pg_hba.conf is set up to allow trusted localhost or trusted socket connections, then you do not need a password.  By tunneling directly to the server hosting the database, you are effectivly on the console.

And then click OK.  The server should appear in pgAdmin, and 'pilot' (and other databases) should appear.  While the tunnel is active in terminal, you can also connect your xTuple Client (or openRPT, updater, etc) to localhost port 5433, db named 'pilot' and login.  You now have as good of access as you would if you were sitting in front of the screen of the server.

Scenario 2:

ABC Company needs you to connect and apply an update to their server named ERP (with internal IP 10.0.1.60).  Server ERP's PostgreSQL ports are not forwarded to the router, and the SSH service on that server is only accessible from the inside the LAN as port 22.  However, there is ssh access via port 2222 to a server named ACCESS which does happen to sit on the same network segment as the PostgreSQL server.  SSH can still save the day.  We do this by establishing a 'double tunnel' through ACCESS, into ERP.

ssh -L5433:localhost:5432 -p 2222 admin@ACCESS.abc.com

Enter your password, but instead of running 'top' on this server, create another tunnel.

ssh -L5432:localhost:5432 -p 22 admin@10.0.1.60

Now, login to ERP and run 'top'.

Alternative to the above two step process: (This is one of the more dark arts type SSH commands... magic? voodoo? IDK, but it's pretty cool.)

ssh -L5433:10.0.1.60:5432 -p 2222 admin@ACCESS.abc.com

Now, login to ACCESS and run 'top'.

Connect your xTuple Client (or other application) to localhost port 5433.  You have now made a double tunnel, which looks something like the graphic below.

 

Scenario 3:

Suppose that your customer is hot for the latest and greatest xTuple release.  You need to run the xTuple Updater and create a sandbox environment.  This is probably the one use of an SSH tunnel, or any non-persistent connection, that I would advise against.  It is NOT a good idea to run the xTuple Updater application remotely in this way due to the likelyhood of losing the ssh tunnel due to network drop out.  This is a case where it really is a good idea to be physically on the server, or at least the wired portion of the LAN.  However, a fairly simple solution to this is to run a VNC session on the ERP (10.0.1.60) server and for security access only via the local network, which you can access with:

ssh -L5900:10.0.1.60:5900 -p 22 admin@ACCESS.abc.com

Then, start up your VNC client and connect to localhost port 5900 (the default VNC port).  You should be greeted with the VNC password prompt, then with the desktop of the server.

Conclusion:

I hope I have given you some helpful information on how you can begin to experiment with making remote connections to the servers you manage.  Besides saving precious bandwith by not relying on remote desktop display applications, you are ensuring that all traffic over the tunnel is encrypted which is especially helpful when dealing with potentially sensitive database information over the internet.  Also of note is that ssh (in one form or another) is available on every platform you are likely to encounter.  With a little practice, you'll soon leave your mouse and wysiwyg GUI editors and ge-gaw installers and programs for the white-on-black calm that is the terminal.

 
brucehohl
brucehohl's picture
Offline
Joined: 04/22/2004
"Scenario 4"

This is the method I use when connecting from a remote location.
This trims the above method to a single script which can be run from the desktop.

Say the server which is running Postgres is also running an ssh server and its host name is psql-server.
Say server psql-server has a user "some_user" which can make an ssh connection.
Say the firewall is configured to forward erp.xyx.com:2222 to that server (psql-server).

From a Linux workstation at remote location which has the xTuple program at /opt/xTuple/Client
the following two command script can be used to (1) open the tunnel and then (2) open xTuple client
and connect to the pb_demo data base running on server psql-server at port 5432

Use of "sleep 10" keeps the tunnel open until the xTuple client makes a connection.
The -f (fork) parameter then keeps the tunnel open until the xTuple client is closed.
When the xTuple client is closed the tunnel closes automatically!

TWO COMMAND SCRIPT:
#!/bin/bash
ssh -p 2222 -f -L 5432:psql-server:5432 -l some_user erp.xyx.com sleep 10
/opt/xTuple/Client/xtuple -databaseURL=psql://localhost/pb_demo:5432

TWO COMMAND SCRIPT (in case the Linux workstation is also running a Postgres server at port 5432):
#!/bin/bash
ssh -p 2222 -f -L 5433:psql-server:5432 -l some_user erp.xyx.com sleep 10
/opt/xTuple/Client/xtuple -databaseURL=psql://localhost/pb_demo:5433

 
ejmarkow
ejmarkow's picture
Offline
Joined: 01/13/2009
Great Tips!

Thank you both (pclark & brucehohl) for some excellent CLI tips for Xtuple on Linux! Really nice stuff and I'm looking forward to many more such quality mini-tutorials / examples in the near future. It's amazing just how powerful the comand line can be. Some can be too critical of the CLI due to its lack of graphics and no point & click. What operates under the cover and inside the Linux engine is what really counts. It makes everything a possibility.

 
rock333
rock333's picture
Offline
Joined: 05/17/2010
like it

Hey...this is a wonderful website buddy and an informative post!!! i am new here and i found this site very interesting and informative ,, you are a professional person i think.. i am ccsp professional and i have a great interest in such things...thank you for the post buddy and keep on posting nice stuff like this :)

 
oregonbob
oregonbob's picture
Offline
Joined: 09/23/2010
Using putty (plink) on Windows

Thanks for this post. SSH makes a simple VPN that can be setup in minutes. Since my remote users were running Windows, this simple script below uses Plink, a companion program that is included with the free PuTTy SSH full Windows package. In the example below SSH uses password authentication.

Assumptions:
Server running SSH is named myserver.com with SSH open on port 4022, it runs xTuple-postgres on port 5432
SSH username is "bob"
The local Windows client has PuTTy full package that includes plink, and the xTuple client in "c:\program files" folder
The plink.exe line below sets a custom xtuple port to be 5444 on the local Windows PC.
---
@echo off
rem A batch command to remote xTuple over an existing SSH port, fully encrypted and secure
echo Conneting to xTuple server via a secure encryped channel (SSH)...
cd "C:\Program Files\xTuple\Client"
set PATH="C:\Program Files\PuTTY";%PATH%

start plink -v -4 -l bob -L 5444:localhost:5432 -P 4022 myserver.com

echo Type your password in the PLink window,
echo Then hit Enter here to start xTuple
pause
rem xTuple login Options box should be server="localhost" and Port="5444", 5444 is a custom port we set in the plink command above.
"C:\Program Files\xTuple\Client\xtuple.exe"
---