Guido Kollerie's Blog

Setting up PostgreSQL on OS X for development

Currently I’m working on my third web based project that uses PostgreSQL as its backend. Two of these projects were/are being developed under OS X. Installing PostgreSQL under OS X is a breeze when one uses MacPorts. However I have seen more than one developer being confused about the steps that should follow the installation and the post installation instructions as printed out by the PostgreSQL port.

The installation instructions can be compressed into three steps:

Install/update MacPorts
Execute: sudo port install postgresql83 postgresql83-server
Follow post-install instructions as printed out by above command

Trying to connect to PostgreSQL

Now that the PostgreSQL is installed you might be tempted to connect to it by starting the PostgreSQL interactive terminal. This is what will happen (gkoller is the user I’m currently logged in as under OS X):

gkoller@Kinchenna $ psql
psql: FATAL: database "gkoller" does not exist

So by default it looks for a database named identically to the currently logged in user. Should we want to connect to a different database we should specify the database’s name after the ‘psql’ command. E.g.:

psql hgh

I have chosen the name hgh as it is the (abbreviated) name of my latest project. The above command will fail with a similar message as the first command. So let’s create the ‘hgh’ database:

gkoller@Kinchenna $ createdb hgh
createdb: database creation failed: ERROR: role "gkoller" does not exist

Again the error message is clear. The currently logged in user does not have access to (does not have a role with the same name defined in) PostgreSQL. Simply executing a createuser gkoller will not help as we don’t have enough privileges to do that. More importantly nor does root. So a ‘sudo createuser gkoller’ does not work either. And this is what stumps most developers that try to get PostgreSQL up and running for web development on OS X

Granting privileges to the currently logged in user

When PostgreSQL was installed it was configured with one superuser, namely postgres. Hence adding new users with superuser privileges should be done as user postgres:

sudo su postgres -c 'createuser -P --superuser gkoller'

Now the currently logged in user has PostgreSQL superuser privileges. This means we don’t have to use sudo and su to executed PostgreSQL commands to create databases, roles, and other users.

Creating a database and user for project HGH

Now that I am a super user it is easy to create additional users and databases. For my HGH project I want a separate database and user. I’ll name them both hgh.

This is how:

gkoller@Kinchenna $ createuser hgh -P
Enter password for new role: <password>
Enter it again: <password>
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

gkoller@Kinchenna $ createdb -E utf8 -O hgh -W -U hgh hgh
Password: <password>

Conclusion

After PostgreSQL installation and post-installation you should create a new superuser named after your OS X login account. This allows for access to PostgreSQL commands without the need to use sudo and su.

This is achieved by executing the following command:

sudo su postgres -c 'createuser -P --superuser <your_username>'

Where <your_username> should be replaced with the username of your OS X account (short) name.

There are comments.

social