Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Connecting to a Redshift Cluster with a Private IP

Connecting to a Redshift Cluster with a Private IP
August 27, 2015

Last week we went through how to set up your Amazon Redshift Cluster. Our instructions covered getting everything set up with a public IP. When your cluster requires a private IP for increased security, the steps for connecting are a little more complicated.

VPC: Private IP

In a VPC with a private IP setup, only connections from inside the network are allowed. This is the most common setup we’ve seen.

There are a few ways to connect to these clusters. The easiest is to SSH into a box in the VPC with a public IP address, often called a Bastion host, and run psql there. The SSH program creates an encrypted connection which lets you run commands and forward network data on remote machines.

To use graphical tools or a client-side copy command, you’ll need a way to forward traffic through the Bastion host. If you already have a VPN running in AWS, you can connect through there. Just make sure the VPN instance is in a security group that can talk to the Redshift cluster.

Otherwise, you can create your own instance to forward through. Either Windows or Linux will work as your Bastion host, but Linux is much easier to set up.

Linux SSH Server

First, launch a Linux distro — we recommend Ubuntu if you don’t have a favorite. You can use a T2 Micro for this, though we recommend a T2 Medium if you plan to move a lot of data.

Log in by clicking the Connect button in the AWS console and following these instructions:

Create a user account. -m creates a home directory, which you need to store your public key for connecting.

$ sudo useradd periscope -m -s /bin/false

Become the user to install your public key. -s sets a shell that quits, so the user can forward ports, but not run commands.

$ sudo su -s /bin/bash periscope
$ mkdir ~/.ssh
$ cat - >> ~/.ssh/authorized_keys

Then paste your public key, press Enter, then press Ctrl-d. Alternatively, you can copy the file there.

Permissions are very important for the authorized_keys file. Its contents allow someone to connect to this machine as your user, so it’s only valid if editing is restricted to your user.

Make sure that only your user has write permissions to your home directory and .ssh folder. For good measure, remove all permissions from the authorized_keys.

$ chmod 600 ~/.ssh/authorized_keys
$ chmod go-w ~/ ~/.ssh

If you want to lock down only that tunnel, you can in the authorized_keys file:

no-pty, permitopen="foo.us-east1.amazonaws.com:5439"
  ssh-rsa AAAAB3NzaC1y...Rdo/R [email protected]

no-pty is a step beyond using /sbin/false as the shell — it restricts the user from even opening a virtual terminal, so you have to connect with -nTN.

For more information and help with troubleshooting, visit the Ubuntu community site.

Windows SSH Server

Windows doesn’t come with an SSH server pre-installed. We recommend using freeSSHd — it’s free and easier to set up than OpenSSHd.

Get started by installing freeSSHd and running it as an administrator. Then click the icon in the system tray to open your settings.

In the Server Status tab, make sure the SSH server is running. In the Users tab, create a user. Set Authorization to Public Key and make sure to allow Tunneling.

In the Tunneling tab, enable Local Port Forwarding. In the Authentication tab, set Public Key Authentication to Required, then open the public key folder.

Copy your public key to a file with the same name as the user. The name has to match exactly, so take out any file extension.

Make sure the public key is in the correct folder and has the correct name. You may also need to restrict it to administrator only. If your changes don’t seem to be taking effect, make sure you are running as an administrator.

SSH Client

SSH has an option called local port forwarding, which causes your SSH client to open a port on your computer and forward any network traffic received to the server. In this case, the server forwards that connection to the database.

Mac/Linux

On Mac/Linux, invoke local port forwarding with -L local-port:redshift-host:redshift-port. You can choose any port greater than 1024 — for this example we chose 5439. -nNT stops the ssh client from opening a shell, and allows you to background the connection by starting with an & at the end.

$ ssh bastion.us-east1.amazonaws.com \
  -L5439:foo.us-east-1.amazonaws.com:5439 -nNT

After the connection starts working, connect using localhost as the hostname and 5439 as the port.

Using psql type the following:

psql -h 127.0.0.1 -p 5439 database_name

If you’re using SQL Workbench/J instead, use the following settings where ‘dev’ is the name of your database:

For more details on port forwarding, and cools tricks like the reverse tunnel, check the Ubuntu wiki.

Windows SSH Client

PuTTY is our go-to Windows SSH client. To set up tunneling in PuTTY, expand the SSH section of the menu on the left, then open the Tunnels menu.

Source port can be anything you’d like — we’ve chosen 5439 in this example. For Destination, use your Redshift hostname and port, separating the two with a colon. Click Add and save the profile by clicking Open.

Then connect, using SQL Workbench/J just as above.

And you’re in! To start loading data, check out our post on importing data into redshift from postgres and mysql.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?