Error: Provider configuration not present - when providers.tf comes from source module

Question

Created rds_cluster using below code,

module "rds_cluster_db" {
  source = "./private-terraform-aws-rds-cluster"

  name      = "my-user-db"    
  db_user       = "db_user"
  db_name       = "db_name"
  instance_type = "db.t4g.small"
}

To delete this resource, I just removed module block. But, it got failed with the below error as source module contains providers.tf file.

Error:

│ Error: Provider configuration not present
│ 
│ To work with module.rds_cluster_db.postgresql_role.iam_user
│ (orphan) its original provider configuration at
│ module.rds_cluster_db.provider["registry.terraform.io/cyrilgdn/postgresql"]
│ is required, but it has been removed. This occurs when a provider
│ configuration is removed while objects created by that provider still exist
│ in the state. Re-add the provider configuration to destroy
│ module.rds_cluster_db.postgresql_role.iam_user (orphan),
│ after which you can remove the provider configuration again.

./private-terraform-aws-rds-cluster/main.tf:

module "rds_cluster" {
  source  = "cloudposse/rds-cluster/aws"
  version = "1.9.0"

  zone_id                               = var.zone_id
  security_groups                       = var.security_groups
  vpc_id                                = var.vpc_id
  subnets                               = var.subnets
  instance_type                         = var.instance_type

  context = module.this.context
}

resource "postgresql_role" "iam_user" {
  name            = var.db_user
  login           = true
  roles           = ["rds_iam"]
  create_database = true
  create_role     = true
  inherit         = true

  depends_on = [
    module.rds_cluster
  ]
}

./private-terraform-aws-rds-cluster/providers.tf:

provider "postgresql" {
  host             = module.rds_cluster.endpoint
  port             = var.db_port
  database         = var.db_name
  username         = var.admin_user
  password         = var.admin_password
  superuser        = false
  expected_version = var.engine_version
}

terraform {
  required_version = ">= 0.13.0"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = ">= 3.56"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = ">= 1.16.0"
    }
  }
}

Terraform version:

Terraform v1.5.5
on darwin_arm64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.22.0
+ provider registry.terraform.io/hashicorp/archive v2.4.2
+ provider registry.terraform.io/hashicorp/aws v5.42.0
+ provider registry.terraform.io/hashicorp/kubernetes v2.27.0
+ provider registry.terraform.io/hashicorp/null v3.2.2
+ provider registry.terraform.io/hashicorp/random v3.6.0
+ provider registry.terraform.io/hashicorp/tls v4.0.5

I have seen SO posts with similars error, but not really sure on how can I remove only terraform part without removing the reference to provider as removing the module rds_cluster_db will remove both terraform and provider?