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!