ER_ACCESS_DENIED_ERROR CloudSQL

Question

I am receiving an error which looks like this (in my functions log)

Access denied for user \'varun_admin\'@\'cloudsqlproxy~84.117.112.32\' (using password: YES)',

    sqlMessage:
`\'varun_admin\'@\'cloudsqlproxy~84.117.112.32\' (using password: YES)',`
    sqlState: '28000',
 

fatal: true } (84.117.112.32) intentionally modified.

I have double checked my username and password, In fact, I made a request from workbench and it went fine.

This is how I am creating/initialising my sql

const mysql = require('mysql')
const config = require('./../../config.js')


const connectionName = config.DB_CONNECTION_NAME
console.log(`Connection name: ${config.DB_CONNECTION_NAME}`)
const configSQL = {
    host: config.DB_HOST,
    user: config.DB_USER,
    password: config.DB_PASSWORD,
    database: config.DB_DATABASE
}

// Connection to cloud sql in production

if (!process.env.dev) {
    configSQL.socketPath = `/cloudsql/${connectionName}`;
}

//SQL Config
const pool = mysql.createPool(configSQL)

// Checking if it was connected sucessfully or not on server startup
pool.getConnection((err, connection) => {
    if (err) {
        console.error('error connecting: ' + err)
        return
    }
    console.log('connected as id ' + connection.threadId)
    connection.release()
    return
})

And the following function would typically make a call to get data

const getEverythingFromTable = tableName => {
    return new Promise((resolve, reject) => {
        pool.getConnection((error, connection) => {
            if (error) return reject(error)
            const query = `SELECT * FROM ${tableName}`
            connection.query(query, (err, response) => {
                connection.destroy()
                if (err) return reject(err)
                return resolve(response)
            })
        })
    })
}

Any idea what I could be doing wrong?

SQL Logs

Update: 1

These are the environment values I am passing to the Cloud SQL Config

(Please refer to the code snippet above)

Where my cloudSQL config in the UI looks like this

How I am invoking functions/ calling them, the NodeJS code for it is above.

Answer 1

The error you are getting can be caused by an issue with your password or with the SSL encryption that is being used, as mentioned in the Verify how you connect section of the documentation.

I actually tried to see if I could reproduce the issue by I changing my instance configurations to Allow only SSL connections, as suggested by the Enforcing SSL/TLS section of the documentation. However, it didn’t cause the issue for me

This would not usually be a problem since, as mentioned in this post, the connections from Cloud Functions are encrypted by default when you use the cloudsqlproxy, but I had to test it in case something changed.

I also tried changing the configuration in order to restrict the access to my instance even more. However the only thing that failed my connection was disabling the connection through the Public IP and only allowing it through the Private one, and this made so the connection did not even reach the instance.

Since you mentioned you are able to connect with the Workbench, I believe there are 2 possible causes for your issue:

1. There could be a problem with the encoding of some characters in your password, that only get mess up when trying to access it from the env variables. I suggest you try with a very basic password to see if you get the same result.
2. There could be an issue with the encryption of the connection from the Cloud Function. If that is the case, this would be very specific to your project and the best way to address this issue would be to open an issue on Google’s Issue Tracker, or to open a support case, in case you have a support plan.

I hope this helps you.

Answer 2

Make sure that Cloud SQL user varun_admin has the permission to connect from host cloudsqlproxy~84.117.112.32. This could also be %, but I'd rather recommend to permit only what is required to connect (which is a single host only). Also make sure to flush privileges on mySQL, so that the account changes will be applied instantly. Also see Configuring SSL/TLS.