Any idea why my deployment fails?

I’m getting the same error now and this thread appears to be the only match. I was able to manually add the extension.

2 Likes

I am also seeing this error.

1 Like

I am seeing this issue as well.
I’m trying to make a db migration on a new hosted postgres db with Pleroma:

bash-5.1$ /opt/pleroma/bin/pleroma_ctl migrate

01:46:04.890 [info] == Running 20170522160642 Pleroma.Repo.Migrations.CaseInsensivtivity.up/0 forward

01:46:04.895 [info] execute "create extension if not exists citext"
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "citext"

    hint: Must have CREATE privilege on current database to create this extension.
    (ecto_sql 3.9.0) lib/ecto/adapters/sql.ex:905: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.11.4) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.9.0) lib/ecto/adapters/sql.ex:997: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.9.0) lib/ecto/migration/runner.ex:326: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.9.0) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.11.4) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.9.0) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (ecto_sql 3.9.0) lib/ecto/migration/runner.ex:289: Ecto.Migration.Runner.perform_operation/3
bash-5.1$

Manually adding to the db does not fix the issue for me but that may be an Elixir / Pleroma bug.

Also having this:

  21:31:11.829 [info] == Running 20211119215456 PetalPro.Repo.Migrations.CreateUsersAuthTables.change/0 forward
     21:31:11.838 [info] execute "CREATE EXTENSION IF NOT EXISTS citext"
     ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "citext"
3 Likes

I’m having the same issue.

3 Likes

I have a theory it’s related to the application reading the DATABASE_URL= environment variable which is seemingly incorrect. Mine was setting it as a different user than postgres

Running env will help determining that.

I did redeploy the db before I discovered this but it does look like DATABASE_URL is incorrectly set by fly (it uses a user based on the db sever’s name).

Setting the proper DATABASE_URL via fly secrets appears to be a workaround.

I am having this same problem when deploying a fresh Phoenix 1.7.0-rc.3 app with phx.gen.auth .

Starting from a clean “fly launch”, everything works until in deployment it gets to the database and then it blocks on

==> Release command detected: /app/bin/migrate

--> This release will not be available until the release command succeeds.

     Starting instance
     Configuring virtual machine
     Pulling container image
     Unpacking image
     Preparing kernel init
     Configuring firecracker
     Starting virtual machine
     Preparing to run: `/app/bin/migrate` as nobody
     2023/02/24 04:04:27 listening on [fdaa:0:5127:a7b:a1:3f3a:6c6e:2]:22 (DNS: [fdaa::3]:53)
     04:04:29.732 [info] execute "CREATE EXTENSION IF NOT EXISTS citext"
     ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied to create extension "citext"
         hint: Must have CREATE privilege on current database to create this extension.
         (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
         (elixir 1.14.1) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
         (ecto_sql 3.9.2) lib/ecto/migration/runner.ex:326: Ecto.Migration.Runner.log_and_execute_ddl/3
         (ecto_sql 3.9.2) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
         (elixir 1.14.1) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
         (ecto_sql 3.9.2) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
         nofile:1: (file)
     Starting clean up.
Error release command failed, deployment aborted

I tried to install citext manually as per @zblanco but that didn’t seem to do anything, and it made no difference on a subsequent deploy.

Removing the “CREATE EXTENSION IF NOT EXISTS citext” command from the migration file does allow migration to continue but - unurprisingly - it generates a new error right afterwards:

==> Release command detected: /app/bin/migrate

--> This release will not be available until the release command succeeds.
     Starting instance
     Configuring virtual machine
     Pulling container image
     Unpacking image
     Preparing kernel init
     Configuring firecracker
     Starting virtual machine
     2023/02/24 04:03:11 listening on [fdaa:0:5127:a7b:a1:804f:683f:2]:22 (DNS: [fdaa::3]:53)
         (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
         (ecto_sql 3.9.2) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
         (elixir 1.14.1) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
         (ecto_sql 3.9.2) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
         nofile:1: (file)
     Starting clean up.
Error release command failed, deployment aborted

I am blocked on this now, does anyone have a workaround?

1 Like

Were you able to investigate the environment variable DATABASE_URL? I found it was incorrectly setting the user to something other than “postgres”.

1 Like

I’m having the exact same problem since today on a new app I’m trying to deploy.
Setting the DATABASE_URL didn’t work and recreating a new db and attaching it manually through flyctl postgres attach doesn’t work either.

Error message in the terminal:

ERROR 42501 (insufficient_privilege) permission denied to create extension "citext"
             hint: Must have CREATE privilege on current database to create this extension.

I’m using flyctl v0.0.469

Exactly the same issue. Changing DATABASE_URL didn’t help.

I absolutely love the service here when it works. But fly is either super ridiculously unstable, or I just keep catching bad weather days because I haven’t been able to get a successful app deployment out of it in about three months.

It looks like you’ll need to grant your attached user SUPERUSER privileges to make that work. Extensions typically can’t be installed with normal privileges.

To grant superuser privileges:

  1. Connect to your postgres app with fly pg connect --pg-app-name

  2. ALTER user <target-user> with SUPERUSER;

  3. Confirm the change with \du

Let me know if that does the trick for you.

1 Like

I am having trouble deploying my app and my app server is becoming dead after every restart, apart from it while detaching and creating a new DB I cannot see the maa region in the option anymore. My app server is already in the maa region. I am not sure what is going on in the system but seems highly unreliable as of now.

I just cut a new release of flyctl v0.0.470, which will address this issue. It should be available shortly.

4 Likes

Thank you!

I am waiting patiently to deploy this app. :smiley:

-Steven

This worked for me, thanks @shaun!

Will the new release of flyctl v0.0.470 fix and apply this automatically from now on, or are we expected to run the command ourselves on first deploy?

Will the new release of flyctl v0.0.470 fix and apply this automatically from now on

Yeah, should address it automatically.

1 Like

Thank you @shaun the updated script worked like a charm.

FYI just running fly deploy again gave the same error as before, I guess this is expected.

What worked was to delete fly.toml and Dockerfile from the app directory, and then run fly launch again.

This time the postgres migration incl CREATE EXTENSION IF NOT EXISTS citext succeeded and the deployment reached its conclusion.

Much appreciate you providing the update @shaun!

1 Like

FWIW, I am on fly v0.0.470 and just ran into this issue

for anyone after me, I followed the advice in the previous post, namely adding SUPERUSER privileges. you can get the user name by running fly ssh console, then env to see the DATABASE_URL. The database url has the following format: postgres://<username>:<password>@<host>/<db_name>, so just find the section right after postgres://, up to the colon.

Then fly pg connect -a my-app-db to run the ALTER user command in @shaun 's post.

For some reason I am not able to ssh into my app, I am getting following error:

Error no instances found for <app_name>

I can see my instance in the web console and its in pending state.