Deploy RDS migrations from GitHub with Actions

Question

I have a stack configured in Cloudformation with RDS (Postgres) on a private subnet, to be accessed from Elastic Beanstalk which has ec2 instances on the public subnet.

I'd like to use [sqitch](https://sqitch.org) to manage my database migrations (updates.) I have a GitHub repository specifically for my Postgres database, and each time there is a commit to the main branch I'd like to have a GitHub Action deploy the migration to my RDS instance using the `sqitch` command.

How can I do this with RDS on the private subnet? Is there some way I could use `eb ssh` in a GitHub Action to create an ssh tunnel to RDS, such that `sqitch` can connect directly from GitHub to the DBMS on RDS and deploy the migration?

Should I setup a bastion host? Or should I have a GitHub Action that somehow creates an ephemeral ec2 instance to retrieve my database repository and deploy the migration to RDS on the private subnet?

Or are there alternatives I haven't thought of?

Apologies if I've mixed up some AWS/Cloudformation terminology, I'm pretty new to this.

Accepted Answer

Hello!

You should create a bastion host to access your RDS instance. With AWS Systems Manager it's not necessary anymore to create the bastion host in a public subnet. You can connect to the host directly with the AWS CLI. You can then set up port forwarding to bind the remote database port to a local port for the migration.

Please see instructions for setting up Systems Manager here: https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-getting-started.html

Here are instructions for Systems Manager port forwarding: https://aws.amazon.com/blogs/aws/new-port-forwarding-using-aws-system-manager-sessions-manager/

Answer

I'd recommend to launch an EC2 instance within your VPC acting as a self-hosted runner. I created [HyperEnv for GitHub Actions Runner](https://hyperenv.com/github-actions/) which provides a highly scalable and cost-efficient way to host GitHub runners on AWS.

Deploy RDS migrations from GitHub with Actions

Relevanter Inhalt