SSH Tunnel Support

/**
 * SSH tunnel manager for secure warehouse connections
 * Generic over credentials type to support any warehouse with SSH configuration
 * Currently supported: PostgreSQL and Redshift
 */
class SshTunnel<T extends CreateWarehouseCredentials> {
  /** Original warehouse credentials before tunnel modification */
  readonly originalCredentials: T;

  /** Modified credentials with 127.0.0.1 and tunnel port */
  overrideCredentials: T;

  /** Local port number for the SSH tunnel (undefined until connected) */
  localPort: number | undefined;

  /**
   * Initialize SSH tunnel with warehouse credentials
   * Stores original credentials and prepares for tunnel establishment
   * @param credentials - Warehouse credentials with SSH configuration
   */
  constructor(credentials: T);

  /**
   * Establish SSH tunnel and return modified credentials
   * Only creates tunnel if useSshTunnel is true and warehouse is PostgreSQL/Redshift
   * Returns original credentials unchanged if tunnel not needed
   * When tunnel is active, returns credentials with host='127.0.0.1' and port=localPort
   * Also updates the overrideCredentials property with the returned credentials
   * @returns Promise resolving to modified credentials (or original if tunnel not needed)
   * @throws SshTunnelError if SSH connection fails or credentials invalid
   */
  connect(): Promise<T>;

  /**
   * Close the SSH tunnel connection
   * Terminates SSH connection and frees local port
   * @returns Promise that resolves when tunnel is closed
   */
  disconnect(): Promise<void>;
}

import { SshTunnel, PostgresWarehouseClient } from '@lightdash/warehouses';

// Create credentials with SSH configuration
const credentials = {
  type: 'postgres',
  host: 'internal-db.private.local',
  port: 5432,
  user: 'dbuser',
  password: 'dbpassword',
  dbname: 'analytics',
  schema: 'public',
  sslmode: 'disable',
  useSshTunnel: true,
  sshTunnelHost: 'bastion.example.com',
  sshTunnelPort: 22,
  sshTunnelUser: 'sshuser',
  sshTunnelPrivateKey: `-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEA...
-----END RSA PRIVATE KEY-----`,
  startOfWeek: null,
};

// Create and connect SSH tunnel
const tunnel = new SshTunnel(credentials);
const tunnelCredentials = await tunnel.connect();

console.log('Tunnel established on port:', tunnel.localPort);
console.log('Modified credentials:', tunnelCredentials);
// Modified credentials will have: host='127.0.0.1', port=<localPort>

// Create client with tunnel credentials
const client = new PostgresWarehouseClient(tunnelCredentials);

// Use client normally
await client.test();
const results = await client.runQuery('SELECT COUNT(*) FROM users');
console.log('User count:', results.rows[0].count);

// Clean up: disconnect tunnel when done
await tunnel.disconnect();
console.log('Tunnel closed');

// Example with error handling
let tunnel: SshTunnel | undefined;
try {
  tunnel = new SshTunnel(credentials);
  const modifiedCreds = await tunnel.connect();
  const client = new PostgresWarehouseClient(modifiedCreds);

  // Use client...
  await client.test();

} catch (error) {
  console.error('SSH tunnel failed:', error.message);
} finally {
  if (tunnel) {
    await tunnel.disconnect();
  }
}

import { PostgresWarehouseClient } from '@lightdash/warehouses';

// PostgreSQL and Redshift clients automatically handle SSH tunnels
// If credentials include ssh configuration, tunnel is created internally

const credentials = {
  type: 'postgres',
  host: 'internal-db.private.local',
  port: 5432,
  user: 'dbuser',
  password: 'dbpassword',
  dbname: 'analytics',
  schema: 'public',
  sslmode: 'require',
  useSshTunnel: true,
  sshTunnelHost: 'bastion.example.com',
  sshTunnelPort: 22,
  sshTunnelUser: 'sshuser',
  sshTunnelPrivateKey: '-----BEGIN RSA PRIVATE KEY-----\n...',
  startOfWeek: null,
};

// Client automatically creates and manages SSH tunnel
const client = new PostgresWarehouseClient(credentials);

// Tunnel is established on first connection
await client.test(); // Tunnel created here

// All subsequent queries use the tunnel
await client.runQuery('SELECT * FROM orders');

// Tunnel is cleaned up when client is destroyed

/**
 * SSH tunnel configuration fields
 * These fields are included directly in PostgreSQL and Redshift credentials
 */
interface SshTunnelConfiguration {
  /** Enable SSH tunnel (must be true to use tunnel) */
  useSshTunnel?: boolean;

  /** SSH bastion host hostname or IP */
  sshTunnelHost?: string;

  /** SSH port on bastion host (default: 22) */
  sshTunnelPort?: number;

  /** SSH username for authentication */
  sshTunnelUser?: string;

  /** SSH public key (stored but not used by tunnel implementation) */
  sshTunnelPublicKey?: string;

  /** SSH private key in PEM format (RSA, DSA, ECDSA, or Ed25519) */
  sshTunnelPrivateKey?: string;
}

/**
 * Base warehouse credentials type with optional SSH
 * T must extend CreateWarehouseCredentials
 */
type CreateWarehouseCredentials =
  | CreateRedshiftCredentials
  | CreateBigqueryCredentials
  | CreatePostgresCredentials
  | CreateSnowflakeCredentials
  | CreateDatabricksCredentials
  | CreateTrinoCredentials;

Original:  Client -> internal-db.private.local:5432 (blocked by firewall)

With SSH:  Client -> 127.0.0.1:12345 -> [SSH Tunnel] -> bastion.example.com:22
           -> internal-db.private.local:5432 (✓ accessible)

const privateKey = `-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEA1234567890abcdefghijklmnopqrstuvwxyz...
[multiple lines of base64-encoded key data]
...
-----END RSA PRIVATE KEY-----`;

Error: SSH tunnel connection timeout

Error: SSH authentication failed

Error: Failed to establish port forward

Error: SSH tunnel disconnected unexpectedly

const tunnel = new SshTunnel(credentials);
await tunnel.connect();
console.log('Tunnel port:', tunnel.localPort); // e.g., 12345

const tunnel1 = new SshTunnel(postgresCredentials);
const creds1 = await tunnel1.connect();

const tunnel2 = new SshTunnel(redshiftCredentials);
const creds2 = await tunnel2.connect();

const pgClient = new PostgresWarehouseClient(creds1);
const rsClient = new RedshiftWarehouseClient(creds2);

// Use both clients...

await tunnel1.disconnect();
await tunnel2.disconnect();

class ManagedTunnel {
  private tunnel: SshTunnel<CreatePostgresCredentials>;
  private client: PostgresWarehouseClient;

  async connect() {
    this.tunnel = new SshTunnel(credentials);
    const modifiedCreds = await this.tunnel.connect();
    this.client = new PostgresWarehouseClient(modifiedCreds);
  }

  async query(sql: string) {
    try {
      return await this.client.runQuery(sql);
    } catch (error) {
      // Reconnect on tunnel failure
      if (error.message.includes('tunnel') || error.message.includes('ECONNRESET')) {
        await this.reconnect();
        return await this.client.runQuery(sql);
      }
      throw error;
    }
  }

  async reconnect() {
    await this.disconnect();
    await this.connect();
  }

  async disconnect() {
    await this.tunnel?.disconnect();
  }
}