Best Practice: How to use Terraform to create custom MySQL (RDS) user via CI?

Question

I have currently created an RDS instance in the private subnets of my VPC. Now I want to create an user with some privileges and grants. For this I am using the mysql_user and mysql_grants resource types in TF. Since my instance is in the private subnet, the TF code cannot be executed as part of the CI pipeline to create the Mysql user and grants.

To my understanding I have two options:

1. Do not terraform the user and grants. Rather log in to the RDS instance via the bastion host and manually create the user.

2. Create the RDS instance in the public subnet (db subnet with the public subnets). Then use the TF to create the user and grants. The modify the db instance and move it to the private subnet.

I am more inclined to do no. 2 since everything is code and repeatable. But want to know if there is any better way in achieving this.

Answer 1

We had a similar situation. Nr. 2 sounds like a good situation until you realise you describe state in Terraform, and something like that is really hard to express easily. Also, if you need to add more users later on, you are temporarily making your database accessible to the outside world, which is not ideal.

Our solution was to use Liquibase (a tool we had already been using for database migrations, but any such tool would work) to create the users and grants.

We where running in Kubernetes, so I could create a Kubernetes Job via Terraform that triggered Liquibase and executed the user creation, but a similar solution is probably possible for your situation. Terraform supports the remote-exec provisioner to trigger a job on a remote host, which can be used to trigger a job on the bastion-host.