Reputation: 111
I'm testing the required permissions to create a scheduled query on BigQuery.
The scheduled query will be programmatically created in project1
with a service account (sa1@project1.iam.gserviceaccount.com
) that I'm able to impersonate.
The scheduled query will run as another service account from project2
(sa2@project2.iam.gserviceaccount.com
).
I have granted the following roles:
sa1@project1.iam.gserviceaccount.com
on project1
sa1@project1.iam.gserviceaccount.com
on sa2@project2.iam.gserviceaccount.com
sa2@project2.iam.gserviceaccount.com
on target dataset (test_dataset
)sa2@project2.iam.gserviceaccount.com
on project1
sa2@project2.iam.gserviceaccount.com
on organization (because the query is using the region-us.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
view)I'm running the following while connected to project1
(the target dataset is in this project).
I also tried achieving the same with terraform, with the same error message.
gcloud config set auth/impersonate_service_account sa1@project1.iam.gserviceaccount.com
bq mk \
--transfer_config \
--target_dataset=test_dataset \
--display_name='test bq scheduled query' \
--params='{"destination_table_template":"test_jobs", "write_disposition":"WRITE_APPEND", "query":"SELECT job_id FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION` WHERE DATE_TRUNC(creation_time, DAY) = '2025-01-22'"}' \
--data_source=scheduled_query \
--service_account_name=sa2@project2.iam.gserviceaccount.com
I get the following output:
Updated property [auth/impersonate_service_account].
WARNING: This command is using service account impersonation. All API
calls will be executed as [sa1@project1.iam.gserviceaccount.com].
BigQuery error in mk operation:
Requesting user sa1@project1.iam.gserviceaccount.com does not have
iam.serviceAccounts.actAs permission to act as service account
sa2@project2.iam.gserviceaccount.com
The error is a lie, since sa1@project1.iam.gserviceaccount.com
has the Service Account User role on sa2@project2.iam.gserviceaccount.com
and this role includes iam.serviceAccounts.actAs
permission.
Is there something I'm missing here? GCP support is driving me crazy, not being helpful at all.
(service account, dataset and project names have been changed)
Upvotes: 0
Views: 80
Reputation: 31
I set the constraint to Not Enforced in project2 and I was able to execute the command successfully.
This answer works for us, but in our case, we have to change the constraint to 'Not Enforced' in project1
.
Upvotes: 0
Reputation: 111
Finally, Google support guided me to the answer.
The iam.disableCrossProjectServiceAccountUsage
constraint (organization policy) was Enforced (as it is by default apparently), preventing me from attaching a service account from project2
to a resource in project1
.
I set the constraint to Not Enforced in project2
and I was able to execute the command successfully.
The error message is very misleading and the logs didn't hint towards the constraint either.
Upvotes: 1