Connecting dbt cloud to BigQuery via WIF won't work. GCP Policy override for BQ Service Account key creation. #169
Description
Course
data-engineering-zoomcamp
Question
04-analytics-engineering
When setting up dbt cloud environment, we need to create a connection to BigQuery.
If you are still not able to create JSON key for your BQ (or other GCP) service account, and attempting to use Workload Identity Federation route -- DON'T!
I was going mad, trying many differnet hacks and setups in GCP. Save button simply always turned "retry" in dbt.
[FAILED] Workload Identity Federeation route:
-
Setup a pool in GCP with auth.cloud.getdbt.com as link:
<your_wif_pool_id> -
Create a pricipal uder your GCP service account:
"de-zoomcamp-runner@<full_gcp_project_id>.iam.gserviceaccount.com"
with Service Token Creator permissions on top of BigQuery, Compute, Storage Admin roles."principalset://iam.googleapis.com/projects/<digits_only_project_id>/locations/global/workloadIdentityPools/<your_wif_pool_id>/*"
this service account "principal" user has Workload Identity User role as well as Service Account Token Creator.gcloud projects describe <full_gcp_project_id> --format="value(projectNumber)"
returns digits only GCP project id.
-
Create OAuth Client ID with Secret for dbt.
-
Try to create Development environemnt in dbt cloud, enter your project-id, in Optional, enter service accoutn to impersonate.
-
FAIL regardless...
Answer
The only solution is to bruteforce delete the iam.disableServiceAccountKeyCreation
- Add yourself "Service Account Key Admin" and "Organization Policy Administrator" role at organization level.
- Manually disabling both legacy and enforced policies does not work for some reason.
- This GCP CLI command does it!
gcloud org-policies delete iam.disableServiceAccountKeyCreation --organization=[your-org-id]Finally, can create JSON key for BQ service account!
Proceed with normal sbt cloud setup in Module 04-analytics-engineering
Checklist
- I have searched existing FAQs and this question is not already answered
- The answer provides accurate, helpful information
- I have included any relevant code examples or links