GCP 403 The caller does not have permission for create_transfer_config

Question

I am working on GCP and trying to schedule queries in BigQuery by creating transfer configs through programming and required permissions are assigned to the new service account (bigquery.transfers.get, bigquery.transfers.update) and trying to create transfer config using below code. I am able get the information about other already created schedule queries and able to update them. But I am unable to create them. Getting 403 caller does not have permission.

from google.cloud import bigquery_datatransfer

transfer_client = bigquery_datatransfer.DataTransferServiceClient()

project_id = "My_Project_Id"
dataset_id = "My_dataset_id"
service_account_name = "<serviceAccount>"
query_string = "update dataservices.temp_bq_schedule set current_time=current_timestamp() where some_integer=17"

parent = transfer_client.common_project_path(project_id)

transfer_config = bigquery_datatransfer.TransferConfig(
    destination_dataset_id=dataset_id,
    display_name="Test_Schedule_QUERY",
    data_source_id="scheduled_query",
    params={
        "query": query_string,
        "write_disposition": "",
        "partitioning_field": "",
    },
    schedule="every 24 hours",
)
transfer_config = transfer_client.create_transfer_config(
    bigquery_datatransfer.CreateTransferConfigRequest(
        parent=parent,
        transfer_config=transfer_config,
        service_account_name=service_account_name,
    )
)

print("Created scheduled query '{}'".format(transfer_config.name))

This is the error while executing the code

Traceback (most recent call last):
  File "/env/lib/python3.7/site-packages/google/api_core/grpc_helpers.py", line 66, in error_remapped_callable
    return callable_(*args, **kwargs)
  File "/env/lib/python3.7/site-packages/grpc/_channel.py", line 946, in __call__
    return _end_unary_response_blocking(state, call, False, None)
  File "/env/lib/python3.7/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
    raise _InactiveRpcError(state)
grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
        status = StatusCode.PERMISSION_DENIED
        details = "The caller does not have permission"
        debug_error_string = "{"created":"@1637593645.956604992","description":"Error received from peer ipv4:<some ip address with port>","file":"src/core/lib/surface/call.cc","file_line":1063,"grpc_message":"The caller does not have permission","grpc_status":7}"
>

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "merge_query.py", line 36, in <module>
    service_account_name=service_account_name,
  File "/env/lib/python3.7/site-packages/google/cloud/bigquery_datatransfer_v1/services/data_transfer_service/client.py", line 646, in create_transfer_config
    response = rpc(request, retry=retry, timeout=timeout, metadata=metadata,)
  File "/env/lib/python3.7/site-packages/google/api_core/gapic_v1/method.py", line 154, in __call__
    return wrapped_func(*args, **kwargs)
  File "/env/lib/python3.7/site-packages/google/api_core/grpc_helpers.py", line 68, in error_remapped_callable
    raise exceptions.from_grpc_error(exc) from exc
google.api_core.exceptions.PermissionDenied: 403 The caller does not have permission

FYI, I also tried omitting the service_account but still the same issue.

And code snippet can be found here https://github.com/googleapis/python-bigquery-datatransfer/blob/main/samples/snippets/scheduled_query.py

Answer 1

You also need to make sure that the service account should come from the same project as where the scheduled query runs. Otherwise it won't work. I found that out the hard way.

And of course make sure that you as a user and the service account have all permissions that are needed, which are described here:
https://cloud.google.com/bigquery/docs/scheduling-queries#required_permissions

See also: How to show and change user in Scheduled Queries

Answer 2

This error is about permissions. You need these permissions below.

To schedule a query, you need the following Identity and Access Management (IAM) permissions:

• bigquery.transfers.update or both bigquery.jobs.create and bigquery.transfers.get to create the transfer
• bigquery.jobs.create to run the scheduled query
• bigquery.datasets.update on the target dataset

To modify a scheduled query, you must be the creator of the schedule and have the following permissions:

• bigquery.jobs.create
• bigquery.transfers.update

You can see more details about permissions.

You can see this example code.

from google.cloud import bigquery_datatransfer
 
transfer_client = bigquery_datatransfer.DataTransferServiceClient()
 
# The project where the query job runs is the same as the project
# containing the destination dataset.
project_id = "your-project-id"
dataset_id = "your_dataset_id"
 
# This service account will be used to execute the scheduled queries. Omit
# this request parameter to run the query as the user with the credentials
# associated with this client.
service_account_name = "abcdef-test-sa@abcdef-test.iam.gserviceaccount.com"
 
# Use standard SQL syntax for the query.
query_string = """
SELECT
  CURRENT_TIMESTAMP() as current_time,
  @run_time as intended_run_time,
  @run_date as intended_run_date,
  17 as some_integer
"""
 
parent = transfer_client.common_project_path(project_id)
 
transfer_config = bigquery_datatransfer.TransferConfig(
    destination_dataset_id=dataset_id,
    display_name="Your Scheduled Query Name",
    data_source_id="scheduled_query",
    params={
        "query": query_string,
        "destination_table_name_template": "your_table_{run_date}",
        "write_disposition": "WRITE_TRUNCATE",
        "partitioning_field": "",
    },
    schedule="every 24 hours",
)
 
transfer_config = transfer_client.create_transfer_config(
    bigquery_datatransfer.CreateTransferConfigRequest(
        parent=parent,
        transfer_config=transfer_config,
        service_account_name=service_account_name,
    )
)
 
print("Created scheduled query '{}'".format(transfer_config.name))

You can see more details about the code.

You can see more information about working with transfers.