How to connect Google Cloud SQL from Cloud Functio

问题:

I am trying to use Cloud Functions for Firebase to build an API that talks with a Google Cloud SQL (PostgreSQL) instance.

I am using HTTP(S) trigger.

When I white-list my desktop's IP address, I can connect to the Cloud SQL with the function's node.js code from my local machine. But when I deploy, I can't connect, and I can't figure out the HOST IP address of Firebase Function's server, to white-list.

How do you talk to Google Cloud SQL from Cloud Functions for Firebase?

Thanks!

// Code Sample, of what's working on Localhost.
var functions = require('firebase-functions');

var pg = require('pg');
var pgConfig = {
  user: functions.config().pg.user,
  database: functions.config().pg.database,
  password: functions.config().pg.password,
  host: functions.config().pg.host
}

exports.helloSql = functions.https.onRequest((request, response) => {
  console.log('connecting...');
  try {
    client.connect(function(err) {
      if (err) throw err;

      console.log('connection success');
      console.log('querying...');

      client.query('SELECT * FROM guestbook;', function(err, result){
        if (err) throw err;

        console.log('querying success.');
        console.log('Results: ', result);
        console.log('Ending...');

        client.end(function(err){
          if (err) throw err;
          console.log('End success.');
          response.send(result);
        });
      });

    });
  } catch(er) {
    console.error(er.stack)
    response.status(500).send(er);
  }
});

回答1:

It's not currently possible. It is however a feature request on the issue tracker #36388165:

Connecting to Cloud SQL from Cloud Functions is currently not supported, as the UNIX socket does not exist (causing ENOENT) and there is no defined IP range to whitelist (causing ETIMEDOUT). One possibility is to whitelist 0.0.0.0/0 from the Cloud SQL instance but this is not recommended for security reasons.

If this is an important feature for you I would suggest you visit the issuetracker and star the feature request to help it gain popularity.

回答2:

I found answer in further discussion of #36388165.

disclaimer: this does not seem to be announced officially, so may change afterward. also I only test in mysql. but nature of this solution, I think same way should work as in pg module (it seems to accept domain socket path as host parameter)

EDIT(2017/12/7): google seems to provide official early access, and same method still works.
EDIT(2018/07/04): it seems that there is someone just copy-and-paste my example code and get into trouble. as google says, you should use connection pool to avoid sql connection leak. (it causes ECONNREFUSE) so I change example code a bit.

in https://issuetracker.google.com/issues/36388165#comment44 google guy says cloud function instance can talk with cloud sql through domain socket in special path '/cloudsql/$PROJECT_ID:$REGION:$DBNAME'.

I actually can connect and operate cloud SQL from below cloud function code.

const mysql = require('mysql');
const pool = mysql.createPool({
    connectionLimit : 1,
    socketPath: '/cloudsql/' + '$PROJECT_ID:$REGION:$DBNAME',
    user: '$USER',
    password: '$PASS',
    database: '$DATABASE'
});
exports.handler = function handler(req, res) {
    //using pool instead of creating connection with function call
    pool.query(`SELECT * FROM table where id = ?`, 
                                req.body.id, function (e, results) {
        //made reply here
    });
};

I hope this would be help for those cannot wait for official announce from google.

回答3:

Find your database region and instance name on GCP > SQL > Instances page:

Save your database password into Firebase environment by running:

$ firebase functions:config:set \
    db.user="<username>" \
    db.password="<password>" \
    db.database="<database>"

Then...

db.js

const { Pool } = require('pg');
const { config } = require('firebase-functions');

const project = process.env.GCP_PROJECT;
const region = 'europe-west1';
const instance = 'db';

module.exports = new Pool({
  max: 1,
  host: `/cloudsql/${project}:${region}:${instance}`,
  ...config().db
});

someFunction.js

const { https } = require('firebase-functions');
const db = require('./db');

module.exports = https.onRequest((req, res) =>
  db
    .query('SELECT version()')
    .then(({ rows: [{ version }]) => {
      res.send(version);
    }));

See also https://stackoverflow.com/a/48825037/82686 (using modern JavaScript syntax via Babel)

回答4:

there's now official documentation for this, still in Beta though as at July 2018

https://cloud.google.com/functions/docs/sql

回答5:

Cloud Functions - Supported Services - I don't see Cloud SQL on this list, so perhaps it's not supported yet.

回答6:

You can also authorize Firebase IP addresses range since we don't really know which IP address Firebase use externally.

I've experimented on it. Google Cloud SQL DOES NOT USE internal IP addresses. So, you CAN NOT use 10.128.0.0/20 to allow internal IP addresses for your Google Cloud SQL.

Answer

So from the console, go to Google Cloud SQL > Instance > Authorization, you can add:

151.101.0.0/17

Which will allow you 151.101.0.0 to 151.101.127.255 IP address range, wherein the Firebase server domain is currently 151.101.1.195 and 151.101.65.195.

I'm not sure if this IP address is ever going to change.

Also, make sure that your Cloud SQL Database is using us-central zone. Firebase seems to be available in us-central.