Connecting to CloudSQL from Node.js on Heroku

Question

I have a Node.js app (running on AppEngine) connecting to a GCP CloudSQL (MySQL) instance. Now I want to connect to the same database from Node.js (Knex) running on Heroku.

From AppEngine, Node.js connects via user/password and socketPath. I'm also connecting to the same MySQL DB from MySQL Workbench via host IP (over SSL).

I'm trying to use the same host, port, user and pass as Workbench from Heroku and it's not working. To try and make it easy, I've temporarily allowed all networks to connect (0.0.0.0/0) and I've allowed non-SSL connections.

Here's the error: ER_ACCESS_DENIED_ERROR: Access denied for user 'usernamehere'@'xx.xxx.xxx.xx' (using password: YES)"

The environment variables are stored in the Heroku app and they must be working because the username is correct.

It's not very helpful, but here's the code:

import Knex = require('knex');
const envConfig = require('../config/environments').get(process.env.NODE_ENV);
module.exports = knex;

the error message you are getting is a MySQL please verify that you connection information is fine. [here](https://stackoverflow.com/questions/40477625/nodejs-mysql-er-eccess-denied-error-access-denied-for-user-rootlocalhost) the same issue was happening and there are some workarounds — Soni Sol, Dec 12 '19 at 22:17
Does this answer your question? [NodeJS/mySQL - ER\_ECCESS\_DENIED\_ERROR Access denied for user 'root'@'localhost' (using password: YES)](https://stackoverflow.com/questions/40477625/nodejs-mysql-er-eccess-denied-error-access-denied-for-user-rootlocalhost) — Soni Sol, Dec 12 '19 at 22:19
@JoséSoní no, unfortunately not. I've worked through those suggestions and the problem is still happening with a new user, new pass, and npm package update. — beachCode, Dec 13 '19 at 07:27
how are you connecting to cloudSQl, and could you share if you are getting logs on cloudSQL side? — Soni Sol, Dec 13 '19 at 20:11

score 1 · Accepted Answer · answered Dec 14 '19 at 09:43

1

The only way I found to resolve this issue was to connect to CloudSQL over SSL.

const mysql = require("mysql");
const fs = require('fs');
const knex = require('knex')({
    client: 'mysql',
    version: '5.7',
    connection: {
      host : 'xx.xx.xx.xx',
      ssl: {
        ca: fs.readFileSync('ca.pem'),
        key: fs.readFileSync('client-key.pem'),
        cert: fs.readFileSync('client-cert.pem'),
      },
      user : 'root',
      password : 'xxxxxxxxx',
      database : 'mydbname',
    },
});

answered Dec 14 '19 at 09:43

beachCode

3,202
8
36
69

Where/how did you generate the files you are passing to the ssl configuration? – 1252748 Dec 31 '19 at 07:09
I generated the credentials in the GCP console. – beachCode Dec 31 '19 at 07:13
I'm sorry, do you mind linking me where exactly. I have tried making a service account, but that only allows me to download a key file, not the other two pems. Thanks. – 1252748 Dec 31 '19 at 07:17
Sure... Go to https://console.cloud.google.com/sql/instances, choose your instance and then go to Connections. Under Connections, you'll find an option to configure SSL including "Create a Client Certificate." – beachCode Dec 31 '19 at 08:08

Connecting to CloudSQL from Node.js on Heroku

1 Answers1