Need help?

Our experts have had an average response time of 12.14 minutes in September 2021 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

Access a Private Amazon Redshift Cluster from Local Machine

by | Aug 8, 2021

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" [email protected]_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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF