How to force bigrquery to re-authorize

[abalter]

Brief description of the problem

I have an RStudio Server instance running on a GCP VM. I'm getting the following error

library(tidyverse)
library(magrittr)
#> 
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#> 
#>     set_names
#> The following object is masked from 'package:tidyr':
#> 
#>     extract
library(bigrquery)

bq_deauth()
bq_auth(email="[email protected]")
bq_conn = dbConnect(
  bigquery(),
  project = "?????????????",
  dataset = "balter"
)


tbl(bq_conn, "bigquery-public-data.austin_311.311_service_requests")
#> # Source:   table<bigquery-public-data.austin_311.311_service_requests> [?? x
#> #   24]
#> # Database: BigQueryConnection
#>    unique_key  complaint_type complaint_descrip… owning_departme… source  status
#>    <chr>       <chr>          <chr>              <chr>            <chr>   <chr> 
#>  1 19-00243479 DRVECTRL       Creek & Pond Vege… Watershed Prote… Phone   Closed
#>  2 20-00440346 SCHOOLZ2       School Zone Flash… Transportation   Spot31… Closed
#>  3 19-00492617 ACCOYTE        Coyote Complaints  Animal Services… Phone   Closed
#>  4 20-00349773 ACCOYTE        Coyote Complaints  Animal Services… Phone   Closed
#>  5 19-00140825 SBSTRES        Street Resurfacing Public Works     Phone   Closed
#>  6 19-00399194 ACCOYTE        Coyote Complaints  Animal Services… Phone   Closed
#>  7 19-00424223 ACCOYTE        Coyote Complaints  Animal Services… Phone   Closed
#>  8 19-00215436 DRSDPIPE       Storm Drain Pipe … Watershed Prote… Phone   Closed
#>  9 19-00461812 STREETL1       Street Lights New  Austin Energy D… Phone   Closed
#> 10 20-00333823 ACCOYTE        Coyote Complaints  Animal Services… Phone   Closed
#> # … with more rows, and 18 more variables: status_change_date <dttm>,
#> #   created_date <dttm>, last_update_date <dttm>, close_date <dttm>,
#> #   incident_address <chr>, street_number <chr>, street_name <chr>, city <chr>,
#> #   incident_zip <int>, county <chr>, state_plane_x_coordinate <chr>,
#> #   state_plane_y_coordinate <dbl>, latitude <dbl>, longitude <dbl>,
#> #   location <chr>, council_district_code <int>, map_page <chr>, map_tile <chr>
tbl(bq_conn, "??????????.s.balter.gnomad_v2_select_allele_frequencies")
#> Error: Access Denied: Table ??????????.gnomad_v2_select_allele_frequencies: Permission bigquery.tables.get denied on table ??????????.:balter.gnomad_v2_select_allele_frequencies (or it may not exist). [accessDenied]

^{Created on 2021-05-11 by the reprex package (v2.0.0)}

However, this code runs fine on my local machine and in AI notebooks.

Also, in the RStudio terminal I can run gcloud auth login, set up auth.

So the VM can connect to data in my project, but for some reason bigrquery can't auth.

How can I reset the auth information in bigrquery?

[jennybc]

With current gargle (v1.1.0), you should be able to do bq_auth(email=NA) to force the OAuth dance. This has always been the intention (and what the documentation says), but there was at least one version of gargle where that didn't work as advertised. But it does now.

[jennybc]

Also: what is your intent ... to use the regular OAuth2 web flow + gargle's token cache or to use Application Default Credentials (ADC)? Above I see evidence of both.

bq_auth() is associated with "regular OAuth2 web flow + gargle's token cache".

"Also, in the RStudio terminal I can run gcloud auth login, set up auth, and then ..." is associated with ADC.

Which do you mean to use?

[abalter]

@jennybc I did try that, as well as deleting the .httr_oauth file.

On my local machine as well as AI notebook when I do as you suggested I get a query in the terminal to choose an email, then a browser window opens for authentication.

Any other ideas?

I realize I didn't directly answer your question, but I don't actually understand your question. I have no idea at that level of detail. I just know that it usually "works" but on the particular platform I described, it "doesn't work."

[jennybc]

Any other ideas?

I'm not getting enough info to provide more help.

I did try that, as well as deleting the .httr_oauth file.

On my local machine as well as AI notebook when I do as you suggested I get a query in the terminal to choose an email, then a browser window opens for authentication.

What exactly happens in the case where you do this and it does not work? What does the failure mode look like?

I realize I didn't directly answer your question, but I don't actually understand your question. I have no idea at that level of detail.

When you say "Also, in the RStudio terminal I can run gcloud auth login, set up auth, and then: ..." are we talking about your local computer, your AI notebook life, or your GCP VM?

[abalter]

On my local machine:

then

On the GCP VM:

And I still can't access the tables that need authentication.

[jennybc]

On the GCP VM, nothing happens? You do not get taken to the account picker? (You'll have to forgive me, but I don't actually use these packages in many of these cloud settings and can't easily stand up the exact environment you are dealing with).

I wouldn't imagine that could work. Depending on the context, I think you'll need to specify out-of-band auth, with bq_auth(use_oob = TRUE) or possibly one of the other methods of auth (I can't tell if the VM you are talking about would be able to use the GCE method https://gargle.r-lib.org/reference/credentials_gce.html).

[abalter]

On the GCP VM, nothing happens? You do not get taken to the account picker?
Exactly

I think the VERY first time I ran my code that did happen, but then I lost my permissions.

Some progress:

I allowed popups.

I thought I was close, but even after authenticating via email,

> gargle::credentials_user_oauth2()
→ Is it OK to cache OAuth access credentials in the folder ~/.cache/gargle between R sessions?

1: Yes
2: No

Selection: 1
Enter authorization code: ##############################################

── <Token (via gargle)> ───────────────────────────────────────────────────────────────────────────────
oauth_endpoint: google
           app: gargle-clio
         email: [email protected]
        scopes: ...userinfo.email
   credentials: access_token, expires_in, refresh_token, scope, token_type, id_token
> tbl(bq_conn, "????????????:.balter.gnomad_v2_select_allele_frequencies")
Error: Access Denied: Table ????????????::balter.gnomad_v2_select_allele_frequencies: Permission bigquery.tables.get denied on table ????????????:balter.gnomad_v2_select_allele_frequencies (or it may not exist). [accessDenied] 
Run `rlang::last_error()` to see where the error occurred.

[jennybc]

I can tell from the above that this is not the currently released version of gargle. So you should update gargle before you do anything else.

Also you should not call gargle directly, i.e. you should not call gargle::credentials_user_oauth2(). Above it's resulting in a token that has essentially no scopes, which is why (at least in this case), you can't do anything with the token on BigQuery. You should work through bq_auth(), which adds the scopes needed to do BigQuery work.

[abalter]

(base) balter@ab-rstudio:~$ conda list | grep gargle
r-gargle                  1.1.0             r40hc72bb7e_0    conda-forge

The reason I was calling gargle directly is because that was the only way I could get the credentials operation to run.

[jennybc]

Ah, re: my version claim, my bad. This cache path is what's currently used on linux. I was confused because we no longer use this path on macOS or Windows.

[abalter]

Bumping this---still having the same problem. The only time I can't properly authorize to bigquery is when I'm working on a VM actually on GCP.

[abalter]

Still having this problem. RStudio Server in cloud VM, and can't authenticate.

[abalter]

https://stackoverflow.com/questions/71327718/cant-authorize-bigrquery-on-google-cloud-vm-but-can-on-local-machine/71356377#71356377

[abalter]

r-lib/gargle#138 (comment)