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
and the outbound rule as
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:
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.