[Python] Bigquery: "User does not have bigquery.jobs.create permission" with...

Background: I have been developing a backend for the last few days and would like to push some analytic data to BigQuery using a cloud function triggered by Firestore. I am using Terraform for the deployment.

Problem: The python script works locally with my user account (owner) without any problems. But when I use the cloud function, it cannot perform a query. For some reason, the insert works, but the query does not. I always get the error User does not have bigquery.jobs.create permission and an exception is thrown, even though the function service account has the permission "roles/bigquery.admin" in the same project.

However, this works (python):

errors = client.insert_rows_json(table_id_users, user_data)


But this does not:

query = f"SELECT user_id FROM `project-xyz.analytics.users` WHERE user_id = '{user_id}'"
query_job = client.query(query)
result = list(query_job.result())


Terraform Code for the permissions:

resource "google_bigquery_dataset_iam_binding" "editor" {
dataset_id = google_bigquery_dataset.default.dataset_id
role = "roles/bigquery.admin"

members = [
"serviceAccount:${module.my_function_2.service_account_email}",
]
}


When I click on the permission settings on the BigQuery table, I can see that the service account is listed in the BigQuery-Administrator section, which should have the bigquery.jobs.create permission.

The service account logs also says that the user account has the permission "bigquery.jobs.create" but in the same log entry the error appears:

authorizationInfo: [
0: {
resource: "projects/project-xyz"
permission: "bigquery.jobs.create"
resourceAttributes: {0}
}
]
...
...
status: {
code: 7,
message: "Access Denied: Project higym-ecafb: User does not have bigquery.jobs.create permission in project project-xyz."
}


For me it looks like everything should be fine and my function has all the permissions to do a query (the insert works), but in the end it does not work.

I also noticed that the service account created with Terraform does not appear in the IAM view. Only on the service accounts page they are visible.

Is there anything else I can try? What are the next steps on how I can debug this problem? Maybe i have overlooked something fundamental.

Thank You for your help!

Edit: I found the problem. In teraform the role must be specified for iam and bigquery. Only one of them is not enough.

resource "google_bigquery_dataset_iam_member" "admin" {
dataset_id = google_bigquery_dataset.default.dataset_id
role = "roles/bigquery.admin"

member = "serviceAccount:${module.my_function_2.service_account_email}"
}

resource "google_project_iam_member" "admin" {
project = var.project_id
role = "roles/bigquery.admin"
member = "serviceAccount:${module.my_function_2.service_account_email}"
}

Continue reading...