Using PostgreSQL as your database for Ruby on Rails

The following blogpost does an outstanding job of explaining how to set up PostgreSQL for Ruby on Rails development on OS X (for Rails 3).

https://willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/

However, there is one part I want to draw attention to:

$ createuser shawsome
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

He says that it's okay to specify "No" for each of these, but you'll face a testing problem later:

$ bundle exec rake db:test:prepare
PGError: ERROR: permission denied to create database

Instead of replying "n" to "Shall the new role be allowed to create databases?" you should have replied "y" (yes) because using Rails' testing frameworks inherently requires that the test db is destroyed and regenerated frequently, as is shown by the existence of the rake command "rake db:test:prepare".

So what can you do now to fix the role? Instead of retracing your steps and making a new user with proper permissions, just change it via postgres:

1) Connect to the standard "postgres" db of your postgres installation (replace "your_username" with your username):

psql -U your_username postgres

If you're not sure what your_username is, run the following; it's the name under "Owner" across from "postgres":

psql -l

Note that not specifying your username might let you connect, too:

psql postgres

2) Run the postgres command:

ALTER ROLE that_username CREATEDB

...where that_username is the username of your Rails application.

That should do it. If interested, read on about useful postgres commands.


Here are some typical postgres commands:

# info on the homebrew install
brew info postgres

# start server manually; I don't do this because my server starts automatically
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

# stop server manually; I don't do this because my server starts automatically
pg_ctl -D /usr/local/var/postgres stop -s -m fast

# view process list - kind of neat
ps auxwww | grep postgres

# create user myuser
# NOTE: for a Rails app called keybook, it's good to make the user keybook
createuser myuser

# create db (-O specifies owner; -E specifies encoding)
# This is good convention for a Rails app called keybook:
# development db name = keybook_development; test db name = keybook_test
createdb -O myuser -E utf8 keybook_development

# connect to the db (activates cmd interface)
psql -U myuser keybook_development
psql keybook_development # this may also work
  # after connecting, you can run these:
  \? # help
  \dt # list db tables
  \d posts # look at posts table in more detail
  
  # selects the specified columns for the specified table
  select id, title, author, created_at from posts;

  \l # list all databases
  select rolname from pg_roles; # see list of all roles

# list all databases from the command line - USEFUL to see what dbs you already have
psql -l

# drop a database
dropdb myapp_test 

Other Links:
http://www.postgresql.org/docs/9.0/static/sql-alterrole.html - the postgres ALTER command
http://mrfrosti.com/2011/06/setup-postgresql-with-rails-on-linux/ - a different but similar blogpost