CODEWITHSUNDEEP
postgresqlpsycopg2
Anki2244
Anki2244

Reputation: 35

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

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?

Upvotes: 1

Views: 3153

Answers (1)

Ionut Ticus
Ionut Ticus

Reputation: 2789

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

Upvotes: 2

Related Questions