manage_master_user_password: not possible to change from true to false

Description

Once RDS instance was created with the option manage_master_user_password = true, it's not possible to change it to false and set a custom password. However, it's possible to do via AWS Console.

[x] ✋ I have searched the open/closed issues and my issue is not listed.

Versions

Module version: 6.8.0
Terraform version: 1.8.5
Provider version(s): aws [5.63.0]

Reproduction Code

provider "aws" {
  region = local.region
}

data "aws_caller_identity" "current" {}
data "aws_availability_zones" "available" {}

locals {
  name    = "complete-postgresql"
  region  = "eu-west-1"
  region2 = "eu-central-1"

  vpc_cidr = "10.0.0.0/16"
  azs      = slice(data.aws_availability_zones.available.names, 0, 3)

  tags = {
    Name       = local.name
    Example    = local.name
    Repository = "https://github.com/terraform-aws-modules/terraform-aws-rds"
  }
}

module "vpc" {
  source  = "terraform-aws-modules/vpc/aws"
  version = "~> 5.0"

  name = local.name
  cidr    = local.vpc_cidr

  azs                          = local.azs
  public_subnets      = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k)]
  private_subnets     = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k + 3)]
  database_subnets = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k + 6)]

  create_database_subnet_group = true

  tags = local.tags
}

module "security_group" {
  source  = "terraform-aws-modules/security-group/aws"
  version = "~> 5.0"

  name           = local.name
  description = "Complete PostgreSQL example security group"
  vpc_id         = module.vpc.vpc_id

  # ingress
  ingress_with_cidr_blocks = [
    {
      from_port   = 5432
      to_port        = 5432
      protocol      = "tcp"
      description = "PostgreSQL access from within VPC"
      cidr_blocks = module.vpc.vpc_cidr_block
    },
  ]

  tags = local.tags
}

module "rds" {
  source  = "terraform-aws-modules/rds/aws"
  version = "~> 6.8.0"

  identifier                             = local.name
  engine                                 = "postgres"
  engine_version                          = "16.4"
  engine_lifecycle_support                = "open-source-rds-extended-support-disabled"
  family                                  = "postgres16"
  major_engine_version                    = 16
  instance_class                          = "db.t3.micro"

  allocated_storage                       = 5
  max_allocated_storage                   = 10

  db_name                                 = "completePostgresql"
  username                                = "complete_postgresql"
  port                                    = 5432

  manage_master_user_password             = true
  manage_master_user_password_rotation     = true
  master_user_password_rotate_immediately = false
  master_user_password_rotation_schedule_expression = "rate(15 days)"

  multi_az                                = false
  db_subnet_group_name                    = module.vpc.database_subnet_group
  vpc_security_group_ids                  = [module.security_group.security_group_id]

  maintenance_window                      = "Mon:00:00-Mon:03:00"

  create_cloudwatch_log_group             = false
  skip_final_snapshot                      = true
  deletion_protection                      = true
  performance_insights_enabled             = false
  create_monitoring_role                  = false
  create_db_parameter_group               = false

  tags                                    = local.tags
}

Steps to reproduce the behavior:

Deploy all the services according to the "Reproduction Code".
Apply the following changes to the code (module "rds"):
- set manage_master_user_password = false
- remove parameters manage_master_user_password_rotation, master_user_password_rotate_immediately, master_user_password_rotation_schedule_expression
- add parameter password = "completePostgresql16"
Run terraform apply

Expected behavior

RDS credentials management option is changed from "Managed in AWS Secrets Manager" to "Self managed" with the Master password set as in the parameter password.

Actual behavior

Terraform throws the error: Error: updating RDS DB Instance (complete-postgresql): operation error RDS: ModifyDBInstance, https response error StatusCode: 400, RequestID: xxxx0xxx-000x-0000-00xx-x00xxxx0000x, api error InvalidParameterValue: You can't specify MasterUserPassword for an instance with ManageMasterUserPassword enabled.

terraform-aws-modules / terraform-aws-rds