Access fly.io database from Retool

Questions / Help
#1

I need to access a fly.io hosted database from the managed version of Retool. What is the best way to do this? Retool supports ssh tunneling and direct TCP database connections. It doesn’t support wireguard or running custom commands.

Is it possible to connect to a fly.io Postgres instance through pure SSH tunneling without having to use a bastion/proxy? I would also prefer not to have to renew SSH certs every 72 hours.

#2

Here is an example Dockerfile for the exact same use case, in this case it is to allow to our Database read-replicas from https://hightouch.io

As you can see we also include a ssh key.pem file in the image that is given to us from hightouch in this case as their servers are the ones accessing the tunnel.

Dockerfile

FROM alpine:3.2

RUN echo "http://dl-cdn.alpinelinux.org/alpine/edge/community" >> /etc/apk/repositories
RUN apk update

RUN apk add --update openssh-client && apk add autossh && rm -rf /var/cache/apk/*
ADD ./key.pem key.pem

CMD autossh -M 0 -i key.pem \
    -R 0.0.0.0:57510:postgres-app-name.internal:5433 \
    tunnel.hightouch.io -p 49228 \
    -o ExitOnForwardFailure=yes \
    -o "ServerAliveInterval 30" \
    -o StrictHostKeyChecking=no

fly.toml

app = "my-tunnel-app"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[experimental]
  auto_rollback = true
1 Like
#3

Another thing you can do is open a public port to make the database accessible:

1. Assign a Public IP

$ fly ips allocate-v4    # you can also allocate ipv6

2. Configure External Port

Start by saving the database’s fly config to disk in an empty dir:

$ fly config save --app $YOUR_DB

This will create a new fly.toml file with the database config. You can also save it as something like fly.db.toml in your existing app’s directory. Add the following services section to the config (remember to remove the empty services section at the top first):

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

# Example of opening port 12500 for insecure connections.
[[services.ports]]
  handlers = []
  port = 12500

3. Deploy changes

First, you’d need to confirm the database version:

$ fly pg connect $YOUR_DB

psql> SELECT VERSION();
PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) ....

Then redeploy your database with the correct image:

$ fly deploy . --config fly.db.toml --image flyio/postgres:14.2

4. Create a new user for Retool

This is optional but you should create a separate role for retool and any other services that use your DB:

$ fly pg connect $YOUR_DB

psql>  CREATE USER retool WITH PASSWORD 'password';
psql>  \du;  -- assign databases and more...

5. Connect externally

You can now connect to the database instance from retool at:

postgres://retool:password@$YOUR_DB:12500/db_name
1 Like