3

UPDATE: Added screenshot of the IAM settings at bottom.

I've checked the following without success:

I’ve setup Firebase Functions in a new project to run an express server that connects to the PostgreSQL database and serve out endpoints for the API (to allow the simple to read records from the DebStroutData database).

I’ve authorized the db to allow connections from my local machine. It works perfectly in development mode. (I run firebase serve to simulate the cloud functions locally.)

Now comes the problem - I cannot connect to the database in production. I know why: because firebase cloud functions technically have no specific IP addresses that I can whitelist in the database and allow connections from.

So this is what I’ve done so far:

  1. I’ve whitelisted the IP range for all the IPs running in Google’s Central region: 151.101.0.0/17 (The cloud functions run on https://[[remote-cloudfunctions-url]].cloudfunctions.net/app - No success.

  2. I’ve tried to connect internally from firebase to the data via this document https://cloud.google.com/sql/docs/postgres/connect-functions. That means creating a service account, providing the appropriate permissions, and using the internal instance name for the app as the host: "optivois-prod:us-east1:[[postgres-instance-connection-name]]". No success here either. Here is my config:

const Pool = require('pg').Pool
const pool = new Pool({ 
  user: '[[db_user]]',
  host: '/cloudsql/[[postgres-instance-connection-name]]/.s.PGSQL.5432',
  database: '[[db_name]]',
  password: '[[db_password]]'
})
  1. I’ve tried to create a private IP for the app, but that isn’t working in a different way.

Wondering if any has ideas, thoughts, or questions around this.

Here's a screenshot of the Google Cloud IMA members: enter image description here

Here's the error stack (and I have ensured the instance name is correct and it has access to the db):

3:52:41.541 PM
app
Function execution started 
3:52:42.862 PM
app
CloudSQL warning: your action is needed to update your function and avoid potential disruptions. Please see https://cloud.google.com/functions/docs/sql#troubleshooting for additional details: ensure that the account has access to "[[postgres-instance-connection-name]]" (and make sure there's no typo in that name). Error during createEphemeral for [[postgres-instance-connection-name]]: googleapi: Error 403: The client is not authorized to make this request., notAuthorized 
3:52:43.117 PM
app
Failed to receive message from instance optivois-prod:us-east1:postgres12-dreamfactory: ZERO_APP::1: stream aborted: server handler returned error: ERROR_NOT_AUTHORIZED: end user google account not authorized to access instance: [[postgres-instance-connection-name]] 
3:52:43.130 PM
app
Error getting residents:  Error: connect ECONNREFUSED /cloudsql/[[postgres-instance-connection-name]]/.s.PGSQL.5432 
3:52:43.130 PM
app
    at PipeConnectWrap.afterConnect [as oncomplete] (net.js:1141:16) 
3:52:43.130 PM
app
    at PipeConnectWrap.callbackTrampoline (internal/async_hooks.js:120:14) { 
3:52:43.130 PM
app
  errno: 'ECONNREFUSED', 
3:52:43.130 PM
app
  code: 'ECONNREFUSED', 
3:52:43.130 PM
app
  syscall: 'connect', 
3:52:43.130 PM
app
  address: '/cloudsql/[[postgres-instance-connection-name]]/.s.PGSQL.5432' 
3:52:43.130 PM
app
}
3:52:43.135 PM
app
TypeError: Cannot read property 'rows' of undefined
3:52:43.135 PM
app
at /workspace/queries.js:19:39
3:52:43.135 PM
app
at PendingItem.callback (/workspace/node_modules/pg-pool/index.js:352:16)
3:52:43.135 PM
app
at Client._connectionCallback (/workspace/node_modules/pg-pool/index.js:244:23)
3:52:43.135 PM
app
at Client._handleErrorWhileConnecting (/workspace/node_modules/pg/lib/client.js:305:19)
3:52:43.135 PM
app
at Client._handleErrorEvent (/workspace/node_modules/pg/lib/client.js:315:19)
3:52:43.135 PM
app
at Connection.emit (events.js:315:20)
3:52:43.135 PM
app
at Connection.EventEmitter.emit (domain.js:483:12)
3:52:43.135 PM
app
at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:53:12)
3:52:43.135 PM
app
at Socket.emit (events.js:315:20)
3:52:43.135 PM
app
at Socket.EventEmitter.emit (domain.js:483:12)
3:52:43.146 PM
app
Function execution took 1605 ms, finished with status: 'crash'
akaHeimdall
  • 949
  • 3
  • 10
  • 29
  • Have you checked your Cloud Function's service account and made sure that it has "Cloud SQL Client" role on IAM? – Donnald Cucharo Jan 08 '21 at 01:14
  • I can see that you created a service account and assigned appropriate roles in it but didn't mentioned that you've configured your function to use it. By default, Cloud Functions uses App Engine service account which has this format: `PROJECT_ID@appspot.gserviceaccount.com`. Check your function details and see what service account you're using. – Donnald Cucharo Jan 08 '21 at 03:14
  • @DonnaldCucharo Thanks for asking. Yes, I have the "Cloud SQL Client" role turned on. Even tried to add the "Cloud SQL Admin" to see if that helped. – akaHeimdall Jan 08 '21 at 09:24
  • @DonnaldCucharo Do you mean that the PROJECT_ID@appspot.gserviceaccount.com should have the "Cloud SQL Client" role attached or something else? I see the PROJECT_ID@appspot.gserviceaccount.com in the list of IAM members, and I've added the "Cloud SQL Client" and "Cloud Functions Admin" roles. I'll add a screenshot of my IAM members above. Thanks. – akaHeimdall Jan 08 '21 at 09:34
  • Is there a possibility that the prefix before the instance_name in the host value should be something other than "/cloudsql"? – akaHeimdall Jan 08 '21 at 09:48
  • Just noticed this line in the "Connecting from Cloud Functions" documentation: "If the authorizing service account belongs to a different project than the Cloud SQL instance, enable the Cloud SQL Admin API, and add the IAM permissions listed above, on both projects." I am connecting from one project to another. Seeing if I can hone in on this. – akaHeimdall Jan 08 '21 at 10:16
  • Yes, on that case you need to [enable Cloud SQL Admin API](https://cloud.google.com/sql/docs/postgres/admin-api#enabling_the_api) on both projects. Were you able to do so? Reading the answers [on this thread](https://stackoverflow.com/questions/59278823/cannot-access-cloud-sql-from-firebase-cloud-functions-in-a-different-project) can also help. – Donnald Cucharo Jan 11 '21 at 01:43
  • To answer your comment question, if you are connecting Cloud Functions to Cloud SQL, you are connecting with Unix sockets. The prefix before the instance connection name should always be `/cloudsql`. – Donnald Cucharo Jan 12 '21 at 04:24

2 Answers2

3

I had to create a new IAM for

<project-id>@appspot.gserviceaccount.com and grant Cloud SQL Client to fix this.

I also connect sql via using socketPath: "/cloudsql/<project-id>:<region>:<instance-name>"

d-_-b
  • 21,536
  • 40
  • 150
  • 256
1

From the image, I could say that your function is deployed on an API project and you mentioned in the comments that your Cloud SQL instance is on a different project.

To fix your problem, go to your project where your Cloud SQL instance is located and select IAM. You need to add there the service account that your Cloud Function is using and put Cloud SQL Client role in it.

Donnald Cucharo
  • 3,866
  • 1
  • 10
  • 17