PostgreSQL DB under Mac OS X Lion

Background

Currently I am learning Ruby on Rails an I am following the excellent tutorial by Michael Hartl "Ruby on Rails Tutorial - Learn Web Development with Rails", where you learn how to build a full featured web miniblog app.
The application of the tutorial is based on a database, where all the data required for the app is stored in.  In the exercises of chapter 3.5 of the tutorial, the student is asked to move his/her development environment to a PostgreSQL database environment as this is a better choice than the previously used Sqlite3 db. I performed this exercise and want to report on the experiences I made. From reading a couple of blogs and forums, my impression was that there is not too much information available about the specifics of a Mac OS X system environment - I am working on OS X 10.6 (Lion).

Installing the DB on the System

As I am not a hacker, but rather like the straight forward path, I simply downloaded the postgresql Mac OS X app, which is available on http://postgresapp.com/ in its latest version. The downloaded ZIP file only contains one file "postgres.app". You can copy this file to your "Applications" folder to have it in a convenient place. The database is started simply by double-clicking on it.
So far so simple. What is kind of confusing is the fact that the postgres.app starts without any configuration dialog or alike. It simply starts to the background and that's it. At least I asked myself at this point: "and what about user and connection settings???". Well as I didn't find any configuration file I first tried to continue my RoR tutorial.

Using the Postgres DB in Ruby on Rails

The first challenge - well not a big one - was to identify the right connection parameters to be used in the database.yml file. Google brings up a plentitude of hits to the query "postgresql database.yml rails". I picked "Postgres - how to connect?" which provides:

production:
    adapter: postgresql
    database: foo_production
    username: postgres
    password: secret
    host: localhost
    encoding: UTF8

What confused me was the use of username and password.  As mentioned before, I didn't have any option to choose a user or password, so what to enter here? I played around a bit and in the end it worked by simply skipping these two parameters together with the encoding parameter. The following configuration worked in my development environment:

production:
    adapter: postgresql
    database: sample_db
    host: localhost

Database GUI Application

So with these settings I was able to continue the tutorial without problems. However, in chapter 6 the first user data is to be created in the DB and here I started to look for a database GUI application. There are a couple of choices, and I picked pgadmin, which seemed to be the standard. Pgadmin is available for download at pgadmin.org, for Mac OS X the is a file available here. The installation of pgamin is simple as for every Mac programm, simple put the pgadmin.app into the Application directory and that's it.
The usage of the program however, I did not find so simple. Pgadmin starts with a screen where only a "server group" is available without any content:

To add a connection to a database / database server one can do this via the menu File --> Add Server:

The window that opens to enter the connection data looks like this:

Here I got stuck:

Name: freetext, description of your configuration 

Host: localhost 

Port: in a couple of forums it seemed that 5432 is OK 

Service: ???? no idea 

Maintenance DB: "sample_db" assumed 

User Name: ???? no idea 

Password: ???? no idea, but should be empty 

I tried "sample_db" as value for the service and "postgres" as User Name. In the end everything I tried ended in an error messge:

My Solution

A google search did not yield any really useful results. I found one article which talks about "Securely Manage Remote PostgreSQL Servers with pgAdmin on Mac OS X". I used the information about the SSH tunnel, followed the instructions there and created the PERL script file postgresql-tunnel.pl with the corresponding information therein but in the end this did not provide any solution.
A bit desperate I tried to connect to my DB via the command line by using the console command

$ psql -h localhost

which gives console access to the local Postgresql database server. What finally gave me the hint solving the problem was the look to the available databases:

# \l

which yielded the following result:

Very promising seemed to try the database owner "Wolfi" (my user on the Mac) together with a blank password:

Name: freetext, description of your configuration 

Host: localhost 

Port: 5432

Service: <leave blank> 

Maintenance DB: sample_db 

User Name: Wolfi 

Password: <leave blank>

Finally this worked. I was able to connect to my DB and able to administer it:

Here a screenshot showing my development db and the table I created in the tutorial. 

That was it - I hope you find this blog kind of helpful...