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 @harry 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.