Connector Improvement: Fivetran service account should not have bigquery.datasets.create IAM permission
Not planned
Currently, Fivetran wants to create BigQuery datasets on my behalf. This is a problem because I want to manage IAM for these dataset using Terraform, which requires me to also create the datasets via Terraform.
My ideal workflow is as follows:
1. I use Terraform to create the BigQuery Dataset and grant the Fivetran service account permissions to create/update/delete tables and write to tables in the dataset.
2. I manage read-only permission on this dataset using Terraform (this is complicated if Fivetran creates the dataset).
The security principles I want in my GCP projects are as follows:
A. Nobody has admin access to modify IAM permissions except for the Terraform service account. (Fivetran is breaking this principle because by creating a dataset it implicitly gains roles/bigquery.dataOwner permission on the dataset, which includes IAM management permissions).
B. Permissions are granted on a least-privileged basis. Currently Fivetran is asking for `bigquery.datasets.create` permission at the project level, which is unnecessary because I want Terraform to create the dataset and only grant the necessary permissions to the Fivetran service account.
According to your docs, Fivetran supports writing data to an existing dataset:
This is what I've done with one of my destinations. However, Fivetran refuses to write to the dataset even after being granted `roles/bigquery.dataOwner` permission on the dataset because it doesn't have project-level `bigquery.datasets.create` permission.
My ask in this feature request is to simply remove this error about the Fivetran service account not having project-level `bigquery.datasets.create` permission when writing to an existing dataset.
-
Official comment
Hi Thinh,
Datasets (schemas) are dynamically created based on the source application configuration and are only known at the time each individual connector is set up. For database connectors, schemas are determined once the customer selects them during setup, while for SaaS connectors, the schema name is at connector creation.
In addition, we create temporary datasets to be used as staging areas for organizing new data before it is loaded into the destination tables.
An approach many customers prefer is to dedicate a separate area specifically for ingestion, as it provides a cleaner separation for security control. In your case, this could be a project dedicated to that purpose, while continuing to maintain your existing Terraform configuration for permissions in your primary project.
Best,
-
Hi Thinh,
Your request regarding removing the requirement for the Fivetran service account to have project-level
bigquery.datasets.createpermission when writing to an existing dataset has been added to our feature improvements backlog. This is a valuable idea, particularly for customers leveraging Terraform to manage BigQuery IAM and datasets.We will keep the community updated on this thread.
Thanks,
Amanda
Please sign in to leave a comment.
Comments
2 comments