Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

8.8 KB
Raw Blame
/**
 * pg_dump binary resolution.
 *
 * `pg_dump` REFUSES to dump a server newer than itself ("server version
 * mismatch"), but a newer `pg_dump` dumps an older server fine. On machines
 * with multiple Postgres installs (the common Homebrew case: an old
 * `postgresql@NN` keg shadowing a newer running server in PATH) the bare
 * `pg_dump` is often the wrong one, so these helpers discover the installed
 * binaries and select the closest whose major is >= the running server's.
 *
 * Shared by both pg_dump consumers — the backup snapshot path
 * (`server/services/backup.js`) and the native↔Docker backend-copy / export
 * path (`server/services/dbAdmin.js`) — so the version-aware selection and the
 * `PORTOS_PGDUMP` override behave identically in both. Self-contained: no
 * imports out to other PortOS services.
 */

import { randomUUID } from 'node:crypto';
import { tmpdir } from 'node:os';
import { mkdtemp, readdir, rm, writeFile } from 'fs/promises';
import { getCACertificates } from 'node:tls';
import { join } from 'path';
import { execFileAsync } from './fileUtils.js';

/**
 * Choose which discovered pg_dump binary to run, given the server's major
 * version. pg_dump REFUSES to dump a server newer than itself ("server version
 * mismatch"), but a newer pg_dump dumps an older server fine — so the rule is:
 * pick the closest binary whose major is >= the server's.
 *
 * Pure + exported for unit testing (the IO-bound discovery lives in
 * resolvePgDump, which feeds this the runnable candidates it found).
 *
 * @param {number|null} serverMajor - server's PG major version (null = unknown)
 * @param {Array<{binary: string, major: number}>} candidates - runnable pg_dumps
 * @returns {string|null} chosen binary path, or null if none discovered
 */
export function pickPgDump(serverMajor, candidates) {
  if (!candidates?.length) return null;
  // Unknown server version: keep prior behavior — trust the first (PATH) entry.
  if (!Number.isFinite(serverMajor)) return candidates[0].binary;
  // Prefer the smallest major that still satisfies >= server (closest match,
  // avoids reaching for a wildly newer keg when an exact one is installed).
  const viable = candidates.filter(c => c.major >= serverMajor).sort((a, b) => a.major - b.major);
  if (viable.length) return viable[0].binary;
  // Nothing is new enough. Return the newest we have so the resulting error is a
  // clear "still too old" instead of an arbitrary pick — and so the version
  // detection in dumpPostgres can flag it as version_mismatch.
  return [...candidates].sort((a, b) => b.major - a.major)[0].binary;
}

/**
 * Read a pg_dump binary's major version, or null if it isn't runnable.
 * `pg_dump --version` prints e.g. "pg_dump (PostgreSQL) 17.10 (Homebrew)".
 */
async function pgDumpMajor(binary) {
  const { stdout } = await execFileAsync(binary, ['--version']).catch(() => ({ stdout: '' }));
  const m = stdout.match(/PostgreSQL\)\s+(\d+)/i);
  return m ? parseInt(m[1], 10) : null;
}

/**
 * Enumerate candidate pg_dump locations: the bare PATH binary, then versioned
 * Homebrew kegs and Postgres.app bundles (where multiple majors coexist and
 * PATH order silently picks the wrong one).
 */
export async function discoverPgDumpCandidates() {
  // Note: the PORTOS_PGDUMP override is NOT a candidate here — resolvePgDumpBinary
  // honors it outright before discovery runs, so it's never subject to the
  // closest-major auto-selection below.
  const paths = ['pg_dump']; // whatever PATH resolves — preserves the old default
  const kegDirs = ['/opt/homebrew/opt', '/usr/local/opt', '/Applications/Postgres.app/Contents/Versions'];
  for (const dir of kegDirs) {
    const entries = await readdir(dir).catch(() => []);
    for (const entry of entries) {
      if (dir.endsWith('Versions') || entry.startsWith('postgresql')) {
        paths.push(join(dir, entry, 'bin', 'pg_dump'));
      }
    }
  }
  // Probe versions concurrently (each `pg_dump --version` is an independent
  // spawn); [...new Set(paths)] de-dups while preserving priority order, which
  // Promise.all keeps — so pickPgDump still sees PATH > kegs.
  const probed = await Promise.all(
    [...new Set(paths)].map(async (binary) => ({ binary, major: await pgDumpMajor(binary) }))
  );
  return probed.filter(c => c.major != null);
}

/**
 * Auto-select a pg_dump binary for a server at `serverMajor` by discovering
 * installed binaries and picking the closest whose major is >= the server's.
 * Returns the chosen path and whether it satisfies the server's version
 * (false ⇒ the dump will fail with a version mismatch and there's nothing
 * newer installed).
 *
 * The explicit `PORTOS_PGDUMP` override is NOT handled here — the caller
 * (`resolvePgDumpBinary`) honors it before reaching this resolver, so the
 * override works even when the server version is unknown and this resolver
 * is skipped.
 *
 * @param {number} serverMajor - known server major (caller guards on isFinite)
 * @returns {Promise<{binary: string, satisfies: boolean}>}
 */
export async function resolvePgDump(serverMajor) {
  const candidates = await discoverPgDumpCandidates();
  const binary = pickPgDump(serverMajor, candidates) || 'pg_dump';
  const chosen = candidates.find(c => c.binary === binary);
  const satisfies = !Number.isFinite(serverMajor) || !chosen || chosen.major >= serverMajor;
  return { binary, satisfies };
}

/**
 * The full pg_dump-binary decision shared by every consumer:
 *  - an explicit PORTOS_PGDUMP override always wins — the escape hatch must
 *    work even when version detection failed (its main use case);
 *  - else, when we know the server version, auto-select a binary whose major
 *    is >= it (resolvePgDump scans Homebrew kegs / Postgres.app);
 *  - else keep the prior behavior (bare `pg_dump` off PATH) without paying for
 *    discovery I/O we couldn't act on.
 *
 * `satisfies` is false only when a known server version has no new-enough
 * installed binary — the caller can warn before the dump fails with a clear
 * "server version mismatch".
 *
 * @param {number|null} serverMajor - server's PG major (null/NaN = unknown)
 * @returns {Promise<{binary: string, satisfies: boolean}>}
 */
export async function resolvePgDumpBinary(serverMajor) {
  if (process.env.PORTOS_PGDUMP) {
    // user forced it; the stderr classifier still catches a too-old pick
    return { binary: process.env.PORTOS_PGDUMP, satisfies: true };
  }
  if (Number.isFinite(serverMajor)) {
    return resolvePgDump(serverMajor);
  }
  return { binary: 'pg_dump', satisfies: true };
}

/** Build a libpq environment from the pool's captured connection policy.
 * Keep executable resolution and locale, but never inherit a second PG policy.
 * Inline certificate material needs a separate managed-file adapter; refuse it
 * rather than silently sending a child with weaker authentication.
 */
function buildPgToolEnv(config, inherited = process.env, rootCertPath) {
  const env = Object.fromEntries(Object.entries(inherited).filter(([key]) => !/^PG/i.test(key)));
  const ssl = config.ssl;
  if (ssl && typeof ssl === 'object' && Object.keys(ssl).some(key => key !== 'rejectUnauthorized')) {
    throw new Error('PostgreSQL tools do not support inline pool TLS certificate settings');
  }
  env.PGPASSWORD = config.password;
  env.PGSSLMODE = ssl ? (ssl.rejectUnauthorized === false ? 'require' : 'verify-full') : 'disable';
  if (env.PGSSLMODE === 'verify-full') {
    if (!rootCertPath) throw new Error('Verified PostgreSQL TLS requires a managed root certificate file');
    env.PGSSLROOTCERT = rootCertPath;
  }
  // libpq's require mode verifies a default root.crt when one exists. An
  // intentionally absent, unique path preserves node-postgres no-verify TLS.
  if (env.PGSSLMODE === 'require') env.PGSSLROOTCERT = join(tmpdir(), `portos-unused-root-${randomUUID()}.crt`);
  // libpq otherwise discovers client identity files in the user's home; the
  // pool does not load them. Missing explicit paths disable that discovery.
  const unusedIdentity = join(tmpdir(), `portos-unused-identity-${randomUUID()}`);
  env.PGSSLCERT = `${unusedIdentity}.crt`;
  env.PGSSLKEY = `${unusedIdentity}.key`;
  env.PGSSLNEGOTIATION = config.sslnegotiation || 'postgres';
  return env;
}

/** Run a tool with the Node TLS trust roots in a private, temporary PEM file.
 * Explicit files work on older supported libpq versions too. Cleanup covers
 * successful children, spawn failures, timeouts and rejected callbacks.
 */
export async function withPgToolEnv(config, run) {
  if (!config.ssl || config.ssl.rejectUnauthorized === false) return run(buildPgToolEnv(config));
  const dir = await mkdtemp(join(tmpdir(), 'portos-pg-tls-'));
  return (async () => {
    const path = join(dir, 'roots.pem');
    await writeFile(path, getCACertificates('default').join('\n'), { mode: 0o600 });
    return run(buildPgToolEnv(config, process.env, path));
  })().finally(() => rm(dir, { recursive: true, force: true }));
}