Unable to connect to aws redshift from python within lambda

Question

I am trying to connect to redshift with python through lambda. The purpose is to perform queries on the redshift database.

I've tried this by getting the temp aws credentials and connecting with psycopg2, but it isn't successful without any error messages. (IE: the lambda just time out)

rs_host = "mytest-cluster.fooooooobaaarrrr.region111111.redshift.amazonaws.com"
rs_port = 5439
rs_dbname = "dev"
db_user = "barrr_user"

def lambda_handler(events, contx):
    # The cluster_creds is able to be obtained successfully. No issses here
    cluster_creds = client.get_cluster_credentials(DbUser=db_user,
                                                   DbName=rs_dbname,
                                                   ClusterIdentifier="mytest-cluster",
                                                   AutoCreate=False)

    try:
        # It is this psycopg2 connection that cant work... 
        conn = psycopg2.connect(host=rs_host,
                                port=rs_port,
                                user=cluster_creds['DbUser'],
                                password=cluster_creds['DbPassword'],
                                database=rs_dbname
    )
        return conn

    except Exception as e:
        print(e)

Also, the lambda execution role itself has these policies:

I am not sure why am I still not able to connect to redshift via python to perform queries.

I have also tried with the sqlalchemy libary but no luck there.

Could be a networking issue. Is your Redshift security group open to where the Lambda function runs from? — Jonathan Jacobson
– Jonathan Jacobson, Commented Oct 12, 2020 at 13:50
Hi Jonathan, sorry but what do you mean by that? Are you asking if redshift cluster permissions have an IAM role attached to it? — jake wong
– jake wong, Commented Oct 12, 2020 at 13:52
No, I'm asking if you've opened your cluster from the network standpoint: docs.aws.amazon.com/redshift/latest/mgmt/… — Jonathan Jacobson
– Jonathan Jacobson, Commented Oct 12, 2020 at 13:54
Yes, you are right. security groups that my VPC was attached to... I was using default, and by default, everything is blocked off. Thanks! — jake wong
– jake wong, Commented Oct 12, 2020 at 14:58

jake wong · Accepted Answer · 2020-10-12 14:58:51Z

1

As what Johnathan Jacobson mentioned above. It was the security groups and network permissions that caused my problem.

answered Oct 12, 2020 at 14:58

jake wong

5,24813 gold badges51 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eralper · Accepted Answer · 2020-10-13 19:41:26Z

You can maybe review the documentation at Create AWS Lambda Function to Connect Amazon Redshift with C-Sharp in Visual Studio

Since you have already your code in Python, you can concentrate on the networking part of the tutorial

While launching AWS Lambda functions, it is possible to select a VPC and subnet where the serverless lambda function servers will spinup

You can choose exactly the same VPC and the subnet(s) where you have created your Amazon Redshift cluster

Also, revise the IAM role you have attached to the AWS Lambda function. It requires additionally the AWSLambdaVPCAccessExecutionRole policy

This will be solving issues between connections from different VPCs Again, even you have launched the lambda function in the same VPC and subnet with Redshift cluster, it is better to check the security group of the cluster so that it accepts connections

Hope it works,

Collectives™ on Stack Overflow

Unable to connect to aws redshift from python within lambda

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related