Can't connect to postgres database from remote host using psycopg2?

Question

I am unable to connect to postgres database through remote host using psycopg2 and getting the error like

Error:

    File "/usr/lib64/python2.7/site-packages/psycopg2/__init__.py", line 126, in connect
     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
     psycopg2.OperationalError: could not connect to server: Connection timed out
    Is the server running on host "192.x.x.x" and accepting
    TCP/IP connections on port 5432?

Note: I have made following changes

1.updated the pg_hba config file host all all 0.0.0.1/32 trust

2.updated the postgresql file listen_addresses = '*'

3.changed the firewall rule to allow connection from port 5432 What else I have to do to make it work?

The error message suggest the client cannot reach the server so make sure postgres is listening on the needed interfaces: sudo ss -lntp | grep 5432. Did you restart the service after setting listen_address? — Ionut Ticus
– Ionut Ticus, Commented Mar 18, 2020 at 10:05
The range 0.0.0.1/32 contains only one host (0.0.0.1); I doubt this is the address you want to allow but this can be fixed after the TCP connection is OK. — Ionut Ticus
– Ionut Ticus, Commented Mar 18, 2020 at 10:08
"changed the firewall rule to allow connection from port 5432" You need to allow connection to 5432. — jjanes
– jjanes, Commented Mar 18, 2020 at 13:46
Make sure the listen_addresses = '*' line is not commented in the config file; are you sure you are restarting the service correctly? What's the output of ps aux | grep postgres? Use something like pastebin to paste it. — Ionut Ticus
– Ionut Ticus, Commented Mar 18, 2020 at 15:10

Ionut Ticus · Accepted Answer · 2020-03-18 17:23:38Z

For others that might encounter the same issue, here are some things you can try.

Make sure the service is listening on the needed interfaces

sudo ss -lntp | grep 5432

If you see something like 127.0.0.1:5432 or ::1:5432 this means localhost.
Tweak postgresql.conf:

listen_addresses='192.168.1.2, 127.0.0.1'

You can also use '*' which means any interface but depending on your network configuration it might be dangerous.
Make sure the listen_addresses line isn't commented (as was the case above) and restart the service after making changes.

Check your firewall configuration
After making sure the service is listening on the correct interface make sure your firewall(s), if any, permit the client to connect to the service (this can mean your local firewall, a network device sitting between the client and the service).

Check pg_hba.conf
This file controls PostgreSQL's host-based authentication mechanism:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.3/32          md5

The line above allows all users to connect from 192.168.1.3 if they provide the required password; if you want to allow a whole subnet you can use something like 192.168.1.0/24.

If you suspect an issue with psycopg2 or Python you can test the connectivity using PostgreSQL's client: psql:
psql -U postgres -h 192.168.1.2 db_name

Collectives™ on Stack Overflow

Can't connect to postgres database from remote host using psycopg2?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related