Showing posts with label FreeBSD. Show all posts
Showing posts with label FreeBSD. Show all posts

Wednesday, January 21, 2015

SSH Tunneling on Linux, Unix or BSD Server to bypass NAT

How can I set encrypted tunnel between my desktop/laptop computer and server in a remote data center to bypass the limits in a network? How do I create a reverse SSH tunnel on Unix-like systems?

SSH tunnelling can be thought as a poor-man's-VPN. It is handy in situations where you would like to hide your traffic from any body who might be listening on the wire or eavsdropping. 

You can use such tunnel between your computer and your Unix/BSD/Linux server to bypass limits placed by a network or to bypass NAT, and more.

More about the Internet protocol, ports, tcp and udp

The Internet protocol is nothing but a set of rules for sending information between your desktop and the server on the Internet (or WAN or Lan). Each computer at least has one IP address. Each rule is like a language and in computer science you call it as a protocol. One can use protocols for communication over the Internet.

Common application protocol

For example, you can use HTTP (Hypertext Transfer Protocol) or HTTPS (Hypertext Transfer Protocol Secure) protocol to view images or download files from the Internet. You can use DNS (Domain Name System) protocol translates domain names such as www.cyberciti.biz, which can be easily memorized by humans, to the numerical IP addresses such as 75.126.153.206 and vice versa. You can use ssh (Secure Shell) for secure data communication, remote command-line login, remote command execution, and more.

Our sample setup

                        ///////////
                       //Internet//
                       ////////////
                            |
     +---------------+      |       +-------------+
     | Unix/Linux    |      |       | Linux/Unix  |
     | Server with   +------+-------+ OSX/*BSD    |
     | OpenSSH SSHD  |              | Client      |
     +---------------+              +-------------+
       randomhost.net            client1.cyberciti.biz
     75.126.153.206                  192.168.1.42
                               (loopback interface 127.0.0.1)
Where,
  • randomhost.net - You have an accounts on this Linux/Unix based server.
  • client1.cyberciti.biz - Your private desktop/laptop computer that you use to connect to server1.cyberciti.biz server. You need to use loopback interface with the IP address 127.0.0.1. Only apps installed on the desktop such as browser, irc client, email client and more have access to 127.0.0.1.

Example: SSH tunnel for an IRC client

A tunnel between local port 8888 on the local interface (IP 127.0.0.1) and the IRC server at irc.freenode.net, bound to a remote machine's port 6667. You are going connect to it using the loopback interface:
                        ///////////
                       //Internet//
                       ////////////
                            |
     +---------------+      |       +-------------+
     | Unix/Linux    |      |       | Linux/Unix  |
     | Server with   +------+-------+ OSX/*BSD    |
+----+ OpenSSH SSHD  |              | Client      |
|    +---------------+              +-------------+
|      randomhost.net            client1.cyberciti.biz
|    75.126.153.206                  192.168.1.42
|                              (loopback interface 127.0.0.1:8888) <=== SSH client
|
|       +-----------------------+
+-------+ irc.freenode.net:6667 |
        +-----------------------+
If you login to your shell account with: ssh myuser@randomhost.net for SSH tunnelling you have to add additional arguments. It goes like this:
 
ssh -L 8888:irc.freenode.net:6667 myuser@randomhost.net
 
If the server/shell account you are using to tunnel through is listening on a different port, for example 2745, it would be written like this:
 
ssh -p2745 -L8888:irc.freenode.net:6667 myuser@randomhost.net
 
This will open a tunnel between your computer to irc.freenode.net through your shell account on randomhost.net. All traffic will go through your shell account box as encrypted SSH traffic before reaching irc.freenode.net. Your computer now acts like a irc server listening to port 8888. Replace it with any ports you want above 1024 to avoid conflict.
To connect to your local port as if it's irc server. On irssi this would be:
  /server 127.0.0.1 8888
This will also apply for any other irc clients such as X-Chat. Use 127.0.0.1/8888 for server name and you are good to go. Other fields remain the same.

Howto setup a reverse SSH tunnel

In a scenario where a machine is behind NAT or company firewall a normal SSH tunnel won't work. To overcome this, we have to make use of reverse SSH tunnel. To achieve this, you need an internet reachable box along with the machine behind NAT/firewall. During this guide we will call the machine behind NAT/firewall a NATbox and internet reachable machine an OPENbox.
For reverse SSH Tunnel, there are basically three ports involved. One is the SSH port of workstation, we use it forward the reverse tunnel port into it. The second, is the reverse tunnel port which gets forwarded to workstation's SSH port. The third, is the SSH port of the public box, we need that port to SSH into public box.
From outside if you use public box's SSH port, you log in to that box. If you use reverse tunnel port you get forwarded to workstation. You must be careful about usernames when doing this.
public box ip + public box SSH port + public box username -> access to public box
public box ip + reverse tunnel port + workstation username -> access to workstation
To establish reverse tunnel from workstation, you will use:
public box ip + public box SSH port + public box username 
And setup the reverse tunnel on a different random port and forward it to local SSH port.
To further protect the SSH daemon, you can implement port knocking where the SSH port is blocked by default and when you send special packets the port is opened for you. You can have the SSH port opened for certain IPs.

Example

So, let us consider, OPENbox is listening to SSH on port 1234. Type following on NATbox:
 
ssh -p1234 -R 5555:localhost:22 openboxuser@OPENbox.example.com
 
This will initiate a connection from behind the NATd/firewalled box to the publicly reachable box listeing to SSH on port 1234. Once the connection is established it will create a reverse tunnel at port 22 for the remote party to connect and get in. Type the following command on OPENbox:
 
ssh  -p5555 natboxuser@localhost
 
Since the NATd/firewalled box has an established connection to OPENbox, the tunnel will go through the same channel. In addition, type the following from anywhere else to access NATbox which will tunnel the traffic through OPENbox:
 
ssh -p5555 natboxuser@OPENbox
 
This requires an additional setup on the OpenSSHD server, add the lines to/etc/ssh/sshd_config
 GatewayPorts yes
Save and close the file. Make sure you restart/reload the SSHD on the remote server.

Summary

The syntax is as follows to access remote server port without modifying firewall settings:
 ## Syntax ##
ssh -f -L {local-port}:localhost:{remote-server-port} user@remote.server-name-here.org
 
# Use port 8888 on the localhost to connect to port 4444 on host foo for user bar and run in the background#
# Use port 8888 for your apps such as firefox, xchat, and more #
ssh -D 8888 -f -C -q -N -p 4444 foo@bar &
 
Where,
  1. -f : Requests ssh to go to background just before command execution.
  2. -L port : Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side.
  3. -p port : Port to connect to on the remote host.
  4. -R : Specifies that the given port on the remote (server) host is to be forwarded to the given host and port on the local side.
  5. -D port: Specifies a local "dynamic" application-level port forwarding.
  6. -C : Requests compression of all data. This is useful for speeding up connection.
  7. -q : Quiet mode. Causes most warning and diagnostic messages to be suppressed.
  8. -N: Do not execute a remote command. This is useful for just for warding ports.
For more information see man pages: ssh(1)sshd_config(5)ssh_config(5).

PostgreSQL: Installation and configuration

Here's another plug for FreshPorts.  I've been playing with ideas and I've come up a few good improvements.  But they will require a database with more features than mySQL.  Specifically, I'm going to need stored procedures and functions.I actually installed PostgreSQL back in late July, but never did anything with it.  This article will help you along the way.
PostgreSQL caught my attention because it's been recommended by others.  It also has the stored procedures and triggers.   These facilities will form the heart and soul of the new database.

NOTE: Since this article was written, the path for the PostgreSQL binaries has changed. When I wrote this article, the pathnames were /usr/local/pgsql/bin/. I have since updated the article to refer to the new location /usr/local/bin/.
Installation - from ports
If you want to use php first, well, I've already installed that.  I'm not sure what you'd do if you want PostgreSQL and php.  Perhaps install mod_php later.  Does anyone know?  If so, add your comments.As always, I'm installing this from ports.  If you haven't already installed your ports tree, you should.  Because this is how easy it is to install a port:
# cd /usr/ports/databases/postgresql7/
# make install
There.  Done.
Actually, I'm sure there might have been more to it than that.  Such as specifying php options.  But I can't recall.
You also need to initialize the database with the following command [note that the user pgsql is not used on all systems, on some systems it might be postgres):
# su -l pgsql -c initdb
This database system will be initialized with username "pgsql".
This user will own all the data files and must also own the server process.

Creating directory /usr/local/pgsql/data
Creating directory /usr/local/pgsql/data/base
Creating directory /usr/local/pgsql/data/global
Creating directory /usr/local/pgsql/data/pg_xlog
Creating template1 database in /usr/local/pgsql/data/base/1

[snip]

Success. You can now start the database server using:

    /usr/local/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
NOTE: Recent versions of FreeBSD will require this entry in /etc/rc.conf in order for PostgreSQL to start:
postgresql_enable="YES"
Now I'm ready to start the database server:
/usr/local/etc/rc.d/010.pgsql.sh start
NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:
/usr/local/etc/rc.d/postgresql start
That should be the name of the file (i.e. 010.pgsql.sh) but if you can't find it, just hunt around in that directory for a simlarly named file.
Allowing users to use psql
This section documents the steps required to allow a user to access a database.  You may also want to read the instructions for adding a database user.  In this section, the term user refers to a login.I like the way PostgreSQL works.  It creates a special user for you, pgsql.  This user does all the work.  The database runs as this user, and all work (database creation, adding users, etc) is done as this user.
NOTE: pgsql is not used on all systems, on some systems it might be postgres.
The first step is to add myself as a user, so I don't have to do all my work as pgql.  Here's how I added myself as a user.  I typed the bits in bold. See also the NOTE below regarding the path.
su -l
Password:
[root@set:~] # su pgsql
/usr/local/bin/createuser dan
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
Done.  Now that I've added myself as a user who can create databases, I can use my normal login.
NOTE: In more recent versions of PostgreSQL, the binary is /usr/local/bin/createuser.
Adding a database
Now I dropped back to my usual login and created a database.
logout
[root@set:~] # logout
[dan@set:/usr/home/dan] $ /usr/local/bin/createdb mydb
CREATE DATABASE
Done.
Creating a user for this database
Now I dropped back to my usual login and created a database.
/usr/local/bin/psql mydb
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

mydb=#
Now I'll create a user, tester,  for this database.
mydb=# create user tester with password 'mypassword';
CREATE USER
To remove a user:
mydb=# drop user tester;
DROP USER
NOTE: In recent versions of PostgreSQL, CREATE USER has been deprecated by CREATE ROLE.
Creating groups
You can also create groups and place the users in those groups.  You can grant permissions collectively to the group instead of individually to the user.
mydb=# CREATE GROUP testers WITH USER dan;
CREATE GROUP
Then you can grant SELECT permission on table thedata   permissions to group testers:
mydb=# GRANT SELECT ON thedata TO GROUP testers;
CHANGE
Creating a table
I created a rather simple table for my testing.
mydb=# create table test (id serial, name varchar(10));
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq'
for SERIAL column 'test.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit
index 'test_id_key' for table 'test'
CREATE
Then I inserted data:
mydb=# insert into test (name) values ('test');
INSERT 18879 1
mydb=# insert into test (name) values ('test2');
INSERT 18880 1
Then I read that data back out:
freshports2=# select * from test;
id | name
----+-------
1 | test
2 | test2
(2 rows)
Getting php going
I create a simple php test in an existing website.  For help on creating websites, look at Apache - virtual hosts.I added this to testpsql.php3 in my website.  Note the amended while loop at the end of this section.
<head>
<title>PostgreSQL test</title>
<body>

<?php
$database=pg_connect("dbname=mydb user=test password=mypassword");
if ($database) {
   $result = pg_exec ($database, "select * from test");
   if ($result) {
      echo pg_numrows($result) . " rows to fetch\n";
      echo "<table>\n";
      $i = 0;
      while ($myrow = pg_fetch_array ($result, $i)) {
         $i++;
         echo "   <tr><td>" . $myrow["id"] . "</td><td>" . 
                              $myrow["name"] . "</td></tr>\n";
         if ($i > 10) break;
      }
      echo "</table>\n";
   } else {
      echo "read from test failed";
   }

   pg_exec ($database, "end");
} else {
   echo "no connection";
}
?>

</body></html>
As you can see, I had to manually break the loop.  I have no idea why.  I thought pg_fetch_array would return false at the end of the result set, as mentioned in the documentation.  But it didn't.  So far, it appears I'll have to use a for  loop for that and not a while.  Any ideas on why should be added as comments.  pg_fetch_array was behaving like that?  It seems to be standard behaviour.
A search at http://google.com found this example, which I used to create this amended while loop:
for ($i = 0; $i < $NumRows; $i++) {             
   $myrow = pg_fetch_array ($result, $i);       
   echo "   <tr><td>" . $myrow["id"] . "</td><td>" . 
                 $myrow["name"] . "</td></tr>\n";
}
What's next?
I would like a Windows GUI inteface to PostgreSQL.  Any suggestions should be added to the comments.   I found ZEOS, but couldn't get it to connect.  I suspect someone wrong with my access rights, but I was looking at /usr/local/pgsql/lib/pg_hba.conf.I'm sure the next PostgreSQL article will have more information.
backups
It's time I added backups to this article.  This information is taken from the Admin documentation at /usr/local/share/doc/pgsql/admin/.A backup is done with this:
% pg_dump dbname > dbname.pgdump
A restore is done with this:
cat dbname.pgdump | psql dbname
Depending upon your path settings, you may have to specify the full path to these binaries.  Under FreeBSD, this would be /usr/local/bin/pg_dump.
For a backup script, please read the section on mySQL backups in the article I wrote for mySQL. Just substitute pg_dump for mysqldump.
Various notes
Tonight I was upgrading, accidentally mind you, from 7.0.3 to 7.1.3. I did this without first deinstalling the old version. Bad idea. As a precaution, you should always dump your old databases before upgrading. I didn't. When I tried to run psql, I was getting these errors:
psql FreshPort2Test
psql: FATAL 1: SetUserId: user 'root' is not in 'pg_shadow'
The mistake was that I was doing this as root. DOH! I had created all my databases as dan. So asking on IRC, I was told to do this:
su - pgsql
psql FreshPort2Test
That worked. I then dumped all my databases as shown in a previous section. Then I saved them all to CD.
Then I did the right thing:
pkg_delete -f postgresql-7.0.3
pkg_delete -f postgresql-7.1.3 # cd /usr/ports/databases/postgresql7
make deinstall
make install
Then I had to do the initdb manually (and I'm not sure if this is usually done automatically):
su -l pgsql
initdb
This database system will be initialized with username "pgsql".
This user will own all the data files and must also own the server process.

Creating directory /usr/local/pgsql/data
Creating directory /usr/local/pgsql/data/base
Creating directory /usr/local/pgsql/data/global
Creating directory /usr/local/pgsql/data/pg_xlog
Creating template1 database in /usr/local/pgsql/data/base/1
DEBUG: database system was shut down at 2001-11-29 17:59:29 EST
DEBUG: CheckPoint record at (0, 8)
DEBUG: Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE
DEBUG: NextTransactionId: 514; NextOid: 16384
DEBUG: database system is in production state
Creating global relations in /usr/local/pgsql/data/global
DEBUG: database system was shut down at 2001-11-29 17:59:34 EST
DEBUG: CheckPoint record at (0, 108)
DEBUG: Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 514; NextOid: 17199
DEBUG: database system is in production state
Initializing pg_shadow.
Enabling unlimited row width for system tables.
Creating system views.
Loading pg_description.
Setting lastsysoid.
Vacuuming database.
Copying template1 to template0.

Success. You can now start the database server using:

/usr/local/bin/postmaster -D /usr/local/pgsql/data
or
/usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
$
Then I started the database server:
# /usr/local/etc/rc.d/010.pgsql.sh start
NOTE: in recent versions of PostgreSQL (e.g. 8.2), the command is:
# /usr/local/etc/rc.d/postgresql start
Note that this script had been sitting around from my previous install. It may not be the same name on your system, but it will be in the same directory.
Improving performance
If you find that a query isn't running fast enough, look at the situation and act accordingly. Here is an example I encountered when working on FreshSource.
I was looking for all the children of a particular element:
freshports=# select * from element where parent_id = 77340;
  id   |   name   | parent_id | directory_file_flag | status
-------+----------+-----------+---------------------+--------
 77341 | files    |     77340 | D                   | A
 77449 | Makefile |     77340 | F                   | A
 77450 | distinfo |     77340 | F                   | A
(3 rows)
freshports=# explain analyse select * from element where parent_id = 77340;
NOTICE: QUERY PLAN:

Seq Scan on element (cost=0.00..2165.41 rows=11 width=30) (actual time=548.68..655.47 rows=3 loops=1)
Total runtime: 655.59 msec
As you can see, this query is accomplished by doing a sequential scan on the element table and it takes 0.6s. Let's refresh the statistics on this table, and then run the query again.
freshports=# vacuum analyze element;
VACUUM

freshports=# explain analyse select * from element where parent_id = 77340;
NOTICE: QUERY PLAN:

Seq Scan on element (cost=0.00..2201.85 rows=12 width=30) (actual time=178.50..236.41 rows=3 loops=1)
Total runtime: 236.53 msec
That gets us down to 0.2s, but we are still doing a sequential scan. Let's try an index.
freshports=# create index element_parent_id on element(parent_id);
CREATE
freshports=# explain analyse select * from element where parent_id = 77340;
NOTICE: QUERY PLAN:

Index Scan using element_parent_id on element (cost=0.00..25.89 rows=12 width=30) (actual time=0.38..0.49 rows=3 loops=1)
Total runtime: 0.62 msec
OK, now that is impressive. We've gone from 600ms to 0.6ms. That's 1000 times faster overall. For more information, read the documentation regarding vacuum.