2

I have a Redshift Cluster in my AWS account. I am able to connect to it in python and when I run the script locally, it runs perfectly fine:

import psycopg2
con = psycopg2.connect(dbname='some_dbname', host='hostname.us-east-2.redshift.amazonaws.com', port='port#', user='username', password='password')
    cursor=con.cursor()
    query = "select * from table;"
    cursor.execute(query)
    data = np.array(cursor.fetchall())
cursor.close()
con.commit()
con.close()

But, when I copy the above script to my EC2 instance (Amazon Linux AMI), and then try running it, I get the following error:

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection timed out
    Is the server running on host "hostname.us-east-2.redshift.amazonaws.com" and accepting
    TCP/IP connections on port port#?

Can anybody help me out in how to connect to my Redshift cluster from my EC2 instance? Thanks!

Rajiv
  • 135
  • 2
  • 9
  • 1
    Did you open the port in the security group assigned to the Redshift cluster to allow connections from the EC2 instance? – Mark B Sep 11 '17 at 18:53
  • @MarkB No how do I do that? – Rajiv Sep 11 '17 at 18:56
  • How did you open the security group to allow access from your local computer? It's the same process. – Mark B Sep 11 '17 at 18:58
  • Both the cluster and the EC2 instance are under the same VPC ID. But still it is not connecting and is timing out. – Rajiv Sep 11 '17 at 19:24
  • What is a VPC ID? Do you mean Security Group? Just because they are both in the same VPC and in the same Security Group doesn't mean they can automatically connect. You still have to create a rule in the Security Group that opens the port. – Mark B Sep 11 '17 at 19:26
  • So, I went on the security group for the cluster, under the inbound tab, clicked on edit, now in the Port Range and IP address, should those be the ones that of the EC2 instance? – Rajiv Sep 11 '17 at 19:30
  • Port range should be the Redshift port, IP address should be either the private IP of the EC2 instance, or the security group ID that the EC2 instance belongs to. – Mark B Sep 11 '17 at 19:39
  • Do I need to go to the Security group of the EC2 instance as well and do the same? Add port range of Ec2 port, security group ID of the Redshift? – Rajiv Sep 11 '17 at 19:54
  • 1
    No, just the Redshift security group. – Mark B Sep 11 '17 at 20:04
  • Okay, still getting the same error :( – Rajiv Sep 11 '17 at 20:25
  • Oh it worked! Thanks very much! – Rajiv Sep 11 '17 at 20:52

2 Answers2

0

It's either networking or security groups

When you provision an Amazon Redshift cluster, it is locked down by default so nobody has access to it. To grant other users inbound access to an Amazon Redshift cluster, you associate the cluster with a security group.

See http://docs.aws.amazon.com/redshift/latest/mgmt/working-with-security-groups.html

If the EC2 is in the same VPC as the Redshift cluster you should be ok for networking. If not, look at this guide http://docs.aws.amazon.com/redshift/latest/mgmt/enhanced-vpc-routing.html

Vorsprung
  • 32,923
  • 5
  • 39
  • 63
  • So when I go to my EC2 instance, and under the Description tab, I click on Security Groups, I see that its VPC ID is the same as the one for the Redshift cluster, both of their Group ID is different though. But still I'm not able to connect and it's timing out. Sorry, very new to this. – Rajiv Sep 11 '17 at 19:24
0

The issue is related to Security Group(s) attached to the Redshift Cluster. I have faced this issue myself, So I would like you to follow these steps -

VPC & Region Checking

Check -

  1. VPC-if your EC2 is in the Same VPC as that of the Redshift cluster

  2. Account Region - Ensure the Regions are same too.

if the above to criterias are true, then -

  • Check if your EC2's Private IP is whitelisted in the security group attached to the redshift cluster. ( I usually dont create instances with public IP's instead I login via bastion host for better security and ease as I have to handle a lot of EC2's)

  • You may refer to this AWS Documentation to Do the above :- Authorize Access To Cluster

  • After this, verify your work by logging into your ec2 and run -

telnet < redshift_cluster_endpoint > < redshift_cluster_port >

In Addition to the above, You are able to connect to Redshift while running the code locally from your machine because your public IP(Home/Work-Place) must have been whitelisted in Redshift's attached security groups. So consider EC2 as a similar machine on cloud which needs Whitelisting(IP).

Golokesh Patra
  • 1
  • 2