I am trying to use the service catalog of Google Kubernetes to connect to BigQuery. I had however a lot of issues regarding IAM/ACL permissions.

I added the Owner role to the myProjectId@cloudservices.gserviceaccount.com account, since Editor was not enough to access IAM during the creation of a binding's service account.

After manually adding projectReaders, projectWriters and projectOwners to the ACL of the dataset, I could finally read and write to BigQuery, but I can not create jobs, since this requires project permissions. The command to update the dataset was

bq update --source /tmp/roles myDatasetId

After that I tried to query bq, but it failed with

root@batch-shell:/app#   cat sql/xxx.sql | bq query --format=none --allow_large_results=true --destination_table=myDatasetId.pages_20180730 --maximum_billing_tier 3

BigQuery error in query operation: Access Denied: Project my-staging-project: The user k8s-bigquery-acc@my-staging-project.iam.gserviceaccount.com does not have
bigquery.jobs.create permission in project my-staging-project.

I tried to set the account's role to "Owner" and "BigQuery Job User" with no effect. I even tried all the other accounts as Owner.

This are my current ACL permissions:

[16:52:45] blackfalcon:~/src/myproject/batch :chris $ bq --format=prettyjson show myDatasetId

{
  "access": [
    {
      "role": "WRITER",
      "specialGroup": "projectWriters"
    },
    {
      "role": "OWNER",
      "specialGroup": "projectOwners"
    },
    {
      "role": "OWNER",
      "userByEmail": "myProjectId@cloudservices.gserviceaccount.com"
    },
    {
      "role": "OWNER",
      "userByEmail": "k8s-bigquery-acc@my-staging-project.iam.gserviceaccount.com"
    },
    {
      "role": "READER",
      "specialGroup": "allAuthenticatedUsers"
    },
    {
      "role": "READER",
      "specialGroup": "projectReaders"
    }
  ],
  "creationTime": "1532859638248",
  "datasetReference": {
    "datasetId": "myDatasetId",
    "projectId": "my-staging-project"
  },
  "defaultTableExpirationMs": "8000000000",
  "description": "myproject Access myDatasetId",
  "id": "my-staging-project:myDatasetId",
  "kind": "bigquery#dataset",
  "lastModifiedTime": "1533184961736",
  "location": "US",
  "selfLink": "https://www.googleapis.com/bigquery/v2/projects/my-staging-project/datasets/myDatasetId"
}

[16:53:02] blackfalcon:~/src/myproject/batch :chris $ gcloud projects get-iam-policy my-staging-project bindings:

- members:
  - serviceAccount:k8s-bigquery-acc@my-staging-project.iam.gserviceaccount.com
  - user:myemail@somedomain.com
  role: roles/bigquery.admin
- members:
  - serviceAccount:k8s-cloudsql-acc-staging@my-staging-project.iam.gserviceaccount.com
  role: roles/cloudsql.client
- members:
  - serviceAccount:service-myProjectId@compute-system.iam.gserviceaccount.com
  role: roles/compute.serviceAgent
- members:
  - serviceAccount:service-myProjectId@container-engine-robot.iam.gserviceaccount.com
  role: roles/container.serviceAgent
- members:
  - serviceAccount:myProjectId-compute@developer.gserviceaccount.com
  - serviceAccount:myProjectId@cloudservices.gserviceaccount.com
  - serviceAccount:service-myProjectId@containerregistry.iam.gserviceaccount.com
  role: roles/editor
- members:
  - serviceAccount:service-myProjectId@cloud-ml.google.com.iam.gserviceaccount.com
  role: roles/ml.serviceAgent
- members:
  - serviceAccount:myProjectId@cloudservices.gserviceaccount.com
  - user:myemail@somedomain.com
  role: roles/owner
- members:
  - serviceAccount:scg-fv6fz3sjnxo3cfpppcl2qs5edm@my-staging-project.iam.gserviceaccount.com
  role: roles/servicebroker.operator
- members:
  - serviceAccount:service-myProjectId@gcp-sa-servicebroker.iam.gserviceaccount.com
  role: roles/servicebroker.serviceAgent
- members:
  - serviceAccount:k8s-bigquery-acc@my-staging-project.iam.gserviceaccount.com
  - user:myemail@somedomain.com
  role: roles/storage.admin
version: 1

It seems I need to set the projects ACL for BigQuery, but everything I found indicates, that setting the roles with IAM should be enough

Any help would be greatly appreciated.

UPDATE: I solved that for now.

Turns out that the service account itself was not working properly. I tried giving an Owner role to the service account and used the service account locally to access a few gcloud resources, all failed with permission errors. I created then a new service account with the same permissions and tried again and it worked. So, it seems the service account was somehow broken.

I deleted the bindings, then the IAM and service account and rebuild the bindings.

Now it is working like a charm