Unable to connect Cloud SQL with Firebase Functions V2

[service-paradis]

Related issues

#1217 and #1366 that were closed without proper resolution.
I'm reopening a new issue as asked by @taeold in #1366 (comment)

[REQUIRED] Version info

node: 18.13.0

firebase-functions: 4.2.1

firebase-tools: 11.25.2

firebase-admin: 11.6.0

[REQUIRED] Test case

I am attempting to migrate a Firebase Functions project from V1 to V2 to try out beta features. However, it appears that Cloud SQL connections are not yet supported in V2. Cloud SQL connection works automatically and required no actions. It would be beneficial if connecting to a Cloud SQL instance using V2 could be made as simple as it was before.

[REQUIRED] Steps to reproduce

1. Create a Firebase Function that connects to a Cloud SQL database using a socket path like /cloudsql/INSTANCE_CONNECTION_NAME.
2. Call this function.
3. Receive an ENOENT error that was not received using V1.

[REQUIRED] Expected behavior

I expected that Cloud SQL would be available to the function as it was in V1.

[REQUIRED] Actual behavior

I got ENOENT error while trying to connect to the database. As mentioned in #1217, I tried a few things to make this works without any success. @Berlioz mentioned in #1366 (comment) that configuration could be made using Cloud Run configuration. Unfortunately, it does not work. Even if it worked, this is not the expected solution since Cloud SQL database connections were automatically supported in V1.

Even if there is a solution using Cloud Run configuration in Google console eventually, V1 already works automatically, so it would be beneficial if connecting to a Cloud SQL instance using V2 could be made as simple as it was before.

Were you able to successfully deploy your functions?

Yes, without any errors. The function itself seems to be working as expected, but it cannot connect to a Cloud SQL instance.

Ideally, Cloud SQL database connections should be established seamlessly, without requiring any manual service updates. In version 1 of Firebase Functions, Cloud SQL database connections were automatically supported. Therefore, it is reasonable to expect that version 2 should also provide this functionality with minimal additional effort.

[google-oss-bot]

I couldn't figure out how to label this issue, so I've labeled it for a human to triage. Hang tight.

[colerogers]

Hi @service-paradis sorry for the confusion around the original issues that you posted and apologies for preemptively closing them. Since I believe this issue is more about adding a feature to the Firebase SDK/CLI to automatically import a Cloud SQL connection into the Function, I'm going to change this to a feature-request since we don't support this behavior yet. This sounds like a great experience for developers and we'll try to prioritize the work internally. Thanks!

[service-paradis]

Thank you for your feedback. I really appreciate it. I hope that support for Cloud SQL will be added before V2 becomes the officially supported version, as it would be a breaking change for me. Right now, Cloud SQL is working well for me in V1.

[iurage]

Is there any progress on this issue? As a user new to Cloud SQL, I literally spent 6 hours researching and debugging into this issue until I realize that the problem is due to an unsupported feature with Cloud Functions V2. There is zero documentation on this on the Firebase Cloud Function website (and contradictory documentation with GCP Cloud Functions), which is very poor and confusing in my opinion.

[mordy]

Not sure why this is marked as a "feature request", its a breaking change while upgrading from v1 to v2.

The workaround is that if you go into the cloud run and you add the SQL connection manually. The SQL connection will remain when deploying updates so its once per function only.

[mcphearson]

I was having the same issue you were in #1217 where adding the Cloud SQL connection to the Cloud Run service resulted in an "Image not found" error. A fix I discovered is going to the Cloud Function, clicking the source tab, clicking Edit, then clicking Save and Redeploy. Once the redeploy finishes you should be able to successfully add the Cloud SQL connection to the Cloud Run service.

Definitely not an ideal way to deploy functions (especially if there are a lot of functions that use Cloud SQL) and needs to get fixed, but at least your function will work.

[big213]

I was having the same issue you were in #1217 where adding the Cloud SQL connection to the Cloud Run service resulted in an "Image not found" error. A fix I discovered is going to the Cloud Function, clicking the source tab, clicking Edit, then clicking Save and Redeploy. Once the redeploy finishes you should be able to successfully add the Cloud SQL connection to the Cloud Run service.

Definitely not an ideal way to deploy functions (especially if there are a lot of functions that use Cloud SQL) and needs to get fixed, but at least your function will work.

I tried these steps and was able to go from the ENOENT error that was described originally to now a ECONNREFUSED error, so that's some improvement at least. But still, I'm unsure of how to resolve the ECONNREFUSED error from here. Any ideas? I'm using postgres, and here's how I'm connecting:

import * as knexBuilder from "knex";

export const knex = knexBuilder({
  client: "pg",
  connection: {
    host: "/cloudsql/project-id:region:instance-name",
    user: pgUser.value(),
    password: pgPassword.value(),
    database: pgDatabase.value(),
  },
  pool: { min: 0, max: 1 },
});

// execute a DB query here...
knex.raw("SELECT...")

My v1 functions was working on the same project using this configuration. What's your configuration looking like? @mcphearson

[mcphearson]

@big213 There are a couple things I would check. Are pgUser, pgPassword, and pgDatabase secrets or environment variables? If they are secrets they can only be used when the actual function is called, they cannot be used to configure the function. So you might have to do something like:

import * as knexBuilder from 'knex';
import { defineSecret } from 'firebase-functions/params';
import { https } from 'firebase-functions/v2';

const pgUser = defineSecret('SQL_USER');
const pgPassword = defineSecret('SQL_PASS');
const pgDatabase = defineSecret('SQL_DB');

let knex: any;

export const myV2Function = https.onCall({
    secrets: [pgUser, pgPassword, pgDatabase]
  }, (request) => {
    // Configure knex
    if (!knex) {
      conn = knexBuilder({
      client: "pg",
      connection: {
        host: "/cloudsql/project-id:region:instance-name",
        user: pgUser.value(),
        password: pgPassword.value(),
        database: pgDatabase.value(),
      },
      pool: { min: 0, max: 1 },
    });
  }
		
    // execute a DB query here...
    knex.raw("SELECT...")
  }
);

You should also check to make sure the service account that's running the function has one of the following IAM roles:

• Cloud SQL Client (preferred)
• Cloud SQL Editor
• Cloud SQL Admin

[big213]

@mcphearson thanks for that, I double checked and it appears that the environment variables are being successfully passed to the function. I checked the logs and here's the error I'm getting:

Cloud SQL connection failed. Please see https://cloud.google.com/sql/docs/mysql/connect-run for additional details: ensure that the account has access to "[connection string]" (and make sure there's no typo in that name). Error during generateEphemeral for [connection string]: googleapi: Error 403: boss::NOT_AUTHORIZED: Not authorized to access resource. Possibly missing permission cloudsql.instances.connect on resource instances/[instance]., forbidden

I've indeed confirmed that the project has Cloud SQL Client permissions on the project with the database. In fact, I tried deploying the cloud function to the same project containing the database instance, and it still gives me that exact error.

Any chance you can share if you are using a MySQL database? I'm using Postgres so maybe that has something to do with it.

[mcphearson]

@big213 I am using MySQL in my project. I tried creating a new project with a Postgres Cloud SQL instance and deployed a function in the same project but I didn't get the issue you are. My test function is:

import {onRequest} from "firebase-functions/v2/https";
import {knex} from "knex";

const conn = knex({
  client: "pg",
  connection: {
    user: "user",
    password: "pass",
    database: "db",
    host: "/cloudsql/project-id:region:instance-name",
  },
});

export const testFunction = onRequest(async (req, res) => {
  try {
    const result: number = await conn("data").count();
    res.status(200).send(result);
  } catch (err) {
    res.status(500).send(err);
  }
});

Here's what I would check: If you go to the function in Cloud Run and check the security tab does it allow unauthenticated invocations? On the Networking tab is Ingress Control set to All? If you run it locally does it work? If you explicitly provide the credentials rather than use environment variables does it work?

Some Googling lead me to this, which helped other people who had the Error during generateEphemeral... problem.

Since it works as a v1 function but not v2 I'm leaning towards the issue being on the Cloud Run side of things. A weird thing I noticed is that the error message you get has a link to MySQL documentation, but you're running Postgres. Of course that could just be a generic error message.

[big213]

@mcphearson I figured out the issue by spinning up a new Cloud SQL instance on a brand new project, and then inspecting the different IAM permissions. For whatever reason, the [x]-compute@developer.gserviceaccount.com and [project-id]@appspot.gserviceaccount.com service accounts on my project were set to something that was not "Editor." I went and updated these 2 service accounts to the Editor role and now trying to connect to the Cloud SQL instance from a Cloud Run instance on the same project works properly. However, connecting to the the Cloud SQL instance from another project still gives the same error:

Cloud SQL connection failed. Please see https://cloud.google.com/sql/docs/mysql/connect-run for additional details: ensure that the account has access to "[connection string]" (and make sure there's no typo in that name). Error during generateEphemeral for [connection string]: googleapi: Error 403: boss::NOT_AUTHORIZED: Not authorized to access resource. Possibly missing permission cloudsql.instances.connect on resource instances/[instance]., forbidden

Furthermore, to check if cloudsql.instances.connect permissions were really missing on the Cloud SQL instance project, I used the IAM Policy Troubleshooter Interface, which told me that access was granted.

I tried a few configurations and still can't get it to work, so I'm afraid it might only be possible at this time to connect to a Cloud SQL instance from the same project, if using Cloud Run.

I'm curious, were your Cloud SQL instances on the same project as your Cloud Run instance?

[big213]

Ok, just when I was about to give up, I think I managed to figure out a solution to getting Cloud SQL access from a different project's Cloud Run instance.

I got the [x]-compute@developer.gserviceaccount.com from the project with the Cloud Run instance, and granted Editor permissions to this service account from the project with the Cloud SQL instance. Then it worked. No idea why, or how. In fact, when I remove this service account afterwards from the project with the Cloud SQL instance, it still works(?!).

Update: removing the service account permissions caused the Cloud SQL connection to stop working after some amount of time, so I had to add it back. But I realized that just granting "Cloud SQL Client" role was sufficient (for the cloudsql.instances.connect permission), rather than "Editor" role. It appears that Cloud Run uses the Compute Engine Service Account for permissions, rather than the App Engine Service Account.

Update 2: When deploying a revision to the Cloud Run instance, on the security tab, you have the option to specify which service account to use. By default it is set to the Compute Engine service account, but it looks like it can be switched to the App Engine service account (which appears to work fine, and is more consistent with the V1 functions).