How do I access a private Amazon Redshift cluster from my local machine?

5 minute read

I want to use my local computer to access an Amazon Redshift cluster that's in an Amazon Virtual Private Cloud (Amazon VPC) private subnet.

Short description

Use an Amazon Elastic Compute Cloud (Amazon EC2) instance and SQL Workbench/J to create an SSH tunnel. The tunnel routes all incoming traffic from the local machine to the private Amazon Redshift cluster.

Resolution

Create the Amazon VPC, EC2 instance, and Amazon Redshift cluster

To create the Amazon VPC, EC2 instance, and Amazon Redshift cluster, complete the following steps:

Create an Amazon VPC with public and private subnets.
Launch an EC2 instance from an Amazon Linux 2 Amazon Machine Image (AMI) into the public subnet of the Amazon VPC. When you create the instance, choose the following options:
For Auto-assign Public IP, choose Enable. Or, assign an Elastic IP address to the instance.
Create a new security group with an SSH rule.
For Source, choose Custom, and then enter your CIDR block. Or, choose My IP.
On the Amazon Redshift console, create a cluster subnet. Enter the following information:
For VPC ID, choose the ID of your Amazon VPC.
For Subnet ID, choose the ID of the private subnet.
Create a new security group.
Add a rule to the security group that allows inbound traffic from the instance's security group. Enter the following information:
For Type, choose Custom TCP.
For Port Range, enter the default port for Amazon Redshift, 5439.
For Source, choose Custom, and then enter the name of your security group.
Launch a new Amazon Redshift cluster, or restore a cluster from a snapshot. On the Additional Configuration page, choose the following options:
For Choose a VPC, choose your Amazon VPC.
For Cluster subnet group, choose your subnet group.
For Publicly accessible, choose No.
For VPC security groups, choose your security group. Wait until the cluster is in the available state.
Run the following command to connect to the EC2 instance from your local machine:
```
ssh -i "your_key.pem" ec2-user@your_EC2_endpoint
```
Note: Replace your_key.pem and your_EC2_endpoint with your values. For more information, see Connect to your Linux instance from Linux or macOS using SSH.

Run the following command to install telnet:

sudo yum install telnet

To use telnet to test the connection to your Amazon Redshift cluster, run the following command:
```
telnet cluster-endpoint cluster-port
```
Note: Replace cluster-endpoint and cluster-port with your values.
Or, run the following command to use dig to confirm that your local machine can reach the private IP address of the Amazon Redshift cluster:
```
dig cluster-endpoint
```
Note: Replace cluster-endpoint with your cluster endpoint.

Create the tunnel

To create a tunnel, complete the following steps:

Install SQL Workbench/J on your local machine.
Download the latest Amazon Redshift JDBC driver.
In SQL Workbench/J, use the JDBC driver to create a connection profile.
To configure the SSH connection in SQL Workbench/J, choose SSH, and then enter the following information:
For SSH hostname, enter the public IP address or DNS of the EC2 instance.
For SSH port, enter 22.
For Username, enter ec2-user.
For Private key file, enter the .pem file that you downloaded when you created the EC2 instance.
For Password, keep the field blank.
For Local port, enter any free local port. Your Amazon Redshift cluster uses port 5439 by default.
For DB hostname, enter the cluster endpoint. The endpoint can't include the port number or database name.
For DB port, enter 5439.
Select the Rewrite JDBC URL option.
Choose OK.
Make sure to correctly enter the JDBC URL, superuser name, and password.
To confirm that the connection is working, choose Test. For more information, see Connecting through an SSH tunnel on the SQL Workbench/J website.

(Optional) Modify the connection for an AWS Identity and Access Management (IAM) user

To connect to the Amazon Redshift cluster as an IAM user, complete the following steps to modify the connection profile:

Review the IAM user policy. The IAM user policy must allow the GetClusterCredentials, JoinGroup, and CreateClusterUser Amazon Redshift actions for the dbgroup, dbuser, and dbname resources. The following is an example IAM user policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:GetClusterCredentials",
                "redshift:CreateClusterUser",
                "redshift:JoinGroup"
            ],
            "Resource": [
                "arn:aws:redshift:eu-west-2:012345678912:dbgroup:clustername/group_name",
                "arn:aws:redshift:eu-west-2:012345678912:dbuser:clustername/user_name or * ",
                "arn:aws:redshift:eu-west-2:012345678912:dbname:clustername/database_name"
            ]
        }
    ]
}

Note: Replace us-west-2 with the AWS Region that your cluster is in, 012345678912 with your AWS account ID, and user_name with the name of the Amazon Redshift user. Or, you can use "*" instead of a specific username.

In SQL Workbench/J, change the first part of connection profile's JDBC URL to jdbc:redshift:iam. For example, change the JDBC URL to jdbc:redshift:iam://127.0.0.1:5439/example.
Choose Extended Properties, and then enter the following information:
For AccessKeyID, enter the IAM user's access key ID.
For SecretAccessKey, enter the IAM user's secret access key.
(Optional) For DbGroups, choose this option to force the IAM user to join an existing group.
For DbUser, enter the IAM user's name.
For AutoCreate, set the option to true.
For ClusterID, enter the name of the Amazon Redshift cluster.
For Region, enter the Region that the cluster is in, such as us-east-1.
On the cluster connection profile page, choose Test.