MCP Server for Agents

View as Markdown

PREVIEW This feature is in public preview. It is under active development and may have stability or performance issues. It isn't subject to our backwards compatibility guarantees.

Starting in v26.24, Materialize provides a built-in materialize-agent Model Context Protocol (MCP) server (/api/mcp/agent, port 6876) for querying data products. The server is provided directly by Materialize; no sidecar process or external server is required.

Overview

The materialize-agent MCP server lets AI agents query business-facing data products over HTTP. You can connect an MCP-compatible client (such as Claude Code, Claude Desktop, or Cursor) to the MCP server and ask the agent to discover and query your data products using either natural language or SQL:

SELECT * FROM mcp_product_performance LIMIT 5;
What’s the total_revenue for product 42?
Perform a Pareto analysis on my products.

Connection methods

There are two ways to authenticate to the materialize-agent MCP server. Your method determines whether you need to set up a dedicated agent query environment:

OAuth: Starting in v26.30, your MCP client can sign you in through your browser. The agent connects as your user role with your existing privileges. You can skip the environment setup and go to Method 1: OAuth. Available for Cloud and for Self-Managed using SSO.
Token-based: You provide Base64-encoded credentials (the MCP token) to the client. The agent connects as a dedicated, least-privilege service account (i.e., a separate login role acting as a service account). Set up the agent query environment and data products first and then go to Method 2: Token-based authentication. Available for Cloud, Self-Managed, and the Emulator

Set up the agent query environment and data products

This setup is required only for the token-based connection method. If you’re using OAuth, you can skip to Connect to the MCP server.

NOTE:

Starting in v26.27, the query tool is enabled by default and can execute arbitrary SELECT queries (including joins) on all objects the agent can access (including system catalog objects), not just those discoverable by the get_data_products tool.

To prevent agents from reading system catalog objects, set restrict_to_user_objects on each agent role.

In Materialize, querying data products (i.e., running SELECT) requires:

SELECT privileges on each directly referenced data product.
USAGE privileges on the schemas that contain the data products.
USAGE privileges on the cluster where the query runs.

To use the materialize-agent MCP server, we recommend:

Creating a dedicated query environment for agents.
Defining curated data products within that environment.

NOTE: The examples below use the default materialize database.

Create an agent query environment

In general, AI agents that access the materialize-agent MCP server should be isolated to:

Query environment	Granted privileges
Serving cluster dedicated to agents	`USAGE` on this cluster only
Schema dedicated to agents	`USAGE` on this schema only

Create a dedicated cluster and schema:

CREATE CLUSTER mcp_cluster SIZE '25cc';
CREATE SCHEMA materialize.mcp_schema;

Create a functional role mcp_agent that can be assigned to individual agents:
```
CREATE ROLE mcp_agent;
```

Grant privileges to the functional role:

GRANT USAGE ON CLUSTER mcp_cluster TO mcp_agent;
GRANT USAGE ON SCHEMA materialize.mcp_schema TO mcp_agent;

Set the default cluster and schema for mcp_agent to mcp_cluster and mcp_schema:
```
ALTER ROLE mcp_agent SET cluster TO mcp_cluster;
ALTER ROLE mcp_agent SET search_path TO mcp_schema;
```
Later on, you will also set these role configurations on the specific agent roles since role configurations are not inherited; only privileges are inherited.
Recommended. Restrict the role to user objects only so that the query tool cannot read system catalog objects. You must run the following as a superuser:
```
ALTER ROLE mcp_agent SET restrict_to_user_objects = true;
```
As mentioned before, role configurations are not inherited; you must also set it on each specific agent role. Setting the parameter on the functional role is recommended as a precaution in case the role is ever used directly to run queries.

See also Restrict query tool access to user objects only.

Define data products and grant access

Once a dedicated agent environment is set up, create the curated data products in the dedicated cluster and schema rather than granting access to existing objects in other schemas; this allows you to:

Project, mask, or filter their contents before exposing them to the agent.
Restrict the agent’s USAGE to the dedicated schema.

💡 Tip:

To expose an existing object (such as a table, view, or materialized view) to the agent, create a view in mcp_schema that selects from it, then add an index on that view IN CLUSTER mcp_cluster. If the existing object is a materialized view, the index reuses the already-maintained result instead of recomputing it.
When a view (regular view or materialized view) is indexed, the indexed columns are surfaced in the tool input schema as preferred lookup keys, enabling index point-lookups instead of index scans.
Adding comments to the data product and its columns is optional but recommended. Comments are surfaced to the agent to help it better understand when and how to use the data products:
- Object-level comments: When a data product is indexed, if the index also has a comment, the index’s comment is surfaced to the agent. Otherwise, the view or materialized view’s comment is surfaced.
- Column comments: Column comments are made on the view or materialized view. Indexes do not support comments on columns.

Define data products

The following example assumes a materialized view sales.product_performance exists.

Create a view in the dedicated schema that selects from the existing materialized view:

CREATE VIEW materialize.mcp_schema.mcp_product_performance AS
SELECT * FROM sales.product_performance;

Index the view IN CLUSTER mcp_cluster. The indexed columns are surfaced to the agent as preferred lookup keys:

CREATE INDEX mcp_product_performance_idx
IN CLUSTER mcp_cluster
ON materialize.mcp_schema.mcp_product_performance (product_id);

Optional but recommended. Add comments to the view and column(s):

COMMENT ON VIEW materialize.mcp_schema.mcp_product_performance IS
'Per-product performance metrics including stock status. Use this to answer
questions about a specific product''s sales performance or inventory.';

COMMENT ON COLUMN materialize.mcp_schema.mcp_product_performance.total_revenue IS
'Lifetime gross revenue for this product, computed as SUM(quantity *
unit_price) across all order_items. Returns 0 for products that have
not been ordered yet.';

COMMENT ON COLUMN materialize.mcp_schema.mcp_product_performance.stock_status IS
'Derived inventory state: ''out_of_stock'' (stock_quantity = 0),
''low_stock'' (< 20), or ''in_stock'' (>= 20).';

Comments are surfaced to the agent to help the agent better understand when and how to use the data products.

Grant access

Grant SELECT privilege on the data products. For each existing data product, grant SELECT to the mcp_agent functional role:
```
GRANT SELECT ON materialize.mcp_schema.mcp_product_performance TO mcp_agent;
```
Optionally, set a default privilege to automatically grant SELECT to the mcp_agent functional role for future data products created in the mcp_schema:
```
ALTER DEFAULT PRIVILEGES
  FOR ROLE <creator_role> -- creator of the object
  IN SCHEMA materialize.mcp_schema
  GRANT SELECT ON TABLES TO mcp_agent;
```
- The FOR ROLE <creator_role> clause scopes the default privilege to those objects created by that role. Specify the role that will actually create your data products.
- TABLES includes views and materialized views also.
- ALTER DEFAULT PRIVILEGES only applies to objects created after the ALTER DEFAULT PRIVILEGES statement runs. For objects that already exist, use GRANT SELECT ON <object> TO mcp_agent.

Connect to the MCP server

Connect using OAuth or token-based authentication, as described in Connection methods.

Method 1: OAuth

Available starting in v26.30

NOTE: The OAuth method is available for Cloud and for Self-Managed using SSO.

With OAuth, the agent connects as your user role with your existing privileges. It is not confined to a dedicated agent query environment and can read anything your user can. You do not need to set up the agent query environment to connect this way.

💡 Tip:

If you have set up the agent query environment and data products, you can optionally grant the mcp_agent functional role to your user. This grants access to the curated data products if your user does not already have the necessary privileges.

GRANT mcp_agent TO <your_user>;

To limit what the agent can reach, set restrict_to_user_objects on your role (this excludes the system catalog only). For a confined, least-privilege agent, use a token-based service account instead.

Step 1. Get your MCP server URL

To connect, the MCP-compatible client needs the materialize-agent MCP server URL: <baseURL>/api/mcp/agent.

Log in to the Materialize Console.
Click the Connect link (lower-left corner) to open the Connect modal and click on the MCP Server tab.
In the Connect your client section, click on the Agent tab.

You can find your materialize-agent MCP server URL <baseURL>/api/mcp/agent as part of the code block.

If using Claude Code as your MCP-compatible client, you can copy the code block wholesale for the next step.

Self-Managed deployments using OAuth require SSO, which uses TLS. Get your MCP server URL from the Materialize Console:

Log in via the Materialize Console.
Click the Connect link (lower-left corner) to open the Connect modal and click on the MCP Server tab.
In the Connect your client section, click on the Agent tab.

You can find your materialize-agent MCP server URL <baseURL>/api/mcp/agent as part of the code block.

If using Claude Code as your MCP-compatible client, you can copy the code block wholesale for the next step.

Step 2. Configure your MCP client

In the following, replace <baseURL> with the MCP server URL from Step 1. For Cloud, the base URL has the format https://<region-id>.materialize.cloud.

Add the materialize-agent MCP server as local-scoped server (i.e., the configurations are stored in ~/.claude.json):
```
claude mcp add --transport http "materialize-agent" \
  "<baseURL>/api/mcp/agent"
```
Restart Claude Code. On first connection, your browser opens to complete sign-in and connect.
Upon successful connection, you can Start querying.

To configure Claude Desktop/Chrome, add a custom connector. The exact steps depend on your Claude plan; for example:

Organization settings → Connectors → Add → Custom → Web, or
Customize → Connectors → + → Add custom connector.

Refer to the Add a custom connector section of the Get started with custom connectors using Remote MCP guide to get the exact steps for your plan. For the Remote MCP server URL field, enter your materialize-agent MCP server URL.

For additional information, including network requirements and security and privacy concerns, see the Get started with custom connectors using Remote MCP article.

Add the materialize-agent MCP server entry to your local MCP settings file (~/.cursor/mcp.json).
- When merging into an existing mcpServers object, remember to add commas between entries.
- If the mcpServers field does not already exist, add it as well.
```
{
  "mcpServers": {
    "materialize-agent": {
      "url": "<baseURL>/api/mcp/agent"
    }
  }
}
```
Restart Cursor. On first connection, your browser opens to complete sign-in and connect.
Upon successful connection, you can Start querying.

Method 2: Token-based authentication

Step 1. Create the specific agent role

For your specific agent, create the dedicated role with which the agent will connect.

Create a dedicated service account for your specific AI agent (only an Org admin can create service accounts).¹

For example, to create a new my_agent service account:

Click + Create New and select App Password to open the New app password modal.

In the New app password modal, specify:

Field	Value
Type	Service
Name	MCP
User	my_agent
Roles	Organization Member

Click Create Password. The Password and the MCP Token are created.
Save the MCP Token in a secure place. Once you navigate away, the password and the MCP token will not display again. You will use the MCP Token to connect.

Ensure the corresponding database role has been created, either by:
- Manually issuing the following commands in the SQL Shell:
```
CREATE ROLE my_agent;
```
- Or, connecting to Materialize (not the MCP server) using the new account. On first connection, Materialize automatically creates the corresponding database role if it does not exist.
Grant mcp_agent role to your agent:
```
GRANT mcp_agent TO my_agent;
```
Set the default cluster and schema for my_agent to mcp_cluster and mcp_schema:
```
ALTER ROLE my_agent SET cluster TO mcp_cluster;
ALTER ROLE my_agent SET search_path TO mcp_schema;
```
You set these role configurations on the individual roles as configurations are not inherited.
Recommended. Restrict the role to user objects only so that the query tool cannot read system catalog objects. You must run the following as a superuser (an Organization Admin):
```
ALTER ROLE my_agent SET restrict_to_user_objects = true;
```

Avoid using a personal app account instead of a service account as a personal app account would include all your roles and privileges as well. ↩︎

Create a login role for your specific AI agent, replacing <your_app_password> with an actual password:
```
CREATE ROLE my_agent LOGIN PASSWORD '<your_app_password>';
```
Grant mcp_agent role to your agent:
```
GRANT mcp_agent TO my_agent;
```
Set the default cluster and schema for my_agent to mcp_cluster and mcp_schema:
```
ALTER ROLE my_agent SET cluster TO mcp_cluster;
ALTER ROLE my_agent SET search_path TO mcp_schema;
```
You set these role configurations on the individual roles as configurations are not inherited.
Recommended. Restrict the role to user objects only so that the query tool cannot read system catalog objects. You must run the following as a superuser:
```
ALTER ROLE my_agent SET restrict_to_user_objects = true;
```

Create a role for your specific AI agent (the Emulator does not support the LOGIN PASSWORD option):
```
CREATE ROLE my_agent;
```
Grant mcp_agent role to your agent:
```
GRANT mcp_agent TO my_agent;
```
Set the default cluster and schema for my_agent to mcp_cluster and mcp_schema:
```
ALTER ROLE my_agent SET cluster TO mcp_cluster;
ALTER ROLE my_agent SET search_path TO mcp_schema;
```
You set these role configurations on the individual roles as configurations are not inherited.
Recommended. Restrict the role to user objects only so that the query tool cannot read system catalog objects. You must run the following as a superuser:
```
ALTER ROLE my_agent SET restrict_to_user_objects = true;
```

Step 2. Get connection details

When connecting to the MCP server, the MCP-compatible client needs:

The Base64-encoded user:password credentials (i.e., the MCP token) of your agent.
The materialize-agent MCP server URL: <baseURL>/api/mcp/agent.

Log in to the Materialize Console.
Go to App Passwords and for the service account created my_agent, click Connect.
Click on the MCP Server tab.
In the Get your MCP token section¹,
- If using my_agent, use the MCP Token that was returned when you created the service account. You can skip to the next step.
- Otherwise, you can:
  - Create a different service account and use the generated MCP token; or
  - Use an existing service account, Base64 encoding the role:password to generate the MCP token. Ensure the existing account does not have more privileges than necessary.
In the Connect your client section, click on the Agent tab.

You can find your materialize-agent MCP server URL <baseURL>/api/mcp/agent as part of the code block.

If using Claude Code as your MCP-compatible client, you can copy the code block wholesale for the next step.

Avoid using a personal app account instead of a service account as a personal app account would include all your roles and privileges as well. ↩︎

Encode your agent role’s credentials <role>:<password> in Base64 to create the MCP token, replacing <your_app_password> with the actual password:
```
printf 'my_agent:<your_app_password>' | base64
```
Find your deployment’s host name to determine your materialize-agent MCP URL:
```
http://<host>:6876/api/mcp/agent
```
- For your Self-Managed Materialize deployment in AWS/GCP/Azure, the <host> is the load balancer address. If deployed via Terraform, run the Terraform output command for your cloud provider:
```
# AWS
terraform output -raw nlb_dns_name

# GCP
terraform output -raw balancerd_load_balancer_ip

# Azure
terraform output -raw balancerd_load_balancer_ip
```
- For local kind clusters, use port forwarding and use localhost for <host>:
```
kubectl port-forward svc/<instance-name>-balancerd 6876:6876 -n materialize-environment
```

Encode your agent role’s credentials <role>:<password> in Base64 to create the MCP token (the Emulator does not support passwords):
```
printf 'my_agent:' | base64
```
For the Emulator, you will use http://localhost:6876 as the <baseURL> portion of the MCP URL:
```
<baseURL>/api/mcp/agent
```

Step 3. Configure your MCP client

WARNING! When saving your credentials or other sensitive information in a config file, do not commit these files to version control or share them publicly.

Add the materialize-agent MCP server as local-scoped server (i.e., the configurations are stored in ~/.claude.json):

claude mcp add --transport http "materialize-agent" \
  "<baseURL>/api/mcp/agent" \
  --header "Authorization: Basic <mcp-token>"

Update the <baseURL> and <mcp-token> placeholders with your values:

Deployment	`<baseURL>`	`<mcp-token>`
Cloud	Replace with your value	Replace with your value
Self-Managed	Replace with your value	Replace with your value
Emulator	`http://localhost:6876`	Replace with your value

Restart Claude Code to pick up the new setting.

Add the materialize-agent MCP server entry to your Claude Desktop configuration (claude_desktop_config.json).

When merging into an existing mcpServers object, remember to add commas between entries.
If the mcpServers field does not already exist, add it as well.
For older Claude Desktop versions, you may need to include the transport "type": "http", as well as part of the materialize-agent entry.

{
  "mcpServers": {
    "materialize-agent": {
      "url": "<baseURL>/api/mcp/agent",
      "headers": {
        "Authorization": "Basic <mcp-token>"
      }
    }
  }
}

Update the <baseURL> and <mcp-token> placeholders with your values:

Deployment	`<baseURL>`	`<mcp-token>`
Cloud	Replace with your value	Replace with your value
Self-Managed	Replace with your value	Replace with your value
Emulator	`http://localhost:6876`	Replace with your value

Restart Claude Desktop to pick up the new setting.

Add the materialize-agent MCP server entry to your local MCP settings file (~/.cursor/mcp.json).

When merging into an existing mcpServers object, remember to add commas between entries.
If the mcpServers field does not already exist, add it as well.

{
  "mcpServers": {
    "materialize-agent": {
      "url": "<baseURL>/api/mcp/agent",
      "headers": {
        "Authorization": "Basic <mcp-token>"
      }
    }
  }
}

Update the <baseURL> and <mcp-token> placeholders with your values:

Deployment	`<baseURL>`	`<mcp-token>`
Cloud	Replace with your value	Replace with your value
Self-Managed	Replace with your value	Replace with your value
Emulator	`http://localhost:6876`	Replace with your value

Restart Cursor to pick up the new setting.

Any MCP-compatible client can connect by sending JSON-RPC 2.0 requests; update the <baseURL> and <mcp-token> placeholders with your values:

curl -X POST <baseURL>/api/mcp/agent \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic <mcp-token>" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/list"
  }'

Start querying

WARNING!

By default, the query tool is enabled. This tool allows arbitrary SELECT queries (including joins) on all objects for which the agent has the appropriate privileges (SELECT on the object, USAGE on the object’s schema).

To disable it, set enable_mcp_agent_query_tool to false. See Agent endpoint configuration.

💡 Tip: Because the query tool can join across objects, consider maintaining an ontology table: a curated catalog of the join relationships in your schema that the agent can query to confirm exact join keys before writing multi-table SQL.

Once connected to the MCP server, you can query your curated data products using either natural language or SQL:

Via materialize-agent: What data products can I query?
SELECT * FROM mcp_product_performance LIMIT 5;
What’s the total_revenue for product 42?
Perform a Pareto analysis on my products.

MCP Server for Agents

Overview

Connection methods

Set up the agent query environment and data products

Create an agent query environment

Define data products and grant access

Define data products

Grant access

Connect to the MCP server

Method 1: OAuth

Step 1. Get your MCP server URL

Step 2. Configure your MCP client

Method 2: Token-based authentication

Step 1. Create the specific agent role

Step 2. Get connection details

Step 3. Configure your MCP client

Start querying

Related pages