Phoenix: The database for Hello.Repo couldn't be created: ERROR 42501 (insufficient_privilege): permission denied to create database

[TangMonk]

Environment

• Elixir version (elixir -v): 1.4.4
• Phoenix version (mix deps): 1.3
• Operating system: OS X 10.11

I am following official guides to create a phoenix project, when I running following commands:

$ mix ecto.create
** (Mix) The database for Hello.Repo couldn't be created: ERROR 42501 (insufficient_privilege): permission denied to create database

it throw the error to me, But I check the files permissions, it is fine

$ ls -al
total 40
drwxr-xr-x  13 tangmonk  staff   442 Aug  8 10:14 .
drwxr-xr-x   3 tangmonk  staff   102 Aug  8 10:13 ..
-rw-r--r--   1 tangmonk  staff   617 Aug  8 10:13 .gitignore
-rw-r--r--   1 tangmonk  staff   755 Aug  8 10:13 README.md
drwxr-xr-x   3 tangmonk  staff   102 Aug  8 10:14 _build
drwxr-xr-x   9 tangmonk  staff   306 Aug  8 10:15 assets
drwxr-xr-x   7 tangmonk  staff   238 Aug  8 10:13 config
drwxr-xr-x  21 tangmonk  staff   714 Aug  8 10:14 deps
drwxr-xr-x   6 tangmonk  staff   204 Aug  8 10:13 lib
-rw-r--r--   1 tangmonk  staff  1580 Aug  8 10:13 mix.exs
-rw-r--r--   1 tangmonk  staff  4472 Aug  8 10:14 mix.lock
drwxr-xr-x   5 tangmonk  staff   170 Aug  8 10:15 priv
drwxr-xr-x   5 tangmonk  staff   170 Aug  8 10:13 test

[aphillipo]

You need to create a user with permission to do this in Postgres. You can Google for how 👍🏻

[TangMonk]

@aphillipo thanks for your reply, but how can I know the name of the postgresql user?

[aphillipo]

@TangMonk This isn't an issue with Phoenix and the error message is okay - Postgresql support is beyond the scope of the phoenix framework issue tracker - it says the following when you create an issue:

• For help and support, use Stack Overflow or the Phoenix Talk mailing list
• For proposing a new feature, start a discussion on the Phoenix Core mailing list
• For bugs, do a quick search and make sure the bug has not yet been reported

But seeing as people might Google and find your issue, hopefully stopping them from re-reporting it I'll explain here:

1) in ./config/dev.exs you'll find phoenix settings for Repo

...

# Configure your database
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "phoenix-database-user",
  password: "some-random-password",
  database: "the-database",
  hostname: "localhost",
  pool_size: 10

...

To create a new Postgres role/user you can follow the instructions here:

https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2

Probably just type:

createuser --interactive -P

And follow the instructions. Please close this and seek further help on slack, IRC or the elixir forums!

Thanks!

[josevalim]

@aphillipo :heart: :green_heart: :blue_heart: :yellow_heart: :purple_heart:

[TangMonk]

@aphillipo thanks! but I think the official doc should be improved when execute command mix ecto.create

In https://hexdocs.pm/phoenix/up_and_running.html#content:

Ok, let’s give it a try. First, we’ll cd into the hello/ directory we’ve just created:

$ cd hello
Now we’ll create our database:

$ mix ecto.create
The database for Hello.Repo has been created

change to:

Ok, let’s give it a try. First, we’ll cd into the hello/ directory we’ve just created:

$ cd hello
Now we’ll create our database:

Then change the `config/dev.exs` file to match your database configuration

$ mix ecto.create
The database for Hello.Repo has been created 

that's will more friendly for beginners without interrupted learning when occurs this problem

[aphillipo]

Seems reasonable @TangMonk - you can find the guide here:

https://github.com/phoenixframework/phoenix_guides/blob/master/docs/up_and_running.md

I'm sure making that clearer would be accepted, but create a pull request there and see if they like it. Shouldn't take long!

[zzz6519003]

same here, could use default?

[zzz6519003]

definite solution:

➜  hello git:(master) ✗ createuser --interactive -P

Enter name of role to add: postgres
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y

[rahbal]

If user already exists, then changing permissions might help.
````
$ su postgres

$ psql

postgres=# alter user puser createdb;
ALTER ROLE
````

[hakanai]

It's weird, I don't recall anything in the guide actually telling me how to do this bit... I'm sure I should have seen something somewhere. I feel like every potentially smooth new language/framework introduction is hindered by an hour long tromp to get a database up and running.

It's especially annoying because creating a database is such an infrequent thing that the commands to do it are forgotten so easily. Surely there must be some common resource that we can just point people at to get the initial postgres install working as smoothly as possible?

[peterpetrov]

Hey guys, i have been struggling with that. It looks like there is very specific way how postgres works. So since i am newbie to Phoenix/Elixir/Postgres stack i decided to test and experiment. My goal was to get non priveledged user created in the database and provide the credentials to the phx app. I am using PSQL 9.1. The difference to make it work was executing the create database command with additional options as follows:

Connect to psql with the master potgres account and perform the commands below. In my case i was trying to get the demo task_app so assuming my user will be taskapp, database taskapp and password set to the same:

Create the user:

create user taskapp;

create the database:

create database "taskapp" with owner taskapp;

Set the user password:

ALTER USER taskapp WITH PASSWORD 'taskapp';

Set privilidges:

GRANT ALL PRIVILEGES ON DATABASE taskapp TO taskapp;

and give the user the option to create databases:

ALTER USER taskapp WITH createdb;

Without the last line my phx app wasn't willing to create the tables. With it all went well. I hope that can be better starting point for other lost souls as myself.

[bitbd83]

If user already exists, then changing permissions might help.

$ su postgres

$ psql

postgres=# alter user puser createdb;
ALTER ROLE

Easy and effective solution.