Failed to deploy `ProxyTargetGroup` for RDS Aurora postgresql

Question

I have deployed an Aurora cluster and a db instance (postgresql 11.8) and a proxy as AWS. When I try to add a proxy target group like below code, the deploy takes one hour and timed out in 2 hours. I have attached the screenshot. It works fine if I manually add the target group through AWS console. I wonder what wrong with my configuration?

ProxyTargetGroup:
    Type: AWS::RDS::DBProxyTargetGroup
    Properties:
      DBProxyName: !Ref DBProxy
      DBClusterIdentifiers: [!Ref AuroraDBCluster]
      TargetGroupName: default
      ConnectionPoolConfigurationInfo:
          MaxConnectionsPercent: 100
          MaxIdleConnectionsPercent: 50
          ConnectionBorrowTimeout: 120

DBProxy:
    Type: AWS::RDS::DBProxy
    Properties: 
      Auth:
        - {AuthScheme: SECRETS, SecretArn: !Ref DBSecret, IAMAuth: REQUIRED}
      DBProxyName: ${self:provider.stackName}-dbproxy 
      DebugLogging: true
      EngineFamily: POSTGRESQL
      IdleClientTimeout: 30
      RequireTLS: true
      RoleArn: !GetAtt DBProxyRole.Arn
      VpcSecurityGroupIds:
        - !Ref ClusterSecurityGroup
      VpcSubnetIds:
        - !Ref SubnetAPublic
        - !Ref SubnetAPrivate
        - !Ref SubnetBPrivate
        - !Ref SubnetCPrivate
DBProxyRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: ${self:provider.stackName}-dbproxyRole
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - rds.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: ${self:provider.stackName}-dbproxyPolicy
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - secretsmanager:GetSecretValue
                  - secretsmanager:GetResourcePolicy
                  - secretsmanager:DescribeSecret
                  - secretsmanager:ListSecretVersionIds
                Resource:
                  - "arn:aws:secretsmanager:${opt:region}:${self:provider.accountId}:secret:${opt:stage}/${self:service.name}/AuroraUserSecret"

              - Effect: Allow
                Action:
                  - kms:*
                Resource: 'arn:aws:kms:${opt:region}:${self:provider.accountId}:key/*'
ClusterSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow traffic to client host
      VpcId:
        Ref: VPC
      SecurityGroupIngress:
        - IpProtocol: -1
          CidrIp: 0.0.0.0/0
      SecurityGroupEgress:
        - IpProtocol: -1
          CidrIp: 0.0.0.0/0

When the cloudformation is showing update in progress, I can see that the target group is added and available. But the cloudformation keeps showing in progress until timed out.

Answer 1

I tried to recreate the issues using my own Aurora cluster. I had to fill out a lot of blanks as there are only few bits and pieces provided in the question.

Havever, I had no problems creating proxy with the fixed role. The full template that I've used is below:

Parameters:

  AuroraDBCluster:
    Type: String
    Default: database-22

  DBSecret:
    Type: String
    Default: arn:aws:secretsmanager:us-east-1:xxxxxxx:secret:postgres-wCBBqC   

  ClusterSecurityGroup:
    Type: AWS::EC2::SecurityGroup::Id
    Default: sg-0f52f72631fa40b56

  SubnetAPublic:
    Type: AWS::EC2::Subnet::Id

  SubnetAPrivate:
    Type: AWS::EC2::Subnet::Id

  SubnetBPrivate:
    Type: AWS::EC2::Subnet::Id

  SubnetCPrivate:
    Type: AWS::EC2::Subnet::Id


Resources:

  ProxyTargetGroup:
    Type: AWS::RDS::DBProxyTargetGroup
    Properties:
      DBProxyName: !Ref DBProxy
      DBClusterIdentifiers: [!Ref AuroraDBCluster]
      TargetGroupName: default
      ConnectionPoolConfigurationInfo:
          MaxConnectionsPercent: 100
          MaxIdleConnectionsPercent: 50
          ConnectionBorrowTimeout: 120


  DBProxy:
    Type: AWS::RDS::DBProxy
    Properties: 
      Auth:
        - {AuthScheme: SECRETS, SecretArn: !Ref DBSecret, IAMAuth: DISABLED}
      DBProxyName: ggggg-dbproxy 
      DebugLogging: true
      EngineFamily: POSTGRESQL
      IdleClientTimeout: 30
      RequireTLS: true
      RoleArn: !GetAtt DBProxyRole.Arn
      VpcSecurityGroupIds:
        - !Ref ClusterSecurityGroup
      VpcSubnetIds:
        - !Ref SubnetAPublic
        - !Ref SubnetAPrivate
        - !Ref SubnetBPrivate
        - !Ref SubnetCPrivate

  DBProxyRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: dbproxyRole
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - rds.amazonaws.com
            Action: sts:AssumeRole
      Policies:
          - PolicyName: AccessSecretAndKMS
            PolicyDocument: !Sub |
              {
                  "Version": "2012-10-17",
                  "Statement": [
                      {
                          "Sid": "VisualEditor0",
                          "Effect": "Allow",
                          "Action": "secretsmanager:GetSecretValue",
                          "Resource": "${DBSecret}"
                      },
                      {
                          "Sid": "VisualEditor1",
                          "Effect": "Allow",
                          "Action": "kms:Decrypt",
                          "Resource": "*",
                          "Condition": {
                              "StringEquals": {
                                  "kms:ViaService": "secretsmanager.${AWS::Region}.amazonaws.com"
                              }
                          }
                      }
                  ]
              }