How to secure your Spreadsheet Admin interface

Chukwuma Nwaugha
Oct 29, 2019 · 5 min read

Restricting access to only authorized users in your Firebase project

Photo by Masaaki Komori on Unsplash

This is a sequel to How to use Google Spreadsheet as Admin interface for your Firebase project, please read it for background knowledge.

As described in the sequel, Google Apps Script extends the power of Google Spreadsheet just like Cloud Functions extends the power of Firebase services, and with both, it is possible to perform admin-level transactions in an application, using Google Spreadsheet.

However, the risk of unauthorized access is high when using a spreadsheet as an admin interface: either in the spreadsheet being exposed to persons not cleared to perform transactions or the cloud functions HTTP onRequest trigger endpoint not being secure by allowing requests from any client application. Here, we’ll see how to safeguard an application and the admin interface using secure OAuth tokens generated by Google to authorize transactions in a Firebase project.


The Firebase Admin SDK supports defining custom attributes on user accounts for Firebase services like Realtime database (RDB), Storage or Cloud Firestore. This provides the ability to implement various access control strategies, including role-based access control, in Firebase apps. These custom attributes can give users different levels of access (roles), which are enforced in an application’s security rules.

In order to ensure a secure transaction on the spreadsheet admin interface and cloud functions, we will do the following:

  1. Define security rules on the database to grant access to only users who have defined custom claims or meet certain conditions.
  2. Send the OAuth token generated by the Spreadsheet as part of the transaction payload to the HTTP onRequest trigger endpoint.
  3. Check the validity of the token using googleapis OAuth token-info endpoint: this provides a response with user email.
  4. Get userRecord using Firebase Admin SDK getUserByEmail, and verify the user’s custom claim and access level.

The request from the spreadsheet would be handled by confirming the user’s authorization with the steps above and, afterward, access can be granted or denied for the transaction based on whether the user is authorized or not.

When using custom claims to grant access to resources in a Firebase project, one should define when/how the custom claim is attached to the user’s auth object. One approach is when a new user completes email verification.

userDocumentChanged.ts
assignClaim.ts

What was done in the snippet is attach admin claim to the user’s auth object after email verification. Other custom claims can also be attached like isDeveloper or isPremiumUser. Now, we’ll ensure that the resources on the database are only accessible to users with the defined claims and this can be achieved using Security rules on the database.

RDB 
---
{
"rules": {
"adminContent": {
".read": true,
".write": "auth.token.admin === true",
}
}
}
Firestore
---
service cloud.firestore {
match /databases/{database}/documents {
match /adminContents/{contentId} {
allow read;
allow write: if request.auth.token.admin == true
}
}
}

Write access is now allowed for authenticated sessions with admin claim set to true on the auth token, which is also known as the user's JWT.

This process can be simplified by ignoring the step for attaching custom claims on a user’s auth object after email verification and writing a security rule with the same condition that makes a user an admin

RDB 
---
{
"rules": {
"adminContent": {
".read": true,
".write": "auth.token.email_verified == true && auth.token.email.matches(/.*@example.com$/)"
}
}
}
Firestore
---
service cloud.firestore {
match /databases/{database}/documents {
match /adminContents/{contentId} {
allow read;
allow write: if request.auth.token.email_verified == true && request.auth.token.email.matches(/.*@example.com$/)
}
}
}

Read more here and here on other attributes accessible on the auth.token parameter, and read more about using custom claims here


With security rules defined for the database resources, let’s go to sending OAuth tokens as part of the transaction payload from Google Apps Script. In the sequel, onEdit trigger was set up to listen for edit operations on the spreadsheet and on Google Apps Script, dispatch a payload of the action to the Cloud Functions HTTP onRequest trigger endpoint. Now, we’ll leverage the API ScriptApp.getOAuthToken() to retrieve the OAuth token for the current user of the spreadsheet and add it as part of the payload.

One thing to note is the scope of the spreadsheet, which is defined by the type of googleapis resources used or required in the sheets transaction.

The goal is to ensure that when the OAuth token is sent from Google Apps Script, it is possible to decode it on the backend and retrieve the user email which can be used to verify the user’s authenticity and authorization.

The OAuth token-info endpoint returns a response based on the scopes of the token. So to add user email to the response object, we must ensure to consume user data on the Google Apps Script, thus making the userinfo.email scope available on the token. If everything goes well, the following scopes should be found on the OAuth token sent to the server:

const scopes = [   'https://www.googleapis.com/auth/script.external_request',   'https://www.googleapis.com/auth/spreadsheets',   'https://www.googleapis.com/auth/userinfo.email']

with the Google Apps Script looking like the following:

updateCustomerEntry-modified.gs

The next step is to check the validity of the token using googleapis OAuth token info endpoint which provides a response containing user info, use Firebase Admin SDK to get the userRecord from the user email and confirm the custom claim and access level. (3) & (4)


With the steps above in place, we can listen for the request on the server and get the token info, which looks like the following:

{
"azp": "603570933139-42q9e2a9kcug7h5qesipvafrad90f6dl.apps.googleusercontent.com",
"aud": "603570933139-42q9e2a9kcug7h5qesipvafrad90f6dl.apps.googleusercontent.com",
"scope": "https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/userinfo.email",
"exp": "1572032989",
"email": "user@example.com", // exposed by userinfo.email scope
"email_verified": true, // exposed by userinfo.email scope
"expires_in": "2654",
"access_type": "offline"
}

The attributes in the JSON above describe the token based on available scopes, the audience (the recipients that the JWT is intended for) and expiration time. We have the user email and email_verified attributes and the token is valid for an hour (3600s). Learn more about JWT terminologies here.

On the Cloud Functions HTTP onRequest trigger, we get the request body as was done in the sequel, but now containing the OAuth token, and verify the user’s identity and authorization. See the following:

updateCustomerEntry-modified.ts
verifyUser.ts

What has been done is use the token-info endpoint to get the token attributes, confirm that it has the right scopes, retrieve the userRecord, confirm that it’s a user on the project and ensure it has the admin claim on the auth object.

It’s now possible to determine the following:

  1. the request is coming from the spreadsheet,
  2. the user exists in the database,
  3. the user has the right access, admin claim, to modify database resources.

And what is obtained is a secure integration of the Spreadsheet Admin Interface with the Firebase project, as it is certain that each transaction is coming from an authorized user.


The approach described here secures the application, but it is important to apply basic spreadsheet security such as sharing access to the spreadsheet with only authorized persons in the organization and restricting editors from copying or resharing the spreadsheet. With basic security comes an extra layer that is certain to completely secure the application from unauthorized users.

I hope you liked it? Please share with me other ways to engineer and enhance the capability of a product using day to day tools and resources like Google Spreadsheet.

Chukwuma Nwaugha

Written by

JavaScript and Laravel Enthusiast. Everything Firebase. I love to help Devs. Twitter: @ChukwumaNwaugha

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade