Can a Fly.io VM Connect to a DigitOcean (Managed Postgres) DB?

[nelsonic]

The Fly.io Postgres is unmanaged i.e. a YOYO ("You're On Your Own") sometimes up, sometimes down. 😕
But the stateless Virtual Machines (VMs) have decent uptime and the deployment is good.
So my question is this: can we connect a Fly.io VM to our Managed Postgres on DigitalOcean.
Both the our MVP (Fly VM) and the DB are hosted in "London" which means "Docklands" (financial district).
So the latency between the Fly VM and DB should be low enough to be negligible for a small App where people don't expect sub 200ms latency.

Where we might run into issues is on outbound bandwidth costs. ☁️ 💸
But the with 1TB of outbound monthly bandwidth included with the base DB instance see:
https://www.digitalocean.com/community/tools/bandwidth
I'm not concerned in the short term.

Going to try and get this setup now. Wish me Luck! 🤞

Relates to: #90 and dwyl/learn-analytics#3

Update: Answer: No 😢

Short version: DigitalOcean Managed Postgres can only whitelist IPv4 addresses.
fly.io machines can only have IPv6 addresses for outbound requests.
Therefore the fly.io machine cannot communicate with the DigitalOcean Managed Postgres. 😢

[nelsonic]

I'm going to try and run the Auth App with DO Postgres first
because it's been offline the longest and is the least perf-bound app
and the MVP depends on it to be UP. ref: dwyl/mvp#459 (comment)

[nelsonic]

Will return to this once kids are in bed. ⏳

[nelsonic]

Attempting to run the mvp on localhost using the DigitalOcean Managed Postgres DB ... 🧑‍💻
Getting the following error:

22:47:49.122 [warning] setting ssl: true on your database connection offers only limited protection,
as the server's certificate is not verified.
Set "ssl: [cacertfile: path/to/file]" instead

22:47:49.213 [error] Postgrex.Protocol (#PID<0.6035.0>) failed to connect: 
** (DBConnection.ConnectionError) ssl connect: Options (or their values)
  can not be combined: [{verify,verify_peer},
  {cacerts,undefined}] - {:options, :incompatible, [verify: :verify_peer, cacerts: :undefined]}

Reading:
https://elixirforum.com/t/warning-setting-ssl-true-on-your-database-connection-offers-only-limited-protection-as-the-servers-certificate-is-not-verified-set-ssl-cacertfile-path-to-file-instead/65441

The answer appears to be: https://elixirforum.com/t/warning-setting-ssl-true-on-your-database-connection-offers-only-limited-protection-as-the-servers-certificate-is-not-verified-set-ssl-cacertfile-path-to-file-instead/65441/5

But need to have a certificate ... 🔒
https://cloud.digitalocean.com/databases/38253546-fe28-4a33-9a44-cb175b6f698f?i=933568
(note: this link won't work for you unless you have been granted explicit access ...)

Download it to localhost:

Config:

config :app, App.Repo,
  ssl: [
    verify: :verify_peer,
    cacertfile: ".postgres-cert.crt"
  ]

Currently hard-coded but I will wrap in an if specific to DigitalOcean when I PR the change.

To run the MVP on localhost, I've exported the

Next error:

23:00:21.737 [error] Postgrex.Protocol (#PID<0.225.0>) failed to connect: ** (Postgrex.Error) FATAL 3D000 (invalid_catalog_name) database "postgres" does not exist

23:00:21.739 [error] :gen_statem #PID<0.225.0> terminating
** (Postgrex.Error) FATAL 3D000 (invalid_catalog_name) database "postgres" does not exist
    (db_connection 2.7.0) lib/db_connection/connection.ex:104: DBConnection.Connection.handle_event/4
    (stdlib 5.2.3) gen_statem.erl:1397: :gen_statem.loop_state_callback/11
    (stdlib 5.2.3) proc_lib.erl:241: :proc_lib.init_p_do_apply/3
Queue: [internal: {:connect, :init}]
Postponed: []
State: Postgrex.Protocol
Callback mode: :handle_event_function, state_enter: false

Searched for: https://www.google.com/search?q=phoenix+digitalocean+FATAL+3D000+%28invalid_catalog_name%29+database+%22postgres%22+does+not+exist
Reading: https://www.digitalocean.com/community/questions/elixir-ecto-phoenix-managed-postgres-db 👀

Have to manually create the postgres DB ... 🤦‍♂️
Or define the maintenance_database ...
https://hexdocs.pm/ecto_sql/Ecto.Adapters.Postgres.html#module-connection-options
https://docs.digitalocean.com/products/databases/postgresql/how-to/connect/

So in the config/prod.exs I added the following lines:

# Configure DB for Digital Ocean via SSL
config :app, App.Repo,
  ssl: [
    verify: :verify_peer,
    cacertfile: ".postgres-cert.crt"
  ],
  maintenance_database: "defaultdb"

From localhost, rand the following command:

MIX_ENV=prod mix ecto.setup

which is:

https://github.com/dwyl/mvp/blob/6c140683bb3f9ac53f8c2dfe81a1ea514a0fcec5/mix.exs#L116-L120

That appears to have worked:

But get the floowing error:

23:59:42.758 [error] Could not warm up static assets: could not find static manifest at "~/mvp/_build/prod/lib/app/priv/static/cache_manifest.json". 
Run "mix phx.digest" after building your static files or remove the "cache_static_manifest" 
configuration from your config files.
23:59:42.804 [debug] AUTH_API_KEY Environment Variable is not set
23:59:42.804 [debug] .env file path: ~/mvp/.env

[nelsonic]

MIX_ENV=prod mix phx.digest
MIX_ENV=prod mix ecto.setup
MIX_ENV=prod mix s

Get the following error:

12:17:35.486 [error] Could not check origin for Phoenix.Socket transport.

Origin of the request: http://localhost:4000

This happens when you are attempting a socket connection to
a different host than the one configured in your config/
files. For example, in development the host is configured
to "localhost" but you may be trying to access it from
"127.0.0.1". To fix this issue, you may either:

  1. update [url: [host: ...]] to your actual host in the
     config file for your current environment (recommended)

  2. pass the :check_origin option when configuring your
     endpoint or when configuring the transport in your
     UserSocket module, explicitly outlining which origins
     are allowed:

        check_origin: ["https://example.com",
                       "//another.com:888", "//other.com"]

Googled for the exact error message:
https://www.google.com/search?q=phoenix+prod+check_origin+localhost&oq=phoenix+prod+check_origin+localhost

Read: https://elixirforum.com/t/check-origin-for-production-environment/53600

Added the following to config/prod.exs:

config :app, AppWeb.Endpoint,
  cache_static_manifest: "priv/static/cache_manifest.json",
  check_origin: ["//localhost"]

Works!!!!!! 😍

Data stored on DigitalOcean:

[nelsonic]

Now reading: https://www.reddit.com/r/elixir/comments/x1h7fk/access_external_database_on_flyio/

[nelsonic]

Next:

1. Export the DO Postgres certificate as an env var on localhost.
2. Test that it still works on localhost
3. If that works, attempt to export the same env var to Fly.io

[nelsonic]

Using the answer to my SO question:
https://stackoverflow.com/questions/49457787/how-to-export-a-multi-line-environment-variable

export POSTGRES_CERT=`cat ./.postgres-cert.crt`

Confirm it works:

That works on localhost. ✅

Now to attempt it on Fly.io ...

fly secrets set POSTGRES_CERT=$POSTGRES_CERT

flyctl ssh console -a mvp -C "echo $POSTGRES_CERT"

Sadly, stuck in a loop where the VM does not boot without the

22:20:31.348 [error] Postgrex.Protocol (#PID<0.163.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (dwyl-postgres-do-user-0.k.db.ondigitalocean.com): non-existing domain - :nxdomain

Running mvp release_command: /app/bin/migrate

-------
 ✖ release_command failed
-------
Error release_command failed running on machine 28716d6c0e6468 with exit code 1.
Check its logs: here's the last 100 lines below, or run 'fly logs -i 28716d6c0e6468':
  Pulling container image registry.fly.io/mvp:deployment-01JAP0BR6TFPSBFJMDYE0ZFWS0
-------
Error: release command failed - aborting deployment. error release_command machine 28716d6c0e6468 exited with non-zero status of 1

So cannot "migrate" the db (even though no migration is required!) ...

[nelsonic]

So the problem appears to be that the (fly.io) "builder" instance changes IP address each time
thus it's impossible to "whitelist" it on DigitalOcean ... 🤦‍♂️

[nelsonic]

DigitalOcean isn't allowing me to add one of the Fly.io IPV6 addresses:

[nelsonic]

2024-10-21T01:28:49.475 app[5683049c137318] lhr [info] 01:28:49.474 [error]
Postgrex.Protocol (#PID<0.2271.0>) failed to connect: ** (DBConnection.ConnectionError)
tcp connect (dwyl-postgres-do-user-0.k.db.ondigitalocean.com:25060): non-existing domain - :nxdomain

[nelsonic]

Lame. Fly.io are using iPV6 by default now ...
https://community.fly.io/t/fly-launch-database-has-non-existing-domain-nxdomain/20400/9?u=dwyl_auth

[nelsonic]

fly secrets set ERL_AFLAGS="-proto_dist inet6_tcp"
fly secrets set ECTO_IPV6="true"

Sadly that doesn't work. The outbound IP address on fly.io is IPv6:

And DigitalOcean only supports IPv4:
https://www.digitalocean.com/community/questions/adding-ipv6-to-mongodb-cluster-s-trusted-sourcess-ip-binding

Yes, that question appears to be for MongoDB but it's applicable to all managed databases on DO. 🤦‍♂️

https://fly.io/docs/about/pricing/#anycast-ip-addresses

fly ips allocate-v4
? Looks like you're accessing a paid feature. Dedicated IPv4 addresses now cost $2/mo.
Are you ok with this? Alternatively, you could allocate a shared IPv4 address with the --shared flag. Yes
VERSION	IP           	TYPE                     	REGION	CREATED AT
v4     	137.66.32.221	public (dedicated, $2/mo)	global	1s ago

Sadly, all that does is add the IP Address to the App level:

https://fly.io/apps/mvp

But no change at the machine level which is where we need the update:

We _could find a way to proxy the requests from the Fly.io Machine to the DigitalOcean Managed Postges ...
But then it feels like we're doing many extra steps just to host the App on Fly.io ...
Feels like we are jumping through hoops just to keep using Fly.io when we know there's an alternative. 💭

#96 + #97 Could be the "End Game" for Deployment! 💭

[nelsonic]

I've enjoyed the experience/workflow of working with fly.io and genuinely believe they have a great product/service. ❤️
But their lack of desire to offer Managed Postgres means we are forced to look elsewhere. 😢 💔

Released the IPv4 address as no use paying $2/month for something we don't need/use:

fly ips release 137.66.32.221