I'm trying to follow good security practices: I put my RDS in a private subnet, accessible only by a public EC2 (jump box). My web app server that needs the RDS is hosted in another cloud (fly.io, not AWS). In order for the web app to connect to RDS, my plan is to execute the command on my web server to open an SSH tunnel between the app host and the jump box.
Is there a recommended workflow to setup the app deployment process to include such a port-forwarding command?
Also, With a platform like fly.io, I'm probably not controlling 1 dedicated server / pod every time I deploy. SSH port-forwarding requires the private key pem file. So does it mean I must include a step during deployment to "copy the private key file onto the web server, so that it can SSH to the EC2 jump box"?
It is generally a bad idea to separate Compute and Database resources. Putting them in different clouds will increase the latency between the two services, making every database query slower. This will be especially obvious where they are located in different countries. Sending traffic via a Jump-box will also increase latency.
You do not actually need to put the database in a private subnet. This is only a recommendation based upon traditional networking. You could instead put it in a public subnet and use the Security Group to add a layer of protection. This would normally grant access to inbound traffic coming from a particular IP address. However, looking at fly.io, your app will appear to be located at many different IP addresses so this won't work.
Therefore, using an SSH jump-box is probably the only way to really protect your database from general Internet access. You don't need to "copy the private key file onto the web server". Rather, the code to create the SSH connection can be part of your app.
You didn't mention the technologies your app uses, but as an example Python has a library called Paramiko that can establish SSH connections via code. You would include these capabilities within your app, including the private key used to establish the SSH connection. This way, it is your app that is making the SSH connection rather than the server.