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

Advertisements

November 17, 2011 - Posted by | PgSQL | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: