4

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.

user2290820
  • 2,709
  • 5
  • 34
  • 62
  • 1
    Check that you have really set user `user` for production config. Also, if it is set, connect to production database with `psql` and try to execute `CREATE EXTENSION citext;` manually. Most probably your `alter with superuser` command wasn't successful or you was setting it for other user. – denis.peplin Oct 06 '17 at 08:30
  • @denis.peplin `Check that you have really set user user for production config.` where should i check this? – user2290820 Oct 06 '17 at 08:41
  • `config/prod.exs` or `config/prod.secret.exs` in your app. – denis.peplin Oct 06 '17 at 09:18

1 Answers1

0

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.

Szymon Jeż
  • 8,273
  • 4
  • 42
  • 60