Enable remote connection to PostgresqlPosted: July 28, 2014
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
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.