MCP Tools for Amazon Redshift — Data API SigV4 auth, async statement execution, cluster health vs API health

Authentication — IAM SigV4 via Redshift Data API

The Redshift Data API authenticates via standard AWS IAM SigV4. No VPC access, no Redshift cluster network access, no JDBC driver. The IAM principal (user or role) needs the redshift-data:ExecuteStatement, redshift-data:DescribeStatement, and redshift-data:GetStatementResult permissions. For provisioned clusters, it additionally needs redshift:GetClusterCredentials when using temporary credentials via DbUser.

import {
  RedshiftDataClient,
  ExecuteStatementCommand,
  DescribeStatementCommand,
  GetStatementResultCommand,
  ListDatabasesCommand,
} from '@aws-sdk/client-redshift-data';

// SDK credentials chain: env vars → shared credentials file → instance profile
const client = new RedshiftDataClient({ region: process.env.AWS_REGION || 'us-east-1' });

// Provisioned cluster connection — DbUser is required for temp credentials
// DbUser can differ from the IAM user — Redshift creates it on first connection
const provisionedConn = {
  ClusterIdentifier: process.env.REDSHIFT_CLUSTER_ID,
  Database: process.env.REDSHIFT_DATABASE,
  DbUser: process.env.REDSHIFT_DB_USER,
};

// Redshift Serverless connection — no ClusterIdentifier, no DbUser
const serverlessConn = {
  WorkgroupName: process.env.REDSHIFT_WORKGROUP,
  Database: process.env.REDSHIFT_DATABASE,
  // SecretArn: ... can be used instead of DbUser for credential management
};

// Required IAM policy for the Data API:
// {
//   "Effect": "Allow",
//   "Action": [
//     "redshift-data:ExecuteStatement",
//     "redshift-data:DescribeStatement",
//     "redshift-data:GetStatementResult",
//     "redshift-data:ListDatabases",
//     "redshift-data:ListSchemas",
//     "redshift-data:ListTables",
//     "redshift-data:DescribeTable",
//     "redshift:GetClusterCredentials"  // provisioned only
//   ],
//   "Resource": "*"
// }

Alternatively, use SecretsArn instead of DbUser — store the Redshift username and password in AWS Secrets Manager and pass the secret ARN to the Data API. This enables credential rotation without code changes and removes the need for redshift:GetClusterCredentials on the IAM principal.

Async statement execution and polling

ExecuteStatement enqueues the SQL and returns immediately with a statement ID. The status transitions are: SUBMITTED → PICKED → STARTED → FINISHED (success) or FAILED or ABORTED. Poll DescribeStatement until the terminal state, then fetch results.

async function executeQuery(client, conn, sql, timeoutMs = 60_000) {
  // Submit
  const submit = await client.send(new ExecuteStatementCommand({
    ...conn,
    Sql: sql,
  }));

  const statementId = submit.Id;
  const deadline = Date.now() + timeoutMs;

  // Poll for completion
  while (Date.now() < deadline) {
    const describe = await client.send(new DescribeStatementCommand({ Id: statementId }));
    const status = describe.Status; // 'SUBMITTED' | 'PICKED' | 'STARTED' | 'FINISHED' | 'FAILED' | 'ABORTED'

    if (status === 'FINISHED') {
      return { statementId, describe };
    }

    if (status === 'FAILED') {
      throw new Error(
        `Redshift statement failed: ${describe.Error || 'unknown error'} ` +
        `(QueryString: ${describe.QueryString?.slice(0, 100)})`
      );
    }

    if (status === 'ABORTED') {
      throw new Error(`Redshift statement ${statementId} was aborted`);
    }

    await new Promise(r => setTimeout(r, 2_000));
  }

  // Cancel the statement on timeout to avoid orphaned queries
  await client.send(new CancelStatementCommand({ Id: statementId })).catch(() => {});
  throw new Error(`Redshift statement ${statementId} did not complete in ${timeoutMs}ms`);
}

// Fetch rows — paginate via NextToken
async function fetchResults(client, statementId) {
  const rows = [];
  let columnMetadata = null;
  let nextToken;

  do {
    const result = await client.send(new GetStatementResultCommand({
      Id: statementId,
      NextToken: nextToken,
    }));

    if (!columnMetadata) columnMetadata = result.ColumnMetadata;
    rows.push(...result.Records);
    nextToken = result.NextToken;
  } while (nextToken);

  return { columnMetadata, rows };
}

// Type-decode a field value from GetStatementResult
function decodeField(field) {
  if (field.isNull) return null;
  if ('longValue' in field) return field.longValue;
  if ('doubleValue' in field) return field.doubleValue;
  if ('booleanValue' in field) return field.booleanValue;
  if ('stringValue' in field) return field.stringValue;
  if ('blobValue' in field) return Buffer.from(field.blobValue).toString('hex');
  return null;
}

// Convert rows to objects keyed by column name
function rowsToObjects(columnMetadata, records) {
  return records.map(record =>
    Object.fromEntries(columnMetadata.map((col, i) => [col.name, decodeField(record[i])]))
  );
}

Provisioned cluster vs Redshift Serverless

Provisioned clusters and Serverless workgroups expose different APIs for cluster status. Health probes must use the appropriate API for their deployment type. Both are accessible through the same Redshift Data API for query execution, but the status checks differ.

import {
  RedshiftClient,
  DescribeClustersCommand,
} from '@aws-sdk/client-redshift';

import {
  RedshiftServerlessClient,
  GetWorkgroupCommand,
} from '@aws-sdk/client-redshift-serverless';

// Provisioned cluster health
async function getProvisionedClusterStatus(clusterIdentifier, region) {
  const client = new RedshiftClient({ region });
  const result = await client.send(new DescribeClustersCommand({
    ClusterIdentifier: clusterIdentifier,
  }));

  const cluster = result.Clusters[0];
  return {
    status: cluster.ClusterStatus,
    // 'available' — normal
    // 'paused' — cluster is paused, Data API queries will fail
    // 'creating' | 'deleting' | 'rebooting' | 'resizing' — transitional
    // 'maintenance' | 'prep-for-resize' — temporary maintenance
    availabilityStatus: cluster.ClusterAvailabilityStatus,
    // 'Available' | 'Unavailable' | 'Maintenance' | 'Modifying'
    nodeType: cluster.NodeType,
    numberOfNodes: cluster.NumberOfNodes,
    endpoint: cluster.Endpoint?.Address,
  };
}

// Serverless workgroup health
async function getServerlessWorkgroupStatus(workgroupName, region) {
  const client = new RedshiftServerlessClient({ region });
  const result = await client.send(new GetWorkgroupCommand({
    workgroupName,
  }));

  const wg = result.workgroup;
  return {
    status: wg.status,
    // 'AVAILABLE' | 'CREATING' | 'DELETING' | 'MODIFYING'
    baseCapacity: wg.baseCapacity, // RPUs
    endpoint: wg.endpoint?.address,
    namespaceName: wg.namespaceName,
  };
}

Schema introspection via the Data API

The Redshift Data API provides dedicated commands for schema introspection that don't require executing SQL queries — useful for populating LLM context about available tables.

import {
  ListDatabasesCommand,
  ListSchemasCommand,
  ListTablesCommand,
  DescribeTableCommand,
} from '@aws-sdk/client-redshift-data';

async function listTables(client, conn, schemaPattern = 'public') {
  const result = await client.send(new ListTablesCommand({
    ...conn,
    SchemaPattern: schemaPattern,
    // MaxResults: 100 (default), paginate via NextToken
  }));
  return result.Tables.map(t => ({
    name: t.name,
    schema: t.schema,
    type: t.type, // 'TABLE' | 'VIEW' | 'FOREIGN' | 'SHARED TABLE'
  }));
}

async function describeTable(client, conn, table, schema = 'public') {
  const result = await client.send(new DescribeTableCommand({
    ...conn,
    Table: table,
    Schema: schema,
  }));
  return result.ColumnList.map(col => ({
    name: col.columnName,
    type: col.columnTypeName,
    nullable: col.nullable,
    length: col.length,
    precision: col.precision,
    scale: col.scale,
    isAutoIncrement: col.isAutoIncrement,
  }));
}

Health monitoring for Redshift MCP tools

A complete Redshift health probe has two layers: (1) cluster availability status — a paused or rebooting cluster will fail all queries even though the Data API endpoint itself is reachable, and (2) an actual query execution test to verify credentials, database access, and query queue health. Check both independently so AliveMCP can distinguish infrastructure problems from access problems.

async function probeRedshift(dataClient, conn, clusterType = 'provisioned') {
  const results = await Promise.allSettled([

    // 1. Cluster/workgroup availability
    (async () => {
      if (clusterType === 'serverless') {
        const status = await getServerlessWorkgroupStatus(conn.WorkgroupName, 'us-east-1');
        if (status.status !== 'AVAILABLE') {
          throw new Error(`Redshift Serverless workgroup ${conn.WorkgroupName} is ${status.status}`);
        }
        return { kind: 'cluster', type: 'serverless', status: status.status };
      } else {
        const status = await getProvisionedClusterStatus(conn.ClusterIdentifier, 'us-east-1');
        if (status.status !== 'available') {
          throw new Error(`Redshift cluster ${conn.ClusterIdentifier} is ${status.status}`);
        }
        return { kind: 'cluster', type: 'provisioned', status: status.status };
      }
    })(),

    // 2. Query execution — end-to-end test
    (async () => {
      const { statementId, describe } = await executeQuery(
        dataClient, conn, 'SELECT 1 AS alive, CURRENT_USER AS user', 10_000
      );
      const { columnMetadata, rows } = await fetchResults(dataClient, statementId);
      const parsed = rowsToObjects(columnMetadata, rows);
      return {
        kind: 'query',
        user: parsed[0]?.user,
        durationMs: Number(describe.Duration) / 1_000_000, // nanoseconds → ms
      };
    })(),
  ]);

  return {
    healthy: results.every(r => r.status === 'fulfilled'),
    components: results.map(r =>
      r.status === 'fulfilled'
        ? { ...r.value, ok: true }
        : { ok: false, error: r.reason?.message }
    ),
  };
}

Register both the cluster status check and the query execution probe as separate monitors in AliveMCP. The cluster check can use a 30-second interval; the query probe should run every 2 minutes to avoid adding unnecessary load to the query queue.

Common integration pitfalls

Confusing provisioned and Serverless connection parameters

ExecuteStatement requires either ClusterIdentifier (provisioned) or WorkgroupName (Serverless) — not both. Providing the wrong one or neither returns ValidationException: Either ClusterIdentifier or WorkgroupName must be provided. Use environment variables to distinguish deployment type at startup rather than trying to detect it at query time.

DbUser auto-creation behavior

When using DbUser with GetClusterCredentials, Redshift creates the database user on first connection if it doesn't exist. The created user has minimal permissions — no SCHEMA USAGE, no SELECT grants. Run GRANT USAGE ON SCHEMA public TO {DbUser} and GRANT SELECT ON ALL TABLES IN SCHEMA public TO {DbUser} after first connection to grant read access.

Statement ID lifetime

Statement results are available for 24 hours after the statement finishes. After 24 hours, GetStatementResult returns ResourceNotFoundException. For long-running pipelines, store result rows in your own storage rather than re-fetching from the Data API later.

Field type union decoding

GetStatementResult returns each field as a union: { longValue: 42 } or { stringValue: "hello" } or { isNull: true }. Code that accesses field.value directly gets undefined for all fields. Always use a type-dispatch decoder that checks which key is present.

Cluster paused but Data API returns 200

When a provisioned cluster is paused, the Redshift Data API endpoint remains reachable and returns HTTP 200 from ExecuteStatement — the statement ID is returned immediately. The failure only appears when polling DescribeStatement, which returns Status: FAILED with an error like Cluster is paused. A health probe that only checks the Data API's reachability misses this failure mode.

Related guides

• MCP tools for BigQuery — service account auth, async Jobs API, dry-run cost estimation
• MCP tools for Snowflake — SQL API v2, JWT auth, warehouse auto-suspend
• MCP tools for Databricks — SQL Warehouse REST API, PAT auth, warehouse state lifecycle
• MCP tools for AWS S3 — SigV4 auth, S3-compatible API patterns
• MCP server health check patterns — composite endpoints and failure classification
• MCP server uptime monitoring — probes, intervals, and alert routing