Exposing PostgreSQL on vagrant to host OS

I've been working with vagrant lately on a Ruby project and I wanted to the ability to view the PostgreSQL database on the vagrant image through DbVisualizer on my OS X system. This is quite easy to set up in PostgreSQL and vagrant.

1. Add a port forwarding entry to your Vagrantfile: config.vm.network "forwarded_port", guest: 5432, host: 15432. I'm using Vagrant 1.6.3 here. Start/restart your Vagrant image.
2. Edit the pg_hba.conf file on the vagrant image. This file may reside in different areas; ours was in /etc/postgresql/9.1/main/. Change the IPv4 local connections entry from...

   host    all             all             127.0.0.1/32            trust
   

   to

   host    all             all             0.0.0.0/0               trust
   

3. Restart PostgreSQL on the vagrant image: sudo service postgresql restart
4. Configure your database tool to connect to localhost, port 15432.

You should be able to connect to your Vagrant PostgreSQL databases now.

Connecting DbVisualizer to Heroku PostgreSQL database

Just a quick note on how to connect DbVisualizer to a Heroku PostgreSQL database. The trick is to get the SSL stuff to work, as DbVisualizer needs to be told to use a SSL factory (via the sslfactory driver property). I used the 'org.postgresql.ssl.NonValidatingFactory' SSL factory. I set this property and the ssl property (to true) in the Properties tab of the connection information. The rest of the connection information you need to get from Heroku, in particular the Heroku database configuration. Go to your Heroku dashboard, drill into your app of interest and click on the Heroku Postgres link (mine was labelled "Heroku Postgres Dev"). This will take you to the herokupostgres setting for your application. Click on the bi-directional arrows icon (Connection Settings) and you can find your connection setting there. I used the JDBC properties and filled out the Server Info settings format. This format seems to work better in DbVisualizer. Also allows you to ping the database server.