Using Secrets Manager to Authenticate with an RDS Database Using Lambda

Create a Lambda function using Node.js version18.x. Join it to the provided VPC and the 2 public subnets. Name your function testRDS. Update the function timeout setting to 6 seconds.

1. Create a new layer called mysql and select Node.js 18.x. Upload the file MySQL Library Zip file,
2. Add the mysql layer to your testRDS function as a custom layer.

1. Use the Secrets Manager console to create a secret (username = username, initial password = password), and enable automatic credential rotation to reoccur every 1 day.
2. Name the secret RDScredentials and your Lambda rotation function rotateRDS.

In the index.js, file replace the current code with the code shown below. Issue a deploy, and once deployed, issue a test. Ensure you replace the <RDS Endpoint> placeholder with the endpoint of your RDS MySQL database.

import mysql from 'mysql2/promise';
import AWS from 'aws-sdk';

const secretName = 'RDScredentials';
const region = 'us-east-1';
const rdsEndpoint = '<RDS Endpoint>';
const databaseName = 'example';

AWS.config.update({ region: region });

const secretsManager = new AWS.SecretsManager();

export const handler = async (event, context) => {
  try {
    const data = await secretsManager.getSecretValue({ SecretId: secretName }).promise();
    const secret = JSON.parse(data.SecretString || Buffer.from(data.SecretBinary, 'base64').toString('ascii'));

    const { username, password } = secret;

    const connection = await mysql.createConnection({
      host: rdsEndpoint,
      user: username,
      password: password,
      database: databaseName,
    });

    const [rows] = await connection.execute('SHOW TABLES');

    console.log('Tables:');
    rows.forEach((row) => {
      console.log(row[`Tables_in_${databaseName}`]);
    });

    connection.end();

    return {
      statusCode: 200,
      body: 'Tables listed successfully',
    };
  } catch (err) {
    console.error('Error:', err.message);
    return {
      statusCode: 500,
      body: 'Error listing tables',
    };
  }
};

Copy and paste the following code in your index.mjs tab, replacing the <RDS Endpoint> placeholder with your own RDS endpoint. This code adds a table into the example database called pets. We will use this table as a reference to ensure successful database communication:

import mysql from 'mysql2/promise';

export const handler = async (event, context, callback) => {
  try {
    const connection = await mysql.createConnection({
      host: "<RDS Endpoint>",
      user: "username",
      password: "password",
      database: "example",
    });

    // Create 'pets' table
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS pets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
      )
    `);

    console.log('Table created: pets');

    // List all tables
    const [rows] = await connection.execute('SHOW TABLES');
    console.log('Tables:');
    rows.forEach((row) => {
      console.log(row[`Tables_in_example`]);
    });

    connection.end();

    callback(null, {
      statusCode: 200,
      body: 'Tables listed successfully',
    });
  } catch (err) {
    console.error(err);
    callback(err, {
      statusCode: 500,
      body: 'Error listing tables',
    });
  }
};

The IAM role created for Lambda to use does not have permission to the Secrets Manager service. Edit the Lambda execution role that was created and add the following AWS managed policy: SecretsManagerReadWrite.