shared-db-keycloak-demo/server.js

const crypto = require('crypto');
const express = require('express');
const session = require('express-session');
const { Issuer, generators } = require('openid-client');
const { Pool } = require('pg');

const app = express();
const port = Number(process.env.PORT || 3000);
const appUrl = String(process.env.APP_URL || process.env.COOLIFY_URL || '').replace(/\/$/, '');
const issuerUrl = process.env.KEYCLOAK_ISSUER;
const clientId = process.env.KEYCLOAK_CLIENT_ID;
const clientSecret = process.env.KEYCLOAK_CLIENT_SECRET;
const databaseUrl = process.env.DATABASE_URL;
const tenantId = process.env.TENANT_ID || 'demo-tenant';
const tenantMode = process.env.TENANT_DB_MODE || 'unknown';

for (const [key, value] of Object.entries({
  APP_URL: appUrl,
  KEYCLOAK_ISSUER: issuerUrl,
  KEYCLOAK_CLIENT_ID: clientId,
  KEYCLOAK_CLIENT_SECRET: clientSecret,
  DATABASE_URL: databaseUrl,
})) {
  if (!value) throw new Error(`${key} is required`);
}

const pool = new Pool({ connectionString: databaseUrl });
let oidcClient;
const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

async function waitForDatabase() {
  let lastError;
  for (let attempt = 1; attempt <= 45; attempt++) {
    try {
      await pool.query('SELECT 1');
      return;
    } catch (error) {
      lastError = error;
      console.log(`database not ready (attempt ${attempt}/45): ${error.message}`);
      await sleep(2000);
    }
  }
  throw lastError;
}

function escapeHtml(value) {
  return String(value ?? '')
    .replaceAll('&', '&amp;')
    .replaceAll('<', '&lt;')
    .replaceAll('>', '&gt;')
    .replaceAll('"', '&quot;')
    .replaceAll("'", '&#039;');
}

function asyncRoute(fn) {
  return (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next);
}

async function main() {
  await waitForDatabase();

  const issuer = await Issuer.discover(issuerUrl);
  oidcClient = new issuer.Client({
    client_id: clientId,
    client_secret: clientSecret,
    redirect_uris: [`${appUrl}/callback`],
    response_types: ['code'],
  });

  app.set('trust proxy', 1);
  app.use(session({
    secret: process.env.SESSION_SECRET || crypto.randomBytes(32).toString('hex'),
    resave: false,
    saveUninitialized: false,
    cookie: { secure: true, httpOnly: true, sameSite: 'lax', maxAge: 8 * 60 * 60 * 1000 },
  }));

  app.get('/health', asyncRoute(async (_req, res) => {
    await pool.query('SELECT 1');
    res.status(200).send('ok');
  }));

  app.get('/login', (req, res) => {
    const codeVerifier = generators.codeVerifier();
    const state = generators.state();
    req.session.codeVerifier = codeVerifier;
    req.session.state = state;
    res.redirect(oidcClient.authorizationUrl({
      scope: 'openid profile email',
      code_challenge: generators.codeChallenge(codeVerifier),
      code_challenge_method: 'S256',
      state,
    }));
  });

  app.get('/callback', asyncRoute(async (req, res) => {
    const params = oidcClient.callbackParams(req);
    const tokenSet = await oidcClient.callback(`${appUrl}/callback`, params, {
      code_verifier: req.session.codeVerifier,
      state: req.session.state,
    });
    req.session.user = tokenSet.claims();
    delete req.session.codeVerifier;
    delete req.session.state;
    res.redirect('/');
  }));

  app.get('/logout', (req, res) => req.session.destroy(() => res.redirect('/logged-out')));
  app.get('/logged-out', (_req, res) => res.type('html').send('<!doctype html><html><body style="font-family:sans-serif;max-width:760px;margin:4rem auto"><h1>Signed out</h1><p><a href="/login">Sign in again</a></p></body></html>'));

  app.get('/', asyncRoute(async (req, res) => {
    if (!req.session.user) return res.redirect('/login');

    const user = req.session.user;
    const username = user.preferred_username || user.email || user.name || user.sub;
    const db = await pool.query('SELECT current_database() AS database, current_user AS role, NOW() AS checked_at');
    const info = db.rows[0];

    res.type('html').send(`<!doctype html>
<html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Arrowstack DB Demo</title></head>
<body style="font-family:system-ui,sans-serif;max-width:820px;margin:4rem auto;padding:0 1.5rem;line-height:1.5">
  <h1>Authenticated + tenant database connected ✅</h1>
  <p>Keycloak authenticated this request and the application connected to PostgreSQL using its restricted runtime role.</p>
  <table style="border-collapse:collapse;width:100%;max-width:720px">
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>User</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(username)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>Tenant</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(tenantId)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>DB mode</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(tenantMode)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>Database</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(info.database)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>Runtime role</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(info.role)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>DB check</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(info.checked_at)}</td></tr>
    <tr><td style="padding:.5rem;border-bottom:1px solid #ddd"><strong>Keycloak client</strong></td><td style="padding:.5rem;border-bottom:1px solid #ddd">${escapeHtml(clientId)}</td></tr>
  </table>
  <p style="margin-top:2rem"><a href="/">Check again</a> · <a href="/logout">Local sign out</a></p>
</body></html>`);
  }));

  app.use((error, _req, res, _next) => {
    console.error(error);
    res.status(500).type('text').send('Application error');
  });

  app.listen(port, '0.0.0.0', () => console.log(`shared-db-keycloak-demo listening on ${port}`));
}

main().catch((error) => {
  console.error(error);
  process.exit(1);
});