ERROR 42501 (insufficient_privilege) permission denied to create extension "pg_stat_statements"

Questions / Help
1

I’m deploying a new Elixir/Phoenix application and I’m getting the above error

13:58:45.036 [info] execute "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"
         ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "pg_stat_statements"
             hint: Must be superuser to create this extension.```

The migration was proceeding fine until that point. I have a previous Phoenix application that didn't run into this issue.

My thoughts are
- somehow I created the db app improperly
- the DATABASE_URL is incorrect - though it is connecting with a user that appears to not be a super user.
- That extension is no longer supported or allowed?


Any help is appreciated.

Thanks!
2

I was able to create the pg_stat_statements module in my Postgres instance, so I believe it is supported. I think you’re correct in that the user you’re connecting with is not a superuser.
You can use: flyctl postgres users list to find a list of users with their roles.

If you were not using a superuser, try choosing a user that is a superuser to run your script

1 Like
3

Thanks - it is likely that I used the wrong DATABASE_URL output from the database cluster creation (I should have used the ‘postgres’ user

4

fly pg attach intentionally creates a lower privilege user, so you did the right thing! For enabling extensions, you might need more permissions! You can either login with the postgres user we generated for you and enable extensions, or you can give the app user enough permissions to run that migration. Up to you!

5

I have the same problem, but how do i change user or give my app user more privilage ?
Edit: found the way, i changed the DATABASE_URL in the secrets of my app with the right postgres credentials givent when i started the app