AWS - connect to PostgreSQL from Python not working

Question

Sorry to bother you, but I have been struggling to use Python to connect to the AWS PostgreSQL database based on this instruction https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Python.html, as it always displays timeout error.

I have also set up a config file in ~/.aws folder to configure the boto3, with the following:

[default] 
aws_access_key_id = X
aws_secret_access_key = X


import os
import boto3
import psycopg2

ENDPOINT="url"
PORT="5432"
USR="kaggle"
REGION="us-east-2a"
os.environ['LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN'] = '1'

session = boto3.Session(profile_name='default')
client = boto3.client('rds',region_name=REGION)

token = client.generate_db_auth_token(DBHostname=ENDPOINT, Port=PORT, DBUsername=USR, Region=REGION)

try:
    conn = psycopg2.connect(host=ENDPOINT, port=PORT, user=USR, password=token)
    cur = conn.cursor()
    cur.execute("""SELECT now()""")
    query_results = cur.fetchall()
    print(query_results)
except Exception as e:
    print("Database connection failed due to {}".format(e))

The error is:


Database connection failed due to could not connect to server: Connection timed out (0x0000274C/10060)
    Is the server running on host "url" (IP) and accepting
    TCP/IP connections on port 5432?

NEVER reveal your credentials anywhere! Please immediately disable those credentials in your IAM console! — John Rotenstein
– John Rotenstein, Commented Jul 31, 2020 at 23:49
Where is this code running (EC2, your own computer)? Is the Amazon RDS database configured as Publicly Accessible = Yes? Have you been able to connect to the database in any other way (eg using an SQL client)? — John Rotenstein
– John Rotenstein, Commented Jul 31, 2020 at 23:59
I am running this code on my own computer. I have configured it as Publicly Accessible. I tried to connect to the database using this SQL server, but it also shows connection failed. (aws.amazon.com/getting-started/hands-on/…) — Yuheng Wang
– Yuheng Wang, Commented Aug 1, 2020 at 0:08
do you have telnet installed. if yes try telnet endpointUrl 5432 . Also in the RDS's security group, make sure that your public ip address is allowed in the In Bound rules — Arun Kamalanathan
– Arun Kamalanathan, Commented Aug 1, 2020 at 0:18

John Rotenstein · Accepted Answer · 2020-08-01 04:31:41Z

1

It appears your scenario is:

An Amazon RDS database in a VPC with Publicly Accessible = Yes
Your own computer on the Internet (outside of AWS)
You want to connect to Amazon RDS from your computer

Things to check:

Amazon RDS has been launched in a public subnet (defined as a subnet with a Route Table entry that points to an Internet Gateway)
You are using the DNS Name of the RDS database to connect (as provided in the RDS console)
A Security Group on the RDS database that permits inbound access on port 5432 to your computer's public IP address, or to 0.0.0.0/0 (but that is bad from a security perspective)

answered Aug 1, 2020 at 4:31

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

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

Collectives™ on Stack Overflow

AWS - connect to PostgreSQL from Python not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related