SQLAlchemy connection error with Postgres

Question

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.

Answer 1

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.

Answer 2

It seems this might be an issue with Postgres and local connections - it affects connections using both psql and psycopg2.

It apparently does not like when it is passed the socket which does not exist in pg_hba.conf;

A suggested work around is to add another line for the link-local address prefix in your pg_hba.conf:

host all all fe80::/64 trust

Might need to restart service after modifying pg_hba.conf.

Aside: I know that SQLAlchemy had trouble with IPV6 addresses in the past, but this was fixed in the 0.8.7 release - Release Notes