Re: Access denied for user >service account email>...

franamu · 02-26-2025 10:58 PM

Hello, how are you? I'm having trouble connecting to the SQL database. I have a Node container with Sequelize managed by Kubernetes. I followed this documentation closely: https://cloud.google.com/sql/docs/mysql/connect-instance-kubernetes?hl=en#node.js

I created a service account with the necessary permissions to access SQL. When I check the cloud-sql-proxy container, I see these logs:

franciscoamuchastegui@MacBook-Pro-de-francisco sils-help-apis % kubectl logs sils-help-apis-stage-9759784bd-5fsgt -c cloud-sql-proxy
{"severity":"INFO","timestamp":"2025-02-27T06:25:52.212Z","message":"Authorizing with Application Default Credentials"}
{"severity":"INFO","timestamp":"2025-02-27T06:25:53.728Z","message":"[sils-help-desk-stage:us-central1:sils-help-desk-stage] Listening on 127.0.0.1:3306"}
{"severity":"INFO","timestamp":"2025-02-27T06:25:53.728Z","message":"The proxy has started successfully and is ready for new connections!"}
{"severity":"INFO","timestamp":"2025-02-27T06:26:04.673Z","message":"[sils-help-desk-stage:us-central1:sils-help-desk-stage] Accepted connection from 127.0.0.1:34222"}
{"severity":"INFO","timestamp":"2025-02-27T06:26:05.273Z","message":"[sils-help-desk-stage:us-central1:sils-help-desk-stage] instance closed the connection"}
{"severity":"INFO","timestamp":"2025-02-27T06:26:10.328Z","message":"[sils-help-desk-stage:us-central1:54450] Accepted connection from 127.0.0.1:54450"}
{"severity":"INFO","timestamp":"2025-02-27T06:26:10.924Z","message":"[sils-help-desk-stage:us-central1:sils-help-desk-stage] instance closed the connection"}
{"severity":"INFO","timestamp":"2025-02-27T06:26:29.544Z","message":"[sils-help-desk-stage:us-central1:sils-help-desk-stage] Accepted connection from 127.0.0.1:33474"}

And when I check the logs of my Node server image:

AccessDeniedError [SequelizeAccessDeniedError]: Access denied for user 'mysql-service-account@sils-help-desk-stage.iam.g'@'cloudsqlproxy~34.95.220.133' (using password: YES)
    at ConnectionManager.connect (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:94:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async ConnectionManager._connect (/usr/src/app/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:222:24)
    at async /usr/src/app/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:174:32 {
  parent: Error: Access denied for user 'mysql-service-account@sils-help-desk-stage.iam.g'@'cloudsqlproxy~34.95.220.133' (using password: YES)
        at Packet.asError (/usr/src/app/node_modules/mysql2/lib/packets/packet.js:740:17)
        at ClientHandshake.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:29:26)
        at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/base/connection.js:475:34)
        at PacketParser.onPacket (/usr/src/app/node_modules/mysql2/lib/base/connection.js:93:12)
        at PacketParser.executePayload (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:139:14)
        at Socket.<anonymous> (/usr/src/app/node_modules/mysql2/lib/base/connection.js:100:25)
        at Socket.emit (node:events:517:28)
        at addChunk (node:internal/streams/readable:368:12)
        at readableAddChunk (node:internal/streams/readable:341:9)
        at Readable.push (node:internal/streams/readable:278:10) {
      code: 'ER_ACCESS_DENIED_ERROR',
      errno: 1045,
      sqlState: '28000',
      sqlMessage: "Access denied for user 'mysql-service-account@sils-help-desk-stage.iam.g'@'cloudsqlproxy~34.95.220.133' (using password: YES)",
      sql: undefined
    },

This is what I have in my Sequelize connection:

import { Sequelize } from 'sequelize';

export const sequelize = new Sequelize(
  `${process.env.DB_NAME}`,
  'mysql-service-account@sils-help-desk-stage.iam.gserviceaccount.com',
  '', //I also tried with null, but it didn't work
  {
    host: 'localhost',
    dialect: 'mysql',
    logging: false,
    port: 3306
  }
);

sequelize.authenticate();

Deployment YAML:

Lastly, I'm including my deployment.yaml:

YAML

apiVersion: apps/v1
kind: Deployment
metadata:
  name: sils-help-apis-stage
spec:
  replicas: 2
  selector:
    matchLabels:
      app: sils-help-apis-stage
      environment: stage
  template:
    metadata:
      labels:
        app: sils-help-apis-stage
        environment: stage
    spec:
      serviceAccountName: ksa-cloud-sql
      containers:
        - name: sils-help-apis-stage
          image: gcr.io/sils-help-desk-stage/sils-help-apis:v21
          ports:
            - containerPort: 80
          resources:
            requests:
              cpu: "100m"
              memory: "256Mi"
            limits:
              cpu: "500m"
              memory: "512Mi"
      initContainers:
        - name: cloud-sql-proxy
          image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.14.1
          restartPolicy: Always
          args:
            - "--port=3306"
            - "--auto-iam-authn" # Enables automatic IAM authentication
            - "--structured-logs" # Enables structured logs
            - "sils-help-desk-stage:us-central1:sils-help-desk-stage"
          securityContext:
            runAsNonRoot: true
          resources:
            requests:
              cpu: 100m # 100 millicores
              memory: 128Mi # 128 megabytes
            limits:
              cpu: 200m # 200 millicores
              memory: 256Mi # 256 megabytes

fabriferiozzi

Same issue

GonDel

i try different variants but have the same issue, help!

mokit

Hi, @franamu.

Since you're using a service account to authenticate with CloudSQL but it's missing from your YAML file in the cloud-sql-proxy init container. I recommend reading this document for further guidance - Connect to Cloud SQL from Google Kubernetes Engine.

Regards,
Mokit

pgstorm148

You're very close to getting this working! The issue lies in how you're configuring Sequelize to authenticate with Cloud SQL using IAM authentication. Let's break down the problem and how to fix it:

Understanding the Problem

IAM Authentication vs. MySQL User Authentication:
- You're using the Cloud SQL Proxy with --auto-iam-authn, which enables IAM authentication. This means the proxy uses the service account's credentials to authenticate with Cloud SQL.
- However, Sequelize is still trying to authenticate with MySQL using a username and password. While the proxy handles the initial connection using IAM, once the connection is established, MySQL still needs a valid user account.
- The error "Access denied for user 'mysql-service-account@sils-help-desk-stage.iam.g'@'cloudsqlproxy~34.95.220.133'" indicates that the service account email is being used as the MySQL username, which is incorrect in this scenario.
Cloud SQL Proxy's Role:
- The Cloud SQL Proxy handles the secure connection to your Cloud SQL instance using IAM authentication.
- It then presents a local port (3306 in your case) that your application can connect to as if it were a regular MySQL database.
- The Proxy does not handle the MySQL user login. The MySQL user login must be handled by the application.

Solution

Create a MySQL User:
- You need to create a MySQL user in your Cloud SQL instance that your Node.js application will use to connect. This user should have the necessary privileges to access your database.
- Connect to your Cloud SQL instance using the gcloud command-line tool or the Cloud Console and create a user:
SQL
```
CREATE USER 'your_mysql_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_mysql_password';
GRANT ALL PRIVILEGES ON your_database.* TO 'your_mysql_user'@'%';
FLUSH PRIVILEGES;
```
- Replace 'your_mysql_user' with your desired username, 'your_mysql_password' with a strong password, and 'your_database' with the name of your database.
- The '%' host allows connection from any IP. If you wish to make it more secure, you can limit the host. But when using the proxy, this is not needed.

Update Sequelize Configuration:

Modify your Sequelize connection configuration to use the MySQL user and password you created:

JavaScript

import { Sequelize } from 'sequelize';

export const sequelize = new Sequelize(
  `${process.env.DB_NAME}`,
  'your_mysql_user', // Use the MySQL username
  'your_mysql_password', // Use the MySQL password
  {
    host: 'localhost', // Connect to the local port exposed by the proxy
    dialect: 'mysql',
    logging: false,
    port: 3306,
  }
);

sequelize.authenticate();

Replace 'your_mysql_user' and 'your_mysql_password' with the actual credentials you set in the previous step.

Ensure Environment Variables:
- make sure that your DB_NAME enviroment variable is correctly set.

Key Points

The Cloud SQL Proxy handles the secure connection using IAM.
You still need a MySQL user and password for your application to authenticate with the database.
The host for the sequelize connection will always be localhost, as the proxy forwards the connection to that port.
When using the Cloud SQL proxy, the IP address that the database sees will be the proxy's IP. This is why the '%' host is needed on the mysql user, if you do not want to restrict the host.

By following these steps, you should be able to successfully connect to your Cloud SQL database from your Node.js application running in Kubernetes.

Access denied for user >service account email> (using password: YES)