New Destination does not run setup tests (redshift)

ann8ty commented 1 year ago

Describe the bug

A new REDSHIFT destination is created with:

  trust_certificates = "true"
  trust_fingerprints = "true"
  run_setup_tests    = "true"

but in the fivetran console, it is INCOMPLETE and requires manual "Test connection" to change to CONNECTED

To Reproduce

resource "fivetran_destination" "destination_redshift" {
  group_id           = fivetran_group.group_for_everything.id
  service            = "redshift"
  time_zone_offset   = "0"           
  region             = "GCP_US_EAST4" 
  trust_certificates = "true"
  trust_fingerprints = "true"
  run_setup_tests    = "true"

  config {
    tunnel_host     = data.aws_cloudformation_export.bastion_elastic_ip_export.value
    tunnel_port     = 22
    tunnel_user     = "fivetran"
    host            = local.redshift_host
    port            = 5439
    auth_type       = "PASSWORD"
    user            = jsondecode(data.aws_secretsmanager_secret_version.fivetran_database_user_secret.secret_string)["username"]
    password        = jsondecode(data.aws_secretsmanager_secret_version.fivetran_database_user_secret.secret_string)["password"]
    database        = var.redshift_database
    connection_type = "SSHTunnel"
    cluster_region  = "us-east-2"
  }
}

Expected behavior

When

  trust_fingerprints = "true"
  run_setup_tests    = "true"

the setup should be CONNECTED without requiring any manual action

Logs & Output

ann@ann-work tf-fivetran-group % AWS_PROFILE="dbf" terraform plan -var-file="./$ENVIROMENT_LONG_NAME/$CDK_PREFIX.tfvars" -out=.terraform/$CDK_PREFIX.out

fivetran_group.group_for_everything: Refreshing state... [id=wafted_liturgy] data.aws_cloudformation_export.fivetran_redshift_user_export_secret_arn: Reading... data.aws_cloudformation_export.bastion_elastic_ip_export: Reading... aws_ssm_parameter.ssm_fivetran_group_id_external_id_data_account: Refreshing state... [id=/data1-datainfra-v2/schema-v2/fivetranGroupId] aws_secretsmanager_secret.fivetran_public_key_secret: Refreshing state... [id=arn:...] aws_ssm_parameter.ssm_fivetran_group_id_external_id_app_account: Refreshing state... [id=/data1-datainfra-v2/schema-v2/appenvironment/fivetranGroupId] data.aws_cloudformation_export.bastion_elastic_ip_export: Read complete after 0s [id=cloudformation-exports-us-east-2-data1-bastions-v2-tf-fivetran-group-elastic-ip] data.aws_cloudformation_export.fivetran_redshift_user_export_secret_arn: Read complete after 0s [id=cloudformation-exports-us-east-2-data1-datainfra-v2-tf-fivetran-secret-arn] data.aws_secretsmanager_secret.fivetran_database_user_secret_by_arn: Reading... data.aws_secretsmanager_secret.fivetran_database_user_secret_by_arn: Read complete after 0s [id=arn:...] data.aws_secretsmanager_secret_version.fivetran_database_user_secret: Reading... data.aws_secretsmanager_secret_version.fivetran_database_user_secret: Read complete after 0s [id=arn:...|AWSCURRENT]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:

create

Terraform will perform the following actions:

resource "aws_secretsmanager_secret_version" "fivetran_public_key" {
- arn = (known after apply)
- id = (known after apply)
- secret_id = "arn:..."
- secret_string = (sensitive value)
- version_id = (known after apply)
- version_stages = (known after apply) }
resource "fivetran_destination" "destinationredshift" {
- group_id = "wafted_liturgy"
- id = (known after apply)
- last_updated = (known after apply)
- region = "GCP_US_EAST4"
- run_setup_tests = true
- service = "redshift"
- setup_status = (known after apply)
- time_zone_offset = "0"
- trust_certificates = true
- trust_fingerprints = true
- config {
  - auth_type = "PASSWORD"
  - cluster_region = "us-east-2"
  - connection_type = "SSHTunnel"
  - database = "removed"
  - host = "removed"
  - is_private_key_encrypted = (known after apply)
  - password = (sensitive value)
  - port = 5439
  - public_key = (known after apply)
  - tunnel_host = "removed"
  - tunnel_port = "22"
  - tunnel_user = "fivetran"
  - user = (sensitive value) } }

Plan: 2 to add, 0 to change, 0 to destroy.

──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Saved the plan to: .terraform/data1.out

To perform exactly these actions, run the following command to apply: terraform apply ".terraform/data1.out" Releasing state lock. This may take a few moments...

tf-fivetran-group % AWS_PROFILE="dbf" terraform apply .terraform/$CDK_PREFIX.out

fivetran_destination.destination_redshift_datainfra_v2: Creating... fivetran_destination.destination_redshift_datainfra_v2: Still creating... [10s elapsed] fivetran_destination.destination_redshift_datainfra_v2: Creation complete after 13s [id=wafted_liturgy] aws_secretsmanager_secret_version.fivetran_public_key: Creating... aws_secretsmanager_secret_version.fivetran_public_key: Creation complete after 1s [id=arn:...] Releasing state lock. This may take a few moments...

Apply complete! Resources: 2 added, 0 changed, 0 destroyed.

Plugin version: Terraform v1.5.2 on darwin_arm64

provider registry.terraform.io/fivetran/fivetran v0.7.3
provider registry.terraform.io/hashicorp/aws v5.8.0

Additional context Add any other context about the problem here.

beevital commented 1 year ago

Created an internal ticket to investigate the problem. Thank you for report!

beevital commented 1 year ago

@ann8ty could you please try to perform the same with connection_type = "SshTunnel" instead of "SSHTunnel"? It seems to me that these two cases are related.

dominicroessner commented 1 year ago

Seeing similar issue with S3 destinations:

terraform {
  required_version = "~> 1.4"
  required_providers {
    fivetran = {
      version = "1.0.0-pre"
      source = "fivetran/fivetran"
    }
    aws = {
      source  = "hashicorp/aws"
      version = "5.9.0"
    }
  }
}

resource "fivetran_group" "this" {
  name = "my_group"
}

resource "fivetran_destination" "this" {
  group_id         = fivetran_group.this.id
  service          = "new_s3_datalake"
  region           = "AWS_US_EAST_1"
  time_zone_offset = "-5"
  run_setup_tests  = "true"

  config {
    bucket            = "my-s3-bucket"
    fivetran_role_arn = aws_iam_role.s3.arn
    prefix_path       = "my-path"
    region            = "us-east-1"
  }

I have noticed that this may be a timing issue. If I run the apply without the destination, then add the destination in a separate run it runs the set up test just fine. I have also added an arbitrary 10 second wait and that also runs the tests. I cannot get both to run in one plan/apply with having it run tests. The Fivetran UI (and my terraform state) always give me the incomplete status.

resource "fivetran_group" "this" {
  name = "my_group"
}

resource "time_sleep" "wait_10_seconds" {
  create_duration = "10s"
}

resource "fivetran_destination" "this" {
  group_id         = fivetran_group.this.id
  service          = "new_s3_datalake"
  region           = "AWS_US_EAST_1"
  time_zone_offset = "-5"
  run_setup_tests  = "true"

  config {
    bucket            = "my-s3-bucket"
    fivetran_role_arn = aws_iam_role.s3.arn
    prefix_path       = "my-path"
    region            = "us-east-1"

  depends_on = [ time_sleep.wait_10_seconds ]
}

beevital commented 5 months ago

@ann8ty @dominicroessner could you please give us some feedback about the fix?

dominicroessner commented 5 months ago

@beevital So this is partially working now, but I'm still getting a warning when setup tests run sometimes because Fivetran is unable to assume the role. In these cases, the destination still ends up with an incomplete status.

If I had to guess, it's due to the role being setup immediately before the Fivetran destination is and the role / attached policies not having fully propagated yet. This would be due to how AWS handled propagation in IAM, but I'm wondering if this could be handled somehow in the Fivetran provider in the setup test through some type of retry mechanism. The logs do show 2 warnings with different request IDs, so I assume it already does retry?

Here is the code with some values redacted.

# ./modules/fivetran-foo-s3-destination/main.tf

################################################################################
# Providers
################################################################################
terraform {
  required_version = "~> 1.4"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "5.40.0"
    }
    fivetran = {
      source  = "fivetran/fivetran"
      version = "1.1.15"
    }
    time = {
      source  = "hashicorp/time"
      version = "0.9.1"
    }
  }
}

################################################################################
# Fivetran S3 Destination
################################################################################
data "aws_caller_identity" "current" {}
data "aws_region" "current" {}

locals {
  fivetran_aws_account_id = "834469178297"
  path                    = "${var.tenant}/foos"
}

data "aws_s3_bucket" "this" {
  bucket = var.s3_bucket
}

data "aws_iam_policy_document" "s3" {
  statement {
    actions = [
      "s3:DeleteObjectTagging",
      "s3:ReplicateObject",
      "s3:PutObject",
      "s3:GetObjectAcl",
      "s3:GetObject",
      "s3:DeleteObjectVersion",
      "s3:ListBucket",
      "s3:PutObjectTagging",
      "s3:DeleteObject",
      "s3:PutObjectAcl"
    ]
    effect = "Allow"
    resources = [
      "${data.aws_s3_bucket.this.arn}/${local.path}/*",
      data.aws_s3_bucket.this.arn
    ]
  }
}

resource "aws_iam_policy" "s3" {
  name   = "fivetran-${var.tenant}-s3"
  policy = data.aws_iam_policy_document.s3.json
}

data "aws_iam_policy_document" "glue" {
  statement {
    actions = [
      "glue:GetDatabase",
      "glue:UpdateDatabase",
      "glue:DeleteDatabase",
      "glue:CreateTable",
      "glue:GetTables",
      "glue:CreateDatabase",
      "glue:UpdateTable",
      "glue:BatchDeleteTable",
      "glue:DeleteTable",
      "glue:GetDatabases",
      "glue:GetTable"
    ]
    effect = "Allow"
    resources = [
      "arn:aws:glue:${data.aws_region.current.name}:${data.aws_caller_identity.current.account_id}:*"
    ]
  }
}

resource "aws_iam_policy" "glue" {
  name   = "fivetran-${var.tenant}-glue"
  policy = data.aws_iam_policy_document.glue.json
}

data "aws_iam_policy_document" "fivetran_trust" {
  statement {
    actions = ["sts:AssumeRole"]
    effect  = "Allow"
    condition {
      test     = "StringEquals"
      variable = "sts:ExternalId"
      values   = [fivetran_group.this.id]
    }
    principals {
      identifiers = [local.fivetran_aws_account_id]
      type        = "AWS"
    }
  }
}

resource "aws_iam_role" "s3" {
  name               = "fivetran-${var.tenant}-s3"
  assume_role_policy = data.aws_iam_policy_document.fivetran_trust.json
  managed_policy_arns = [
    aws_iam_policy.glue.arn,
    aws_iam_policy.s3.arn
  ]
}

################################################################################
# Fivetran
################################################################################
resource "fivetran_group" "this" {
  name = "${var.tenant}_foo"
}

resource "fivetran_destination" "this" {
  group_id         = fivetran_group.this.id
  service          = "new_s3_datalake"
  region           = "AWS_US_EAST_1"
  time_zone_offset = "-5"
  run_setup_tests  = "true"

  config {
    bucket            = var.s3_bucket
    fivetran_role_arn = aws_iam_role.s3.arn
    prefix_path       = local.path
    region            = data.aws_s3_bucket.this.region
  }
}

I'm calling this with:

# main.tf

module "fivetran_foo_s3_destination" {
  for_each = toset(["test001"])
  source = "./modules/fivetran-foo-s3-destination"

  tenant    = each.key
  s3_bucket = "REDACTED"
}

And the logs when I run terraform apply:

➜  terraform-test git:(main) ✗ tf apply -auto-approve 
module.fivetran_foo_s3_destination["test001"].data.aws_s3_bucket.this: Reading...
module.fivetran_foo_s3_destination["test001"].data.aws_caller_identity.current: Reading...
module.fivetran_foo_s3_destination["test001"].data.aws_region.current: Reading...
module.fivetran_foo_s3_destination["test001"].data.aws_region.current: Read complete after 0s [id=us-east-1]
module.fivetran_foo_s3_destination["test001"].data.aws_caller_identity.current: Read complete after 0s [id=REDACTED]
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.glue: Reading...
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.glue: Read complete after 0s [id=575410248]
module.fivetran_foo_s3_destination["test001"].data.aws_s3_bucket.this: Read complete after 0s [id=REDACTED]
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.s3: Reading...
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.s3: Read complete after 0s [id=2949107403]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create
 <= read (data resources)

Terraform will perform the following actions:

  # module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.fivetran_trust will be read during apply
  # (config refers to values not yet known)
 <= data "aws_iam_policy_document" "fivetran_trust" {
      + id   = (known after apply)
      + json = (known after apply)

      + statement {
          + actions = [
              + "sts:AssumeRole",
            ]
          + effect  = "Allow"

          + condition {
              + test     = "StringEquals"
              + values   = [
                  + (known after apply),
                ]
              + variable = "sts:ExternalId"
            }

          + principals {
              + identifiers = [
                  + "834469178297",
                ]
              + type        = "AWS"
            }
        }
    }

  # module.fivetran_foo_s3_destination["test001"].aws_iam_policy.glue will be created
  + resource "aws_iam_policy" "glue" {
      + arn         = (known after apply)
      + id          = (known after apply)
      + name        = "fivetran-test001-glue"
      + name_prefix = (known after apply)
      + path        = "/"
      + policy      = jsonencode(
            {
              + Statement = [
                  + {
                      + Action   = [
                          + "glue:UpdateTable",
                          + "glue:UpdateDatabase",
                          + "glue:GetTables",
                          + "glue:GetTable",
                          + "glue:GetDatabases",
                          + "glue:GetDatabase",
                          + "glue:DeleteTable",
                          + "glue:DeleteDatabase",
                          + "glue:CreateTable",
                          + "glue:CreateDatabase",
                          + "glue:BatchDeleteTable",
                        ]
                      + Effect   = "Allow"
                      + Resource = "arn:aws:glue:us-east-1:REDACTED:*"
                    },
                ]
              + Version   = "2012-10-17"
            }
        )
      + policy_id   = (known after apply)
      + tags_all    = (known after apply)
    }

  # module.fivetran_foo_s3_destination["test001"].aws_iam_policy.s3 will be created
  + resource "aws_iam_policy" "s3" {
      + arn         = (known after apply)
      + id          = (known after apply)
      + name        = "fivetran-test001-s3"
      + name_prefix = (known after apply)
      + path        = "/"
      + policy      = jsonencode(
            {
              + Statement = [
                  + {
                      + Action   = [
                          + "s3:ReplicateObject",
                          + "s3:PutObjectTagging",
                          + "s3:PutObjectAcl",
                          + "s3:PutObject",
                          + "s3:ListBucket",
                          + "s3:GetObjectAcl",
                          + "s3:GetObject",
                          + "s3:DeleteObjectVersion",
                          + "s3:DeleteObjectTagging",
                          + "s3:DeleteObject",
                        ]
                      + Effect   = "Allow"
                      + Resource = [
                          + "arn:aws:s3:::REDACTED/test001/foos/*",
                          + "arn:aws:s3:::REDACTED",
                        ]
                    },
                ]
              + Version   = "2012-10-17"
            }
        )
      + policy_id   = (known after apply)
      + tags_all    = (known after apply)
    }

  # module.fivetran_foo_s3_destination["test001"].aws_iam_role.s3 will be created
  + resource "aws_iam_role" "s3" {
      + arn                   = (known after apply)
      + assume_role_policy    = (known after apply)
      + create_date           = (known after apply)
      + force_detach_policies = false
      + id                    = (known after apply)
      + managed_policy_arns   = (known after apply)
      + max_session_duration  = 3600
      + name                  = "fivetran-test001-s3"
      + name_prefix           = (known after apply)
      + path                  = "/"
      + tags_all              = (known after apply)
      + unique_id             = (known after apply)
    }

  # module.fivetran_foo_s3_destination["test001"].fivetran_destination.this will be created
  + resource "fivetran_destination" "this" {
      + group_id           = (known after apply)
      + id                 = (known after apply)
      + region             = "AWS_US_EAST_1"
      + run_setup_tests    = true
      + service            = "new_s3_datalake"
      + setup_status       = (known after apply)
      + time_zone_offset   = "-5"
      + trust_certificates = (known after apply)
      + trust_fingerprints = (known after apply)

      + config {
          + always_encrypted         = (known after apply)
          + auth                     = (known after apply)
          + auth_type                = (known after apply)
          + bucket                   = "REDACTED"
          + cloud_provider           = (known after apply)
          + connection_method        = (known after apply)
          + connection_type          = (known after apply)
          + create_external_tables   = (known after apply)
          + data_format              = (known after apply)
          + enable_remote_execution  = (known after apply)
          + external_id              = (known after apply)
          + fivetran_role_arn        = (known after apply)
          + is_private_key_encrypted = (known after apply)
          + is_private_link_required = (known after apply)
          + is_redshift_serverless   = (known after apply)
          + num_of_partitions        = (known after apply)
          + port                     = (known after apply)
          + prefix_path              = "test001/foos/"
          + public_key               = (known after apply)
          + region                   = "us-east-1"
          + replication_factor       = (known after apply)
          + sasl_mechanism           = (known after apply)
          + schema_compatibility     = (known after apply)
          + schema_registry          = (known after apply)
          + security_protocol        = (known after apply)
          + snowflake_cloud          = (known after apply)
          + tunnel_port              = (known after apply)
        }
    }

  # module.fivetran_foo_s3_destination["test001"].fivetran_group.this will be created
  + resource "fivetran_group" "this" {
      + created_at   = (known after apply)
      + id           = (known after apply)
      + last_updated = (known after apply)
      + name         = "test001_foos"
    }

Plan: 5 to add, 0 to change, 0 to destroy.
module.fivetran_foo_s3_destination["test001"].fivetran_group.this: Creating...
module.fivetran_foo_s3_destination["test001"].fivetran_group.this: Creation complete after 0s [id=twerp_cette]
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.fivetran_trust: Reading...
module.fivetran_foo_s3_destination["test001"].aws_iam_policy.s3: Creating...
module.fivetran_foo_s3_destination["test001"].data.aws_iam_policy_document.fivetran_trust: Read complete after 0s [id=21447609]
module.fivetran_foo_s3_destination["test001"].aws_iam_policy.glue: Creating...
module.fivetran_foo_s3_destination["test001"].aws_iam_policy.s3: Creation complete after 0s [id=arn:aws:iam::REDACTED:policy/fivetran-test001-s3]
module.fivetran_foo_s3_destination["test001"].aws_iam_policy.glue: Creation complete after 0s [id=arn:aws:iam::REDACTED:policy/fivetran-test001-glue]
module.fivetran_foo_s3_destination["test001"].aws_iam_role.s3: Creating...
module.fivetran_foo_s3_destination["test001"].aws_iam_role.s3: Creation complete after 1s [id=fivetran-test001-s3]
module.fivetran_foo_s3_destination["test001"].fivetran_destination.this: Creating...
module.fivetran_foo_s3_destination["test001"].fivetran_destination.this: Creation complete after 2s [id=twerp_cette]
╷
│ Warning: Setup Tests for destination failed on creation. Running post-creation attempt.
│ 
│   with module.fivetran_foo_s3_destination["test001"].fivetran_destination.this,
│   on modules/fivetran-foo-s3-destination/main.tf line 126, in resource "fivetran_destination" "this":
│  126: resource "fivetran_destination" "this" {
│ 
│ [{S3 Read and write access test FAILED com.amazonaws.services.securitytoken.model.AWSSecurityTokenServiceException: User: arn:aws:iam::834469178297:user/REDACTED
│ is not authorized to perform: sts:AssumeRole on resource: arn:aws:iam::REDACTED:role/fivetran-test001-s3 (Service: AWSSecurityTokenService; Status Code:
│ 403; Error Code: AccessDenied; Request ID: 3f888dbb-9622-430f-9452-033b45f91efd; Proxy: null)}]
╵
╷
│ Warning: Setup Tests for destination failed.
│ 
│   with module.fivetran_foo_s3_destination["test001"].fivetran_destination.this,
│   on modules/fivetran-foo-s3-destination/main.tf line 126, in resource "fivetran_destination" "this":
│  126: resource "fivetran_destination" "this" {
│ 
│ [{S3 Read and write access test FAILED com.amazonaws.services.securitytoken.model.AWSSecurityTokenServiceException: User: arn:aws:iam::834469178297:user/REDACTED
│ is not authorized to perform: sts:AssumeRole on resource: arn:aws:iam::REDACTED:role/fivetran-test001-s3 (Service: AWSSecurityTokenService; Status Code:
│ 403; Error Code: AccessDenied; Request ID: 04fef41c-5fb5-4544-a90b-40a489b60f1b; Proxy: null)}]
╵

Apply complete! Resources: 5 added, 0 changed, 0 destroyed.

ann8ty commented 5 months ago

I've switched over to SshTunnel, but ran into an issue where the config.public_key is now blank, which I will log as https://github.com/fivetran/terraform-provider-fivetran/issues/274

ann8ty commented 5 months ago

working as of 1.1.16

fivetran / terraform-provider-fivetran

New Destination does not run setup tests (redshift) #177