Enable remote connection to Postgresql


Well, I am starting to build our new Pentaho 5.1 infrastructure, and the repository now sits in Postgresql. It can be stored in mysql, but I am keeping with their standard. I want to the repository to sit on the same server as the data warehouse. Keeping all the databases together, kind of makes sense. Well it does to me 🙂

 

So installing Postgresql 9.3 is pretty simple, download and install, how hard can that be, not that hard at all. OK now to connect to it. This is where it can get a bit tricky.

 

There are two files that you need to amend and these are pg_hba.conf and postgresql.conf.

 

The first file allows remote connections to be established, but here is the kicker if you do not set postgresql to listen other than on the localhost, it will not work.  Arrrggghhhh !!!!. By the way also ensure you firewall is off to start with, until you have confirmed the connection works.

 

Edit the pg_hba.conf file and add the following line at the end

host          all          all            0.0.0.0/0               md5

 

Edit the postgresql.conf and find the following section “Connections and Authentication”

Add the following

listen_address = '*'
port = 5432

Restart postgresql

To ensure that the database is listening on the correct port and address

netstat -tnlp | grep 5432

 

You should get an output something similar to this

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 10793/postmaster 
tcp 0 0 :::5432 :::* LISTEN 10793/postmaster

You should now be able to connect. Once you have proven a connection, it is time to close the security down.

Amend the pg_hba.conf file and change the ip address of the host that is allowed to connect to the database. You should have something like this

host   DB_NAme        DB_USername    192.168.0.10/0         md5

 

You should then re-enable your firewall and only allow 5432 from the host also, doubling security is never a bad idea.

 

Good luck, if you have similar issues.

 

Al

 

 

 

Advertisements


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