Friday, June 13, 2014

How to enable your Postgres database accessible by PgAdmin client from other machine remotely?

It is really inconvenience when I found out that I can't connect to my Postgres database (sit in Linux server) remotely through PgAdmin client tools to conduct my troubleshooting works. Even though from security perspective, should turn off the remote access. But, if my PgAdmin client is sitting within the same private network (behind the firewall), I think it is no harm to enable it. The reality is PgAdmin client on Window  is much more user friendly then I use command line tool on Linux.

Step 1 - Edit the Postgres database configuration file to enable remote machine to connect

vi /var/lib/pgsql/data/pg_hba.conf
Add the line as described below:-

host    all         all         [REMOTE MACHINE IP ADDRESS]/24       trust
Step 2 - Enable Postgres database to listen to all the IP address

vi /var/lib/pgsql/data/postgresql.conf
Edit the setting as described below

listen_addresses = '*'
Step 3 - Restart your postgresql database

service postgresql restart
Note: iptables on my Linux box has been turned off. You might want to edit the iptables setting (/etc/sysconfig/iptables) to enable port 5432 for your Linux server

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d [REMOTE MACHINE IP ADDRESS]  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s [REMOTE MACHINE IP ADDRESS] --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Note: Just in case "/etc/sysconfig/iptables" doesn't exists, maybe due to the first configuration, please type in command "service iptables save". This will save the rules at "/etc/sysconfig/iptables".

No comments: