[Database] 회원가입/로그인 실습(MySQL+Redis+PostgreSQL)

우유·2026년 2월 9일
DatabasePostgreSQLcloudmysqlredis

[Cloud] Database

목록 보기
27/28
post-thumbnail

(MySQL=회원 주DB/세션 백업, Redis=세션 캐시, PostgreSQL=이벤트로그)

(우분투 3대, IP 고정: VM1=192.168.80.110 / VM2=192.168.80.120 / VM3=192.168.80.130)

구성 목표:

  • VM1: Node.js(웹+API) + 프론트(정적)
  • VM2: MySQL(회원/세션 백업) + Redis(세션 캐시)
  • VM3: PostgreSQL(JSONB 이벤트 로그/분석)

핵심 데모:

  • 평소엔 Redis로 빠르게 인증, Redis 다운 시 자동으로 MySQL로 인증 폴백
  • 이벤트는 PostgreSQL JSONB로 저장/검색/집계 → “별도 DB 쓰는 이점 체감”

0. 사전 준비(세 VM 공통)

0-1) 호스트명(권장)

각 VM에서:

VM1

sudo hostnamectl set-hostname vm1-app

VM2

sudo hostnamectl set-hostname vm2-data

VM3

sudo hostnamectl set-hostname vm3-log

0-2) /etc/hosts 등록(권장: 편의)

세 VM 모두에서:

sudo tee -a /etc/hosts >/dev/null <<'EOF'
192.168.80.110 vm1-app
192.168.80.120 vm2-data
192.168.80.130 vm3-log
EOF

1. VM2(192.168.80.120) - MySQL + Redis 설치/설정

1-1) 패키지 설치

sudo apt update
sudo apt install -y mysql-server redis-server
sudo systemctl enable --now mysql
sudo systemctl enable --now redis-server

1-2) MySQL 외부 접속 허용(bind-address)

기본은 로컬만 열려있을 수 있어요. 아래로 수정:

sudo sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql

리스닝 확인:

sudo ss -lntp | grep 3306

1-3) Redis 외부 접속 허용(교육용)

Redis 기본은 로컬만 바인드일 수 있습니다.

(1) bind를 0.0.0.0로

sudo sed -i "s/^bind .*/bind 0.0.0.0 ::1/" /etc/redis/redis.conf

(2) protected-mode 해제(실습에서만)

sudo sed -i "s/^protected-mode yes/protected-mode no/" /etc/redis/redis.conf
sudo systemctl restart redis-server

리스닝 확인:

sudo ss -lntp | grep 6379

1-4) MySQL DB/계정/테이블 생성

MySQL root로 실행:

sudo mysql <<'SQL'
CREATE DATABASE IF NOT EXISTS auth_lab DEFAULT CHARACTER SET utf8mb4;

CREATE USER IF NOT EXISTS 'app'@'192.168.80.%' IDENTIFIED BY 'apppass';
GRANT ALL PRIVILEGES ON auth_lab.* TO 'app'@'192.168.80.%';
FLUSH PRIVILEGES;

USE auth_lab;

CREATE TABLE IF NOT EXISTS users (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Redis 장애 대비 "세션 백업" 테이블(자동 폴백에서 핵심)
CREATE TABLE IF NOT EXISTS sessions (
  token VARCHAR(128) PRIMARY KEY,
  user_id BIGINT NOT NULL,
  data JSON NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sessions_expires (expires_at),
  KEY idx_sessions_user_id (user_id)
);
SQL

2. VM3(192.168.80.130) - PostgreSQL(JSONB 이벤트 로그) 설치/설정

2-1) 설치/동작

sudo apt update
sudo apt install -y postgresql
sudo systemctl enable --now postgresql

2-2) DB/사용자 생성

sudo -u postgres psql -c "CREATE DATABASE monitor_lab;"
sudo -u postgres psql -c "CREATE USER pgapp WITH PASSWORD 'pgpass';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE monitor_lab TO pgapp;"

2-3) 외부 접속 허용

(1) postgresql.conf에서 listen_addresses

sudo sed -i "s/^#listen_addresses =.*/listen_addresses = '*'/" /etc/postgresql/*/main/postgresql.conf

(2) pg_hba.conf에 접근 허용

sudo tee -a /etc/postgresql/*/main/pg_hba.conf >/dev/null <<'EOF'
host    all             all             192.168.80.0/24          md5
EOF

재시작:

sudo systemctl restart postgresql

리스닝 확인:

sudo ss -lntp | grep 5432

2-4) 이벤트 테이블(JSONB) 생성

sudo -iu postgres psql -d monitor_lab <<'SQL'
CREATE TABLE IF NOT EXISTS auth_events (
  event_id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  event_type TEXT NOT NULL,
  actor TEXT NULL,
  ip INET NULL,
  payload JSONB NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_auth_events_ts ON auth_events (ts DESC);
CREATE INDEX IF NOT EXISTS idx_auth_events_type ON auth_events (event_type);
CREATE INDEX IF NOT EXISTS idx_auth_events_payload_gin ON auth_events USING GIN (payload);
SQL

권한 추가

sudo -u postgres psql -d monitor_lab <<'SQL'
GRANT USAGE ON SCHEMA public TO pgapp;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.auth_events TO pgapp;

-- serial/identity 사용 시 시퀀스 권한도 필요할 수 있음
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pgapp;

-- 앞으로 생성되는 테이블에도 자동으로 권한 부여(권장)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pgapp;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO pgapp;
SQL

3. VM1(192.168.80.110) - Node.js(웹+API) 설치 + 전체 코드 배포

3-1) Node.js 설치(우분투 기본 repo 사용)

sudo apt update
curl -fsSL https://deb.nodesource.com/setup_20.x | sudo -E bash -
sudo apt install -y nodejs npm
node -v
npm -v

3-2) 프로젝트 생성

mkdir -p ~/signup-lab/{db,store,public}
cd ~/signup-lab
npm init -y
npm install express mysql2 redis bcrypt dotenv pg

3-3) .env 생성

cat > ~/signup-lab/.env <<'EOF'
PORT=8080

MYSQL_HOST=192.168.80.120
MYSQL_PORT=3306
MYSQL_USER=app
MYSQL_PASSWORD=apppass
MYSQL_DATABASE=auth_lab

REDIS_HOST=192.168.80.120
REDIS_PORT=6379

SESSION_STORE=fallback
SESSION_TTL_SECONDS=1800

PG_HOST=192.168.80.130
PG_PORT=5432
PG_USER=pgapp
PG_PASSWORD=pgpass
PG_DATABASE=monitor_lab
EOF

4. VM1 - 전체 코드(파일별로)

4-1) db/mysql.js

cat > ~/signup-lab/db/mysql.js <<'EOF'
const mysql = require("mysql2/promise");

let pool;

function getMysqlPool(env) {
  if (!pool) {
    pool = mysql.createPool({
      host: env.MYSQL_HOST,
      port: Number(env.MYSQL_PORT || 3306),
      user: env.MYSQL_USER,
      password: env.MYSQL_PASSWORD,
      database: env.MYSQL_DATABASE,
      connectionLimit: 10
    });
  }
  return pool;
}

module.exports = { getMysqlPool };
EOF

4-2) db/pg.js

cat > ~/signup-lab/db/pg.js <<'EOF'
const { Pool } = require("pg");

let pool;

function getPgPool(env) {
  if (!pool) {
    pool = new Pool({
      host: env.PG_HOST,
      port: Number(env.PG_PORT || 5432),
      user: env.PG_USER,
      password: env.PG_PASSWORD,
      database: env.PG_DATABASE,
      max: 10
    });
  }
  return pool;
}

module.exports = { getPgPool };
EOF

4-3) store/redisSessionStore.js

cat > ~/signup-lab/store/redisSessionStore.js <<'EOF'
function createRedisSessionStore(redisClient) {
  return {
    async set(token, payloadObj, ttlSec) {
      await redisClient.setEx(`sess:${token}`, ttlSec, JSON.stringify(payloadObj));
    },
    async get(token) {
      const v = await redisClient.get(`sess:${token}`);
      return v ? JSON.parse(v) : null;
    },
    async del(token) {
      await redisClient.del(`sess:${token}`);
    }
  };
}

module.exports = { createRedisSessionStore };
EOF

4-4) store/mysqlSessionStore.js

cat > ~/signup-lab/store/mysqlSessionStore.js <<'EOF'
function createMysqlSessionStore(mysqlPool) {
  return {
    async set(token, payloadObj, ttlSec) {
      await mysqlPool.query(
        `INSERT INTO sessions(token, user_id, data, expires_at)
         VALUES(?, ?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND))
         ON DUPLICATE KEY UPDATE
           user_id=VALUES(user_id),
           data=VALUES(data),
           expires_at=VALUES(expires_at)`,
        [token, payloadObj.user_id, JSON.stringify(payloadObj), ttlSec]
      );
    },

    async get(token) {
      const [rows] = await mysqlPool.query(
        `SELECT data, expires_at
         FROM sessions
         WHERE token = ?`,
        [token]
      );

      if (rows.length === 0) return null;

      const r = rows[0];
      if (new Date(r.expires_at).getTime() <= Date.now()) return null;

      if (r.data == null) return null;

      // mysql2가 JSON 컬럼을 string 또는 object로 반환할 수 있음
      if (typeof r.data === "string") return JSON.parse(r.data);
      return r.data;
    },

    async del(token) {
      await mysqlPool.query(`DELETE FROM sessions WHERE token=?`, [token]);
    },

    async cleanupExpired() {
      await mysqlPool.query(`DELETE FROM sessions WHERE expires_at < NOW()`);
    }
  };
}

module.exports = { createMysqlSessionStore };
EOF

4-5) store/fallbackSessionStore.js (자동 폴백 : redis 장애시 mysql에서 인증)

cat > ~/signup-lab/store/fallbackSessionStore.js <<'EOF'
const { createRedisSessionStore } = require("./redisSessionStore");
const { createMysqlSessionStore } = require("./mysqlSessionStore");

function createFallbackSessionStore({ redisClient, mysqlPool, breakerMs = 3000 }) {
  const redisStore = redisClient ? createRedisSessionStore(redisClient) : null;
  const mysqlStore = createMysqlSessionStore(mysqlPool);

  let redisDownUntil = 0;

  const redisUsableNow = () => redisStore && Date.now() >= redisDownUntil;
  const markRedisDown = () => { redisDownUntil = Date.now() + breakerMs; };

  return {
    async set(token, payloadObj, ttlSec) {
      // 장애 대비: MySQL에 항상 저장
      await mysqlStore.set(token, payloadObj, ttlSec);

      // 성능: Redis는 가능하면 저장(실패해도 로그인 성공)
      if (redisUsableNow()) {
        try {
          await redisStore.set(token, payloadObj, ttlSec);
        } catch (e) {
          markRedisDown();
          console.error("[fallback] Redis set failed -> keep MySQL only:", e.message);
        }
      }
    },

    async get(token) {
      // Redis 우선
      if (redisUsableNow()) {
        try {
          const v = await redisStore.get(token);
          if (v) return v;
        } catch (e) {
          markRedisDown();
          console.error("[fallback] Redis get failed -> fallback to MySQL:", e.message);
        }
      }
      // MySQL 폴백
      return await mysqlStore.get(token);
    },

    async del(token) {
      try { await mysqlStore.del(token); } catch (e) {
        console.error("[fallback] MySQL del failed:", e.message);
      }

      if (redisUsableNow()) {
        try { await redisStore.del(token); } catch (e) {
          markRedisDown();
          console.error("[fallback] Redis del failed:", e.message);
        }
      }
    },

    async cleanupExpired() {
      await mysqlStore.cleanupExpired();
    }
  };
}

module.exports = { createFallbackSessionStore };
EOF

4-6) store/sessionStore.js

cat > ~/signup-lab/store/sessionStore.js <<'EOF'
const { createRedisSessionStore } = require("./redisSessionStore");
const { createMysqlSessionStore } = require("./mysqlSessionStore");
const { createFallbackSessionStore } = require("./fallbackSessionStore");

function createSessionStore({ type, redisClient, mysqlPool }) {
  const t = (type || "redis").toLowerCase().trim();

  if (t === "mysql") return createMysqlSessionStore(mysqlPool);
  if (t === "fallback") return createFallbackSessionStore({ redisClient, mysqlPool, breakerMs: 3000 });

  return createRedisSessionStore(redisClient);
}

module.exports = { createSessionStore };
EOF

4-7) public/index.html

cat > ~/signup-lab/public/index.html <<'EOF'
<!doctype html>
<html lang="ko">
<head>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width,initial-scale=1" />
  <title>Auth Lab</title>
  <link rel="stylesheet" href="/public/style.css" />
</head>
<body>
  <header class="header">
    <h1>Auth Lab: Redis 자동 폴백 + PostgreSQL(JSONB) 이벤트 로그</h1>
    <div class="sub">
      세션은 Redis 우선, Redis 장애 시 MySQL로 자동 폴백. 이벤트는 PostgreSQL JSONB로 저장/분석.
    </div>
  </header>

  <main class="grid">
    <section class="card">
      <h2>회원가입</h2>
      <form id="formSignup" class="stack">
        <input name="email" placeholder="email" required />
        <input name="password" type="password" placeholder="password" required />
        <button type="submit">Signup</button>
      </form>
      <pre id="outSignup" class="pre"></pre>
    </section>

    <section class="card">
      <h2>로그인 / 인증</h2>
      <form id="formLogin" class="stack">
        <input name="email" placeholder="email" required />
        <input name="password" type="password" placeholder="password" required />
        <button type="submit">Login</button>
      </form>

      <div class="row">
        <button id="btnMe" type="button">/me 조회</button>
        <button id="btnLogout" type="button">Logout</button>
      </div>

      <div class="hint">* 토큰은 브라우저 localStorage에 저장됩니다.</div>
      <pre id="outLogin" class="pre"></pre>
    </section>

    <section class="card wide">
      <h2>PostgreSQL 이벤트 로그/분석(체감)</h2>

      <div class="row">
        <button id="btnEvents" type="button">최근 이벤트 50개</button>
        <button id="btnStats" type="button">이벤트 통계(60분)</button>
      </div>

      <div class="row">
        <input id="searchKey" placeholder="payload key 예: reason" />
        <input id="searchValue" placeholder="value 예: bad_password" />
        <button id="btnSearch" type="button">JSONB 검색</button>
      </div>

      <pre id="outEvents" class="pre"></pre>
    </section>

    <section class="card wide">
      <h2>운영 확인 포인트</h2>
      <ul>
        <li><b>Redis 정상</b>: <code>redis-cli keys "sess:*"</code>로 세션 존재 확인</li>
        <li><b>Redis 다운</b>: <code>sudo systemctl stop redis-server</code> 후에도 <code>/me</code>가 되면 폴백 성공</li>
        <li><b>MySQL 세션</b>: <code>SELECT token, expires_at FROM sessions ORDER BY created_at DESC;</code></li>
        <li><b>PostgreSQL 이벤트</b>: <code>SELECT event_type, count(*) FROM auth_events GROUP BY event_type;</code></li>
      </ul>
      <pre id="outInfo" class="pre"></pre>
    </section>
  </main>

  <script src="/public/app.js"></script>
</body>
</html>
EOF

4-8) public/style.css

cat > ~/signup-lab/public/style.css <<'EOF'
* { box-sizing: border-box; }
body { margin:0; font-family: system-ui, -apple-system, Segoe UI, Roboto, sans-serif; background:#0b0f17; color:#e8eefc; }
.header { padding:18px 20px; border-bottom:1px solid #24304b; background:#0f1626; }
.header h1 { margin:0 0 6px 0; font-size:18px; }
.sub { color:#a9b7d6; font-size:12px; }

.grid { display:grid; grid-template-columns: 1fr 1fr; gap:14px; padding:14px; }
.card { background:#0f1626; border:1px solid #24304b; border-radius:12px; padding:14px; }
.card.wide { grid-column: 1 / span 2; }

h2 { margin:0 0 10px 0; font-size:16px; }
.stack { display:flex; flex-direction:column; gap:10px; }
.row { display:flex; gap:10px; margin-top:10px; flex-wrap:wrap; }
input { background:#111a2e; color:#e8eefc; border:1px solid #24304b; border-radius:8px; padding:10px; min-width: 220px; }
button { background:#1f6feb; color:white; border:none; border-radius:8px; padding:10px 12px; cursor:pointer; }
button:hover { filter: brightness(1.05); }
.pre { background:#0b0f17; border:1px solid #24304b; border-radius:10px; padding:12px; overflow:auto; max-height:320px; }
.hint { color:#a9b7d6; font-size:12px; margin-top:8px; }
code { color:#a9b7d6; }
EOF

4-9) public/app.js

cat > ~/signup-lab/public/app.js <<'EOF'
async function api(path, opts) {
  const r = await fetch(path, opts);
  const ct = r.headers.get("content-type") || "";
  const body = ct.includes("application/json") ? await r.json() : await r.text();
  if (!r.ok) throw new Error(typeof body === "string" ? body : JSON.stringify(body));
  return body;
}

function setOut(sel, v) {
  document.querySelector(sel).textContent =
    typeof v === "string" ? v : JSON.stringify(v, null, 2);
}

function getToken() {
  return localStorage.getItem("token");
}
function setToken(t) {
  localStorage.setItem("token", t);
}

async function main() {
  try {
    const info = await api("/api/info");
    setOut("#outInfo", info);
  } catch (e) {
    setOut("#outInfo", e.message);
  }

  document.querySelector("#formSignup").addEventListener("submit", async (e) => {
    e.preventDefault();
    const body = Object.fromEntries(new FormData(e.target).entries());
    try {
      const out = await api("/api/signup", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(body)
      });
      setOut("#outSignup", out);
    } catch (err) {
      setOut("#outSignup", err.message);
    }
  });

  document.querySelector("#formLogin").addEventListener("submit", async (e) => {
    e.preventDefault();
    const body = Object.fromEntries(new FormData(e.target).entries());
    try {
      const out = await api("/api/login", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(body)
      });
      setToken(out.token);
      setOut("#outLogin", out);
    } catch (err) {
      setOut("#outLogin", err.message);
    }
  });

  document.querySelector("#btnMe").addEventListener("click", async () => {
    try {
      const token = getToken();
      const out = await api("/api/me", {
        headers: { "Authorization": `Bearer ${token}` }
      });
      setOut("#outLogin", out);
    } catch (err) {
      setOut("#outLogin", err.message);
    }
  });

  document.querySelector("#btnLogout").addEventListener("click", async () => {
    try {
      const token = getToken();
      const out = await api("/api/logout", {
        method: "POST",
        headers: { "Authorization": `Bearer ${token}` }
      });
      localStorage.removeItem("token");
      setOut("#outLogin", out);
    } catch (err) {
      setOut("#outLogin", err.message);
    }
  });

  document.querySelector("#btnEvents").addEventListener("click", async () => {
    try {
      const out = await api("/api/events?limit=50");
      setOut("#outEvents", out);
    } catch (err) {
      setOut("#outEvents", err.message);
    }
  });

  document.querySelector("#btnStats").addEventListener("click", async () => {
    try {
      const out = await api("/api/events/stats?minutes=60");
      setOut("#outEvents", out);
    } catch (err) {
      setOut("#outEvents", err.message);
    }
  });

  document.querySelector("#btnSearch").addEventListener("click", async () => {
    try {
      const key = document.querySelector("#searchKey").value.trim();
      const value = document.querySelector("#searchValue").value.trim();
      const out = await api(`/api/events/search?key=${encodeURIComponent(key)}&value=${encodeURIComponent(value)}`);
      setOut("#outEvents", out);
    } catch (err) {
      setOut("#outEvents", err.message);
    }
  });
}

main();
EOF

4-10) server.js

cat > ~/signup-lab/server.js <<'EOF'
require("dotenv").config();
const express = require("express");
const path = require("path");
const bcrypt = require("bcrypt");
const crypto = require("crypto");
const { createClient } = require("redis");

const { getMysqlPool } = require("./db/mysql");
const { getPgPool } = require("./db/pg");
const { createSessionStore } = require("./store/sessionStore");

const env = process.env;
const app = express();

app.use(express.json());
app.use("/public", express.static(path.join(__dirname, "public")));

function storeType() {
  return (env.SESSION_STORE || "redis").toLowerCase().trim();
}

function extractBearerToken(req) {
  const h = req.headers["authorization"] || "";
  const m = h.match(/^Bearer\s+(.+)$/i);
  return m ? m[1] : null;
}

function getClientIp(req) {
  return req.socket?.remoteAddress || null;
}

(async () => {
  try {
    const st = storeType();
    console.log("[BOOT] SESSION_STORE =", st);

    const mysqlPool = getMysqlPool(env);
    console.log("[BOOT] MySQL pool ready");

    const pgPool = getPgPool(env);
    console.log("[BOOT] PostgreSQL pool ready");

    // Redis: fallback/redis 모드에서만 연결 시도
    // - redis 모드: Redis 없으면 종료(fail-closed)
    // - fallback 모드: Redis 없으면 MySQL로 계속 서비스
    let redis = null;
    if (st === "redis" || st === "fallback") {
      try {
        redis = createClient({
          socket: { host: env.REDIS_HOST, port: Number(env.REDIS_PORT || 6379) }
        });
        redis.on("error", (e) => console.error("Redis error:", e.message));
        await redis.connect();
        console.log("[BOOT] Redis connected");
      } catch (e) {
        console.error("[BOOT] Redis connect failed:", e.message);
        if (st === "redis") {
          console.error("[BOOT] SESSION_STORE=redis 이므로 종료(fail-closed)");
          process.exit(1);
        }
        console.log("[BOOT] fallback 모드 → Redis 없이 MySQL로 계속 서비스");
        redis = null;
      }
    }

    const sessionStore = createSessionStore({
      type: st,
      redisClient: redis,
      mysqlPool
    });

    async function logEvent({ eventType, actor, ip, payload }) {
      try {
        await pgPool.query(
          `INSERT INTO auth_events(event_type, actor, ip, payload)
           VALUES ($1, $2, $3, $4::jsonb)`,
          [eventType, actor || null, ip || null, JSON.stringify(payload || {})]
        );
      } catch (e) {
        console.error("[event] insert failed:", e.message);
      }
    }

    app.get("/", (req, res) => res.sendFile(path.join(__dirname, "public", "index.html")));

    app.get("/api/info", (req, res) => {
      res.json({
        session_store: st,
        session_ttl_seconds: Number(env.SESSION_TTL_SECONDS || 1800),
        mysql: `${env.MYSQL_HOST}:${env.MYSQL_PORT || 3306}/${env.MYSQL_DATABASE}`,
        postgres: `${env.PG_HOST}:${env.PG_PORT || 5432}/${env.PG_DATABASE}`,
        redis: redis ? `${env.REDIS_HOST}:${env.REDIS_PORT || 6379}` : "(not connected)",
        behavior: [
          "fallback 모드: 로그인 시 세션을 MySQL에 항상 저장하고 Redis가 있으면 Redis에도 저장",
          "인증(/me): Redis 우선 조회 → 실패 시 MySQL로 자동 폴백",
          "이벤트는 PostgreSQL JSONB로 저장되어 검색/집계 실습 가능"
        ]
      });
    });

    app.post("/api/signup", async (req, res) => {
      const ip = getClientIp(req);
      const { email, password } = req.body || {};
      if (!email || !password) return res.status(400).send("email/password required");

      try {
        const password_hash = await bcrypt.hash(password, 10);
        await mysqlPool.query(
          `INSERT INTO users(email, password_hash) VALUES(?, ?)`,
          [email, password_hash]
        );

        await logEvent({ eventType: "signup", actor: email, ip, payload: { email, result: "ok" } });
        res.json({ ok: true });
      } catch (e) {
        const msg = String(e.message || "");
        if (msg.toLowerCase().includes("duplicate")) {
          await logEvent({ eventType: "signup", actor: email, ip, payload: { email, result: "duplicate" } });
          return res.status(409).send("email already exists");
        }
        console.error("[signup] error:", e);
        await logEvent({ eventType: "signup", actor: email, ip, payload: { email, result: "error", error: msg.slice(0, 200) } });
        res.status(500).send("signup failed");
      }
    });

    app.post("/api/login", async (req, res) => {
      const ip = getClientIp(req);
      const { email, password } = req.body || {};
      if (!email || !password) return res.status(400).send("email/password required");

      try {
        const [rows] = await mysqlPool.query(
          `SELECT user_id, email, password_hash FROM users WHERE email=?`,
          [email]
        );
        if (rows.length === 0) {
          await logEvent({ eventType: "login_fail", actor: email, ip, payload: { reason: "no_user" } });
          return res.status(401).send("invalid credentials");
        }

        const user = rows[0];
        const ok = await bcrypt.compare(password, user.password_hash);
        if (!ok) {
          await logEvent({ eventType: "login_fail", actor: email, ip, payload: { reason: "bad_password" } });
          return res.status(401).send("invalid credentials");
        }

        const token = crypto.randomBytes(24).toString("hex");
        const ttl = Number(env.SESSION_TTL_SECONDS || 1800);

        await sessionStore.set(token, { user_id: user.user_id, email: user.email }, ttl);

        await logEvent({
          eventType: "login_success",
          actor: email,
          ip,
          payload: { user_id: user.user_id, ttl_seconds: ttl, session_store: st }
        });

        res.json({ token, ttl_seconds: ttl, session_store: st });
      } catch (e) {
        console.error("[login] error:", e);
        await logEvent({ eventType: "login_error", actor: email, ip, payload: { error: String(e.message || "").slice(0, 200) } });
        res.status(500).send("login failed");
      }
    });

    async function auth(req, res, next) {
      const ip = getClientIp(req);
      try {
        const token = extractBearerToken(req);
        if (!token) return res.status(401).send("missing token");

        const sess = await sessionStore.get(token);
        if (!sess) {
          await logEvent({ eventType: "auth_fail", actor: null, ip, payload: { reason: "no_session" } });
          return res.status(401).send("invalid/expired session");
        }

        req.user = sess;
        req.token = token;
        next();
      } catch (e) {
        console.error("[auth] error:", e);
        await logEvent({ eventType: "auth_error", actor: null, ip, payload: { error: String(e.message || "").slice(0, 200) } });
        res.status(500).send("auth failed");
      }
    }

    app.get("/api/me", auth, async (req, res) => {
      res.json({ ok: true, user: req.user, session_store: st });
    });

    app.post("/api/logout", auth, async (req, res) => {
      const ip = getClientIp(req);
      await sessionStore.del(req.token);
      await logEvent({ eventType: "logout", actor: req.user?.email, ip, payload: { user_id: req.user?.user_id } });
      res.json({ ok: true });
    });

    app.get("/api/events", async (req, res) => {
      const limit = Math.min(Number(req.query.limit || 50), 200);
      const type = req.query.type || null;

      const params = [];
      let where = "";
      if (type) {
        where = "WHERE event_type = $1";
        params.push(type);
      }

      const sql =
        `SELECT event_id, ts, event_type, actor, ip, payload
         FROM auth_events
         ${where}
         ORDER BY ts DESC
         LIMIT ${limit}`;

      const r = await pgPool.query(sql, params);
      res.json({ ok: true, rows: r.rows });
    });

    app.get("/api/events/stats", async (req, res) => {
      const minutes = Math.min(Math.max(Number(req.query.minutes || 60), 1), 1440);

      const r = await pgPool.query(
        `SELECT event_type, COUNT(*)::int AS cnt
         FROM auth_events
         WHERE ts >= NOW() - ($1 || ' minutes')::interval
         GROUP BY event_type
         ORDER BY cnt DESC`,
        [String(minutes)]
      );

      res.json({ ok: true, minutes, rows: r.rows });
    });

    app.get("/api/events/search", async (req, res) => {
      const key = req.query.key;
      const value = req.query.value;
      if (!key || !value) return res.status(400).send("key/value required");

      const r = await pgPool.query(
        `SELECT event_id, ts, event_type, actor, ip, payload
         FROM auth_events
         WHERE payload ->> $1 = $2
         ORDER BY ts DESC
         LIMIT 100`,
        [key, value]
      );

      res.json({ ok: true, key, value, rows: r.rows });
    });

    app.post("/api/admin/sessions/cleanup", async (req, res) => {
      if (typeof sessionStore.cleanupExpired !== "function") {
        return res.status(400).send("cleanup not supported in this mode");
      }
      await sessionStore.cleanupExpired();
      res.json({ ok: true });
    });

    const port = Number(env.PORT || 8080);
    app.listen(port, "0.0.0.0", () => {
      console.log(`[BOOT] Listening on http://0.0.0.0:${port}`);
    });
  } catch (e) {
    console.error("[FATAL] server crashed:", e);
    process.exit(1);
  }
})();
EOF

5) 실행 확인

5-1) VM1에서 서버 기동

cd ~/signup-lab
node server.js

포트 확인:

sudo ss -lntp | grep 8080

5-2) 브라우저 접속

  • http://192.168.80.110:8080

6) “자동 폴백” 실습

Step 1) Redis 정상 상태에서 로그인

  • 회원가입 → 로그인 → /me 정상

VM2에서 Redis 키 확인:

redis-cli keys "sess:*"

VM2에서 MySQL 세션 확인:

mysql -uroot -e "USE auth_lab; SELECT token, expires_at FROM sessions ORDER BY created_at DESC LIMIT 5;"

Step 2) Redis 다운

VM2에서:

sudo systemctl stop redis-server

이 상태에서 VM1 웹에서:

  • /me 버튼을 다시 눌러도 정상이어야 함
    • (자동 폴백 성공) ⇒ 실무에서는 Redis 이중화를 통해 장애대비. mysql에 세션을 백업하는건 중복 쓰기와 저장공간 낭비 측면에서 비효율적.

Step 3) Redis 복구

VM2에서:

sudo systemctl start redis-server

7) “PostgreSQL을 별도로 운영하는 이점”

Step 1) 이벤트 쌓기

  • 로그인 실패(비번 틀림) 3회
  • 로그인 성공 1회
  • 로그아웃 1회

Step 2) 웹에서 이벤트 조회/통계

  • “최근 이벤트 50개”
  • “이벤트 통계(60분)”
  • JSONB 검색:
    • key: reason
    • value: bad_password

Step 3) VM3에서 SQL로 직접 확인(운영자 관점)

VM3에서:

sudo -u postgres psql -d monitor_lab -c "SELECT event_type, count(*) FROM auth_events GROUP BY event_type ORDER BY 2 DESC;"

결론

  • MySQL은 “회원/세션”처럼 구조가 비교적 고정된 데이터에 적합
  • PostgreSQL(JSONB)은 “이벤트/로그”처럼 필드가 계속 늘어나는 데이터를
    • 스키마 변경 없이 저장
    • 검색(payload ->> 'key') 가능
    • GIN 인덱스로 성능 확보 가능 → 분리 운영의 실질적 이점

JSONB 추가 실습

“운영 중에 로그에 새로운 정보가 필요해졌다.

그런데 이미 서비스는 돌아가고 있다.

이때 PostgreSQL JSONB는 테이블 변경 없이 바로 확장할 수 있다.”

실습 개요

기존 상태

  • auth_events 테이블
  • payload JSONB 컬럼 하나에 이벤트 정보 저장
{
  "reason": "bad_password"
}

실습 후 상태

  • 컬럼 추가 없음
  • payload에 새 필드가 계속 추가됨
{
  "reason": "bad_password",
  "attempt": 3,
  "user_agent": "Mozilla/5.0 ...",
  "geo": { "country": "KR", "city": "Seoul" }
}

→ DDL 없음, 서비스 중단 없음

1단계 실습: “요구사항이 늘어남”

시나리오

보안팀에서 요청이 왔다

“로그인 실패 시 몇 번째 실패인지(attempt) 와 브라우저 정보(user_agent) 도 같이 남겨달라”

2단계 실습: 코드 수정

📍 VM1 – server.js

기존 로그인 실패 로그 (이미 있음)

await logEvent({
  eventType: "login_fail",
  actor: email,
  ip,
  payload: { reason: "bad_password" }
});

🔽 필드 추가

await logEvent({
  eventType: "login_fail",
  actor: email,
  ip,
  payload: {
    reason: "bad_password",
    attempt: 1,
    user_agent: req.headers["user-agent"]
  }
});

✔️ 중요

  • DB 테이블 수정 ❌
  • 컬럼 추가 ❌
  • 인덱스 변경 ❌ 👉 코드만 바꿈

“이 상태로 바로 서버 재시작”

node server.js

3단계 실습: 실제로 필드가 늘어났는지 확인

3-1) 로그인 실패 2~3회 발생

  • 비밀번호 틀리게 로그인

3-2) PostgreSQL에서 확인 (VM3)

sudo -u postgres psql -d monitor_lab
SELECT payload
FROM auth_events
WHERE event_type = 'login_fail'
ORDER BY ts DESC
LIMIT 3;

기대 결과

{
  "reason": "bad_password",
  "attempt": 1,
  "user_agent": "Mozilla/5.0 (X11; Linux x86_64)..."
}

👉 컬럼 추가 없이 필드가 늘어남

4단계 실습: “추가된 필드로 검색”

4-1) JSONB 필드 조건 검색

SELECT ts, actor, payload->>'user_agent'
FROM auth_events
WHERE payload->>'reason' = 'bad_password'
ORDER BY ts DESC;

4-2) attempt 조건으로 검색

SELECT ts, actor, payload
FROM auth_events
WHERE payload->>'attempt' = '1';

✔️ JSONB 내부 필드를 컬럼처럼 검색

5단계 실습(확장): 구조가 더 복잡해져도 됨.

코드에서 payload를 이렇게 더 확장

payload: {
  reason: "bad_password",
  attempt: 2,
  user_agent: req.headers["user-agent"],
  geo: {
    country: "KR",
    city: "Seoul"
  }
}

PostgreSQL에서 중첩 구조 조회

SELECT
  payload->'geo'->>'country' AS country,
  payload->'geo'->>'city' AS city
FROM auth_events
WHERE event_type = 'login_fail';

👉 RDB인데 NoSQL처럼 쓸 수 있음.

6단계 실습: JSONB에 인덱스 적용

GIN 인덱스 생성:

CREATE INDEX idx_auth_events_payload_gin
ON auth_events USING GIN (payload);

실행 계획 비교(강사용 시연)

EXPLAIN ANALYZE
SELECT *
FROM auth_events
WHERE payload->>'reason' = 'bad_password';

👉 인덱스 없을 때 / 있을 때 비교 설명

7단계: 정리

PostgreSQL JSONB를 별도로 쓰는 이유

항목MySQL(RDB)PostgreSQL(JSONB)
필드 추가ALTER TABLE 필요❌ 필요 없음
서비스 중단 위험있음없음
로그/이벤트부적합매우 적합
구조 변경어려움자유로움
인덱스제한적GIN으로 JSON 내부까지
profile
우유
Front-end Developer, Cloud Engineer
이전 포스트

[Database] 데이터베이스 내용 정리

다음 포스트

[Database] 서버 모니터링 & 알람 시스템 실습 (MySQL+Redis+MongoDB+PostgreSQL)

0개의 댓글