Streaming data to BQ - ideal least privilege needed for the Service Account

Question

Streaming data to BQ - https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll

May I ask why BigQuery Metadata Viewer access is needed on the Dataset? (despite destination tale already exists)

With just BigQuery Data Editor at Table level, it gives the following error: google.api_core.exceptions.Forbidden: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT/datasets/DATASET/tables/TABLE/insertAll: Access Denied: Dataset PROJECT:DATASET: User does not have bigquery.datasets.get permission for dataset PROJECT:DATASET.

===== UPDATE:

using Python Client Library google-cloud-bigquery==1.28.0 here's minimal code

table_ref = bigquery.DatasetReference(project = BQ_PROJECT, dataset_id = BQ_DATASET).table(table_id = BQ_TABLE)
table =  client.get_table(table_ref)

rows_to_insert = [
    {"col1": "streaming-r1", "col2": "A", "col3": "3"},
    {"col1": "streaming-r2-", "col2": "B", "col4": 1},
]


stream_resp = client.insert_rows(table_ref,rows_to_insert,
                                 selected_fields=table.schema)

I believe your Service Account must check if the table exists before inserting into it, so it's not abnormal that it needs bigquery.datasets.get permission on the dataset (included in BigQuery Metadata Viewer). It seems that granting access only on the table is not sufficient. — norbjd
Do you use directly the API or do you use a Client Library? If a client library, can you share the language that you use? (and a piece of code) — guillaume blaquiere
@guillaumeblaquiere i updated my question with minimal code. — Vibhor Jain
@norbjd that would make sense. can you share a piece of code that that does this check (I cold not locate in PY client library)... but wondering why it needs to work that way. If the table doesn't exists, the response could just throw the error... I can imagine a scenario where Project Owner not willing to give me any Dataset level access when in-fact my code only streams to a specific table. — Vibhor Jain
I checked in the library. No strange call. It's a requirement of the API. — guillaume blaquiere

rodvictor rodvictor · Accepted Answer · 2020-11-17T16:31:17

I have created the following Feature Request in Google Cloud Platform (1) about your suggestion of being able to insert data into a table without dataset permissions.

This can be helpful when the Project owner does not want to give everyone dataset access for security reasons. Instead, it would be useful to be thrown an exception if the table does not exist without needing 'bigquery.datasets.get' permission.

Make sure you upvote it (2) so it is more likely to be implemented. You can also subscribe (3) to the feature request so you can get notified every time there is a new message on the thread.

Streaming data to BQ - ideal least privilege needed for the Service Account

1 Answers