Google oauth for BigQuery is working great for ~12 hours, then it stops with the error below. I confirmed that tokens are being refreshed fine during that 12 hour period, but after that it’s requiring me to go into credentials and get consent again.
I’m using the native bigquery node.
I’ve tried so many things:
moving app from testing to production, then back again
Creating a new project (still an issue).
Trying this on a whole other google account.
Thinking this might be a worker / main issue so setting legacy execution to happen on main
Tried scaling back workers because maybe credential caching was the issue
Tried messing with the access token so that it would require a refresh (refresh works fine until it doesn’t)
This has been particularly painful to diagnose because the failures only happen overnight and I can’t trigger them directly.
Still having this issue.
What is the error message (if any)?
Problem in node ‘Execute a SQL query‘
The provided authorization grant (e.g., authorization code, resource owner credentials) or refresh token is invalid, expired, revoked, does not match the redirection URI used in the authorization request, or was issued to another client.
Please share your workflow
The workflow is as simple as possible: schedule trigger every 30 minutes, then run a simple SQL query.
Reading up on the several threads here, I am currently investigating the access_type=offline. I don’t know where to check if this is being passed in or not.
I am also going to try to use a service account instead of using Oauth.
A few things about my setup:
I noticed is that my applications oauth screen isn’t verified
I was able to confirm that the consent screen was sending access_type=offline. You should be able to confirm that via the consent screen when you re-auth (if you look at the url at the top of the consent browser screen)
I ssh’d into the back-end and then used the n8n command that decrypts the credentials to look at the access token and saw that the access token changed but the refresh token didn’t
I don’t know what the deal is with refresh token expiration. My guess is that there’s an undocumented thing going on with google because I just couldn’t figure this out even with days of trying to work on it.
I tried to make the app be in test mode and added myself as a test user but the same issue. My last attempt to fix this is going to be using the service account instead of the oauth.