Rails: Postgres permission denied to create database on rake db:create:all

Question

I am trying to create postgres databases for development and tests. I'm using:

• OSX Yosemite
• Rails version: 4.2.0
• git version: 2.2.2
• psql version: 9.4.0
• ruby version: 2.1.0p0
• HomeBrew version: 0.9.5

Gemfile:

gem 'pg'

database.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: myapp_development
  username: username
  password: 

test:
  <<: *default
  database: myapp_test

rake db:create:all returns

PG::InsufficientPrivilege: ERROR:  permission denied to create database
: CREATE DATABASE "myapp_development" ENCODING = 'unicode'
.... (lots of tracing)
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"myapp_development", "username"=>"username", "password"=>nil}
myapp_test already exists

What is wrong?

EDIT I just tried changing the username in the database.yml to my username that I'm using on my Mac. It worked. It also told me that not only maybe_test already exists, but it also just told me that myapp_development already exists too.

• Why wouldn't it be able to use the other username that I had created and assigned a role to CREATEDB?
• Why did it say that the development couldn't be created then tell me that it already existed?

This all seems way too confusing and reminds me of PHP setup with Apache back in the very old days. I don't want to have to deal with problems every time I create a new app and try to follow the Heroku recommendations to use PostgreSQL during development too.

Answer 1

I have faced same issues when running rake db:test:prepare in postgresql on my Ruby on Rails project. This is pretty clear from the error message, that its a permission issue for the user. I added CREATEDB permission for new_user as following from the console.

To access postgres console:

$ sudo -u postgres -i

postgres@host:~$ psql

In there:

postgres=# ALTER USER new_user CREATEDB;

It's working perfect for now. You may have another issues with database ownership, for this you can change database privileges and owner as following command.

postgres=# GRANT ALL PRIVILEGES ON  DATABASE database_name to new_user;
postgres=# ALTER DATABASE database_name owner to new_user;