meshcentral-db.js

Go to the documentation of this file.

/**
 * Direct PostgreSQL connection to MeshCentral database
 * 
 * Use this for READING data (fast):
 * - Device list
 * - Online/offline status
 * - Hardware info
 * - Mesh groups
 * 
 * Use MeshCentralAPI for WRITING (safe):
 * - Creating meshes
 * - Updating device tags
 * - Sending commands
 */
 
const { Pool } = require('pg');
 
/**
 * Sanitize JSON data by removing null bytes (\u0000)
 * @description PostgreSQL cannot store null bytes in text/varchar/json fields.
 * MeshCentral device data sometimes contains null bytes in hardware info fields
 * (e.g., ManufacturerId from SMBIOS), causing "unsupported Unicode escape sequence" errors.
 * This function recursively removes null bytes from all string values in JSON.
 * @param {any} data - Data to sanitize (object, array, string, etc.)
 * @returns {any} Sanitized data with null bytes removed
 */
function sanitizeNullBytes(data) {
  if (data === null || data === undefined) {
    return data;
  }
  
  // If it's a string, remove null bytes
  if (typeof data === 'string') {
    return data.replace(/\u0000/g, '');
  }
  
  // If it's an array, sanitize each element
  if (Array.isArray(data)) {
    return data.map(item => sanitizeNullBytes(item));
  }
  
  // If it's an object, sanitize each property
  if (typeof data === 'object') {
    const sanitized = {};
    for (const key in data) {
      if (data.hasOwnProperty(key)) {
        sanitized[key] = sanitizeNullBytes(data[key]);
      }
    }
    return sanitized;
  }
  
  // For primitives (number, boolean), return as-is
  return data;
}
 
// Connection state tracking
let isConnected = false;
let connectionPromise = null;
 
// Create separate connection pool for MeshCentral database
const meshPool = new Pool({
  host: process.env.POSTGRES_HOST,
  port: parseInt(process.env.POSTGRES_PORT) || 5432,
  user: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: 'meshcentral', // MeshCentral database name
  max: 20, // Maximum 20 connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});
 
// Test connection on startup and track state
connectionPromise = meshPool.query('SELECT 1')
  .then(() => {
    console.log('✅ Connected to MeshCentral PostgreSQL');
    isConnected = true;
    return true;
  })
  .catch(err => {
    console.error('❌ MeshCentral PostgreSQL connection failed:', err.message);
    isConnected = false;
    return false;
  });
 
/**
 * Wait for database connection to be ready
 * @param {number} timeoutMs - Timeout in milliseconds
 * @returns {Promise<boolean>} True if connected
 */
async function waitForConnection(timeoutMs = 3000) {
  if (isConnected) {
    return true;
  }
  
  // Wait for initial connection or timeout
  const timeout = new Promise(resolve => setTimeout(() => resolve(false), timeoutMs));
  const result = await Promise.race([connectionPromise, timeout]);
  return result;
}
 
/**
 * Get all devices from MeshCentral database
 * @param {string} meshGroupId - Optional: filter by mesh group ID
 * @returns {Promise<Array>} Array of device objects
 */
async function getDevices(meshGroupId = null) {
  // Wait for connection with timeout
  const connected = await waitForConnection(3000);
  if (!connected) {
    throw new Error('MeshCentral database not ready');
  }
  
  // Query with null byte sanitization at PostgreSQL level
  // MeshCentral SMBIOS data sometimes contains \u0000 bytes which PostgreSQL text fields cannot handle
  // We cast to text, remove null bytes, then cast back to jsonb for safe JSON operations
  const query = `
    SELECT 
      (sanitized_doc->>'_id') as nodeid,
      (sanitized_doc->>'name') as hostname,
      (sanitized_doc->>'meshid') as meshid,
      (sanitized_doc->'agent'->>'osdesc') as os,
      (sanitized_doc->>'host') as ip_address,
      (sanitized_doc->>'conn')::int as connection_status,
      (sanitized_doc->>'lastseen') as last_seen,
      (sanitized_doc->>'lastconnect') as last_connect,
      (sanitized_doc->>'tags')::jsonb as tags,
      (sanitized_doc->>'desc') as description,
      (sanitized_doc->'agent'->>'ver') as agent_version,
      (sanitized_doc->>'lastaddr') as last_address,
      (sanitized_doc->>'rname') as real_name,
      sanitized_doc as full_doc
    FROM (
      SELECT replace(doc::text, CHR(0), '')::jsonb as sanitized_doc,
             doc
      FROM main
      WHERE (doc->>'type') = 'node'
      ${meshGroupId ? "AND (doc->>'meshid') = $1" : ""}
    ) AS sanitized
    ORDER BY (sanitized_doc->>'name')
  `;
  
  const params = meshGroupId ? [meshGroupId] : [];
  
  console.log('[MeshCentral DB] Querying devices (with null byte sanitization):', {
    filterByMesh: meshGroupId ? 'yes' : 'no',
    meshGroupId: meshGroupId || 'all'
  });
  
  const result = await meshPool.query(query, params);
  
  console.log('[MeshCentral DB] Query result:', {
    rowCount: result.rows.length,
    sampleNode: result.rows[0] && {
      nodeid: result.rows[0].nodeid,
      hostname: result.rows[0].hostname,
      meshid: result.rows[0].meshid
    }
  });
  
  // If filtering by mesh but got 0 results, check if there are ANY nodes
  if (meshGroupId && result.rows.length === 0) {
    const totalNodesResult = await meshPool.query(`
      SELECT COUNT(*) as total,
             json_agg(DISTINCT (doc->>'meshid')) as mesh_ids
      FROM main 
      WHERE (doc->>'type') = 'node'
    `);
    
    console.log('[MeshCentral DB] Total nodes in database:', {
      total: totalNodesResult.rows[0].total,
      availableMeshIds: totalNodesResult.rows[0].mesh_ids,
      requestedMeshId: meshGroupId
    });
  }
  
  // Parse each device - sanitization already done at DB level
  return result.rows.map(row => {
    // Additional Node.js level sanitization for extra safety
    const sanitizedRow = sanitizeNullBytes(row);
    
    return {
      nodeId: sanitizedRow.nodeid,
      hostname: sanitizedRow.hostname || 'Unknown',
      meshId: sanitizedRow.meshid,
      os: sanitizedRow.os || 'Unknown',
      ipAddress: sanitizedRow.ip_address,
      connected: isDeviceOnline(sanitizedRow.connection_status),
      connectionStatus: sanitizedRow.connection_status,
      lastSeen: sanitizedRow.last_seen,
      lastConnect: sanitizedRow.last_connect,
      tags: sanitizedRow.tags || [],
      description: sanitizedRow.description,
      agentVersion: sanitizedRow.agent_version,
      lastAddress: sanitizedRow.last_address,
      realName: sanitizedRow.real_name,
      rawDoc: sanitizedRow.full_doc
    };
  });
}
 
/**
 * Get single device by node ID
 * @param {string} nodeId - MeshCentral node ID
 * @returns {Promise<object>} Device object
 */
async function getDevice(nodeId) {
  const devices = await getDevices();
  return devices.find(d => d.nodeId === nodeId);
}
 
/**
 * Get all mesh groups
 * @returns {Promise<Array>} Array of mesh group objects
 */
async function getMeshGroups() {
  // Wait for connection with timeout
  const connected = await waitForConnection(3000);
  if (!connected) {
    throw new Error('MeshCentral database not ready');
  }
  
  // Sanitize null bytes at PostgreSQL level
  const result = await meshPool.query(`
    SELECT 
      (sanitized_doc->>'_id') as meshid,
      (sanitized_doc->>'name') as name,
      (sanitized_doc->>'desc') as description,
      (sanitized_doc->>'mtype')::int as mesh_type,
      sanitized_doc as full_doc
    FROM (
      SELECT replace(doc::text, CHR(0), '')::jsonb as sanitized_doc
      FROM main
      WHERE (doc->>'type') = 'mesh'
    ) AS sanitized
    ORDER BY (sanitized_doc->>'name')
  `);
  
  return result.rows.map(row => {
    const sanitizedRow = sanitizeNullBytes(row);
    return {
      _id: sanitizedRow.meshid,
      meshId: sanitizedRow.meshid,
      name: sanitizedRow.name,
      description: sanitizedRow.description,
      meshType: sanitizedRow.mesh_type,
      rawDoc: sanitizedRow.full_doc
    };
  });
}
 
/**
 * Get device hardware info from SMBIOS table
 * @param {string} nodeId - MeshCentral node ID
 * @returns {Promise<object>} Hardware info object
 */
async function getDeviceHardware(nodeId) {
  // Wait for connection with timeout
  const connected = await waitForConnection(3000);
  if (!connected) {
    throw new Error('MeshCentral database not ready');
  }
  
  const result = await meshPool.query(`
    SELECT doc->'hardware' as hardware_info
    FROM meshcentral_smbios
    WHERE id = $1
  `, [nodeId]);
  
  if (result.rows.length === 0) {
    return null;
  }
  
  return result.rows[0].hardware_info;
}
 
/**
 * Check if device is online based on connection status bitmask
 * @param {number} conn - Connection status bitmask from MeshCentral
 * @returns {boolean} True if device is online
 */
function isDeviceOnline(conn) {
  if (!conn) return false;
  
  // Bitmask values:
  // 1 = Agent connected
  // 2 = CIRA connected
  // 4 = AMT connected
  // 8 = AMT relay connected
  // 16 = MQTT connected
  
  // Device is online if agent (1) or MQTT (16) is connected
  return (conn & 1) > 0 || (conn & 16) > 0;
}
 
/**
 * Parse device tags to extract tenant_id and customer_id
 * @param {Array} tags - Array of tag strings
 * @returns {object} {tenantId, customerId, customTags}
 */
function parseDeviceTags(tags) {
  if (!Array.isArray(tags)) {
    return { tenantId: null, customerId: null, customTags: [] };
  }
  
  let tenantId = null;
  let customerId = null;
  const customTags = [];
  
  for (const tag of tags) {
    if (typeof tag !== 'string') continue;
    
    if (tag.startsWith('tenant:')) {
      tenantId = tag.substring(7);
    } else if (tag.startsWith('customer:')) {
      customerId = tag.substring(9);
    } else {
      customTags.push(tag);
    }
  }
  
  return { tenantId, customerId, customTags };
}
 
/**
 * Get device count by mesh group
 * @param {string} meshGroupId - Mesh group ID
 * @returns {Promise<number>} Number of devices in mesh
 */
async function getDeviceCount(meshGroupId) {
  // Wait for connection with timeout
  const connected = await waitForConnection(3000);
  if (!connected) {
    throw new Error('MeshCentral database not ready');
  }
  
  const result = await meshPool.query(`
    SELECT COUNT(*) as count
    FROM main
    WHERE (doc->>'type') = 'node'
    AND (doc->>'meshid') = $1
  `, [meshGroupId]);
  
  return parseInt(result.rows[0].count);
}
 
/**
 * Get online device count
 * @param {string} meshGroupId - Optional: filter by mesh group
 * @returns {Promise<number>} Number of online devices
 */
async function getOnlineDeviceCount(meshGroupId = null) {
  // Wait for connection with timeout
  const connected = await waitForConnection(3000);
  if (!connected) {
    throw new Error('MeshCentral database not ready');
  }
  
  const query = `
    SELECT COUNT(*) as count
    FROM main
    WHERE (doc->>'type') = 'node'
    ${meshGroupId ? "AND (doc->>'meshid') = $1" : ""}
    AND (
      ((doc->>'conn')::int & 1) > 0 
      OR ((doc->>'conn')::int & 16) > 0
    )
  `;
  
  const params = meshGroupId ? [meshGroupId] : [];
  const result = await meshPool.query(query, params);
  
  return parseInt(result.rows[0].count);
}
 
module.exports = {
  meshPool,
  waitForConnection,
  getDevices,
  getDevice,
  getMeshGroups,
  getDeviceHardware,
  isDeviceOnline,
  parseDeviceTags,
  getDeviceCount,
  getOnlineDeviceCount
};