By guillaume blaquiere．Oct 27, 2022
Modern code development is based on automation, and especially CI/CD pipelines (Continuous Integration Continuous Deployment/Delivery). On Google Cloud, Cloud Build is the serverless solution to achieve CI/CD and offers many features and highly customizable pipelines.
During the code packaging, you might need to rely on other data than only your code, for configuration, enrichment, customization, versioning,… That new source of data can be in a database but also on a Google Workspace document.
Accessing a Google Workspace document
Before trying to access the document, let’s start by writing a small python script
main.py that read a Google Sheet document
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError # The ID and range of a sample spreadsheet. SAMPLE_SPREADSHEET_ID = 'YOUR_SHEET_ID' SAMPLE_RANGE_NAME = 'DATA!A2:E' #Example def main(): """Shows basic usage of the Sheets API. Prints values from a sample spreadsheet. """ creds, _ = google.auth.default() try: service = build('sheets', 'v4', credentials=creds) # Call the Sheets API sheet = service.spreadsheets() result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME).execute() values = result.get('values', ) if not values: print('No data found.') return print('Name, Major:') for row in values: # Print columns A and E print('%s, %s' % (row, row)) except HttpError as err: print(err) if __name__ == '__main__': main()
Install the dependencies (file
google-auth# Install dependencies with: pip3 install -r requirements.txt
You can perform a first try.
Insufficient scopes. There is an issue with the default authentication.
When you authenticate yourself on your environment with the command
gcloud auth application-default login, you use the default scopes, which do not include the Google Sheet scope.
Let’s fix that
To scope your personal account you just have to explicitly define the scopes and add thoses that you want in addition of the Google Cloud Platform ones.
gcloud auth application-default login \
And try your code again. This time it works!!
The naïve first try
To run the Python script in Cloud Build, you have to write a
cloudbuild.yaml file that describe the operation. We will use a single step with a Python image.
- name: 'python:slim-buster'
pip3 install -r requirements.txt
And run the build;
gcloud builds submit
Insufficient scopes!! But this time, you can’t scope the current account, because it is provided by the metadata server.
Let’s update the code to enforce that scope programmatically
Update only the
creds variable definition.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds, _ = google.auth.default(scopes=SCOPES)
And try again. Not better…
The custom service account try
With some services, like Compute Engine, you can’t change the scope of the default service account at runtime.
- You have to stop the VM, change the scopes and restart it. So boring
Or, and it’s the most interesting and my most ambitious option
- You can use a customer-managed service account. Then you can scope the service account as you wish at runtime!
So, let’s use a customer-managed service account with Cloud Build. You have to create a service account, and grant the correct permissions on it.
#Create the service account gcloud iam service-accounts create custom-sa#Grant the permission gcloud projects add-iam-policy-binding \ --member="serviceAccount:custom-sa@PROJECT_ID.iam.gserviceaccount.com" \ --role="roles/storage.objectViewer" PROJECT_IDgcloud projects add-iam-policy-binding \ --member="serviceAccount:custom-sa@PROJECT_ID.iam.gserviceaccount.com" \ --role="roles/logging.logWriter" PROJECT_ID
PROJECT_ID with your own project ID
And update your
cloudbuild.yaml file like that
steps: - name: 'python:slim-buster' script: | pip3 install -r requirements.txt python3 main.py serviceAccount: 'projects/PROJECT_ID/serviceAccounts/custom-sa@PROJECT_ID.iam.gserviceaccount.com' options: logging: CLOUD_LOGGING_ONLY
Finally, have a final try… And fail again
Insufficient scopes. Why??
The impersonation solution
So, the metadata server can’t generate a token with a different scope. The issue is similar as this one we had with the user account at the beginning. Impossible to change the token scope at runtime.
The solution was to regenerate a token with the correct scopes, from the beginning.
Therefore, the next option is to do the same thing and to ask for a new token, on the service account, with the correct scopes at token creation time.
For that, we can use a feature named impersonation. The principle is to generate a token (access token or identity token) on behalf another account.
And, by the way, inherit of all its permissions
In the code, you have to change things:
- You must know the service account email to impersonate (for production purpose, use environment variable to provide it)
- You have to create the impersonated credential and to use it
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = 'YOUR_SHEET_ID'
SAMPLE_RANGE_NAME = 'DATA!A2:E'
"""Shows basic usage of the Sheets API.
Prints values from a sample spreadsheet.
""" SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds, _ = google.auth.default(scopes="https://www.googleapis.com/auth/cloud-platform")
icreds = google.auth.impersonated_credentials.Credentials(
service = build('sheets', 'v4', credentials=icreds)
# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
values = result.get('values', )
if not values:
print('No data found.')
for row in values:
# Print columns A and E, which correspond to indices 0 and 4.
print('%s, %s' % (row, row))
except HttpError as err:
if __name__ == '__main__':
To allow the Cloud Build runtime service account impersonating the target service account, the runtime service account must be allowed to create a token on the target service account.
So, you have to grant the roles
Service Account Token Creator on the runtime service account like that
gcloud projects add-iam-policy-binding \
And have a try… Fail again!!
But the good news is that it’s not the same error! It’s an error from Google Sheet because the target service account hasn’t the permission to access to it!
Wonderful!! Go to your sheet document, click on the SHARE button and add the target service account email as VIEWER of the sheet document.
Have a new try and this time it works!!
Note: the customer-managed service account is not longer required. You can switch back to the Cloud Build default service account; Don’t forget to grant the correct role on it to let it impersonate the service accounts.
Note 2: you can’t impersonate the Cloud Build default service account itself. You can only impersonate a customer-managed service account (a service account that you create and which is attached to your project)
Inconsistent security solutions
Security is paramount and Google Cloud offers really strong and versatile security options.
However, those options are inconsistent from a service to another one and requires expertise, failure and hack to success in an elegant way.
Because of those difficulties, many users don’t spend time and go quickly to the fastest and the ugliest solution: They use a service account key file (which is the anti-pattern of security)!
I hope that article helps you to successfully and elegantly manage your security issues and stay safe in any circumstances!
The original article published on Medium.