Connecting to a postgres DB externally securely

Sorry lots of questions from me today, only got going with Fly yesterday!

So I have a postgres cluster and need to connect to from outside the internal network (for reporting etc). I’m not using wireguard; instead I followed the guide for external port configuration.

This works OK to the extent that I’m able to connect to my db via an “insecure connection”. I.e. <my-db-app>.fly.dev:10000 (as is the port in the guide’s example).

In the guide there is another [[services.ports]] block for a “secure connection” using port 443. I can’t seem to connect via this port. Is there something else I need to do, like specify a certificate?

2 Likes

Ok it turns out, our docs are wrong. We can’t do TLS for postgres through our proxy. Postgres expects to do its own protocol negotiation when people connect (and use the same port for non-TLS and TLS connections).

We don’t have a built-in way to expose Postgres to the public internet and connect to it over TLS.

The best way to do this might be with pgbouncer. You could create a separate pgbouncer application on Fly, configure it to support TLS with an embedded certificate, and point it at your postgres cluster over the private network. In general, building little gateway apps might be better than touching your postgres directly.

Hmmm, this is a bit of an issue. Basically I’m trying to connect my mode analytics account to my DB and Mode only allows directly connecting using an encrypted connection.

Do you know of any resources that could help with configuring pgbouncer on Fly? It’s not something I’ve ever used before.

Alternatively, do you know if Fly intends to support encrypted external connections to postgres dbs anytime soon?

Oh you might be in luck. Try installing this and pointing it at your database over the private network: https://mode.com/help/articles/connecting-mode-to-your-database/#run-bridge-in-a-docker-container

Most BI/analytics tools have a way to do this without opening a DB up to the internet.

Nice, yeah the bridge did the trick.

Thanks for your help

1 Like

Oh amazing, I’m glad that worked!

@kurt @harg hey guys I hope you had a great weekend! If I had cloned GitHub - fly-apps/postgres-ha: Postgres + Stolon for HA clusters as Fly apps. what would I have to add from https://mode.com/help/articles/connecting-mode-to-your-database/#run-bridge-in-a-docker-container to allow a public connection?

I am trying to make my db publicly available so Instant GraphQL APIs on your data | Built-in Authz & Caching (hasura.io) can connect to it.

@cjl does Hasura’s setup work with other hosting? You can run Hasura on Fly.io just fine!

If you really need to open Postgres up to the world, we have instructions. It’s just not ideal and something you should avoid if you can: Postgres on Fly

@kurt we have an enterprise plan with them, I am seeing if they can setup Wireguard. In the meantime we need to test the staging application and connect directly to it. Is it not possible to expose the port like myflyapp.fly.dev:5432 (I would do a random port instead of 5432)?

Looks like this works:

[[services]]
  internal_port = 5432 # Postgres instance
  protocol = "tcp"

[[services.ports]]
  handlers = []
  port = 10000

Is there a more secure way? 443 did not work.

I think you’re asking if you can do Postgres + TLS. The answer is not really. Postgres TLS is a weird protocol that our edge proxies don’t support.

Doing Postgres TLS on Fly would mean generating your own certificates, then installing something like pgbouncer (or possibly haproxy) as a Fly app to manage those connections. It’s probably doable, but a little complex.

I imagine another solution to connect to PG securely from outside Fly would be using Wireguard.

It was quite easy to set up that locally on macOS, but now I need to connect to PG from Google Cloud Run.

I think it should be easy to install wireguard on Docker and then add the conf from a secret mounted to a path on the file system.

I will try that and report back!

Edit:

I failed miserably lol

I’ve never used Wireguard before and just now realized maybe the conf provided by the Fly CLI is probably specific to my dev machine.

Even if that wasn’t the case, I can’t seem to start wg-quick on the Docker instance.

After a number of tries and errors I got stuck at:

[#] ip link add basic type wireguard
RTNETLINK answers: Operation not permitted
Unable to access interface: Operation not permitted

From what I’ve seen online seems to be some Linux kernel headers shenanigans way above my Docker/Linux skill.

Unfortunately, this means I won’t be able to use PG on Fly since it’s not possible to use TLS either (from @kurt comments above).

Edit:

On second thought… I think I will just create an API so that Google Cloud Run never touches PG. It’s a bit of a nuisance but seems like a better choice than using a different PG provider if the main app is running on Fly.

1 Like