Self-hosted supabase with external db failing migrations

[BeppeMarnell]

Hi all,

In my little startup, we recently started using Supabase. It is really good as it speeds up our work.
Initializing Supabase locally with the CLI works really well, but I am experiencing some issues with deployment with a Lightsail database.

I am following the same steps as described in the /docker section of the Supabase repo.
After writing down all .env variables and spinning the containers, the analytics container keeps complaining about the following error:

LOGFLARE_NODE_HOST is: 127.0.0.1

09:21:50.673 [info] Starting migration

09:21:51.007 [error] Postgrex.Protocol (#PID<0.162.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "host.ip", user "myuser", database "postgress_db", no encryption

09:22:01.982 [error] Could not create schema migrations table. This error usually happens due to the following:

  * The database does not exist
  * The "schema_migrations" table, which Ecto uses for managing
    migrations, was defined by another library
  * There is a deadlock while migrating (such as using concurrent
    indexes with a migration_lock)

To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.

To address the second, you can run "mix ecto.drop" followed by
"mix ecto.create", both for the desired MIX_ENV. Alternatively you may
configure Ecto to use another table and/or repository for managing
migrations:

    config :logflare, Logflare.Repo,
      migration_source: "some_other_table_for_schema_migrations",
      migration_repo: AnotherRepoForSchemaMigrations

The full error report is shown below.

** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 10962ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

  1. Ensuring your database is available and that you can connect to it
  2. Tracking down slow queries and making sure they are running fast enough
  3. Increasing the pool_size (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.14.4) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:738: Ecto.Migrator.verbose_schema_migration/3
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:552: Ecto.Migrator.lock_for_migrations/4
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:428: Ecto.Migrator.run/4
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
    nofile:1: (file)

09:22:08.078 [notice]     :alarm_handler: {:set, {:system_memory_high_watermark, []}}

09:22:08.290 [info] Elixir.Logflare.SigtermHandler is being initialized...

What I have tried so far:

• I made sure that the PostgreSQL connection was successful by connecting my machine and logging into the db with psql
• I tried to append '?sslmode=verify-full' as it looks like this may be some errors with an unsecured connection.
• I tried to remove the analytics container, but then the Supavisor pops up with the same identical errors.
• I tried to change some of the variables in the /volumes/pooler/pooler.exs as they seem to not follow the .env variables.
• I looked at the following SSL enforcement page but the instructions are not really clear on how to go about it, especially with an external DB.
• I tried to look at any SSL PostgreSQL variables that could be present in the images, but I could not find any.
• I tried removing the /volume directory and starting anew.

Nothing of what I tried has worked so far.
Maybe there is something really dumb that I am missing, please I would love to have some insights about it.
Thanks for the help!

For reference, here my pooler.exs:

...
params = %{
  "external_id" => System.get_env("POOLER_TENANT_ID"),
  "db_host" => System.get_env("POSTGRES_HOST"),
  "db_port" => System.get_env("POSTGRES_PORT"),
  "db_database" => System.get_env("POSTGRES_DB"),
  "require_user" => false,
  "auth_query" => "SELECT * FROM pgbouncer.get_auth($1)",
  "default_max_clients" => System.get_env("POOLER_MAX_CLIENT_CONN"),
  "default_pool_size" => System.get_env("POOLER_DEFAULT_POOL_SIZE"),
  "default_parameter_status" => %{"server_version" => version},
  "users" => [%{
    "db_user" => System.get_env("POSTGRES_USER"),
    "db_password" => System.get_env("POSTGRES_PASSWORD"),
    "mode_type" => System.get_env("POOLER_POOL_MODE"),
    "pool_size" => System.get_env("POOLER_DEFAULT_POOL_SIZE"),
    "is_manager" => true
  }]
}
...