D 2018-07-15T13:40:22.877
L port-forwarding
P bbbe0fc05b1484ef6c86f7c4642aa7ce906b3835
U sandro
W 9059
Back to the <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=tutorial">tutorial</a>
<br><br>
<h2>Connecting to a remote PostgreSQL server via Port Forwarding / SSH Tunneling</h2>
The well known <b>SSH</b> (<i><b>S</b>ecure <b>SH</b>ell</i>) <a href="https://en.wikipedia.org/wiki/Secure_Shell">protocol</a> is very frequently adopted by system administrators for establishing safe connections to remote servers.<br>
SSH automatically encrypts all the traffic between the local PC and the remote server by using strong cryptograhic ciphers, thus allowing for very secure connections over the intrinsically insecure Internet.<br><br>
You can use SSH also for establishing safe connections to remote PostgreSQL servers, but this requires to activate some special SSH feature known as <a href="https://en.wikipedia.org/wiki/Port_forwarding">port forwarding</a> aka <a href="https://en.wikipedia.org/wiki/Tunneling_protocol">SSH tunneling</a>.<br><br>
<hr>
<h2>Basic concepts</h2>
<img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/Ssh-port-forwarding.png" alt="port-forwarding"><br><br>
<b>The problem</b>: you are attempting to establish a network connection between your local machine and a remote server on IP Port <b>6667</b>.<br>
But this is impossible, because the Firewall forbids any connection to port 6667 (represented in the above figure by the <i><b>red arrow</b></i>); and there is a very good reason for doing this.<br>
If port 6667 was enabled to accept any incoming traffic from the outside this could pose a severe security breach, because anyone (and not you only) could freely connect to the server.<br>
Even worst, the traffic over the connection would be uncyphered, and consequently very easy to be maliciously intercepted or falsified.<br><br>
<b>The perfect solution</b>: as we've already seen, the SSH protocol was invented for ensuring robustly safe encrypted network connections.<br>
The standard IP Port for SSH is <b>22</b>, and firewalls are usually configured so to allow connections on port 22 (<i><b>green arrow</b></i>).<br>
So we just require some appropriate <i><b>magic trick</b></i> capable of <b>tunneling</b> our own traffic over an SSH connection. Let's see in full detail how it works:
<ul>
<li>on your locale machine ths SSH client will start listening at port <b>1234</b> (you can actualy freely configure any unused IP Port).</li>
<li>the SSH client will encrypt any data received on local port 1234, and will send the resulting traffic to the remote server on the standard SSH port, that is 22.</li>
<li>the SSH server (on the remote machine) will examine the incoming traffic, and will recognize a tunnelled request.<br>
Consequently it will uncypher the message, and will forward the plain data to local port <b>6667</b>, again on the remote server (<i><b>violet arrow</b></i>).</li>
<li>symmetrical actions will happen in reverse order for tunneling the server's response.</li>
<li>the final net effect of such an <b><i>SSH tunnel</i></b> is that now port <b>1234</b> on the local machine will be <b><i>forwarded</i></b> to port <b>6667</b> on the remote server, and vice versa.</li>
</ul>
<br><br>
<hr>
<h2>How-to configure Port Forwarding</h2>
We'll suppose that some <b>SSH server</b> will be already installed and configured on the remote server, and that the Firewall configuration will accept external connections on port 22.<br>
If not, please check the appropriate documentation.<br>
<a href="">OpenSSH</a> (both client and server) is almost universally supported by all Linux distros and by many Unix-like systems, this including Mac OS X.<br><br>
Once ensured that anything is correctly working on the server side, setting up <b><i>Port Forwarding</i></b> / <b><i>SSH Tunneling</i></b> just requires few simple actions on the client side (your local machine).<br>
Unhappily this is quite different on Unix-like systems and on MS Windows, so we'll examine each of them separately.
<br><br>
<hr>
<h2>Configuring Port Forwarding on Linux</h2>
<b>Note</b>: the following directives are supposed to be valid on any Unix-like system, ranging from Linux to OpenBSD, Mac OS X and alike.<br><br>
<img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/ssh-tunnel.png" alt="ssh-tunnel"><br><br>
<ol>
<li>from the shell, launch a command like this:
<ul>
<li><b>ssh -L 54321:localhost:5432 sandro@192.168.1.66</b><br>
where<ul>
<li><b>ssh</b> is the OpenSSH client program.</li>
<li><b>-L</b> is a flag enabling SSH Tunneling / Port Forwarding.</li>
<li><b>54321:localhost:5432</b> specifies the Port Forwarding configuration.<br>
pay close attention:
<ul>
<li><b>54321</b> is the IP port <b><i>on your local machine</i></b>.</li>
<li><b>5432</b> is the canonical port listended by PostgreSQL <b><i>on the remote server</i></b>.</li>
<li>an SSH Tunnel like this will forward your local port 54321 directly to PostgreSQL on the server (port 5432).<br>
in other words, you are now able to connect any PostgreSQL client to the local port 12345 and a connection to the remote PostgreSQL will be <b><i>magically</i></b> established.<br>
and this PostgreSQL connection will be robustly cyphered, thus ensuring maximum safety.</li>
</ul></li>
<li>and <b>sandro@192.168.166</b> specifies the <b><i>user name</i></b> and the <b><i>IP address</i></b> of the remote server.<br>
<b>Note</b>: you could eventually identify the remote server by its <b><i>domain name</i></b>, such as in <b>joe@www.utopia.org</b>.</li>
</ul></li>
</ul></li>
<li>execute the above command: you'll be asked for the <b>password</b> corresponding to the <b>user</b> you've specified.</li>
<li>if the user name and the password match, an <b><i>SSH session</i></b> will start, and it will support the required <b><i>SSH Tunnel</i></b>.</li>
<li><b>Important notice</b>: don't close the Shell window, because such an action will immediately terminate the SSH Tunnel.<br>
you could eventually minimize the Shell window, but never close it before the final termination of your PostgreSQL connection.</li>
</ol>
<hr>
<h2>Configuring Port Forwarding on MS Windows</h2>
The most renowned SSH client for MS Windows (all versions) is <a href="https://en.wikipedia.org/wiki/PuTTY">PuTTY</a> (<i>open source</i>).<br>
If you've not already installed PuTTY on your Windows box, you can download it from <a href="https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html">here</a>.
<br><br>
<table cellpadding="6" cellspacing="6" border="1" width="100%">
<tr>
<td><ol>
<li>start a PuTTY session: a dialog box will appear.</li>
<li>enter the IP address or the domain name of the remote server.</li>
<li>then toggle the <b>SSH</b> node so to fully expand it.</li>
</ol></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/putty-1.png" alt="putty-1"></td>
</tr><tr>
<td><ol>
<li>now click on the <b>Tunnels</b> node</li>
<li>a new "<i>Options controlling SSH port forwarding</i>" pane will appear.</li>
</ol></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/putty-2.png" alt="putty-2"></td>
</tr><tr>
<td><ol>
<li>enter the source and destination ports.<br>
Pay close attention:
<ul>
<li>the <b>Source port</b> is the IP port <b>on your local machine</b>.</li>
<li>and the <b>Destination</b> is the port <b>on the remote server</b>.</li>
<li>an SSH Tunnel like this will forward your local port 54321 directly to PostgreSQL on the server (port 5432).</li>
</ul></li>
<li>then press the <b>Add</b> button.</li>
</ol></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/putty-3.png" alt="putty-3"></td>
</tr><tr>
<td><ol>
<li>just a final check so to verify if the port forwarding configuration is correct.</li>
<li>and finally press the <b>Open</b> button.</li>
</ol></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/putty-4.png" alt="putty-4"></td>
</tr><tr>
<td><ol>
<li>the dialog box will disappear, and will be replaced by a PuTTY Shell window.</li>
<li>insert your <b>user name</b> and <b>password</b> when required.</li>
<li>if the user name and the password match, an SSH session will start, and it will support the required <b>SSH Tunnel</b>.</li>
<li>you are now able to connect any PostgreSQL client to the local port 12345 and a connection to the remote PostgreSQL will be <b><i>magically</i></b> established.</li>
<li><b>Important notice</b>: don't close the PuTTY Shell window, because such an action will immediately terminate the SSH Tunnel.<br>
you could eventually minimize the PuTTY Shell window, but never close it before the final termination of your PostgreSQL connection.</li>
</ol></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/port-forwarding-ssh-tunnel/putty-shell.png" alt="putty-shell"></td>
</tr>
</table>
<br>
<hr>
<br>
Back to the <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=tutorial">tutorial</a>
Z f1ea16d94f7fb00eb86f5134e3c460c6