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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s