Archive for the tag 'pgsql database'

SBDavid
Jun 29th, 2009

Remote access to pgsql database

Remote access to pgsql database.

In order to access the pgsql database remotely, you need to make 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

2) Edit the Pgsql configuration file (postgresql.conf).

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

Make the following changes

tcpip_socket = true
port = 5432

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

This file controls what hosts are allowed to connect to what databases and specifies some options on how users on a particular host are identified. It is read each time a host tries to make a connection to a database.

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

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

# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
AUTH_ARGUMENT

local all trust
host all 127.0.0.1 255.255.255.255 trust
host all 192.168.0.1 255.255.255.255 trust

Second line allows any user on the local system to connect to any # database under any username.

Here the IP address 192.168.0.1 has been added, to allow users from 192.168.0.1 host to connect to any database.