24

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!

piro
  • 13,378
  • 5
  • 34
  • 38
Steve Gattuso
  • 7,644
  • 10
  • 44
  • 59

2 Answers2

20

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Embarrassingly enough for me, you're absolutely correct! For anyone who may be in a similar situation: the problem originated from upgrading from 8.4 -> 9.1. It turns out that the new server configures itself to run on 5433, awaiting a transfer of data from the old db. Check `/etc/postgre/9.1/main/postgre.conf` for the port. – Steve Gattuso May 22 '12 at 01:10
  • 1
    @SteveGattuso: Happens to all of us. No need to be embarrassed. :) BTW, full path of the config file typically is `/etc/postgresql/9.1/main/postgresql.conf`. – Erwin Brandstetter May 22 '12 at 01:47
  • 1
    Thanks for your checklist. Indeed, I had to reload. I did so using `sudo systemctl reload postgresql` on a debian 10 running postgresql 11 – Rabarberski May 15 '21 at 11:34
  • I am experiencing this but am not super user (and never was - I just pip installed psycopg2 and it used to work for an older database version)... Any way to do that without root access? – ntg Jun 12 '22 at 11:45
3

I recently got into this same issue and I found the solution for this problem.

System:

  1. I have an application server (with these packages installed python, django, psycopg2 and postgres client 9.6.1 (postgresql-9.6.1.tar.gz)), for instance ip address 10.0.0.1(a private address).
  2. And AWS postgres RDS server "aws_rds_host_name" or any database IP address.

Error:

django.db.utils.OperationalError: FATAL: no pg_hba.conf entry for host "10.0.0.1", user "your_user", database "your_db", SSL off

Solution:

While installing the postgres client 9.6.1 source package in application server 10.0.0.1, we have to pass an argument "--with-openssl". I suggest to remove the existing the postgres client and install with below steps.

  1. Download the postgres client source package 9.6.1 (postgresql-9.6.1.tar.gz)
  2. Untar the package postgresql-9.6.1.tar.gz.
  3. ./configure --prefix="your_preferred_postgres_path_if_needed" --with-openssl (this '--with-openssl' argument is important to get rid of that error)
  4. make
  5. make install
  6. After successful installation, that error didn't occur when we ran the django project with psycopg2.

I hope this solution helps someone.

thewaywewere
  • 8,128
  • 11
  • 41
  • 46
Sathish
  • 51
  • 2
  • Very similar logic applies for the users of psycopg2 and any library which uses this driver under the hood (like SQLAlchemy). In order to get rid of this error you have to compile Psycopg2 with SSL support on local machine, otherwise it won't work! – Rocckk Jan 10 '22 at 12:20