Unable to connect PostgreSQL to remote database using pgAdmin

Question

I installed PostgreSQL on my local server (Ubuntu) with IP 192.168.1.10. Now, I'm trying to access the database from my client machine (Ubuntu) with IP 192.168.1.11 with pgAdmin.

I know I have to make changes in postgresql.conf and pg_hba.conf to allow the client to connect. Could you please guide me?

Answer 1

It is actually a 3 step process to connect to a PostgreSQL server remotely through pgAdmin3.

Note: I use Ubuntu 11.04 and PostgreSQL 8.4.

1. You have to make PostgreSQL listening for remote incoming TCP connections because the default settings allow to listen only for connections on the loopback interface. To be able to reach the server remotely you have to add the following line into the file /etc/postgresql/8.4/main/postgresql.conf:

   listen_addresses = '*'

2. PostgreSQL by default refuses all connections it receives from any remote address, you have to relax these rules by adding this line to /etc/postgresql/8.4/main/pg_hba.conf:

   host all all 0.0.0.0/0 md5

   This is an access control rule that let anybody login in from any address if he can provide a valid password (the md5 keyword). You can use needed network/mask instead of 0.0.0.0/0 .

3. When you have applied these modifications to your configuration files you need to restart PostgreSQL server. Now it is possible to login to your server remotely, using the username and password.

Answer 2

If you're using PostgreSQL 8 or above, you may need to modify the listen_addresses setting in /etc/postgresql/8.4/main/postgresql.conf.

Try adding the line:

listen_addresses = *

which will tell PostgreSQL to listen for connections on all network interfaces.

If not explicitly set, this setting defaults to localhost which means it will only accept connections from the same machine.