Access fly.io database from Retool

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.

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

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
4 Likes

these steps worked for me once but this time it is not working can anybody tell me what could be the issue?

We’re not psychics, and can’t magically figure out what’s wrong in your app we know nothing about.

Please provide details about your configuration, any errors you’re seeing, and what you have already tried to resolve the issue.

Please read this article to learn how to ask a good question.

Thank you very much for your not so helpful reply. :man_facepalming: If i replied in your thread that means the issue was to get the db publicly accessible. Anyways, it worked and just took some time to be implemented on the db app.

1 Like

Easy there :slight_smile:

1 Like