0

This is most likely a duplicated question but I can't find a post the helps me.

I have a postgres database on an EC2 Linux instance (not RDS instance). I can access the database from putty on my local machine (by SSHing to the server then connecting to the database in the same shell then writing a simple SQL) but unable from python.

Here is the python code I used:

import pandas as pd
from sqlalchemy import create_engine

sql = """
SELECT *
FROM table1
"""
engine = create_engine("postgresql://my_user:my_pwd@public_ip_number/database_name")
df = pd.read_sql_query(sql, engine)

I have defined the inbound rules on the security group of the inbound instance as enter image description here

and the outbound rule as enter image description here

Here is the error I get when trying to connect using python from my local machine

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "my_public_ip" and accepting
    TCP/IP connections on port 5432?

Edit:

I changed the file postgresql.conf according to https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

The change I made was: enter image description here

I've restarted the postgres service and now I'm getting the error when I try to connect with python:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  no pg_hba.conf entry for host "24.1X.X.X", user "username", database "database", SSL off

I have no idea what is this IP address (I've substituted real address with X in the post).

This error happens immediately where the original error was taking a few seconds to happen.

somesingsomsing
  • 3,182
  • 4
  • 29
  • 46
  • Does the error come back immediately (which suggests the request is reaching the instance but is being rejected), or does it take some time (which suggests the request is never reaching the instance)? Also, what do you mean by "access the database from putty on my local machine"? Do you mean that you connect to the instance via SSH, then run the above code on the same computer? Feel free to edit your question to add more details, rather than responding via a comment. – John Rotenstein May 21 '20 at 07:25
  • Thanks, @JohnRotenstein. I've made updates to my post as suggested but will answer your questions here as well: 1- I've SSHed to the EC2 server then connected to the DB in the same shell then wrote a simple sql. 2- it was taking a while to get the error but its immidtate now after changing postgresql.conf – somesingsomsing May 21 '20 at 18:15
  • I'm confused. Are you wanting to run the Python script on the same computer that is running the database, or do you wish to run the Python script on your own computer (on the Internet)? If it is the _same_ computer, then refer to it via `localhost`, eg: `create_engine("postgresql://my_user:my_pwd@localhost/database_name")`. If you are trying to access from the Internet, then follow the advice of the error message and edit `pg_hba.conf`. See: [How to configure PostgreSQL to accept all incoming connections](https://stackoverflow.com/a/3278835/174777) (but limit it to your IP address). – John Rotenstein May 21 '20 at 22:29

0 Answers0