By using AWS re:Post, you agree to the AWS re:Post Terms of Use

Access Denied Error when Connecting Node.js App to MySQL RDS Instance

0

I'm working on a Node.js application using mysql2/promise to connect to an Amazon RDS MySQL instance. Despite having correct credentials and network configurations, I'm encountering an Access denied error. Here are the details:

Error Message:

Error: Access denied for user 'db_user'@'my.ip.address' (using password: YES)

Connection Code:

const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT
});

Environment Variables:

DB_HOST=mydatabase.example.com
DB_PORT=3306
DB_USER=db_user
DB_PASSWORD=hidden_password
DB_NAME=my_database

Security Groups: Configured to allow my local IP address.

I've verified the credentials and user privileges but still face this issue. Any insights or suggestions?

A bit more info of what I've tried as well:

GRANT ALL PRIVILEGES ON admin.* TO 'db_user'@'my.ip.address'; FLUSH PRIVILEGES;

While connected to the RDS instance locally through the workbench.

That resulted in

Access Denied

This is just a sample of one of my Express routes:

    const express = require('express');
    const dotenv = require('dotenv');
    const path = require('path');
    const mysql = require('mysql2/promise');
    const router = express.Router();
    const bcrypt = require('bcryptjs');
    const authenticateToken = require('./middleware/authenticateToken.cjs');
    
    const env = process.env.NODE_ENV || 'development';
    dotenv.config({ path: path.resolve(__dirname, '../.env') });
    
    var pool = mysql.createPool({
        host: process.env.DB_HOST,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        port: process.env.DB_PORT
      });
    
    // Handling connection establishment
    pool.getConnection((err, connection) => {
        if (err) {
          console.error('Database connection error:', err);
          return;
        }
        console.log('Database connection established');
        connection.release(); // Release the connection back to the pool
      });
    
    router.get('/get-locations', authenticateToken, async (req, res) => {
      try {
        const [results] = await pool.query('SELECT * FROM location');
        res.status(200).json(results);
      } catch (error) {
        console.error('Error fetching locations:', error);
        res.status(500).send('Error fetching locations');
      }
    });
1 Answer
0

The "Access denied" error you're encountering with your Node.js application connecting to an Amazon RDS MySQL instance can occur for several reasons. Let's explore some potential causes and solutions:

  1. Incorrect credentials: Double-check that the username and password in your environment variables are correct. Even a small typo can cause this error.

  2. Host permissions: The error message indicates that the user 'db_user' is trying to connect from 'my.ip.address'. Ensure that the MySQL user is allowed to connect from this specific IP address. You might need to modify the user's host permissions in MySQL.

  3. SSL requirement: If your RDS instance requires SSL connections, make sure your Node.js application is configured to use SSL when connecting.

  4. Security Group settings: While you mentioned that the security group is configured to allow your local IP address, double-check that the correct port (usually 3306 for MySQL) is open for your IP in the inbound rules.

  5. VPC configuration: If your RDS instance is in a VPC, ensure that your application has the necessary network access to reach the database.

  6. IAM authentication: If you're using IAM database authentication, make sure your IAM permissions are correctly set up.

  7. Database name: Verify that the database specified in DB_NAME actually exists on the RDS instance.

  8. Connection limit: Check if you've reached the maximum number of connections allowed by your RDS instance.

To troubleshoot:

  1. Try connecting to the RDS instance using a MySQL client like MySQL Workbench to isolate whether the issue is with your Node.js application or the RDS configuration.

  2. Review the RDS instance's logs for any relevant error messages.

  3. Temporarily grant broader permissions to the user and gradually restrict them to identify any permission-related issues:

GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Note: This is for testing only. Restrict permissions appropriately for production use.

  1. If using a connection pool, ensure you're handling connection errors correctly and releasing connections back to the pool when done.

  2. Check if there are any network-level issues by trying to telnet to your RDS instance's endpoint on the MySQL port:

telnet your-rds-endpoint 3306

If none of these solutions work, you may need to contact AWS support for further assistance, as there could be an issue with the RDS instance itself.
Sources
Troubleshoot access errors on RDS or Aurora MySQL DB clusters | AWS re:Post
RDS Multi-AZ DB Instance Failover Test with node.js app with select IP Address from MySQL. | AWS re:Post
Troubleshoot Amazon RDS for MySQL and Amazon RDS for MariaDB Errors | AWS Database Blog
Finding the connection information for an RDS for MySQL DB instance - Amazon Relational Database Service

profile picture
answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions