How can 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 a private subnet of an Amazon Virtual Private Cloud (Amazon VPC). How can I do this?

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

1. Create an Amazon VPC with public and private subnets.

2. Launch an EC2 instance from an Amazon Linux 2 Amazon Machine Image (AMI) into the public subnet of the Amazon VPC that you created in step 1. Choose the following options when creating the instance:
In step 3, for Auto-assign Public IP, choose Enable. Or, you can assign an Elastic IP address to the instance.
In step 6, create a new security group with an SSH rule. For Source, choose Custom, and then enter your IP CIDR block, or choose My IP.

3. On the Amazon Redshift console, create a cluster subnet group.
For VPC ID, choose the ID of the Amazon VPC that you created in step 1.
For Subnet ID, choose the ID of the private subnet.

4. Create a new security group.

5. Add a rule to the newly created security group that allows inbound traffic from the instance's security group:
For Type, choose Custom TCP.
For Port Range, enter 5439 (the default port for Amazon Redshift).
For Source, choose Custom, and then enter the name of the security group that you created in step 2.

6. 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 the Amazon VPC that you created in step 1.
For Cluster subnet group, choose the group that you created in step 3.
For Publicly accessible, choose No.
For VPC security groups, choose the security group that you created in step 4.

Wait for the cluster to reach the available state before continuing.

7. Run the following command to connect to the EC2 instance from your local machine. Replace your_key.pem and your_EC2_endpoint with your values. For more information, see Connecting to your Linux instance using SSH.

ssh -i "your_key.pem" ec2-user@your_EC2_endpoint

8. Run the following command to install telnet:

sudo yum install telnet

9. Use telnet to test the connection to your Amazon Redshift cluster. In the following command, replace cluster-endpoint and cluster-port with your values.

telnet cluster-endpoint cluster-port

Or, use dig to confirm that your local machine can reach the private IP address of the Amazon Redshift cluster. In the following command, replace cluster-endpoint with your cluster endpoint.

dig cluster-endpoint

Create the tunnel

1. Install SQL Workbench/J on your local machine.

2. Download the latest Amazon Redshift JDBC driver.

3. In SQL Workbench/J, create a connection profile using the JDBC driver that you downloaded in step 2.

4. To configure the SSH connection in SQL Workbench/J, choose SSH, and then enter the following:
SSH hostname: the public IP address or DNS of the EC2 instance
SSH port: 22
Username: ec2-user
Private key file: the .pem file that you downloaded when you created the EC2 instance
Password: keep this field empty
Local port: any free local port (your Amazon Redshift cluster uses port 5439 by default)
DB hostname: the cluster endpoint (should not include the port number or database name)
DB port: 5439
Rewrite JDBC URL: select this option

5. Choose OK to save the SSH settings.

6. Be sure that the JDBC URL and superuser name and password are entered correctly.

7. Choose Test to confirm that the connection is working. For more information, see Connecting through an SSH tunnel in the SQL Workbench/J documentation.

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

To connect to the Amazon Redshift cluster as an IAM user, modify the connection profile that you created in the previous step:

1. Confirm that the IAM user has a policy that allows the GetClusterCredentials, JoinGroup, and CreateClusterUser Amazon Redshift actions for the dbgroup, dbuser, and dbname resources. Replace these values in the following example:
us-west-2: the Region that your cluster is in
012345678912: your AWS account IDclustername: the name of your cluster
group_name: the database group name
user_name: the name of the Amazon Redshift user (you can use "*" instead of specifying a specific user)
database_name: the database name

{
    "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"
            ]
        }
    ]
}

2. In SQL Workbench/J, change the first part of connection profile's JDBC URL to jdbc:redshift:iam. (For example, you can change the JDBC URL to "jdbc:redshift:iam://127.0.0.1:5439/example".)

3. Choose Extended Properties, and then create the following properties: AccessKeyID: the IAM user's access key ID
SecretAccessKey: the IAM user's secret access key
DbGroups: forces the IAM user to join an existing group
DbUser: the IAM user’s name
AutoCreate: set to true ClusterID: the name of the Amazon Redshift cluster (not the database name)
Region: the AWS Region that the cluster is in, such as us-east-1