python - How to use bastion host to SSH to Redshift

Question

I have been trying to figure out how to connect to Amazon Redshift clusters when they are private rather than publicly accessible. I am trying to securely move data from our current MySQL DB to a cluster in Redshift. Currently I have a Python script which creates a CSV of the data, puts it in S3, creates a table for the data, then uses COPY to put it in Redshift. We are using the Python script to automate the process.

However I am completely unable to establish connections with Redhsift when the cluster is set to private. I did some digging and found out about SSH Tunneling as a potential solution. While Redshift doesn't support SSH to my knowledge it is possible to use a bastion host to connect using an ec2 instance running psql. The issue with this is I need to perform this in the Python script in order to keep the process as automated as possible.

I have created an ec2 instance and used PuTTY to load psql to it, however I am unsure of how to use that instance as a bastion host i.e. connecting to it and using it to connect to Redshift, and how to acually perform this in the python script if it is at all possible. Has anyone done anything similar or know how to connect to ec2 and from ec2 to redshift?

Answer 1

The Bastion will simply be used to forward requests from your own computer to Amazon Redshift. You do not need to install any software (eg psql) on the Bastion.

Assuming that you can successfully use PuTTY to login to the Bastion, change this configuration in PuTTY:

• Go to the Tunnels configuration page
• Source port: 5439
• Destination: The full DNS name of your Redshift cluster, followed by :5439
• Click Add
• Save your PuTTY settings

Here's a picture from Connecting to a Redshift Cluster with a Private IP:

This configuration says: "Redirect local port 5439 through the SSH connection to the Bastion server. From there, have the Bastion send the traffic to REDSHIFT-DNS:5439."

Thus, the Bastion just acts like a Jump Box, forwarding your traffic and not actually running anything else.

You can change the local source port to whatever you like, so you can have multiple tunnels on the same SSH connection.