Support BigQuery Omni external connection for AWS s3

[ismailsimsek]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment. If the issue is assigned to the "modular-magician" user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If the issue is assigned to a user, that user is claiming responsibility for the issue. If the issue is assigned to "hashibot", a community member has claimed the issue already.

Description

As we can create BigQuery external connections with Cloud SQL for federated query, we should support that with AWS as well.
similar request #11053

New or Affected Resource(s)

• google_bigquery_connection

Potential Terraform Configuration

resource "google_bigquery_connection" "connection" {
  provider      = google-beta
  connection_id = "connection_to_aws_s3"
  location      = "US"
  description   = "A BigQuery external connection for AWS "
  aws {
    AWS_ACCOUNT_ID = "the ID number of the connection's AWS IAM user."
    ROLE_NAME            = "the role policy name you chose."
    AWS_LOCATION      = "an AWS location in Google Cloud. Must be set to aws-us-east-1."
    CONNECTION_NAME= "the name you give this connection resource."
  }
}

References

• https://cloud.google.com/bigquery-omni/docs/aws/create-connection#bq

b/374162343

[voycey]

Are there any updates on how this works in practice?
With the release of BQ Omni and BigLake this is more important than ever in order to setup cross cloud external tables.

Specifically I can't see a way to define the connection role and the google_bigquery_connection without creating a cycle as it requires an iam role and that iam role requires the identity from google_bigquery_connection

Example:

resource "google_bigquery_connection" "connection" {
    provider      = google-beta
    connection_id = "bq-connection"
    location      = "aws-ap-southeast-1"
    friendly_name = "👋"
    description   = "BQ Omni Connection"
    aws {
      access_role {
         iam_role_id =  aws_iam_role.bigquery-omni-connection-role.arn
      }
    }
}

resource "aws_iam_role" "bigquery-omni-connection-role" {
    name                 = "bigquery-omni-connection"
    max_session_duration = 43200

    assume_role_policy = <<-EOF
    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Effect": "Allow",
          "Principal": {
            "Federated": "accounts.google.com"
          },
          "Action": "sts:AssumeRoleWithWebIdentity",
          "Condition": {
            "StringEquals": {
              "accounts.google.com:sub": ${google_bigquery_connection.connection.id}
            }
          }
        }
      ]
    }
    EOF
}

[DrFaust92]

rileykarson i think this can be closed.

voycey i think you will need to extract the name and "manually" construct the iam role arn when passing to the connection resource.

[chrisst]

I can confirm, there's really no way around the cycle. Because Omni is using web identity under the hood to make the auth connection between the two clouds it requires a bi-directional configuration. This is by definition always going to be a cycle. Unfortunately the best you can do is use string building for 1 direction and resource reference in the other direction. Here's how I've modeled and e2e AWS Omni connection: https://gist.github.com/chrisst/314d131aa42db685938dee24dea0f912, setting up the references this way, and throwing a sleep in there for IAM propagation, allows terraform to succeed in a single apply.

[voycey]

Thanks @chrisst - if I ever get back to Omni then I will definitely give this a go :) I did get this applying if you look in #12018 however I dont remember how far I got with it after!

[chrisst]

@voycey If you pick Omni back up let me know and I'm happy to help get it working. I think it's even picked up a few new features in the 2 years since you last tackle it 😅

[wj-chen]

This is available https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/bigquery_connection#nested_aws since GoogleCloudPlatform/magic-modules#6025.

Requesting the current terraform-team-oncall @roaks3 to close this issue.

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.