0

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.

  • Was that % in the host 'name' real? – dezso Dec 6 '16 at 10:36
  • I no longer have access to that server, but I believe that it was. – J. Woods Dec 7 '16 at 18:28
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.

  • The plan is to port the application to a different box that reaches out to this db, so it won't always be looking local. I can connect via a separate machine using the IPv4. My only problem now seems to be that cx_freeze is omitting psycopg2 when I compile. – J. Woods Dec 11 '15 at 16:58
  • That's good - and at that time, obviously the "LOCAL" line won't be needed (unless there were ever a need to administrate the db directly from the db server). I was just thinking of your CURRENT dev environment.. Either way, though.. – Joishi Bodio Dec 11 '15 at 17:01
0

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

  • host all all fe80::/64 trust - He already has this line .. it's the ::1/128 line – Joishi Bodio Dec 10 '15 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.