How to create Aurora Serverless database cluster with secret manager in Terraform

I'm guessing you want to randomize the master_password? You can do something like this:

master_password = random_password.DatabaseMasterPassword.result

The SSM parameter can be created like so:

resource "aws_ssm_parameter" "SSMDatabaseMasterPassword" {
  name  = "database-master-password"
  type  = "SecureString"
  value = random_password.DatabaseMasterPassword.result
}

The random password can be defined like so:

resource "random_password" "DatabaseMasterPassword" {
  length           = 24
  special          = true
  override_special = "!#$%^*()-=+_?{}|"
}

The basic example of creating serveless aurora is:

resource "aws_rds_cluster" "default" {
  cluster_identifier      = "aurora-cluster-demo"
  engine                  = "aurora-mysql"  
  engine_mode             = "serverless"  
  database_name           = "myauroradb"  
  enable_http_endpoint    = true  
  master_username         = "root"
  master_password         = "chang333eme321"
  backup_retention_period = 1
  
  skip_final_snapshot     = true
  
  scaling_configuration {
    auto_pause               = true
    min_capacity             = 1    
    max_capacity             = 2
    seconds_until_auto_pause = 300
    timeout_action           = "ForceApplyCapacityChange"
  }  
}

I'm not sure what do you want to do with secret manager. Its not clear from your question, so I'm providing any example for it.

The accepted answer will just create the Aurora RDS instance with a pre-set password -- but doesn't include Secrets Manager. It's a good idea to use Secrets Manager, so that your database and the applications (Lambdas, EC2, etc) can access the password from Secrets Manager, without having to copy/paste it to multiple locations (such as application configurations).

Additionally, by terraforming the password with random_password it will be stored in plaintext in your terraform.tfstate file which might be a concern. To resolve this concern you'd also need to enable Secrets Manager Automatic Secret Rotation.

Automatic rotation is a somewhat advanced configuration with Terraform. It involves:

• Deploying a Lambda with access to the RDS instance and to the Secret
• Configuring the rotation via the aws_secretsmanager_secret_rotation resource.

AWS provides ready-to-use Lambdas for many common rotation scenarios. The specific Lambda will vary depending on database engine (MySQL vs. Postgres vs. SQL Server vs. Oracle, etc), as well as whether you'll connecting to the database with the same credentials that you're rotating.

For example, when the secret rotates the process is something like:

• Invoke the rotation lambda, Secrets Manager will pass the name of the secret as a parameter
• The Lambda will use the details within the secret (DB Host, Post, Username, Password) to connect to RDS
• The Lambda will generate a new password and run the "Update password" command, which can vary based on DB Engine
• The Lambda will update the new credentials to Secrets Manager

For all this to work you'll also need to think about the permissions Lambda will need -- such as network connectivity to the RDS instance and IAM permissions to read/write secrets.

As mentioned it's somewhat advanced--but results in Secrets Manager being the only persistent location of the password. Once setup it works quite nicely though, and your apps can securely retrieve the password from Secrets Manager (one last tip -- it's ok to cache the secret in your app to reduce Secrets Manager calls but be sure to flush that cache on connection failures so that your apps will handle an automatic rotation).