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.
https://docs.retool.com/docs/enabling-ssh-tunnels
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
Another thing you can do is open a public port to make the database accessible:
$ fly ips allocate-v4 # you can also allocate ipv6
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
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
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...
You can now connect to the database instance from retool at:
postgres://retool:password@$YOUR_DB:12500/db_name
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. 
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.
