Secret Manager で Cloud SQL のパスワードを安全に管理する

const functions = require('@google-cloud/functions-framework');
const { SecretManagerServiceClient } = require('@google-cloud/secret-manager');
const { Pool } = require('pg');

async function accessSecretVersion(projectId, secretId, versionId = 'latest') {
  const client = new SecretManagerServiceClient();
  const name = `projects/${projectId}/secrets/${secretId}/versions/${versionId}`;
  const [version] = await client.accessSecretVersion({ name });
  return version.payload.data.toString('utf8');
}

functions.http('connectDb', async (req, res) => {
  let client;

  try {
    // Secret Managerからパスワードを取得
    const password = await accessSecretVersion(
      process.env.GOOGLE_CLOUD_PROJECT,
      'cloudsql-password'
    );

    // Cloud SQL接続設定
    const pool = new Pool({
      host: process.env.DB_HOST, // Cloud SQLのプライベートIP
      user: process.env.DB_USER,
      password: password,
      port: 5432,
      database: process.env.DB_NAME
    });

    // データベースに接続
    client = await pool.connect();

    // クエリの実行例
    const result = await client.query('SELECT NOW()');

    res.status(200).json({
      success: true,
      message: 'Cloud SQLへの接続に成功しました',
      data: result.rows[0]
    });

  } catch (error) {
    console.error('データベース接続エラー:', error);
    res.status(500).json({
      success: false,
      message: 'Cloud SQLへの接続に失敗しました',
      error: error.message
    });

  } finally {
    if (client) {
      client.release();
    }
  }
});

{
  "name": "cloudsql-secret-manager",
  "version": "1.0.0",
  "dependencies": {
    "@google-cloud/functions-framework": "^3.0.0",
    "@google-cloud/secret-manager": "^5.0.0",
    "pg": "^8.11.0"
  }
}

const functions = require('@google-cloud/functions-framework');
const { SecretManagerServiceClient } = require('@google-cloud/secret-manager');
const { Pool } = require('pg');

/**
 * ランダムなパスワードを生成（PostgreSQLのポリシーに準拠）
 */
function generatePassword(length = 20) {
  const crypto = require('crypto');
  const lowercase = 'abcdefghijklmnopqrstuvwxyz';
  const uppercase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  const numbers = '0123456789';
  const special = '!@#$%^&*()';

  // 各カテゴリから最低1文字ずつ確保
  let password = '';
  password += lowercase[crypto.randomInt(0, lowercase.length)];
  password += uppercase[crypto.randomInt(0, uppercase.length)];
  password += numbers[crypto.randomInt(0, numbers.length)];
  password += special[crypto.randomInt(0, special.length)];

  // 残りの文字をランダムに生成
  const allChars = lowercase + uppercase + numbers + special;
  for (let i = password.length; i < length; i++) {
    password += allChars[crypto.randomInt(0, allChars.length)];
  }

  // パスワードをシャッフル
  return password.split('').sort(() => crypto.randomInt(0, 2) - 1).join('');
}

/**
 * Cloud SQL のパスワードを更新
 */
async function updateCloudSQLPassword(host, user, currentPassword, newPassword) {
  const pool = new Pool({
    host: host,
    user: user,
    password: currentPassword,
    port: 5432,
  });

  const client = await pool.connect();

  try {
    await client.query(`ALTER USER ${user} WITH PASSWORD '${newPassword}'`);
  } finally {
    client.release();
    await pool.end();
  }
}

/**
 * Secret Manager のシークレットを更新
 */
async function updateSecret(projectId, secretId, newPassword) {
  const client = new SecretManagerServiceClient();
  const parent = `projects/${projectId}/secrets/${secretId}`;

  const payload = Buffer.from(newPassword, 'utf8');
  const response = await client.addSecretVersion({
    parent: parent,
    payload: {
      data: payload
    }
  });

  return response;
}

/**
 * パスワードをローテーション
 */
functions.http('rotatePassword', async (req, res) => {
  try {
    // Pub/Subメッセージからイベントタイプを取得
    const pubsubMessage = req.body.message;
    const attributes = pubsubMessage?.attributes || {};

    // SECRET_ROTATE イベントのみ処理（SECRET_VERSION_ADD は無視）
    if (attributes.eventType !== 'SECRET_ROTATE') {
      console.log(`Ignoring event type: ${attributes.eventType}`);
      res.status(200).json({
        success: true,
        message: `Event type ${attributes.eventType} ignored`
      });
      return;
    }

    console.log('Processing SECRET_ROTATE event');

    const projectId = process.env.GOOGLE_CLOUD_PROJECT;
    const dbHost = process.env.DB_HOST;
    const dbUser = process.env.DB_USER;

    // 現在のパスワードを取得
    const secretClient = new SecretManagerServiceClient();
    const name = `projects/${projectId}/secrets/cloudsql-password/versions/latest`;
    const [version] = await secretClient.accessSecretVersion({ name });
    const currentPassword = version.payload.data.toString('utf8');

    // 新しいパスワードを生成
    const newPassword = generatePassword();

    // Cloud SQL のパスワードを更新
    await updateCloudSQLPassword(dbHost, dbUser, currentPassword, newPassword);
    console.log('Cloud SQL password updated successfully');


    // Secret Manager のシークレットを更新
    await updateSecret(projectId, 'cloudsql-password', newPassword);

    console.log('Password rotation completed successfully');

    res.status(200).json({
      success: true,
      message: 'Password rotation completed successfully'
    });

  } catch (error) {
    console.error('Password rotation error:', error);
    res.status(500).json({
      success: false,
      message: 'Password rotation failed',
      error: error.message
    });
  }
});

{
  "name": "cloudsql-password-rotation",
  "version": "1.0.0",
  "dependencies": {
    "@google-cloud/functions-framework": "^3.0.0",
    "@google-cloud/secret-manager": "^5.0.0",
    "pg": "^8.11.0"
  }
}