Cannot access cloud SQL from firebase cloud functions in a different project

Question

I have two GCP projects(one of them is a firebase project), and I am trying access a cloud SQL instance on one project from the other project's firebase cloud functions.

I have followed the instructions here: https://cloud.google.com/sql/docs/mysql/connect-functions

On this instruction, it says

When connecting resources in two different projects, make sure that both projects have enabled the correct IAM roles and have given the service account the correct permissions.

and I am not entirely sure if I'm doing this part correct. What I did was to copy the service account email address from my cloud function project that looks like this: "[e-mail]" and copied that into my other GCP project's IAM page and gave it a CloudSQL Client role.

This is the code that I am using to access the database:

import * as functions from "firebase-functions";
import * as mysql from "mysql";

export const sql = functions
  .https.onRequest((request, response) => {
    return new Promise<any>(async (resolve, reject) => {
      const query = "select * from my_table limit 1;";
      const pool: mysql.Pool = mysql.createPool({
        socketPath: functions.config().cloud_sql.socket_path,
        user: functions.config().cloud_sql.user,
        password: functions.config().cloud_sql.password,
        database: functions.config().cloud_sql.database,
        connectionLimit: 5,
        timezone: "+0900"
      });

      pool.query(query, [], (err: mysql.MysqlError | null, results: any) => {
        if (err) {
          console.log(err);
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  });

When I access this function, I get this error in the console:

Error: connect ECONNREFUSED

What could I be missing? Any help is greatly appreciated.

Answer 1

I just solved it shortly after I post this question lol.

I was giving wrong service account the IAM role. Instead of adding Cloud SQL Client role to "[e-mail]" account like it says in the official documentation(https://cloud.google.com/sql/docs/mysql/connect-functions), you need to add Cloud SQL Client role to App Engine default service account, which ends with something like this "<YOUR_PROJECT_ID>@appspot.gserviceaccount.com"

Answer 2

The project default service account that cloud function uses during function execution, (Runtime service account) is [email protected] link:

During function execution, Cloud Functions uses the service account [email protected] as its identity.

By default, the runtime service account has the Editor role, which lets it access many GCP services.

If you have multiple functions all accessing different resources, you'll likely want to give each function its own identity. This can be done by deploying the function with a named service account that has the correct role. The service account being deployed must have been created in the same project as the function it is attached to.

Also :

At runtime, Cloud Functions defaults to using the App Engine default service account ([email protected]), which has the Editor role on the project. You can change the roles of this service account to limit or extend the permissions for your running functions. You can also change which service account is used by providing a non-default service account on a per-function basis.

You mentioned this link link:

Cloud Functions uses a service account to authorize your connections to Cloud SQL. This service account must have the correct IAM permissions to successfully connect. Unless otherwise configured, the default service account is in the format [e-mail].

This service account is used only for administrative actions link:

For administrative actions on your project during the creation, updating, or deletion of functions, the Cloud Functions service uses the Google Cloud Functions service agent service account ([email protected]).

By default, this service account has the cloudfunctions.serviceAgent role on your project. Creating, updating, and deleting functions may fail if you change this account's permissions.

I would suggest to deploy the cloud function with it's own service account:

1. Create services accounts:

    gcloud iam service-accounts create function-one --display-name function-one
   

2. Assign the necessary roles to the services account:

    gcloud projects add-iam-policy-binding my_project --member serviceAccount:function-one@my_project.iam.gserviceaccount.com --role roles/cloudsql.client
   

3. Deploy the function with the service account:

    gcloud beta functions deploy ... --service-account=SERVICE_ACCOUNT
   

SERVICE_ACCOUNT

The email address of the IAM service account associated with the function at runtime. The service account represents the identity of the running function, and determines what permissions the function has. If not provided, the function will use the project's default service account.

I understand form the documentation that both services account should have the necessary roles to connect to Cloud SQL from a different project:

   [email protected]
   [email protected]