0
341
VisualizaçõesCannot connect remotely to postgresql database hosted on amazon ec2 instance
I have a postgresql database hosted on my ec2 instance. I am trying to connect to it using Python from my local computer.
import psycopg2
try:
connection = psycopg2.connect(user="postgres",
password="<password>",
host="ec2-***-***-***-***.***-***-1.compute.amazonaws.com",
port="5432",
database="<db_name>")
cursor = connection.cursor()
cursor.execute(f"SELECT * FROM <tablename>;")
record = cursor.fetchall()
print(record, "\n")
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
try:
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
except:
print("no work")
But I get
Error while connecting to PostgreSQL could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "ec2-***-***-***-***.***-***-1.compute.amazonaws.com" (**.**.**.***) and accepting
TCP/IP connections on port 5432?
no work
My pg_hba.conf file looks like this
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
and my postgresql.conf file looks like
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
I have an ec2 Security Group: 
What am I doing wrong? I am a complete newbie, any help appreciated!
2 Respostas
Responde à pergunta0
Connection timeout usually indicates that you cannot reach the host or are being blocked by a firewall.
First try to ping the host from your machine. If it pings all right, then there is probably a firewall between you and your EC2 instance. Your EC2 security screenshot looks right to me.
Are you behind a firewall that might be blocking outbound sessions from your local computer to the Internet?
After some troubleshooting over chat, we found that the AWS Security Group allowing TCP/5432 inbound was not assigned to the EC2 instance.
0
There are many possible causes, so I would start with a trusted DB client like DBeaver and attempt to make the connection from your local machine to rule out python issues.
Depending on your setup, you may have a second incoming firewall (iptables, etc) running inside your ec2 instance that needs to be configured or disabled.
Log into the ec2 console and see if you can connect to the server. Load a db client like pgsql inside ec2 and attempt to connect to the server with localhost:5432 as the target.
You may need to alter pg_hba.conf so that the server will generate log files, but that can tell you if the server is being reached, and what the problem is.