Fix Phoenix Database Connection on Digital Ocean App Platform

Feb 27, 2025

I generated the default files to deploy a Phoenix app with Docker -> mix phx.gen.release --docker and the database connection failed when deploying the app on Digital Ocean App Platform.

[2025-02-27 21:37:07] 21:37:07.501 [error] Postgrex.Protocol (#PID<0.151.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "157.230.183.70", user "exampleapp-database", database "exampleapp-database", no encryption
[2025-02-27 21:37:07] 21:37:07.501 [error] Postgrex.Protocol (#PID<0.150.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "157.230.183.70", user "exampleapp-database", database "exampleapp-database", no encryption
[2025-02-27 21:37:09] 21:37:09.823 [error] Postgrex.Protocol (#PID<0.150.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "157.230.183.70", user "exampleapp-database", database "exampleapp-database", no encryption
[2025-02-27 21:37:10] 21:37:10.218 [error] Could not create schema migrations table. This error usually happens due to the following:
[2025-02-27 21:37:10]
[2025-02-27 21:37:10]   * The database does not exist
[2025-02-27 21:37:10]   * The "schema_migrations" table, which Ecto uses for managing
[2025-02-27 21:37:10]     migrations, was defined by another library
[2025-02-27 21:37:10]   * There is a deadlock while migrating (such as using concurrent
[2025-02-27 21:37:10]     indexes with a migration_lock)
[2025-02-27 21:37:10]
[2025-02-27 21:37:10] To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.
[2025-02-27 21:37:10]
[2025-02-27 21:37:10] To address the second, you can run "mix ecto.drop" followed by
[2025-02-27 21:37:10] "mix ecto.create", both for the desired MIX_ENV. Alternatively you may
[2025-02-27 21:37:10] configure Ecto to use another table and/or repository for managing
[2025-02-27 21:37:10] migrations:
[2025-02-27 21:37:10]
[2025-02-27 21:37:10]     config :exampleapp, Exampleapp.Repo,
[2025-02-27 21:37:10]       migration_source: "some_other_table_for_schema_migrations",
[2025-02-27 21:37:10]       migration_repo: AnotherRepoForSchemaMigrations
[2025-02-27 21:37:10]
[2025-02-27 21:37:10] The full error report is shown below.
[2025-02-27 21:37:10]
[2025-02-27 21:37:10] ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2914ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
[2025-02-27 21:37:10]
[2025-02-27 21:37:10]   1. Ensuring your database is available and that you can connect to it
[2025-02-27 21:37:10]   2. Tracking down slow queries and making sure they are running fast enough
[2025-02-27 21:37:10]   3. Increasing the pool_size (although this increases resource consumption)
[2025-02-27 21:37:10]   4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
[2025-02-27 21:37:10]
[2025-02-27 21:37:10] See DBConnection.start_link/2 for more information
[2025-02-27 21:37:10]
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
[2025-02-27 21:37:10]     (elixir 1.18.2) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1203: Ecto.Adapters.SQL.execute_ddl/4
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/migrator.ex:779: Ecto.Migrator.verbose_schema_migration/3
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/migrator.ex:563: Ecto.Migrator.lock_for_migrations/4
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/migrator.ex:432: Ecto.Migrator.run/4
[2025-02-27 21:37:10]     (ecto_sql 3.12.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
[2025-02-27 21:37:10]     nofile:1: (file)

To resolve, this update your database adapter SSL settings in config/runtime.exs and add the ENV variables in Digital Ocean.

  config :exampleapp, Exampleapp.Repo,
    ssl: [
      verify: :verify_peer,
      cacerts: [
        System.get_env("DATABASE_CA_CERT")
        |> then(fn pem ->
          [{_type, der, _info}] = :public_key.pem_decode(pem)
          der
        end)
      ],
      server_name_indication: System.get_env("DATABASE_HOSTNAME") |> to_charlist(),
      customize_hostname_check: [
        match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
      ]
    ],
    url: database_url,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
    socket_options: maybe_ipv6

SECRET_KEY_BASE=somekey
DATABASE_URL=${exampleapp-database.DATABASE_URL}
DATABASE_HOSTNAME=${exampleapp-database.HOSTNAME}
DATABASE_CA_CERT=${exampleapp-database.CA_CERT}