Zero-Trust Enterprise Database Access via MCP

export READONLY_DATABASE_URL="postgresql://mcp_analyst_ro:secure_entropy_pass@db-replica.internal:5432/analytics"
export MAX_QUERY_ROWS="200"
export QUERY_TIMEOUT_MS="5000"

{
  "mcpServers": {
    "enterprise-database": {
      "command": "node",
      "args": ["/opt/mcp-servers/database/dist/index.js"],
      "env": {
        "READONLY_DATABASE_URL": "postgresql://mcp_analyst_ro:secure_entropy_pass@db-replica.internal:5432/analytics",
        "MAX_QUERY_ROWS": "200",
        "QUERY_TIMEOUT_MS": "5000"
      }
    }
  }
}

{
  "mcpServers": {
    "enterprise-database": {
      "command": "node",
      "args": ["./dist/index.js"],
      "env": {
        "READONLY_DATABASE_URL": "postgresql://mcp_analyst_ro:secure_entropy_pass@/analytics"
      }
    }
  }
}

[mcp_servers.enterprise_database]
command = "node"
args = ["/opt/mcp-servers/database/dist/index.js"]

[mcp_servers.enterprise_database.env]
READONLY_DATABASE_URL = "postgresql://mcp_analyst_ro:secure_entropy_pass@db-replica.internal:5432/analytics"
MAX_QUERY_ROWS = "200"
QUERY_TIMEOUT_MS = "5000"

-- Vulnerability: Simple keyword regexes miss comment injection
SELECT * FROM users; /* DROP TABLE users; */

import { Parser, AST } from 'node-sql-parser';

const parser = new Parser();

export interface ValidationResult {
  isValid: boolean;
  error?: string;
}

export function validateSafeQuery(sql: string): ValidationResult {
  let astList: AST[] | AST;

  try {
    astList = parser.astify(sql, { database: 'postgresql' });
  } catch (err: any) {
    return { isValid: false, error: `SQL Syntax Error: ${err.message}` };
  }

  // 1. Enforce single-statement queries
  if (Array.isArray(astList) && astList.length > 1) {
    return { isValid: false, error: 'Multi-statement execution is strictly prohibited.' };
  }

  const ast = Array.isArray(astList) ? astList[0] : astList;

  // 2. Enforce SELECT-only statement types
  if (ast.type !== 'select') {
    return {
      isValid: false,
      error: `Prohibited operation: '${ast.type.toUpperCase()}'. Only read-only SELECT queries are allowed.`
    };
  }

  // 3. Prevent access to system authentication catalogs
  const disallowedTables = ['pg_shadow', 'pg_authid', 'pg_user'];
  const tableList = parser.tableList(sql, { database: 'postgresql' });

  for (const tableEntry of tableList) {
    const tableName = tableEntry.split('::')[1]?.toLowerCase();
    if (disallowedTables.includes(tableName)) {
      return {
        isValid: false,
        error: `Access to restricted system catalog '${tableName}' is denied.`
      };
    }
  }

  return { isValid: true };
}

-- 1. Create dedicated unprivileged analyst role
CREATE ROLE mcp_analyst_ro WITH LOGIN PASSWORD 'secure_entropy_pass';

-- 2. Grant read-only access to analytics schema
GRANT CONNECT ON DATABASE analytics TO mcp_analyst_ro;
GRANT USAGE ON SCHEMA public TO mcp_analyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_analyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_analyst_ro;

-- 3. Engine-level execution bounds
ALTER ROLE mcp_analyst_ro SET default_transaction_read_only = on;
ALTER ROLE mcp_analyst_ro SET statement_timeout = '5000'; -- Max 5 seconds
ALTER ROLE mcp_analyst_ro SET work_mem = '64MB';         -- Max RAM per query
ALTER ROLE mcp_analyst_ro SET idle_in_transaction_session_timeout = '2000';

import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import { CallToolRequestSchema, ListToolsRequestSchema } from '@modelcontextprotocol/sdk/types.js';
import { Pool } from 'pg';
import { validateSafeQuery } from './ast-validator.js';

const dbPool = new Pool({
  connectionString: process.env.READONLY_DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 10000,
  connectionTimeoutMillis: 3000
});

const server = new Server(
  { name: 'enterprise-database-mcp', version: '2.0.0' },
  { capabilities: { tools: {} } }
);

server.setRequestHandler(ListToolsRequestSchema, async () => {
  return {
    tools: [
      {
        name: 'execute_safe_query',
        description: 'Execute a read-only SQL SELECT query against the read replica. Maximum 200 rows returned.',
        inputSchema: {
          type: 'object',
          properties: {
            sql: { type: 'string', description: 'ANSI SQL SELECT statement.' }
          },
          required: ['sql']
        }
      }
    ]
  };
});

server.setRequestHandler(CallToolRequestSchema, async (request) => {
  if (request.params.name === 'execute_safe_query') {
    const sql = request.params.arguments?.sql as string;

    const validation = validateSafeQuery(sql);
    if (!validation.isValid) {
      return {
        isError: true,
        content: [{ type: 'text', text: `Access Denied: ${validation.error}` }]
      };
    }

    const client = await dbPool.connect();
    try {
      await client.query('BEGIN READ ONLY');
      const result = await client.query(`${sql} LIMIT 200`);
      await client.query('COMMIT');

      if (result.rows.length === 0) {
        return { content: [{ type: 'text', text: 'Query returned 0 records.' }] };
      }

      const headers = Object.keys(result.rows[0]);
      const headerRow = `| ${headers.join(' | ')} |`;
      const dividerRow = `| ${headers.map(() => '---').join(' | ')} |`;
      const dataRows = result.rows
        .map(r => `| ${headers.map(h => String(r[h] ?? 'NULL')).join(' | ')} |`)
        .join('\n');

      return {
        content: [{ type: 'text', text: `${headerRow}\n${dividerRow}\n${dataRows}` }]
      };
    } catch (err: any) {
      await client.query('ROLLBACK');
      return {
        isError: true,
        content: [{ type: 'text', text: `Query Execution Error: ${err.message}` }]
      };
    } finally {
      client.release();
    }
  }

  throw new Error(`Tool ${request.params.name} is not recognized.`);
});

async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
}

main().catch((err) => {
  process.stderr.write(`Fatal error: ${err.message}\n`);
  process.exit(1);
});