By default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:

$ ssh user@remote.pgsql.server.com

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:

$ su - postgres

Edit the file:

$ vi /var/lib/pgsql/data/pg_hba.conf

OR

$ vi /etc/postgresql/8.2/main/pg_hba.conf

Append the following configuration lines to give access to 10.10.29.0/24 network:

host all all 10.10.29.0/24 trust

Save and close the file. Make sure you replace 10.10.29.0/24 with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.

# vi /etc/postgresql/8.2/main/postgresql.conf

OR

# vi /var/lib/pgsql/data/postgresql.conf

Find configuration line that read as follows:

listen_addresses='localhost'

Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to ‘localhost’, and ‘*’ is all ip address:

listen_addresses='*'

Or just bind to 202.54.1.2 and 202.54.1.3 IP address

listen_addresses='202.54.1.2 202.54.1.3'

Save and close the file. Skip to step # 4.

Step #3a – Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:

# vi /var/lib/pgsql/data/postgresql.conf

Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:

tcpip_socket = true

Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:

# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 20.04.23.22  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 20.04.23.22 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:

# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address 20.04.23.22 and login using postgres username and testdatabase database, enter:

$ psql -h 20.04.23.22 -U postgres -d testdatabase