UnixServerAdmin

Server Administration & Management

How to connect Remote PostgreSQL Database

For accessing the pgsql database remotely,  make the following changes in the server.

1) Open the Port 5432 in the server.

# iptables -A INPUT -p tcp –dport 5432 -j ACCEPT

# service iptables restart

If you are using csf firewall, open the port 5432 in /etc/csf/csf.conf and restart csf firewall.

2) Edit the Pgsql configuration file

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

Make sure that the following entries are there:-

tcpip_socket = true
port = 5432
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.11.2 and 202.54.11.3 IP address
listen_addresses=’202.54.11.2 202.54.11.3′

3) Edit PostgreSQL HOST ACCESS CONTROL FILE (pg_hba.conf)

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

Add the machine IP address from which database is accessing remotely.

local all trust
host all 127.0.0.1 255.255.255.255 trust
host all X.X.X.X 255.255.255.255 trust

Here X.X.X.X  is the machine IP address from which database is accessing remotely.

6. For the connection with ssl add the machine IP in the hostSSL section in the /var/lib/pgsql/data/pg_hba.conf

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

Entries will be like this:-

hostssl all all 99.266.122 235 255.255.255.255 trust
host all all 99.266.122.235 255.255.255 trust

November 17, 2011 Posted by | PgSQL | , | Leave a comment