Remote Postgres (pgsql) database access

Inorder 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 61.246.232.245 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 61.246.232.245 has been added, to allow users from 61.246.232.245 host to connect to any database.

Thats all :)

If the same connection is needed with SSL, please add the following in /var/lib/pgsql/data/pg_hba.conf .

hostssl all all 189.71.10.99 255.255.255.255 trust
host all all 189.71.10.99 255.255.255.255 trust

:)

Leave a Reply