Elixir Postgres ERROR 42501 when running mix ecto.setup?

Question

I am trying out a phoenix project (source) but having trouble setting up DB.

When I run mix ecto.setup I am getting this error: (Postgrex.Error) ERROR 42501 (insufficient_privilege): permission denied to create extension "citext...

I am aware there is an almost exact SO thread asking similar question, but that post has no answer.

I went to psql and ran CREATE EXTENSION citext;, but I am still having the same issue.

Can someone point me to the right place so I can setup the proper user privilege so I can run mix ecto.setup successfully?

I am running Phoenix v1.3.2 and elixir 1.6.2.

I also have another phoenix project that I can run mix ecto.setup successfully. I am listing part of the config/dev.exs below for comparison.

Inside rumbl/config/dev.exs (SUCCESSFUL)

# Configure your database
config :rumbl, Rumbl.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "rumbl_dev",
  hostname: "localhost",
  pool_size: 10

Inside mango/config/dev.exs (ERROR)

# Configure your database
config :mango, Mango.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "mango_dev",
  hostname: "localhost",
  pool_size: 10

The repo of the project I am having problem can be found here. Here is another phoenix project I have no problem with, maybe it would help: here

score 3 · Answer 1 · answered Aug 11 '18 at 20:07

3

You have to be connected as a superuser to install the extension citext.

The reason is that this extension creates C functions, and that is restricted to superusers.

answered Aug 11 '18 at 20:07

Laurenz Albe

209,280
17
206
263

Noob question, but how can I be connected as a superuser in this context? – Iggy Aug 11 '18 at 22:27
1

Use `psql` with the option `-U `. – Laurenz Albe Aug 13 '18 at 06:31

score 1 · Answer 2 · answered Aug 14 '18 at 02:54

Figured out the solution. I think it is a combination of Laurenz' answer and me tinkering around.

I went to psql and listed all users \l, and found that my username, iggy, is listed as an owner

...
iggy                            | iggy     | UTF8     | en_US.UTF-8 | en_US.UTF-8 
...

I did psql -U iggy and ran CREATE EXTENSION citext;

I changed username inside dev.exs:

config :mango, Mango.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "iggy",
  password: "postgres",
  database: "mango_dev",
  hostname: "localhost",
  pool_size: 10

Then I ran mix ecto.setup and voila! It ran flawlessly. It is a little weird, because my other project has exact same dev.exs config and I had the username listed as postgres (it worked there) while only iggy works here.

Elixir Postgres ERROR 42501 when running mix ecto.setup?

2 Answers2