Cannot connect remotely to postgresql database hosted on amazon ec2 instance

1

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: Image What am I doing wrong? I am a complete newbie, any help appreciated!

python
postgresql
amazon-web-services
amazon-ec2
asked on Stack Overflow Jun 25, 2020 by MadMan47

2 Answers

2

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.

answered on Stack Overflow Jun 25, 2020 by Mike Organek • edited Jun 25, 2020 by Mike Organek
1

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.

answered on Stack Overflow Jun 25, 2020 by Curt Evans

User contributions licensed under CC BY-SA 3.0