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
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.:
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
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
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
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.