Terraform Multiple Provider issue

I am following the documentation here running Terraform v0.14.10 -> https://www.terraform.io/docs/language/modules/develop/providers.html

My config is as follows:

variables.tf

terraform {
  backend "remote" {
    organization = "the-xxxx"
    workspaces {
      prefix = "non-prod-"
    }
  }
  required_providers {
    aws = {
      source = "hashicorp/aws"
      version = "3.32.0"
    }
  }
}

provider "aws" {
}

provider "aws" {
  alias  = "core_db_middleware"
  profile = "core_db_middleware"
}

provider "aws" {
  alias = "us-east-1"
  region = "us-east-1"
}

in main.tf where I call my module:

module "vpc" {
  source = "../modules/infrastructure/shared/aws/vpc"

  providers = {
    aws.core_db_middleware = aws.core_db_middleware
  }

  class_b_block = var.class_b_block
  platform_name = var.platform_name
  core_platform_aws_account_id = var.core_platform_aws_account_id
  core_platform_vpc_id = var.core_platform_vpc_id
  core_platform_region = var.core_platform_region
  core_platform_cidr_range = var.core_platform_cidr_range
}

Then in the "vpc" module variables.tf I have:

provider "aws" {}
provider "aws" {
  alias = "core_db_middleware"
}

data "aws_caller_identity" "requester" {
  provider = aws.core_db_middleware
}

data "aws_region" "requester" {
  provider = aws.core_db_middleware
}

When I run plan on terraform it keeps giving me the aws account ID of the default provider even though I have:

data "aws_caller_identity" "requester" {
  provider = aws.core_db_middleware
}

and use it like so:

resource "aws_vpc_peering_connection" "core_db_middleware_requester" {
  peer_owner_id = data.aws_caller_identity.requester.account_id
  peer_vpc_id = "vpc-xxxxxxxxxxxxxx"
  vpc_id = aws_vpc.main.id
  peer_region = data.aws_region.requester.name
  auto_accept = false
  tags = {
    Name = "VPC Peering between ${var.platform_name} and core_db_middleware"
  }
}

I tried adding the configuration_aliases = [ aws.core_db_middleware ] to my main.tf in my root directory as described in the official documentation and in the "vpc" module directory but I get the below error when in my root main:

Error: Invalid required_providers object
  on variables.tf line 9, in terraform:
   9:     aws = {
  10:       source = "hashicorp/aws"
  11:       version = "3.32.0"
  12:       configuration_aliases = [ aws.core_db_middleware ]
  13:     }
required_providers objects can only contain "version" and "source" attributes.
To configure a provider, use a "provider" block.
Error: Variables not allowed
  on variables.tf line 12, in terraform:
  12:       configuration_aliases = [ aws.core_db_middleware ]
Variables may not be used here.

and the below error when I place it in the "vpc" module:

Error: Variables not allowed
On ../modules/infrastructure/shared/aws/vpc/variables.tf line 33: Variables
may not be used here.
Error: Variables not allowed
On ../modules/infrastructure/shared/aws/vpc/variables.tf line 33: Variables
may not be used here.
Error: Variables not allowed
On ../modules/infrastructure/shared/aws/vpc/variables.tf line 33: Variables
may not be used here.
Error: Variables not allowed
On ../modules/infrastructure/shared/aws/vpc/variables.tf line 33: Variables
may not be used here.

I cannot figure out where I am going wrong :( I do also have my default provider aws environment variables set in the pipeline environment e.g

AWS_ACCESS_KEY_ID: $xxxxxxxxxxx_AWS_ACCESS_KEY_ID
AWS_SECRET_ACCESS_KEY: $xxxxxxxxxx_AWS_SECRET_ACCESS_KEY
AWS_DEFAULT_REGION: 'ap-southeast-2'