Troubleshooting Postgres Error: Permission denied to create db

Today I tried to setup Postgres as my db for my Rails project, and met with this error:

Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_cute_db", "pool"=>5, "username"=>"rongjun", "password"=>nil}
PG::Error: ERROR:  permission denied to create database

I want to write down how I solved it.

What I intended to do is the create a db called my_cute_db with rake command, rake db:setup. However, it’s giving error like shown above.

Here’s my database.yml

development:  
adapter: postgresql  
encoding: unicode  
database: my_cute_db  
pool: 5  
username: rongjun  
password:

My username is rongjun. And the error is telling me that “permission denied to create database“, so I have to go into psql command-line interface to find out the permission of my role.

my_terminal# psql postgres
postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    |                                                | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

\du command is listing down the role names and their privileges. We can see that rongjun is not having any privileges. On the other hand daodaowang is able to Create DB. Now we would like to enable rongjun to be able to Create DB just like daodaowang.

postgres=# ALTER ROLE rongjun WITH CREATEDB;

Then we run \du again to check:

postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    | Create DB                                      | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

rongjun is now having the correct privilege to Create DB. Let’s run rake db:create again, it will do the job without error now. You can check by running psql -l command to list down all databases you have.

After creating the db, you can grant more privileges to your user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE my_cute_db to rongjun;

Reference:
For how to create database refer to: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

For creating the user with ‘Create DB’ privilege directly without having to ALTER it later:

create user any_username with password 'any_password' createdb;
Advertisements