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.

Follow this answer to give your used CREATEDB role. And your problem will be resolved. And do rake db:drop and rake db:create. — Arup Rakshit
– Arup Rakshit, Commented Jan 23, 2015 at 19:11

Kirk Roybal · Accepted Answer · 2016-07-11 18:49:38Z

122

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;

edited Jul 11, 2016 at 18:49

Kirk Roybal

18k2 gold badges34 silver badges39 bronze badges

answered Jul 28, 2015 at 7:26

Rokibul Hasan

4,1662 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marlin Pierce Over a year ago

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

ADTC Over a year ago

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;

fivetwentysix · Accepted Answer · 2015-01-23 19:11:15Z

0

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.

answered Jan 23, 2015 at 19:11

fivetwentysix

7,5159 gold badges42 silver badges59 bronze badges

1 Comment

Beengie Over a year ago

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.

Manini Muduli · Accepted Answer · 2021-11-29 06:30:02Z

0

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.

answered Nov 29, 2021 at 6:30

Manini Muduli

11 bronze badge

1 Comment

Community Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related