Psycopg2 reporting pg_hba.conf error

Question

I've run into a weird situation while trying to use PostgreSQL and Psycopg2. For some reason, every time I attempt to connect to the postgre database via python, I get the following error:

psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "steve", database "steve", SSL on
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "steve", database "steve", SSL off

Naturally, I checked pg_hba.conf to see what the issue was, but everything appeared to be configured correctly as far as I can see:

pg_hba.conf:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

In addition, I've found that I can connect to the database via psql as I would expect:

$ psql -U steve -h 127.0.0.1
...
steve=>

Anyone have any ideas as to what could be going on here? Thanks in advance!

Answer 1

Typical explanations include:

1. You are connecting to the wrong server.
   Is the DB server running on the same host as Python does?

2. You got the wrong port.
   Check the server log if you see a connection attempt. You have to log connections for that, of course. See the config parameter log_connections.

3. You did not reload (SIGHUP) the server after changing pg_hba.conf - or reloaded the wrong cluster (if you have multiple DB clusters).

   Use pg_ctl or pg_ctlcluser on Debian and derivatives for that.

   Or, on modern Linux installations with systemd (incl. Debian & friends), typically:

    sudo systemctl reload postgresql
   

   Or, if there are multiple installations, check with:

    sudo systemctl status postgres*
   

   And then realod the one you want with something like:

    sudo systemctl reload postgresql@14-main