Enabling Postgres Remote Access

Enabling remote access for the first time on the Postgres SQL server requires editing two configuration files. We will assume you are using the 8.1.x distribution of Postgres as the 8.2.x series uses configuration files in a different location.

The first file to edit is /var/lib/pgsql/data/pg_hba.conf. The bottom of the file will look something like the following:

# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
# IPv4 local connections:
# IPv6 local connections:

local all all        md5
host all all  127.0.0.1   255.255.255.255   md5

The idea is to use the syntax given to add your own remote IP (or the IP from which you will be connecting to the server) to the bottom of the file. If you are connecting from 1.2.3.4, add a line that reads:

host all all 1.2.3.4/32 trust

You can also change the trust to md5 if you only want that host to be able to log in using an MD5 encrypted password (trust allows them to connect unconditionally).

The next step is to enable networking for Postgres so that it listens to remote requests as well as local ones. Edit the file /var/lib/pgsql/data/postgresql.conf and look for the following line:

#listen_addresses='localhost'

You can change this line to one of the two following options, ensuring to remove the # from the beginning of the line if there is one. The first allows Postgres to listen on all local IPs where as the second specifies an explicit IP for Postgres to listen on (change this to your IP!):

listen_addresses='*'

listen_addresses='204.10.37.58'

That's it for configuration; ensure your firewall allows connections in on port 5432 and then simply restart Postgres:

/etc/init.d/postgres restart

Now test your connection!