Holistics Docs - End-to-End Business Intelligence Platform

The Holistics Documentation

Welcome to the Holistics Documentation. You'll find comprehensive guides and documentation to help you start working with Holistics as quickly as possible, as well as support if you get stuck. Let's jump right in!

Documentation    Get Started

Setup Reverse SSH Tunnels

Setup Reverse SSH Tunnels Automatically

A. Introduction

With our automated bash script, connecting Holistics to your data source using reverse tunnel is much faster and more convenient.

NOTE: The script supports Debian and RHEL based distros. For other systems, please see this doc on setting up tunnel manually.

(To learn more about reverse tunnel, please also refer to this)

B. Guide

  1. Enter the Display Name of the data source and choose your Database Type
  2. Choose Use reverse tunnel
  3. Fill in the Host and Port where your database can be accessed from your proxy server
  4. Click Generate Script and wait for the script to be generated
  5. Copy the command and execute it on your proxy/bastion server. You will be asked for sudo privileges to install packages and set up system service. See the last section to know all actions that the script carries out.
  6. Read the script introduction and follow the script execution
  7. Return to your browser when this message appears:
  8. The database configurations will now appear on your form. Fill in and click Test Connection
  9. If the connection is successful, you can save the data source by clicking Save Data Source
  10. After the data source is saved, the reverse tunnel will be running in a system service called h_autossh. Note that the display of the status varies between systems.

C. h_autossh service

As described in Script Operation / Final steps below, the reverse tunnel script creates a service for you to manage all tunnel connections to Holistics, and start them automatically on startup.

Using service

  • to start all Holistics tunnels, run sudo service h_autossh start
  • to stop all Holistics tunnels, run sudo service h_autossh stop
  • to restart all Holistics tunnels, run sudo service h_autossh restart
  • to see the status of Holistics tunnels service, run sudo service h_autossh status

Removing service

To remove the service quickly and conveniently, please see the section Removeing Reverse Tunnel section below.

D. Removing Reverse Tunnel

You should only want to remove the reverse tunnel when you remove a data source.

For data sources using reverse tunnel connection, when you click Delete, we will generate another script for you to execute on the proxy server.

This script removes the reverse tunnel (associated with the chosen data source) from h_autossh and restart the service.
If the reverse tunnel being removed is the last tunnel in h_autossh (i.e. you do not have any other reverse tunnel), the h_autossh service will also be removed.
Finally, the data source will be deleted.

E. Script Operation (Automated)

The script takes the following actions (in order)

1. Create Holistics Directory

Creates a directory to store all files related to the whole process.
Full path: $HOME/.holistics

2. Install Packages

The script tries to install two main packages:

  • curl
    Used to communicate with Holistics web server.
    nss is also updated if possible so that curl can handle ssl connection properly

  • autossh
    The program to handle the tunnel connection and keep the connection persistent. For distros using yum package manager, autossh is not always available (such as in RHEL 6). In this case, gcc and make will also be installed to compile autossh from its source. If you want to install autossh by yourself instead, please install it prior to the script execution, and the script will not try to install again.

3. Generate RSA key pair and submit public key to Holistics

A new key pair will be generated in Holistics directory using ssh-keygen. If the key pair exists, for example when you have already set up another tunnel, the existing key pair will be used. Then, the public key is submitted to Holistics web server. Holistics will add your public key to the tunnel server so that ssh connection can be established, and also assign a specific port on the tunnel server for your new connection.

4. Start the reverse tunnel

The script creates an reverse tunnel ssh connection from your proxy server to our tunnel server. The port on the tunnel server will be the one assigned by Holistics, and the port on your server is the one you configured in the data source form. Afterward, the script will notify Holistics web server about the running tunnel.

5. Create/Update h_autossh service

At the point the scripts tells you to return to the browser, it starts waiting for your data source result. If the data source is saved successfully, the script will stop the reverse tunnel that was made previously. Then, it creates a new service called h_autossh, which is stored in /etc/init.d/ and contains the command to run the reverse tunnel. If the service exists, the script will update it to include the new tunnel configuration. The service is also enabled to run on startup.

Setup Reverse SSH Tunnels Manually

Introduction

Setting up reverse tunnel is great if you want to open a dedicated connection from your
bastion server to our tunnel server.

All connections from Holistics will then through the tunnel for a more secure data transfer. All connections to your DB will look as if it originates from your bastion server.

Guide

We'll be using autossh to make the reverse SSH tunnel, autossh manages the SSH connections automatically for us, restarting it when it dies/drops off, so that we'll try to have a persistent tunnel as much as possible.

Diagram

Generating SSH Key Pair

Generate a new SSH key pair with:

ssh-keygen -t rsa -b 4096

When asked for a passphrase, press 'Enter'. A pair of files will be created as per the path you entered.

Already Have Key Pair

If you already have a keypair, please ensure your private key is read only by your use account:

chmod 400 <path_to_your_private_key_file>

Send Us Your Key Pair

Send us the public key (file ~/.ssh/id_rsa.pub) via support@holistics.io so we can add it to our tunnel server.

We will confirm with you after we have installed your public key and send you the available secured ports to connect to.
Make sure you received the confirmation from us before you continue with the steps below, otherwise the command will fail.

Setting Up Tunnel

Install autossh:

sudo apt-get update
sudo apt-get install autossh

We'll be running autossh in a screen, you can run it as a background daemon too, but that's more difficult to manage. Install screen with sudo apt-get install screen, then run:

screen -S holistics

Now that you're inside a screen:

autossh -M 0 -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \
-R *:[tunnel_port]:[yourdb.server.com]:[db_port] \
autossh@tunnel.holistics.io -p 50022 \
-i <path_to_your_private_key_file>

Before you run, change the second line to fit with the connection string to your database server:

-R *:[tunnel_port]:[aws.ip.to.redshift.com]:[redshift_port]

Example of a command:

autossh -M 0 -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \
  -R *:20032:db.somehost.com:5432 \
  autossh@tunnel.holistics.io -p 50022 \
  -i ~/.ssh/id_rsa

In the above example:

  • Your db host is: db.somehost.com, port 5432 (PostgreSQL)
  • The tunnel port you choose is: 20032

Notes:

  • [tunnel_port] is the port we'll use when connecting to tunnel.holistics.io. We've opened up the range
    20000:20100 for tunneling purpose, so if you create a second tunnel, please pick a port in this range.
  • We've restricted traffic for tunnel.holistics.io port 20000:20100 to only the Holistics web server, so
    connecting to these ports from other computers will not succeed.

Add New Data Source

Now open Holistics and add a data source, use the credentials you have, except:

  • Host: tunnel.holistics.io
  • Port: [tunnel_port]

You should be good to go.

About GNU screen:

  • To exit the screen: Ctrl-a d
  • To enter the screen again: screen -x holistics

Setup Reverse SSH Tunnels