基于Node/Express与Knex.js的数据库密码定时轮换方案

Absolutely! You can update Knex.js database credentials at runtime without restarting your Node/Express app—this is totally doable, and perfect for your AWS Secrets Manager + daily password rotation setup. Let’s break down how to implement this smoothly:

First, remember that Knex uses a connection pool to reuse database connections. Just updating a static config won’t work because existing pooled connections will still use the old password. We need to ensure:

• New connections always fetch the latest credentials
• Old connections are replaced with new ones using the updated password

1. Abstract Credential Fetching from AWS Secrets Manager #

Create a reusable function to pull the latest database credentials, with optional caching (to avoid hitting Secrets Manager on every connection). Make sure the cache expires before your daily rotation window (e.g., 23 hours) to guarantee fresh credentials when needed:

const { SecretsManagerClient, GetSecretValueCommand } = require("@aws-sdk/client-secrets-manager");
const secretsClient = new SecretsManagerClient({ region: process.env.AWS_REGION });

// Cache credentials with an expiration timestamp
let cachedCredentials = null;
let cacheExpiry = 0;

async function getDbCredentials() {
  // Return cached credentials if they're still valid
  if (cachedCredentials && Date.now() < cacheExpiry) {
    return cachedCredentials;
  }

  try {
    const command = new GetSecretValueCommand({ SecretId: process.env.DB_SECRET_ID });
    const response = await secretsClient.send(command);
    const secrets = JSON.parse(response.SecretString);
    
    // Cache for 23 hours (to beat daily rotation)
    cachedCredentials = secrets;
    cacheExpiry = Date.now() + (23 * 60 * 60 * 1000);
    return secrets;
  } catch (err) {
    console.error("Failed to fetch DB secrets:", err);
    // Fallback to cached credentials if available, to avoid app crash
    if (cachedCredentials) {
      return cachedCredentials;
    }
    throw err; // If no cache exists, propagate the error
  }
}

2. Initialize Knex with a Dynamic Connection Function #

Instead of passing a static connection object to Knex, use a function for the connection option. This function runs every time Knex creates a new database connection, ensuring it always uses the latest credentials from Secrets Manager:

const knex = require("knex");

// Initialize Knex with dynamic connection logic
const db = knex({
  client: "postgresql", // Replace with your DB client (mysql2, etc.)
  connection: async () => {
    const secrets = await getDbCredentials();
    return {
      host: process.env.DB_HOST,
      user: secrets.dbUser,
      password: secrets.dbPassword,
      database: secrets.dbName,
      port: process.env.DB_PORT || 5432
    };
  },
  pool: {
    min: 2,
    max: 10,
    // Set idle timeout to 1 hour so old connections are recycled regularly
    idleTimeoutMillis: 3600000,
    // Optional: Enable connection retries if credentials are updated mid-request
    retry: {
      enableTimeout: true,
      maxRetries: 3
    }
  }
});

3. Force Connection Pool Refresh (Optional but Recommended) #

Even with an idle timeout, you might want to immediately replace all existing connections when the password rotates. Create a function to refresh the pool, and trigger it when you get notified of a password change (via AWS SNS, or a scheduled job):

async function refreshDbConnectionPool() {
  try {
    // Clear the cached credentials to force a fresh fetch
    cachedCredentials = null;
    cacheExpiry = 0;

    // Destroy the existing connection pool
    await db.client.pool.destroy();
    
    // Recreate the pool (Knex will automatically use the new credentials for new connections)
    db.client.pool = db.client.createPool();
    
    console.log("Successfully refreshed database connection pool with new credentials");
  } catch (err) {
    console.error("Failed to refresh DB connection pool:", err);
  }
}

Triggering the Refresh #

• Option 1: Scheduled Job Use a library like node-schedule to run refreshDbConnectionPool() shortly after your daily password rotation time:

  const schedule = require("node-schedule");
  // Run every day at 00:30 AM (adjust to match your Secrets Manager rotation time)
  schedule.scheduleJob("30 0 * * *", refreshDbConnectionPool);
  

• Option 2: Event-Driven Subscribe to AWS Secrets Manager’s rotation events via SNS. When your app receives a notification that the secret has been updated, call refreshDbConnectionPool() immediately. This is more reliable than a fixed schedule.

• Secrets Manager Downtime: Always fallback to cached credentials if fetching new secrets fails (as shown in getDbCredentials()).
• Connection Failures: Knex’s retry logic will help handle temporary failures when credentials are rotating. Add logging to track failed connection attempts.
• Testing: Manually trigger a password rotation in Secrets Manager and verify your app continues working without restarting.

内容的提问来源于stack exchange，提问作者fredrik