Postgrex.Error ERROR 42501 insufficient_privilege to create extension citext

Question

I am trying to create a migration. this is the output

MIX_ENV=prod DATABASE_URL="URL" mix ecto.migrate

[info] execute "CREATE EXTENSION citext;"
** (Postgrex.Error) ERROR 42501 (insufficient_privilege): permission denied to create extension "citext"

however until now it has been working in dev mode.

I did try

ALTER USER user WITH SUPERUSER

and installed postgresql-contrib package but nothing works.

Answer 1

I had a similar issue and doing:

1. psql -d postgres, ALTER USER my_user_name WITH SUPERUSER and
2. setting the username in the Repo config to my_user_name

has resolved the issue.

So I think that the answer to the question might be doing 2. so making sure the DB user used by our application is the one that has SUPERUSER. Obviously you could also figure out without doing 2. what DB user name is used by default and then do 1. for that user.