AutoSSH Tunnels for Linux
If your database server is not accessible through a direct connection (via Public IP or Port Forwarding on your Firewall) you can alternatively configure an SSH tunnel using a Linux host on your network which can reach both Chartio's tunnel server (connect.chartio.com) via the Internet and your database server over your private networks.
If you are not comfortable with Linux, you may alternately use AutoSSH under Windows with Cygwin.
- A Chartio account
- A Linux machine where you have administrative rights to install software
- Network connectivity to your database server and internet connectivity to reach connect.chartio.com on port 22 (SSH)
- Database server hostname & port number
- Database database name & schema
- Read-only username and password for your database
Connect to your Linux server and install AutoSSH:
sudo apt-get install autossh
Then verify the version installed:
Generate SSH keys
Generate an SSH keypair without a passphrase in the default locations by pressing enter three times after running:
Get your public key
View the contents of your SSH public key and copy it to the clipboard:
Add a datasource to Chartio
- Login to Chartio and navigate to Settings->Datasources and click "Add a Datasource"
- Select your database type (we'll be doing PostgreSQL here, but this equally applies to MySQL, Amazon Redshift or SQL Server)
- Check the "Is tunneled" checkbox and enter your database username, password, database and schema name.
- Click "Connect"
Test the SSH connection
Find your tunnel username. Below it's "tunnel12895" so port number is 12895 and our username is "tunnel12895".
Substitute your tunnel number in the commands XXXXX below.
Now let's test the SSH connection to make sure we've copied the public key properly and can reach connect.chartio.com via SSH. When prompted accept the SSH public key presented by the server.
(Optional) Test connectivity to your database server
Test and make sure you can reach your database server on the necessary port (likely 5432 for PostgreSQL, 3306 for MySQL, 1433 for SQL Server).
If you see "connected to hostname." it can open a TCP connection. Press Ctrl-C to quit. (or in rare cases Ctrl-], then type "quit")
telnet yourdbserver.domain.local 5432
Connect and test the tunnel:
Substitute your tunnel number for XXXXX below and your database port for 5432 if not using PostgreSQL.
ssh -N -R XXXXX:yourdbserver.domain.local:5432 tunnelXXXXX@connect.chartio.com
Note there is no output from this command once connected.
Go back to Chartio and click the "Test Connection" button at the bottom of the tunnel configuration page. If all has gone well Chartio will reflect your schema pulling out your tables, columns and foreign keys automatically.
Now that we've gotten the connection setup properly, let's make it persistent so it will auto-reconnect in the event of failure and autostart on boot.
Quit the SSH session and now connect with AutoSSH instead
autossh -M 0 -N -R XXXXX:yourdbserver.domain.local:5432 tunnelXXXXX@connect.chartio.com -g -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes
Test the connection again from the Datasource->Debug page to ensure you've correctly copied over the settings correctly. If so, quit autossh (Ctrl-c) and rerun the same command as above, but include a "-f" which cause AutoSSH to "fork" into the background.
autossh -M 0 -f -N -R XXXXX:yourdbserver.domain.local:5432 tunnelXXXXX@connect.chartio.com -g -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes
Now setup a cron job so that this command will automatically run on reboot:
Then paste in the command we ran above but preceeded by "@reboot"
@reboot autossh -M 0 -f -N -R XXXXX:yourdbserver.domain.local:5432 tunnelXXXXX@connect.chartio.com -g -o ServerAliveInterval=10 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes
Congratulations! You've setup an SSH tunnel connection.