Tareque's Personal Portal

Enabling pgAdmin Access to PostgreSQL on Cloud

Today I opened an account on Rackspace so that I can slowly migrate my sites from WebFaction. I have been using MySQL for my DB needs but this time decided to play with PostgreSQL. With v9.0 in the horizon with built in clustering support, Postgres might become the DB of choice on the cloud.

Anyway, I created my first server with CentOS 5.5 (hence instructions that will follow are guaranteed to work on CentOS 5.5 only, but will probably work for most other Linux distros) and installed PostgreSQL 8.4.3. Now I wanted to control this DB from pgAdmin III installed on laptop. How do I do that?

The solution was not apparent. There are few steps involved:

• Open up PostgreSQL port (default 5432) on the server
• Add rule in PostgreSQL's connection filter to allow request from remote machine
• Add server to pgAdmin and connect

Here I will describe each step in detail. All commands should be run as root or using sudo.

Open up PostgreSQL port

Incoming connections to port 5432 are rejected by default. So we have to open up this port using iptables configuration. This will not be making your system vulnerable to hackers. PostgreSQL has its own filters to control activities on its port.

We'll start by editing the configuration file:

emacs /etc/sysconfig/iptables

Then add (after all of the existing ACCEPT entries and before the final REJECT entry):

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Finally we'll restart iptables to load the new confuration.

/etc/init.d/iptables restart

Add rule in PostgreSQL's connection filter

PostgreSQL controls activities on its port (default: 5432) by using filter rules. We need to add a new rule to allow access to the DB from a remote IP.

First we need to edit the pg_hba.conf file where these rules exist. It's usually found within PostgreSQLs data directory. In my case:

emacs /opt/PostgreSQL/8.4/data/pg_hba.conf

The next step will work best if we have a static IP on the remote machine (In my case it's my laptop and it doesn't have a static IP. So I experimented with rules like 172.34.0.0/16 to allow more IPs, but it's not recommended) Let's say the current IP is 172.34.228.5. We'll add the following line at the end of the rules:

# Connection from my laptop
host    all         all         172.34.228.5/32         md5

Save and exit. Next we'll restart PostgreSQL to take new filter into account.

sudo -u postgres ./pg_ctl restart -D /opt/PostgreSQL/8.4/data

Now we can add the cloud server to pgAdmin with port and other information specified. You should have specified a password for your postgres account when installing postgres. This password will provide the final layer of security. PostgreSQL will not accept connection to the DB if you all of the above but provide incorrect password.