NodeJS通过SSH隧道连接PostgreSQL持续超时，求排查问题

我编写了一个简单脚本尝试通过SSH隧道访问远程PostgreSQL数据库，尝试多种配置后，每次执行pool.connect()都会出现连接超时错误，错误信息如下：

建立PostgreSQL连接出错：错误：连接因超时而终止
at /Users/user/Code/mvp/dat/tools/node_modules/pg-pool/index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async run (/Users/user/Code/myscript.js:61:22)

附上脚本代码：

const { createTunnel } = require('tunnel-ssh');
const fs = require('fs');
const { Pool, Client } = require('pg');

const settings = {
  sshHost: 'host52689.provider.net',
  sshPort: 22,
  sshUsername: 'ssh-user',
  sshPrivateKeyPath: '/Users/user/.ssh/id_rsa',
  sshKeepaliveInterval: 1000, 
  sshReadyTimeout: 10000, 
  // SSH端口转发:
  dbRemoteHost: 'db01.provider.net',
  dbRemotePort: 5432, 
  dbLocalHost: '127.0.0.1',
  dbLocalPort: 5433, 
  // 数据库设置:
  dbName: 'db555', 
  dbUser: 'usr555',
  dbPassword: 'password' 
};

async function run() {

  // 创建SSH隧道
  const [server, client] = await createTunnel(
    {
      autoClose: false
    },
    {
      port: settings.dbLocalPort
    },
    {
      host: settings.sshHost,
      port: settings.sshPort,
      username: settings.sshUsername,
      privateKey: fs.readFileSync(settings.sshPrivateKeyPath, 'utf8'),
      keepaliveInterval: settings.sshKeepaliveInterval,
      readyTimeout: settings.sshReadyTimeout,
    },
    {
      srcAddr: settings.dbRemoteHost,
      srcPort: settings.dbRemotePort,
      dstAddr: settings.dbLocalHost,
      dstPort: settings.dbLocalPort,
    }
  );
  console.log('Tunnel created.');

  // 建立PostgreSQL连接
  const pool = new Pool({
    host: settings.dbLocalHost,
    port: settings.dbLocalPort,
    database: settings.dbName,
    user: settings.dbUser,
    password: settings.dbPassword,
    connectionTimeoutMillis: 5000,
  });

  try {
    const clientDb = await pool.connect();
    console.log('PostgreSQL connection established.');

    // .....

    clientDb.release();
  } catch (error) {
    console.error('Error establishing PostgreSQL connection:', error);
  }

  // 关闭PostgreSQL连接
  await pool.end();
  console.log('PostgreSQL connection closed.');

  // 关闭SSH隧道
  server.close();
  console.log('SSH tunnel closed.');

}

run().catch(error => {
  console.error('An error occurred:', error);
});

问题分析与修复方案 #

1. SSH隧道端口转发参数完全搞反 #

tunnel-ssh的端口转发逻辑是：srcAddr/srcPort对应本地监听的地址和端口，dstAddr/dstPort对应SSH服务器侧能访问的目标地址（即远程数据库）。你当前的参数把两者写反了，导致隧道无法正确将本地请求转发到远程数据库。

修复代码：

// 调整隧道的第四参数
{
  srcAddr: settings.dbLocalHost,  // 本地监听地址
  srcPort: settings.dbLocalPort,  // 本地监听端口
  dstAddr: settings.dbRemoteHost, // SSH服务器可访问的数据库地址
  dstPort: settings.dbRemotePort, // 远程数据库端口
}

2. 未等待SSH隧道完全就绪就连接数据库 #

createTunnel返回仅表示隧道对象已创建，不代表SSH连接已完全建立、端口转发已生效。直接连接PostgreSQL会因隧道未就绪导致超时。

修复代码：

// 修改隧道创建逻辑，等待SSH连接就绪
const tunnelPromise = new Promise((resolve, reject) => {
  const [server, client] = createTunnel(
    { autoClose: false },
    { port: settings.dbLocalPort },
    {
      host: settings.sshHost,
      port: settings.sshPort,
      username: settings.sshUsername,
      privateKey: fs.readFileSync(settings.sshPrivateKeyPath, 'utf8'),
      keepaliveInterval: settings.sshKeepaliveInterval,
      readyTimeout: settings.sshReadyTimeout,
    },
    {
      srcAddr: settings.dbLocalHost,
      srcPort: settings.dbLocalPort,
      dstAddr: settings.dbRemoteHost,
      dstPort: settings.dbRemotePort,
    }
  );
  // 监听SSH连接就绪事件
  client.on('ready', () => {
    console.log('Tunnel is ready for use.');
    resolve([server, client]);
  });
  // 监听隧道创建错误
  client.on('error', reject);
});
const [server, client] = await tunnelPromise;

3. PostgreSQL连接超时时间过短 #

当前配置的connectionTimeoutMillis: 5000（5秒）太短，加上SSH隧道建立的延迟，可能还没等到隧道就绪就触发了超时。建议延长超时时间。

修复代码：

const pool = new Pool({
  host: settings.dbLocalHost,
  port: settings.dbLocalPort,
  database: settings.dbName,
  user: settings.dbUser,
  password: settings.dbPassword,
  connectionTimeoutMillis: 15000, // 延长至15秒
});

4. 额外排查点 #

• 登录SSH服务器host52689.provider.net，执行telnet db01.provider.net 5432，确认SSH服务器能正常访问目标数据库
• 检查本地端口5433是否被其他程序占用，可通过lsof -i :5433（Mac/Linux）或netstat -ano | findstr :5433（Windows）查看
• 确保SSH私钥/Users/user/.ssh/id_rsa的权限正确，Unix系统下应为600（仅所有者可读可写），执行chmod 600 /Users/user/.ssh/id_rsa修正

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