0

I am trying to connect to a Postgres server using Python.

import psycopg2


conn = psycopg2.connect(
    host="ec2-blablabla.compute-1.amazonaws.com",
    database="postgres",
    user="myuser",
    password="mypassword",
    port = 5432,
    sslmode ='prefer',
    sslcompression = 0
)
cur = conn.cursor()

I get the following error:

OperationalError: SSL SYSCALL error: Connection reset by peer FATAL: no pg_hba.conf entry for host "207.100.100.100", user "myuser", database "postgres", SSL off

I am able to connect to this server from my IP using pgadmin and same ssl settings as I set in Python code. What could be the issue here?

no pg_hba.conf entry for host I cannot modify pg_hba.conf so if there is any other way, please let me know!!

hsnsd
  • 1,728
  • 12
  • 30
  • 1
    Look at the PostgreSQL log for more information. – Laurenz Albe Jun 11 '21 at 03:16
  • What version of pgadmin? What is the ssl mode setting on that? Are you connecting with all the same parameters (username, dbname, port, host) as from your python code? From the same host? – jjanes Jun 11 '21 at 13:24
  • @jjanes pgAdmin 4 Version 5.2 (4280.88) SSL mode is set to Prefer SSL Compression to No Everything else is blank – hsnsd Jun 11 '21 at 13:53
  • In PgAdmin, change SSL mode to "require" and see if that starts throwing errors, and if so what the error is. But it seems like there must be some overlooked setting differences between your clients. Your aren't using ssh tunneling, are you? – jjanes Jun 11 '21 at 14:51
  • @jjanes No error thrown. I can connect with SSL mode set to either of Prefer, Allow, Require on pgadmin. I am not using ssh tunneling. What other setting differences could be there? :( – hsnsd Jun 11 '21 at 14:58
  • I don't know, I would guess it is just a simple typo or something. Try to get the server's log file, as "connection resets" are often experienced differently from the client perspective vs the server perspective. Check pg_stat_activity for the pgAdmin4 connection to see what client_addr the server thinks it is connecting from, and see if that agrees with "207.100.100.100". – jjanes Jun 11 '21 at 15:43
  • There are two different problems, why is pg_hba matching one "SSL off" but not the other, and why is one "SSL on" working and not the other. The only commonality I can see if the connections are not really coming from the same machine. – jjanes Jun 11 '21 at 15:46

0 Answers0