Postgrex.Error ERROR 42501 insufficient_privilege to create extension citext

Questions / Help
,
1

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.

2

Probably that user does not have superuser privs :slight_smile:

Have you tried “by hand” to create the extention from the command line using psql? You can also get a list of privileges with \l and then interpret using https://www.postgresql.org/docs/8.2/static/sql-grant.html to ensure they are correct.

This, btw, has a couple really good tips for avoiding making your web user the superuser on a server:

If you can not do this, e.g. because mix ecto.migrate is your only interface to the db on the destination machine, then I would recommend creating a second repo in your project with a superuser user and use that repo ONLY for migrations that require such priveleges. This prevents running the rest of your queries in your app as an unecessarily priveleged db user.

3 Likes