Hello retool,
I'm trying to setup the BigQuery Integration on Cloud Hosted retool platform with the goal to have a service account just read data, primarily from a bigquery table and firestore collection. We have our data warehouse in BigQuery present in project_datalake and, firestore collection in project_cash. A service account was deployed to project_cash and it has the permissions to read all collections in project_cash (roles/datastore.viewer) and read permissions to a table of concern (table_name: client) in project_datalake (roles/bigquery.dataViewer).
In the attempt to test connection, I got the error below:
Test connection failed
Access Denied: Project cash: User does not have bigquery.jobs.create permission in project_cash.
The error message indicates that the user does not have the necessary permission to create jobs in the BigQuery project "project_cash." To troubleshoot, check the user's IAM roles and ensure they have the "bigquery.jobs.create" permission assigned. Additionally, verify that the user is using the correct credentials and project ID when making the request.
We currently have two problems:
- Retool seems to be attempting to test BigQuery permissions on project_cash alone and not checking project_datalake which is where our data warehouse resides and what this service account was configured for.
- Knowing that I will need to change the project used by the service account (from project_cash to project_datalake) basically re-deploy service account in project_datalake, how then can it have access to the collections in project_cash?
Please kindly advise and if 1 service account cannot be used to access both resources from your platform, kindly advise on how we can solve the current problem or share its feasibility.
- It seems that Retool requires more than read access (roles/bigquery.dataViewer) to BigQuery and per the setup from your website (Connect to BigQuery | Retool Docs), there was no explicit specification of what level of permissions is required for service account connecting to BigQuery and also, it was mentioned that the permissions for the service account depends on what we need as quoted:
Authentication is performed using a Google service account for which you provide details. The required permissions depend on your use case and the objects you need to access.
Looking forward to your responses.
Hey @Emmanuel_Wachukwu
- Retool seems to be attempting to test BigQuery permissions on project_cash alone and not checking project_datalake which is where our data warehouse resides and what this service account was configured for.
- Knowing that I will need to change the project used by the service account (from project_cash to project_datalake) basically re-deploy service account in project_datalake, how then can it have access to the collections in project_cash?
Please kindly advise and if 1 service account cannot be used to access both resources from your platform, kindly advise on how we can solve the current problem or share its feasibility.
Were you able to run any requests with this resource in an application? It seems possible that the Test Connection check would comprehensively check for permissions to perform all possible Retool actions and that you might be able to run some requests in Apps with your existing permission setup despite failing the connection test.
- It seems that Retool requires more than read access (roles/bigquery.dataViewer) to BigQuery and per the setup from your website (Connect to BigQuery | Retool Docs), there was no explicit specification of what level of permissions is required for service account connecting to BigQuery and also, it was mentioned that the permissions for the service account depends on what we need as quoted:
Looking at the Big Query Resource page in Retool, I see this little callout that indicates the service account needs BigQuery Data Viewer and BigQuery User roles. Out of those two, only the BigQuery User role provides the bigquery.jobs.create permission—could you try adding that role to the service account?
I'll note this internally so that we if we can correct the grammar there and specify the minimum necessary roles to use this resource in the documentation you linked!
Hello @everett_smith
Thank you for your response.
Were you able to run any requests with this resource in an application? It seems possible that the Test Connection check would comprehensively check for permissions to perform all possible Retool actions and that you might be able to run some requests in Apps with your existing permission setup despite failing the connection test.
No. We set service account for applications and services (internal/external) in an isolated manner. Although I configured the service account in the manner an application would use it but, it seems to me that your backend does not work that way. So, I believe just for the BigQuery Integration on Retool, the service account for the setup will need to be configured for only project_datalake which is where our datawarehouse resides.
If you have an idea/alternative to this, please let me know.
Looking at the Big Query Resource page in Retool, I see this little callout that indicates the service account needs BigQuery Data Viewer and BigQuery User roles. Out of those two, only the BigQuery User role provides the bigquery.jobs.create permission—could you try adding that role to the service account?
I'll note this internally so that we if we can correct the grammar there and specify the minimum necessary roles to use this resource in the documentation you linked!
Thanks for that pointer, I focused on the docs when I did this. That withstanding, I'm going to make the permissions change and get back to you on this.
Hello @everett_smith
I was able to figure out a couple of things from my end and would love the Retool team to add this to their official documentation.
- Service account to be used for integrations must be created in the project where the cloud resource resides (e.g
project_datalake).
- Permissions for the service account must be granted on the project level and not the resource level (e.g
roles/bigquery.dataViewer and roles/bigquery.user should be granted to project_datalake and not the dataset containing tables of concern).
Best regards!
1 Like
