'403 Permission denied while getting Drive credentials' when using Deployment Manager to create an 'external table' in BigQuery

Question

Steps to reproduce:

• Create sheet in Google Sheets
• Enable Deployment Manager & Google Drive API in Google Cloud Platform
• add deployment manager service-account with view permissions on sheet
• Create dataset with deployment manager
• Create table with deployment manager, reference external sheet in sourceUris

partial python template:

def GenerateConfig(context):
    name: str = context.env['name']
    dataset: str = context.properties['dataset']
    tables: [] = context.properties['tables']
    location: str = context.properties.get('location', 'EU')
    resources = [{
        'name': name,
        'type': 'gcp-types/bigquery-v2:datasets',
        'properties': {
            'datasetReference': {
                'datasetId': dataset,
            },
            'location': location
        },
    }]

    for t in tables:
        resources.append({
            'name': '{}-tbl'.format(t["name"]),
            'type': 'gcp-types/bigquery-v2:tables',
            'properties': {
                'datasetId': dataset,
                'tableReference': {
                    'tableId': t["name"]
                },
                'externalDataConfiguration': {
                    'sourceUris': ['https://docs.google.com/spreadsheets/d/123123123123123-123123123123/edit?usp=sharing'],
                    'sourceFormat': 'GOOGLE_SHEETS',
                    'autodetect': True,
                    'googleSheetsOptions':
                        {
                            "skipLeadingRows": '1',
                        }
                }
            },
        })
    return {'resources': resources}

I've found a few leads such as this, but they all reference using 'scopes' to add https://www.googleapis.com/auth/drive. I'm not sure of how to add scopes to a deployment manager request, or really how scopes work.

Any help would be appreciated.

Answer 1

Yes, using scopes solves the problem. However, even after adding the scopes, I was facing the same error. Sharing the google sheets document with the GCP service account helped me get rid of this error.

To summarize - use scopes and share the document with the GCP service account that you will use for querying the table.

Also, this document is helpful for querying external tables

Answer 2

I was having the same issue when running Airflow DAGs on Cloud Composer, which is the managed Airflow service on Google Cloud Platform.

Essentially you need to:

1. Share the file with the email of the service account (give Viewer or Editor permissions based on what the DAG is supposed to execute)
2. Enable Google Drive OAuth Scopes

Depending on the Cloud Composer version you are using, the second step should be executed in a slightly different way:

---

For Cloud Composer 1

You should add the Google Drive OAuth Scope through the User Interface:

• "https://www.googleapis.com/auth/drive"

Alternatively, if you are using Infrastructure as a Code (e.g. Terraform), you can specify oauth_scopes as shown below:

config {
    ...
    node_config {
        ...
        oauth_scopes = [
            "https://www.googleapis.com/auth/drive",
        ]

    }
}

---

For Cloud Composer 2

Since Cloud Composer v2 uses GKE Autopilot, it does not support OAuth on the environment level. You can however specify the scope at the connection level, that is being used by your Airflow Operator in order to initiate the connection.

If you are using the default GCP connection (i.e. google_cloud_default which is automatically created upon deployment of the Cloud Composer instance), then all you need to do is specify Google Drive ("https://www.googleapis.com/auth/drive") in the scopes of the connection (through Airflow Connections UI).

Alternatively, you can even create your new connection and once again specify the Google Drive in the scopes field and then pass the name of this connection in the gcp_conn_id argument of your Operator.

Answer 3

GOT IT!

I could solve by forcing the owner login/credentials. It looks like the service account won't acccess the drive. Using gcloud auth login --enable-gdrive-access --update-adc it does the login with your account and replace the json file. Now it's working for me.