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.

Glorify

rake db:drop:all then rake db:create:all – Loney

Follow this answer to give your used CREATEDB role. And your problem will be resolved. And do rake db:drop and rake db:create. – Keelykeen

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;

Pious

Thanks you so much. – Shelah

We don't have create database privileges in our environment. – Cider

Thanks a lot for this one – Delvalle

Sometimes your user may not have sufficient privileges for certain operations. If giving it all the privileges isn't a problem, also run ALTER ROLE new_user SUPERUSER; – Slope

Answer 2

Looking at your schema your credentials for development and test are different.

Perhaps remove username and password from the schema, seeing that your test database did get created.

Gesture

Rails created that default variable which is passing everything to the next unless explicitly overwritten. So the username and the password get passed to the myapp_test database also. – Glorify

Answer 3

create database demo;
create user demotest with password '123';
grant all privileges on database demo to demotest;
commit;

      This is script for creation of database. But any existing database having password '123' then change your password for new database to password '1234'. This procedure working for me.
 

Assiduity

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Decorticate