Amazon Redshift MCP Server

Amazon Redshift MCP Server

Model Context Protocol (MCP) server for Amazon Redshift.

This MCP server provides tools to discover, explore, and query Amazon Redshift clusters and serverless workgroups. It enables AI assistants to interact with Redshift resources safely and efficiently through a comprehensive set of discovery and query execution tools.

[!WARNING] Read-write mode (ACCESS_MODE=read-write) lets an AI model modify and delete your data. The server is read-only by default. Leave it that way unless you accept that risk.

Without using transactions, in read-write mode a statement takes effect the moment it runs, and there is no undo. You can start a transaction and roll it back instead, but only if you ask for one. The server prompts you to confirm each statement that could change something before it executes, which is the only point at which you can stop it. There is also an unsafe mode, off by default, that skips those prompts, leaving nothing between a generated statement and your data, so do not turn it on unless you genuinely have to.

Generative AI can make mistakes. You should consider reviewing all output generated by your chosen AI model and agentic coding assistant. See AWS Responsible AI Policy. In read-write mode, review each statement before you approve it.

The model you use may also be susceptible to prompt injection: untrusted content it reads (a table value, a column comment, a document in the conversation) can steer it into issuing a destructive statement you never asked for.

The database user's privileges are the security boundary, not this server. Never enable read-write mode with an admin or superuser database identity. Use a dedicated database user, and grant it only the privileges the workload needs, on only the objects it touches. On a production cluster the stakes are higher, not different: make sure you have a snapshot you can restore before you point a write-capable server at it.

Features

• Cluster Discovery: Automatically discover both provisioned Redshift clusters and serverless workgroups
• Metadata Exploration: Browse databases, schemas, tables, and columns
• Safe Query Execution: Run SQL queries in a read-only mode by default (single statement; writes rejected), with read-write available as an explicit opt-in
• Multi-Cluster Support: Work with multiple clusters and workgroups simultaneously
• Concurrent Queries: Run multiple queries at the same time, including against the same cluster and database
• Named Transactions: Group statements across several calls under a name you choose, then commit or roll them back, see Transactions
• Consolidated Cluster Review: Review a cluster or workgroup for health and configuration issues, with a recommendation for each finding

Prerequisites

Installation Requirements

1. Install uv from Astral or the GitHub README
2. Install Python 3.10 or newer using uv python install 3.10 (or a more recent version)

AWS Client Requirements

1. Credentials: Configure AWS credentials via AWS CLI, or environment variables
2. Region: Configure AWS region using one of the following (in order of precedence):
   • AWS_REGION environment variable (highest priority)
   • AWS_DEFAULT_REGION environment variable
   • Region specified in your AWS profile configuration
3. Permissions: Ensure your AWS credentials have the required permissions (see Permissions section)

Installation

Kiro	Cursor	VS Code

Configure the MCP server in your MCP client configuration (e.g., for Kiro, edit ~/.kiro/settings/mcp.json):

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "command": "uvx",
      "args": ["awslabs.redshift-mcp-server@latest"],
      "env": {
        "AWS_PROFILE": "default",
        "AWS_DEFAULT_REGION": "us-east-1",
        "LOG_LEVEL": "INFO"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

Windows Installation

For Windows users, the MCP server configuration format is slightly different:

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "disabled": false,
      "timeout": 60,
      "type": "stdio",
      "command": "uv",
      "args": [
        "tool",
        "run",
        "--from",
        "awslabs.redshift-mcp-server@latest",
        "awslabs.redshift-mcp-server.exe"
      ],
      "env": {
        "AWS_PROFILE": "your-aws-profile",
        "AWS_DEFAULT_REGION": "us-east-1",
        "LOG_LEVEL": "ERROR"
      }
    }
  }
}

or docker after a successful docker build -t awslabs/redshift-mcp-server:latest .:

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "command": "docker",
      "args": [
        "run",
        "--rm",
        "--interactive",
        "--env", "AWS_ACCESS_KEY_ID=[your data]",
        "--env", "AWS_SECRET_ACCESS_KEY=[your data]",
        "--env", "AWS_DEFAULT_REGION=[your data]",
        "awslabs/redshift-mcp-server:latest"
      ]
    }
  }
}

Environment Variables

• AWS_REGION: AWS region to use (overrides all other region settings)
• AWS_DEFAULT_REGION: Default AWS region (used if AWS_REGION is not set, ahead of the profile's region)
• AWS_PROFILE: AWS profile to use (optional, uses default if not specified). When set, it takes precedence over credentials in environment variables
• LOG_LEVEL: Logging level (optional, defaults to WARNING). One of DEBUG, INFO, WARNING, ERROR.
• LOG_FILE: Path to log file (optional, logs to stderr if not specified)
• ACCESS_MODE: Access mode for execute_query (optional, defaults to read-only). Supported modes are read-only and read-write; any other value falls back to read-only. Read Read-Write Mode before selecting read-write.
• UNSAFE_SKIP_WRITE_CONFIRMATION: Skips the per-write confirmation prompt (optional, defaults to false). Ignored in read-only mode. See Confirming each write.
• SESSION_KEEPALIVE: How long an open transaction may sit idle before Redshift ends it, in seconds (optional, defaults to 600, maximum 86400). Only transactions are affected; a statement outside one holds nothing open. See Transactions.
• MAX_OPEN_TRANSACTIONS_PER_TARGET: How many transactions may be open at once against one cluster and database (optional, defaults to 10).
• MAX_RESULT_ROWS: The most rows one statement may return (optional, defaults to 1000). A larger result is refused rather than cut short, by every tool but list_clusters. Only a whole number above zero is accepted; anything else falls back to the default. It counts rows, not bytes: a VARCHAR value can be 64 KB, so a result of wide rows within the default can be tens of megabytes.

The log carries statement content: DEBUG writes every statement's full SQL, so a CREATE USER ... PASSWORD lands verbatim, and ERROR — included by the default WARNING — carries the engine's message, which quotes the offending value or relation. Treat LOG_FILE as holding the statements themselves.

Prompt Examples

Discovery Workflow

1. Discover Clusters and Workgroups: Call list_clusters to find all provisioned clusters and serverless workgroups, noting their identifiers, types, and status
2. Select Target Environment: Choose a specific cluster or workgroup based on your query needs
3. List Databases: Use list_databases to explore databases in the selected cluster/workgroup, returning database names, owners, types (local/shared), and access control info
4. Explore Schemas: For a given database, call list_schemas to find available schemas and their owners
5. Inspect Tables: Use list_tables to browse tables, views, and external tables within a schema, including table types and owners
6. Examine Columns: Call list_columns to get column metadata — names, data types, nullability, default values, precision and scale
7. Query Data: Execute SQL queries safely with execute_query using a natural language prompt or direct SQL

Simple Examples

Database Discovery

Explore everything in my Redshift environment.

The assistant will:

1. Call list_clusters to find all available clusters and workgroups, and for each available cluster do steps 2-5
2. Call list_databases to discover databases
3. Call list_schemas on each database to map the schema landscape
4. Call list_tables in each schema to discover tables and views
5. Call list_columns on key tables to get detailed column metadata
6. Present a complete summary of the environment structure

Schema Exploration

What tables are in the 'public' schema of the 'dev' database in my 'analytics-cluster'?

The assistant will:

1. Call list_clusters to find the cluster's type
2. Call list_tables with the cluster, its type, the database, and the schema
3. List all tables with their types (TABLE, VIEW, EXTERNAL TABLE)

Column Inspection

Show me the columns of the 'users' table in the 'analytics-cluster'.

The assistant will:

1. Call list_clusters to find the cluster's type
2. Call list_databases and list_schemas to locate the table
3. Call list_columns to get column metadata
4. Display column names, types, nullability, and default values

Data Querying

How many customers signed up last month? Run the query on my production cluster.

The assistant will:

1. Use list_clusters to find the production cluster
2. Explore the schema to locate the relevant table
3. Execute the appropriate SQL via execute_query
4. Format and present the results

Advanced Examples

Natural Language Data Request

Show me the last 3 sales from the analytics Redshift cluster.

I'll help you retrieve the last 3 sales from your analytics Redshift cluster. Let me start by discovering your available Redshift clusters and then explore the database structure to find the sales data.

...discovering 3 Redshift environments (analytics cluster, marketing cluster, ETL workgroup)...

Here are the last 3 sales from your analytics Redshift cluster:

Most Recent Sales (from tickit.sales table):

1. Sale ID 102373 - December 31, 2008 at 12:58:19
   - Price paid: $1,008.00
   - Seller ID: 26190, Buyer ID: 35491

2. Sale ID 75861 - December 31, 2008 at 12:50:02
   - Price paid: $372.00
   - Seller ID: 43402, Buyer ID: 39545

3. Sale ID 56302 - December 31, 2008 at 12:08:14
   - Price paid: $372.00
   - Seller ID: 5760, Buyer ID: 5797

> Briefly describe your actions.
I performed the following actions:

1. Discovered clusters — Listed all available Redshift clusters and identified redshift-cluster-analytics
2. Explored databases — Found two databases: dev and sample_data_dev
3. Located schemas — Checked the sample_data_dev database and found the tickit schema
4. Found sales table — Identified the sales table within the tickit schema
5. Analyzed table structure — Examined columns in the sales table (salesid, pricepaid, qtysold, saletime, etc.)
6. Executed query — Ran SQL to get the 3 most recent sales ordered by saletime DESC

> Show me the query text.
Here's the exact SQL query:

sql
SELECT salesid, listid, sellerid, buyerid, eventid, qtysold, pricepaid, commission, saletime
FROM tickit.sales
ORDER BY saletime DESC
LIMIT 3;

Cross-Cluster Data Comparison

How off is the analytics sales data from the ETL sales data in Redshift?

I'll help you analyze sales data from your Redshift ETL pipeline. Let me discover your Redshift environment first.

...discovering 3 Redshift environments (analytics cluster, marketing cluster, ETL workgroup)...

# Sales Data Comparison: Analytics vs ETL

## Executive Summary
Sales data between Analytics cluster and ETL workgroup is perfectly synchronized with zero discrepancies found.

## Environment Overview
- Analytics: redshift-cluster-analytics (Provisioned, 2x dc2.large nodes)
- ETL: redshift-workgroup-etl (Serverless)
- Database: sample_data_dev.tickit.sales

## Data Comparison Results

### High-Level Metrics
| Metric          | Analytics Cluster | ETL Workgroup     | Status |
|-----------------|-------------------|-------------------|--------|
| Total Records   | 172,456           | 172,456           | Match  |
| Total Revenue   | $110,765,431      | $110,765,431      | Match  |
| Total Quantity  | 345,349           | 345,349           | Match  |
| Average Price   | $642.28           | $642.28           | Match  |
| Date Range      | 2008-01-01 to 2008-12-31 | 2008-01-01 to 2008-12-31 | Match |

### Recent Sales (Q4 2008)
| Month    | Analytics Revenue | ETL Revenue   | Analytics Txns | ETL Txns | Status |
|----------|-------------------|---------------|----------------|----------|--------|
| Dec 2008 | $4,521,806        | $4,521,806    | 7,059          | 7,059    | Match  |
| Nov 2008 | $9,044,905        | $9,044,905    | 14,261         | 14,261   | Match  |
| Oct 2008 | $10,726,368       | $10,726,368   | 16,741         | 16,741   | Match  |

## Key Findings
- Data Integrity: Perfect synchronization across all metrics
- Verification: Transaction-level details match exactly (verified 10 recent records)

> Briefly describe your actions.
I analyzed sales data from your Redshift environments by:

1. Discovered Infrastructure — Found 3 Redshift environments (analytics, marketing, ETL)
2. Explored Data Structure — Located sales data in sample_data_dev.tickit.sales with 10 columns
3. Compared High-Level Metrics — Ran aggregate queries on both clusters (172,456 records, $110M revenue)
4. Analyzed Recent Data — Examined Q4 2008 monthly trends and verified 10 most recent transactions
5. Generated Report — Created a comprehensive comparison showing perfect synchronization

> Show me the SQL queries you used.
## 1. High-Level Metrics
sql
SELECT
    COUNT(*) as total_records,
    MIN(saletime) as earliest_sale,
    MAX(saletime) as latest_sale,
    SUM(pricepaid) as total_revenue,
    SUM(qtysold) as total_quantity,
    AVG(pricepaid) as avg_price
FROM tickit.sales;

## 2. Recent Monthly Sales
sql
SELECT
    DATE_TRUNC('month', saletime) as sale_month,
    COUNT(*) as transaction_count,
    SUM(pricepaid) as monthly_revenue,
    SUM(qtysold) as monthly_quantity,
    AVG(pricepaid) as avg_transaction_value
FROM tickit.sales
WHERE saletime >= '2008-10-01'
GROUP BY DATE_TRUNC('month', saletime)
ORDER BY sale_month DESC
LIMIT 10;

## 3. Recent Transaction Details
sql
SELECT salesid, listid, sellerid, buyerid, eventid, qtysold, pricepaid, commission, saletime
FROM tickit.sales
WHERE saletime >= '2008-12-30'
ORDER BY saletime DESC, salesid DESC
LIMIT 10;

All-or-Nothing Change (read-write mode + transactions)

On the analytics cluster, remove seller 48950 from tickit: their sales and their listings. All or nothing.

Seller 48950 has 19 sales and 11 listings. Two tables have to change together, so I'll group both deletes in one transaction and close it in the same breath.

> Execute this statement against redshift-cluster-analytics (provisioned):sample_data_dev? It runs inside transaction 'seller_48950', so any change it makes in the database is not final until you commit. Anything it does outside the database, such as writing to S3, is final as soon as it runs.
>
> DELETE FROM tickit.sales WHERE sellerid = 48950
[approved]

...opening transaction "seller_48950" and running the delete...

19 sales deleted, and not yet visible to anything outside the transaction. Now the listings, committing as I go.

> Execute this statement against redshift-cluster-analytics (provisioned):sample_data_dev? It runs inside transaction 'seller_48950' and commits it, so it is final and cannot be rolled back.
>
> DELETE FROM tickit.listing WHERE sellerid = 48950
[approved]

...running the delete and committing transaction "seller_48950"...

Done. Seller 48950 is gone from both tables, 19 sales and 11 listings, in one transaction that took two calls: the first opened it and carried the sales delete, the second carried the listings delete and committed. Had the second delete failed, the first would have been discarded too and the seller would still be there in full. Had you declined it instead, nothing would have run and the transaction would still be open with the sales delete staged, yours to commit or roll back.

Tools

list_clusters

Discovers all available Amazon Redshift clusters and serverless workgroups.

list_clusters() -> list[RedshiftCluster]

Returns: List of cluster information including:

• Cluster identifier and type (provisioned/serverless)
• Status and connection details
• Configuration information (node type, encryption, etc.)
• Tags and metadata. A serverless workgroup's tags need the optional redshift-serverless:ListTagsForResource; without it a workgroup is reported untagged.

Provisioned clusters and serverless workgroups are separate AWS namespaces, so one name can be both. Every other tool therefore takes the type with the identifier: cluster_type, provisioned or serverless, as list_clusters reports it. Every tool that takes a cluster_identifier, except review_cluster, reuses a lookup for up to 5 minutes; list_clusters and review_cluster look again every time they run. Until the lookup is replaced, a cluster created since reads as not found, with the refusal saying how old the lookup is. A lookup that runs while one of the two listings is denied refuses a cluster of that type, names the denial, and is not reused.

list_databases

Lists all databases in a specified Redshift cluster.

list_databases(
    cluster_identifier: str, cluster_type: str, database_name: str = "dev"
) -> list[RedshiftDatabase]

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• database_name: Database to connect to for querying (default: "dev")

Returns: List of database information including:

• Database name and owner
• Database type (local/shared)
• Access control information
• Isolation level

list_schemas

Lists all schemas in a specified database.

list_schemas(
    cluster_identifier: str, cluster_type: str, schema_database_name: str
) -> list[RedshiftSchema]

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• schema_database_name: Database name to list schemas for

Returns: List of schema information including:

• Schema name and owner
• Schema type (local/external/shared)
• Access permissions
• External schema details (if applicable)

list_tables

Lists all tables in a specified schema.

list_tables(
    cluster_identifier: str,
    cluster_type: str,
    table_database_name: str,
    table_schema_name: str,
) -> list[RedshiftTable]

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• table_database_name: Database name containing the schema
• table_schema_name: Schema name to list tables for

Returns: List of table information including:

• Table name and type (TABLE/VIEW/EXTERNAL TABLE)
• Access permissions
• Remarks and metadata

list_columns

Lists all columns in a specified table.

list_columns(
    cluster_identifier: str,
    cluster_type: str,
    column_database_name: str,
    column_schema_name: str,
    column_table_name: str,
) -> list[RedshiftColumn]

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• column_database_name: Database name containing the table
• column_schema_name: Schema name containing the table
• column_table_name: Table name to list columns for

Returns: List of column information including:

• Column name and data type
• Nullable status and default values
• Numeric precision and scale
• Character length limits
• Ordinal position and remarks

execute_query

Executes a SQL query against a Redshift cluster with safety protections. Read-only unless read-write mode is enabled, see Read-Write Mode.

execute_query(
    cluster_identifier: str,
    cluster_type: str,
    database_name: str,
    sql: str | None = None,
    begin_transaction: str | None = None,
    in_transaction: str | None = None,
    commit_transaction: str | None = None,
    rollback_transaction: str | None = None,
) -> QueryResult

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• database_name: Database to execute the query against
• sql: SQL statement to execute (a single statement). Required on its own and with in_transaction; optional with the other three
• begin_transaction / in_transaction / commit_transaction / rollback_transaction (all optional): Name of a transaction to start, run the query in, commit, or roll back. At most one per call, see Transactions

Returns: Query result including:

• Column names
• Result rows, with integers, floats, booleans and nulls as native types and everything else as a string
• Row count
• Query ID for reference

A result set is read to its end however many pages the service splits it into, so the row count is the whole result rather than a first page. A result of more than MAX_RESULT_ROWS rows is refused rather than cut short; add a LIMIT or an aggregate to a query that may match more.

review_cluster

Runs a diagnostic review of a Redshift cluster or serverless workgroup. Returns identified potential issues and their recommendations. Recommendations come in the order their signals first triggered; nothing sorts them by effort or impact.

review_cluster(
    cluster_identifier: str, cluster_type: str, database_name: str = 'dev'
) -> ReviewResult

Parameters:

• cluster_identifier: The cluster identifier from list_clusters
• cluster_type: provisioned or serverless, the type list_clusters reports for it
• database_name: Database to connect to for querying system views (defaults to dev)

Returns: Review result including:

• Number of signals evaluated
• Findings with affected row counts and recommendation IDs
• Deduplicated recommendations with documentation links
• List of diagnostic queries executed

Note: Requires the connecting database user to hold the sys:monitor role (or be a superuser), see Database Permissions. Provisioned-only diagnostics are automatically skipped for serverless workgroups.

Permissions

AWS IAM Permissions

Your AWS credentials need the following IAM permissions:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeClusters",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:ListTagsForResource",
        "redshift-data:BatchExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift-serverless:GetCredentials",
        "redshift:GetClusterCredentialsWithIAM"
      ],
      "Resource": "*"
    }
  ]
}

redshift-serverless:ListTagsForResource is optional: it adds nothing but a serverless workgroup's tags. Without it the server logs a warning on each discovery and reports workgroups untagged. A provisioned cluster's tags come inline from DescribeClusters, so they are unaffected.

redshift-serverless:GetWorkgroup is not needed. ListWorkgroups returns the same fields, so the server does not call it.

redshift:GetClusterCredentials is not needed either, and is best left out: the server names no database user, so the Data API authenticates it as its own IAM identity through GetClusterCredentialsWithIAM, while GetClusterCredentials can mint credentials for any database user, the admin included.

Upgrading from an earlier version

[!IMPORTANT] Add redshift-data:BatchExecuteStatement to your policy. Earlier versions needed only redshift-data:ExecuteStatement, which is what the server now falls back to when the batch action is denied.

Until you add it, the server keeps the read-only behaviour of the earlier version: discovery, review_cluster and reads work, while writes and named transactions are refused with a message naming the action.

The server logs a warning each time it sees the denial, then tries the batch path again on the first request made more than 5 minutes later, or sooner if a batch call on that cluster is accepted, so granting the action takes effect without a restart. Nothing retries in the background. The denial is recorded per cluster, since the action takes resource-level permissions. This compatibility path will be removed in a later release.

Database Permissions

In addition to AWS IAM permissions, you need appropriate database-level permissions:

• Read Access: SELECT permissions on tables/views you want to query

• Schema Access: USAGE permissions on schemas you want to explore

• Database Access: Connection permissions to databases you want to access

• Review Access: The review_cluster tool reads system views such as SYS_AUTO_TABLE_OPTIMIZATION, STV_NODE_STORAGE_CAPACITY, and SVV_TABLE_INFO, which require superuser or sys:monitor access. Grant the connecting database user the sys:monitor role, which is the narrowest grant that covers them:

  GRANT ROLE sys:monitor TO "<database_user>";

  <database_user> is the output of SELECT current_user. When the server authenticates with IAM credentials it is IAM:<user> or IAMR:<role>, and the double quotes are required for those names. The grant must be issued by a superuser, such as the cluster's admin user.

For the strongest protection, grant these to a least-privilege, read-only role rather than a broad or write-capable one.

Read-Write Mode

[!WARNING] Read-write mode allows an AI model to modify and delete data in your cluster. The server is read-only by default and should stay that way unless you have a specific need and have read this whole section.

By default the server runs in read-only mode. Selecting read-write is a deliberate act:

"env": {
  "ACCESS_MODE": "read-write"
}

The mode is read once at startup, so it is fixed for the life of the server process and cannot be changed per call. Only read-write selects it, ignoring surrounding whitespace and letter case; anything else, including a typo, falls back to read-only and logs a warning naming the supported modes.

What changes in read-write mode

	Read-only (default)	Read-write
Transaction wrapper	BEGIN READ ONLY … ROLLBACK, so nothing is persisted	None; each statement runs with autocommit
Named transactions	Opened READ ONLY, giving several statements one snapshot	Writable, so several statements commit or roll back together
Writes and DDL	Rejected, either by the statement guard or by the read-only transaction	Executed and committed immediately
Statement-type deny list	Denies UNLOAD, GRANT, REVOKE, TRUNCATE, VACUUM, ANALYZE, CALL, COMMENT, CANCEL, SET, RESET, the set_config function that reaches the same settings, the pg_cancel_backend and pg_terminate_backend functions that cancel or end a session, the change_query_priority, change_session_priority and change_user_priority functions that change WLM priority, plus PREPARE, EXECUTE, DECLARE and FETCH, whose bodies the guard cannot read	Not applied
Transaction control in sql	Rejected	Rejected
TRUNCATE or CALL inside a named transaction	Rejected	Rejected: TRUNCATE commits and cannot be rolled back, and a NONATOMIC procedure can commit from inside the call. Both are available on their own
Multiple statements	Rejected	Rejected
Per-statement confirmation	Not asked; nothing is persisted	Asked before anything that could change something, unless UNSAFE_SKIP_WRITE_CONFIRMATION=true
How the tool describes itself to your client	Read-only and non-destructive	Destructive, so clients can warn you or ask before running it
Undo	Nothing to undo	Only inside a named transaction the caller opened and then rolls back; an autocommit statement is final, and no snapshot is taken

Only execute_query is affected. The discovery tools (list_clusters, list_databases, list_schemas, list_tables, list_columns) and review_cluster read metadata and system views in both modes.

Confirming each write

In read-write mode the server asks you to approve each statement that could change something, using the MCP elicitation feature. The prompt names the target cluster, its type and the database, and shows the statement. Declining or cancelling it means nothing is executed.

Statements recognized as reads run without a prompt. That is a deliberately short list:

• SELECT, including a WITH clause, QUALIFY, and a parenthesised query
• UNION, UNION ALL, INTERSECT, EXCEPT
• SHOW in all its forms, and EXPLAIN, which returns a plan without running its payload

Anything else that parses is treated as a write and prompts, including session statements such as SET and FETCH. Some cases look like reads but are not: SELECT … INTO creates a table, a WITH clause containing an INSERT writes even though a SELECT fronts it, and a SELECT calling set_config, pg_cancel_backend, pg_terminate_backend or one of the change_*_priority functions changes a setting, cancels or ends a session, or changes other work's WLM priority. All of them prompt. Syntax the server cannot parse at all is rejected outright, before any prompt.

The read list is syntactic. A SELECT that calls any other side-effecting function, a user-defined one included, is classified as a read and runs unprompted, so the database user's privileges remain the boundary that matters.

Showing that prompt is up to the tool you connect the server to, such as your IDE, chat client, or command-line assistant. Not all of them can. One that cannot is refused rather than allowed to run the write unconfirmed, with an error naming the opt-out below. Check if your client supports it.

To run writes without confirmation, on a client that cannot prompt or in an automated pipeline (dangerous — only do this if you know exactly what will run against which cluster):

"env": {
  "ACCESS_MODE": "read-write",
  "UNSAFE_SKIP_WRITE_CONFIRMATION": "true"
}

That leaves the database user's privileges as the only control. Setting it in read-only mode does nothing.

The limits of the guard

Single-statement enforcement still applies in read-write mode, which blocks statement stacking such as SELECT 1; DROP TABLE t. Treat it as defense in depth, not a security boundary. It does not inspect intent: a single DROP TABLE, DELETE, or UPDATE without a WHERE clause is a valid single statement, and will run once confirmed.

The durable control is the database user's privileges. With a least-privilege user in place, a destructive statement the model generates fails at the engine with a permission error regardless of what this server allows.

Recommended configuration

If you select read-write:

1. Know how you would recover. Development and staging clusters are the low-stakes place to start. Production is a legitimate target for write workloads, but confirm your snapshot schedule first, and take a manual snapshot before a batch of writes you cannot reconstruct.

2. Use a dedicated identity. The server connects as the database user of the IAM identity it runs under, IAMR:<role> or IAM:<user>, so run it under an IAM role of its own, never one whose database user is the cluster admin, a superuser, or holds broad GRANTs.

3. Grant only what the workload needs to that database user, naming the objects rather than the whole schema. SELECT current_user through the server shows its name, which is quoted because it contains a colon:

   -- Read and write only the tables the workload touches
   GRANT USAGE ON SCHEMA analytics TO "IAMR:mcp-read-write";
   GRANT SELECT, INSERT, UPDATE, DELETE ON analytics.events TO "IAMR:mcp-read-write";

4. Keep the human in the loop. The server asks you to confirm every write, see Confirming each write. As a second layer, configure your MCP client to require approval for each execute_query call rather than auto-approving the tool.

5. Keep the write-capable server switched off until you need it. Configure it as a second entry alongside a read-only one, disabled by default, and enable it only for the task that needs writes. Running both at once buys nothing on its own, because the model can call whichever it likes.

Transactions

Without a transaction parameter, every execute_query call runs on its own connection. No session state carries over: in read-write mode, a temporary table or a SET from one call is invisible to the next, though the statement that made it reports success. Calls to the same cluster and database run concurrently rather than queueing.

To carry state across calls, name a transaction:

execute_query(sql='CREATE TABLE staging.batch (...)', begin_transaction='load')
execute_query(sql='COPY staging.batch FROM ...',      in_transaction='load')
execute_query(sql='INSERT INTO events SELECT * FROM staging.batch', in_transaction='load')
execute_query(commit_transaction='load')

At most one of begin_transaction, in_transaction, commit_transaction and rollback_transaction per call. The name is the caller's to choose. sql is required on its own and with in_transaction; it is optional with the other three, so a transaction can be opened or closed on its own as well as with a statement.

Both access modes support this. In read-only a transaction is opened READ ONLY, which gives several statements one consistent snapshot; in read-write it makes several statements succeed or fail together. Each write inside one is still confirmed individually, and a declined write leaves the transaction open for you to commit or roll back.

What ends a transaction:

Cause	Result
commit_transaction	Its statements are persisted
rollback_transaction	Its statements are discarded, as far as a rollback reaches: changes in the database go, and anything done outside it, such as an UNLOAD to S3, stands
A statement inside it fails	Rolled back, and the name is dropped. Redshift refuses everything after a failed statement, so a later commit would report success while persisting nothing. A statement sent with in_transaction that ran but whose result could not be read does not end it: the transaction stays open, and the error says so. Nor does a statement refused before it is sent, by the SQL guard or the confirmation step. A commit_transaction that fails once sent always ends it, and says whether the COMMIT stands, may have applied, or was discarded
The call is cancelled	Discarded, and the name is dropped: nothing can reach the transaction again, so it is never committed, though its session can hold its locks until SESSION_KEEPALIVE ends it. A cancelled commit_transaction is the exception, since its COMMIT can still reach the cluster and stand. A call cancelled while it waits, for the cluster to resolve or for another call on the same transaction, ends nothing
Idle longer than SESSION_KEEPALIVE	Redshift ends it and discards its statements

A statement the guard rejects never reaches the cluster, so it does not abort the transaction. One the engine rejects does.

Some statements cannot run inside a transaction at all, whichever mode you are in. A write to an Iceberg table is one: Redshift answers insert into an "iceberg" table is not allowed within multistatement transaction, so submit it on its own rather than inside a named transaction. The same restriction is what keeps such a write out of read-only mode, where every statement runs inside BEGIN READ ONLY.

Two more are refused by this server rather than by Redshift, because each can end the transaction from inside it and leave a rollback reporting success over work that stands: TRUNCATE, which commits what it does, and CALL, since a NONATOMIC procedure may issue its own COMMIT. In read-write mode, run either on its own instead; read-only mode refuses both everywhere.

Keep them short. An open transaction holds a Redshift connection and can block other writers on the tables it touched, so the model is instructed to open one only once the statements it groups are decided, and to put nothing else between them — no waiting on you, no waiting on another system, no exploring. Asking for a transaction to be held open anyway is a legitimate thing to want, and the model is told to warn you what it costs before doing it.

A name is only ever your own: it lives in this server process, so it cannot collide with another client's, and reusing a name that is still open is refused rather than joining it. MAX_OPEN_TRANSACTIONS_PER_TARGET bounds how many you can hold open at once against one cluster and database.