0

I posted an issue here: https://github.com/r-dbi/bigrquery/issues/487 and a similar one previously https://github.com/r-dbi/bigrquery/issues/449. They have not been solved. And, sadly, response to issues has dropped off to almost zero.

If anyone can help, that would be great.

My organization does not allow the use of service account keys. So I have to use email authorization.

On PC

> library(bigrquery)
> bq_deauth()
> bq_auth(email="ariel.balter@providence.org")
> conn = dbConnect(bigrquery::bigquery(), project="???????????", dataset="test_dataset")
> DBI::dbListTables(conn)
character(0)

On cloud VM

> library(bigrquery)
> bq_deauth()
> bq_auth(email="ariel.balter@providence.org")
> conn = dbConnect(bigrquery::bigquery(), project="??????????", dataset="test_dataset")
> DBI::dbListTables(conn)
Error: Access Denied: Dataset ??????????:test_dataset: Permission bigquery.tables.list denied on dataset ???????????:test_dataset (or it may not exist). [accessDenied]
Run `rlang::last_error()` to see where the error occurred.
> rlang::last_error()
<error/bigrquery_accessDenied>
Access Denied: Dataset ??????????:test_dataset: Permission bigquery.tables.list denied on dataset ????????:test_dataset (or it may not exist). [accessDenied]
Backtrace:
 1. DBI::dbListTables(conn)
 2. DBI::dbListTables(conn)
 3. bigrquery::bq_dataset_tables(ds, ...)
 4. bigrquery:::bq_get_paginated(...)
 5. bigrquery:::bq_get(url, ..., query = query, token = token)
 6. bigrquery:::process_request(req, raw = raw)
 7. bigrquery:::bq_check_response(status, type, content)
 8. bigrquery:::signal_reason(json$error$errors[[1L]]$reason, json$error$message)
Run `rlang::last_trace()` to see the full context.
> rlang::last_trace()
<error/bigrquery_accessDenied>
Access Denied: Dataset ?????????:test_dataset: Permission bigquery.tables.list denied on dataset ????????:test_dataset (or it may not exist). [accessDenied]
Backtrace:
    █
 1. ├─DBI::dbListTables(conn)
 2. └─DBI::dbListTables(conn)
 3.   └─bigrquery::bq_dataset_tables(ds, ...)
 4.     └─bigrquery:::bq_get_paginated(...)
 5.       └─bigrquery:::bq_get(url, ..., query = query, token = token)
 6.         └─bigrquery:::process_request(req, raw = raw)
 7.           └─bigrquery:::bq_check_response(status, type, content)
 8.             └─bigrquery:::signal_reason(json$error$errors[[1L]]$reason, json$error$message)
abalter
  • 9,663
  • 17
  • 90
  • 145
  • 1
    Is `bigrquery` [this](https://cran.r-project.org/web/packages/bigrquery/readme/README.html)? – DazWilkin Mar 02 '22 at 18:59
  • 1
    When you authenticate the library locally, it's using your (human|user) credentials probably obtained through your use of `gcloud` (Cloud SDK) locally. When you run the code on a VM, `gcloud` credentials aren't available to the library and you should not authenticate using them there. I'm unfamiliar with the library but, in its documentation, it likely explains how to authenticate using a [Service Account](https://cloud.google.com/iam/docs/service-accounts) and (hopefully) using [Application Default Credentials](https://cloud.google.com/docs/authentication/production#automatically) – DazWilkin Mar 02 '22 at 19:02
  • Aha [`bq_auth`](https://bigrquery.r-dbi.org/reference/bq_auth.html). It doesn't appear (!?) to support automatically acquiring credentials (which would have been helpful). Perhaps someone else is familiar with the library and can provide guidance but you may (!?) need to create a Service Account and possibly a key that can be referenced using the `path` parameter. – DazWilkin Mar 02 '22 at 19:07
  • Edit your question with details. 1) Which scopes are enabled for the virutal machine? 2) How are you setting up credentials for the VM? Attached service account, CLI login, etc? 3) If you have the VM correctly authorized, you can simply use **bq_auth()** to fetch Application Default Credentials. – John Hanley Mar 02 '22 at 21:26

1 Answers1

0

Our cloud engineer was able to solve the issue. He switched my authentication type on VMs to User rather than Service Account. He posted this on our slack:

Since this came up again yesterday, here's a great primer on GCP authentication: https://codeburst.io/google-cloud-authentication-by-example-1481b02292e4 Couple of key takeaways when looking at authentication outside of the console:

  • There are two different types of accounts that can be authenticated: User and Service Accounts.
  • There are two separate components that are authenticated separately: Google Cloud SDK Command Line Tools and Google Cloud Client Libraries.
  • One account can impersonate another
  • One thing this article fails to mention, is usually (but not always) is a service account associated with a VM that the GCP Client Libraries will fall back to use if other methods aren't specified.

Unrelated, I also got an email from GCP this morning with the following:

enter image description here

abalter
  • 9,663
  • 17
  • 90
  • 145