Don’t know how to access a Private Amazon Redshift Cluster from Local Machine? We can help you.

Generally, we may want to access an Amazon Redshift cluster that’s in a private subnet of an Amazon VPC.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today let us discuss how we can do this.

Access a Private Amazon Redshift Cluster from Local Machine

Firstly, we use an Amazon EC2 instance and SQL Workbench/J to create an SSH tunnel.

It is the tunnel that routes all incoming traffic from the local machine to the private Amazon Redshift cluster.

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

1. Initially, we create an Amazon VPC with public and private subnets.

2. Then we launch an EC2 instance from an Amazon Linux 2 AMI into the public subnet of the Amazon VPC.

While we create the instance, for Auto-assign Public IP, we select Enable. Or, we assign an Elastic IP address to the instance.

In addition, we create a new security group with an SSH rule.

After that for Source, we select Custom, and then enter the IP CIDR block, or select My IP.

3. Later, on the Amazon Redshift console, we create a cluster subnet group.

Here, the VPC ID is the ID of the Amazon VPC that we created, and the Subnet ID is the ID of the private subnet.

4. Then we create a new security group.

5. We need to add a rule to the security group that allows inbound traffic from the instance’s security group:

• Type: Custom TCP
• Port Range: 5439
• For Source: Custom

6. Eventually, we launch a new Amazon Redshift cluster or restore a cluster from a snapshot.

On the Additional Configuration page, we need to choose the following options:

• VPC: Amazon VPC
• Publicly accessible: No

7. To connect to the EC2 instance from the local machine, we run:

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

Here, we replace your_key.pem and your_EC2_endpoint with respective values.

8. Then to install telnet, we run:

sudo yum install telnet

9. After that, we test the connection to the Amazon Redshift cluster with telnet:

telnet cluster-endpoint cluster-port

Or, we can use dig to confirm that the local machine can reach the private IP address of the Amazon Redshift cluster:

dig cluster-endpoint

Create the tunnel

1. To do so, we install SQL Workbench/J on the local machine.

2. We then download the latest Amazon Redshift JDBC Driver.

3. Later, in SQL Workbench/J, we create a connection profile with the JDBC driver.

4. To configure the SSH connection in SQL Workbench/J, we select SSH, and enter:

SSH hostname: Public IP address or DNS of the EC2 instance
SSH port: 22
Username: ec2-user
Private key file: The .pem file
Password: We keep this field empty
Local port: Any free local port (5439 by default)
DB hostname: The cluster endpoint (Do not include the port number or database name)
DB port: 5439
Rewrite JDBC URL: Select this option

5. Eventually, we select OK to save the SSH settings.

6. Then we make sure that the JDBC URL and superuser name and password are correct.

7. Finally, to confirm that the connection is working, we select Test.

(Optional) Modify the connection for an AWS IAM user

Generally, to connect as an IAM user, we modify the connection profile that we created in the previous step.

1. Initially, we 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.

Then we replace these values in the following example:

us-west-2: The Region that the cluster is in
012345678912: AWS account ID
clustername: Name of the cluster
group_name: Database group name
user_name: Name of the Amazon Redshift user (we can use “*” instead of specifying a specific user)
database_name: 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. After that, in SQL Workbench/J, we change the first part of the connection profile’s JDBC URL to jdbc:redshift:iam.

3. Then we select the 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

4. Eventually, on the cluster connection profile page, we select Test.

[Stuck in between? We are here to assist you]

Conclusion

In short, we saw how our Support Techs access a private Amazon Redshift Cluster.