Setting up a scheduled query with a service account error PermissionDenied: 403 The caller does not have permission in data_transfer_service

user_5800969 0
Viewed 549

I am trying to schedule query using big query data transfer api and giving required permission bigquery.admin and enabled the big query transfer api. Permission Documentation: https://cloud.google.com/bigquery-transfer/docs/enable-transfer-service Also tried with project owner permission to the service account. But still giving same error.

Code Documentation: (Setting up a scheduled query with a service account) https://cloud.google.com/bigquery/docs/scheduling-queries

Part in which error coming

 transfer_config = transfer_client.create_transfer_config(
            bigquery_datatransfer.CreateTransferConfigRequest(
                parent=parent,
                transfer_config=transfer_config,
                service_account_name=service_account_name,
            )
        )

Error StackTrace

Traceback (most recent call last):
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/grpc_helpers.py", line 73, in error_remapped_callable
    return callable_(*args, **kwargs)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/grpc/_channel.py", line 946, in __call__
    return _end_unary_response_blocking(state, call, False, None)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/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":"@1633536014.842657676","description":"Error received from peer ipv4:142.250.192.138:443","file":"src/core/lib/surface/call.cc","file_line":1070,"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 "__main__.py", line 728, in <module>
    mbc.schedule_query()
  File "/home/ubuntu/prod/trell-ds-framework/data_engineering/data_migration/schedule_quries.py", line 62, in schedule_query
    service_account_name=service_account_name,
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/cloud/bigquery_datatransfer_v1/services/data_transfer_service/client.py", line 647, in create_transfer_config
    response = rpc(request, retry=retry, timeout=timeout, metadata=metadata,)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/gapic_v1/method.py", line 145, in __call__
    return wrapped_func(*args, **kwargs)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/grpc_helpers.py", line 75, in error_remapped_callable
    six.raise_from(exceptions.from_grpc_error(exc), exc)
  File "<string>", line 3, in raise_from
google.api_core.exceptions.PermissionDenied: 403 The caller does not have permission

Service file have all these credentials below.

BigQuery Admin

BigQuery Data Transfer Service Agent

Service Account Token Creator

Storage Admin

I am already setting up json authentication cred in environment variable but still gives permission error.

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = Constants.BIG_QUERY_SERVICE_ACCOUNT_CRED

Can anyone help me out here? Thanks in advance.

google-cloud-data-transfer python-3.6 google-bigquery google-cloud-platform
2 Answers

Take a look at this page on authentication: https://cloud.google.com/bigquery/docs/authentication/service-account-file#python

Assuming you're using Service Account, you can provide the credentials explicitly to confirm they work as expected:

from google.cloud import bigquery
from google.oauth2 import service_account

# TODO(developer): Set key_path to the path to the service account key
#                  file.
# key_path = "path/to/service_account.json"

credentials = service_account.Credentials.from_service_account_file(
    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
)

client = bigquery.Client(credentials=credentials, project=credentials.project_id,)
user_116979870

I would recommend you to see if the service account you are using is referring to the project you are using and has all the permissions needed to schedule the query. My best guess is that you are pointing to another project with the service account.

Also, you need one extra role for the service account that is the next one “Service Account Token Creator”.

user_107828090
Related