Can we use Service Accounts when setting up OAuth2 for Google APIs, or the only option is Web Application?
3 Community Answers
Kalyan Arangam —
The Oauth workflow in matillion is geared to working with regular accounts than service accounts.
However, the component may support authenticating via Service accounts.
I managed to use the following steps to pull data from Google Analytics using a service account rather than a User account. It may or may not work with other Google components we have.
Ensure the relevant API’s are enabled under “API’s & Services→Library”
Create a Service Account in and note its email. Download a P12 file for this service account and note its password.
Create an OAuth App and note the ClientID and ClientSecret
Ensure this service account has access to relevant services. For example, to give access to Google analytics, Login to analytics and add the service-account’s email address to allowed users.
Copy the “P12” file for the service account to the matillion server. For example to /etc/tomcat8/ folder or any other folder on the matillion server that the tomcat user has access to.
Create an new Google Oauth entry and cancel out of the configuration screen. A new OAuth entry is created with status “Not Configured” – thats fine! Its required to bypass component validation and not for oauth itself.
Add a new Google Analytics component to a job canvas and add the following under Connection Options.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthClientId: Set this to the Client Id in your app settings.
OAuthClientSecret: Set this to the Client Secret in your app settings.
OAuthJWTCertType: Set this to “PFXFILE”.
OAuthJWTCert: Set this to the path to the .p12 file on matillion server.
OAuthJWTCertPassword: Set this to the password of the .p12 file.
OAuthJWTCertSubject: Set this to “*” to pick the first certificate in the certificate store.
OAuthJWTSubject: Set this to the email address of the user for whom the application is requesting delegate access. Note that delegate access must be granted by an administrator.
Profile: Set this to the Google Analytics profile or view you want to connect to. This value can be retrieved from the Profiles table. If this is not specified, the first Profile returned will be used.
Which google components do you intend to use with matillion?
Thanks for your reply! I am using Google Analytics and I successfully applied the approach you described.
When working with service accounts, Google recommend using JSON key store over P12 file, which is there for backward compatibility. Do you know if the CDATA driver supports authentication with JSON file?
Here are the options supported by the component for “type” – https://redshiftsupport.matillion.com/customer/en/portal/articles/2328938-google-analytics-data-model?#RSBGoogleAnalytics_p_OAuthJWTCertType.htm
I don’t see a JSON option so I presume its not supported. I will check and let you know.