Postgresql – SQLAlchethe connection error with Postgres

postgresqlpython

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name, it throws the following error:

OperationalError: (psycopg2.OperationalError)
FATAL: no pg_hba.conf entry for host "fe80::19e5:a9a0:bcc2:1c5b%11", user "postgres", database "postgres", SSL off

I checked pg_hba.conf, and here's what I have:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust

host    all             all             0.0.0.0/0               md5

I am able to connect to this Postgres database using other tools, like PgAdmin from a different machine, Tableau, Talend, etc.

UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.

Best Answer

Disclaimer - I'm not a huge sys admin buff, so my information may not be correct.. I also realize that the error says "host" - but I don't think that means it has to be a host entry in pg_hba.conf

When you connect to localhost, it uses the loopback address 127.0.0.1 (unless that configuration on your computer has been tampered with) to connect to your system, which counts as a "HOST" connection..

When you connect to "myComputer" (which happens to be your computer name), I'm pretty sure your OS is smart enough to say "oh hey! that's me!" and doesn't even bother resolving an ip address .. it just does a direct connection. This would be a "LOCAL" connection for postgres..

Try adding local all all ::1/128 trust to your pg_hba.conf file..

Then either restart the server, or connect as a superuser and issue select pg_reload_conf() (preferred method).

UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.

That still works because it's still using a HOST connection (instead of a LOCAL one). Try what I suggest and see if you still get an error when connecting to the computer hostname.

Related Question