Unable to reconcile DBInstance once the instance enters terminal status due to InvalidParameterValue

[vishalkatikineni]

Describe the bug
Unable to reconcile DBInstance once the instance enters terminal status due to InvalidParameterValue. We are creating IAM role for enhanced monitoring as part of the deployment pipeline and using the IAM role ARN as reference in the DBInstance manifest. We are seeing intermittent issues with the DBInstance resource either not able to create the instance or going out of sync. Below is the error message reported in the DBInstance status.

Message:               InvalidParameterValue: IAM role ARN value is invalid or does not include the required permissions for: ENHANCED_MONITORING  
│                            status code: 400, request id: 20fb8454-93f6-4dd8-a574-e7b9f86a61fe 
Status:                True
Type:                  ACK.Terminal
Last Transition Time:  2024-08-17T22:53:21Z
Message:               Resource not synced
Reason:                resource is in terminal condition
Status:                False
Type:                  ACK.ResourceSynced

Steps to reproduce

apiVersion: iam.services.k8s.aws/v1alpha1
kind: Role
metadata:
  name: demo-monitoring
  namespace: test-rds
spec:
  assumeRolePolicyDocument: |
    {
      "Version":"2012-10-17",
      "Statement": [{
        "Sid": "",
        "Effect":"Allow",
        "Principal": {
          "Service": "monitoring.rds.amazonaws.com"
        },
        "Action": ["sts:AssumeRole"]
      }]
    }
  description: IAM role for RDS monitoring
  maxSessionDuration: 3600
  name: demo-monitoring
  path: /
  policies:
  - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole

apiVersion: rds.services.k8s.aws/v1alpha1
kind: DBCluster
metadata:
  name: test-rds
  namespace: test-rds
spec:
  autoMinorVersionUpgrade: false
  backupRetentionPeriod: 15
  copyTagsToSnapshot: true
  databaseName: testdb
  dbClusterIdentifier: testrds
  dbClusterParameterGroupName: default.aurora-mysql8.0
  dbSubnetGroupName: test-rds
  deletionProtection: false
  enableCloudwatchLogsExports:
  - slowquery
  - error
  - audit
  enableIAMDatabaseAuthentication: true
  engine: aurora-mysql
  engineMode: provisioned
  engineVersion: 8.0.mysql_aurora.3.07.1
  masterUserPassword:
    key: password
    name: test-rds
    namespace: test-rds
  masterUsername: root
  networkType: IPV4
  preferredBackupWindow: 07:00-09:00
  preferredMaintenanceWindow: sun:21:00-sun:23:00
  tags:
  - key: testing
    value: testrds
  vpcSecurityGroupRefs:
  - from:
      name: test-rds

apiVersion: rds.services.k8s.aws/v1alpha1
kind: DBInstance
metadata:
  name: test-rds-instance-0
  namespace: test-rds
spec:
  autoMinorVersionUpgrade: false
  dbClusterIdentifier: test-rds
  dbInstanceClass: db.r6g.large
  dbInstanceIdentifier: test-rds-instance-0
  dbParameterGroupName: default.aurora-mysql8.0
  dbSubnetGroupName: test-rds
  engine: aurora-mysql
  engineVersion: "8.0.mysql_aurora.3.07.1"
  monitoringInterval: 60
  monitoringRoleARN: arn:aws:iam::12345678910:role/demo-monitoring
  preferredMaintenanceWindow: sun:21:00-sun:23:00
  tags:
  - key: testing
    value: testrds

Expected outcome
If there is a delay in attaching the policies to the IAM role, then we expect the DBInstance resource to recover from the terminal status once the policies are attached to the IAM role.

Environment
Dev

• Kubernetes version 1.30.0
• Using EKS (yes/no), if so version? no
• AWS service targeted (S3, RDS, etc.) RDS