Postgresql server remote connection

Question

I am trying to set up a postgresql 9.1 server on ubuntu, for remote access of data. I have postgres properly installed, the server process is running and I am trying to configure it so that I can access the server remotely over the internet from a few other computers outside my LAN. I have already modified my pg_hba.conf with: ``` host all all 0.0.0.0 trust ``` and the postgresql.conf with: ``` listen_addresses = '*' port = 5432 ``` I have additionally modified my iptables to accept connections on port 5432. When I try to connect using psycopg2 on python: ``` conn=psycopg2.connect('host=XX.XX.XX.XX port=5432 dbname=postgres user=myUser password=mypassword') ``` I receive the following error: ``` Traceback (most recent call last): File "", line 1, in File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect connection_factory=connection_factory, async=async) psycopg2.OperationalError: could not connect to server: Connection refused Is the…

Accepted Answer

Your `pg_hba.conf` should NOT use `trust`!!! `trust` means no password is required and I don't think that's what you want.

This is the correct configuration

```
host    all             all             0.0.0.0/0               md5
```

Notice the `/0` behind `0.0.0.0`.

The full `pg_hba.conf` should be this:-

```
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
```

Notice that `trust` is only applicable for `local` connections. i.e. for applications running on localhost IP `127.0.0.1` on the machine which also runs your postgresql server.

Postgresql server remote connection

Solution

Problem