> For the complete documentation index, see [llms.txt](https://docs.p0.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.p0.dev/agentic-access/agentic-gateway/mcp-server/redshift.md).

# Amazon Redshift MCP server

Install the Redshift (AI) integration so agents behind the P0 AI Gateway can request temporary Redshift roles and object privileges on your provisioned clusters and Serverless workgroups.

The **Redshift (AI)** integration lets agents behind the P0 AI Gateway (see [P0 Runtime Access for AI](https://docs.p0.dev/readme/agentic-control-plane.md)) request temporary access to an Amazon Redshift provisioned cluster or Serverless workgroup. For each approved request, P0 creates an IAM role named `p0-agent-<request ID>` and a database user, `IAMR:p0-agent-<request ID>`, that signs in only through that role. The gateway runs AWS's [Redshift MCP server](https://awslabs.github.io/mcp/servers/redshift-mcp-server) with short-lived AWS keys for the role, so the agent reaches Redshift only as that user. When the grant ends, P0 deletes the IAM role, hands what the agent created to an owner you choose, and drops the user.

Agents can request two kinds of access:

* **Role**: one or more existing Redshift roles.
* **Objects**: `SELECT`, `INSERT`, `UPDATE`, or `DELETE` on a database, a schema, or a table or view.

Only agents can use this integration. P0 refuses a request that doesn't come from an agentic session.

{% hint style="info" %}
The Redshift (AI) integration is in beta. Functionality and configuration may change.
{% endhint %}

Setup has four parts, in this order:

1. Install the connector, a Lambda function in your AWS account.
2. Install each cluster or workgroup, and run the one-time setup SQL in it.
3. Add a Redshift MCP server to your gateway for each cluster or workgroup.
4. Write the policies that decide which agents get which access.

## Prerequisites

* A registered [Gateway](https://docs.p0.dev/agentic-access/agentic-gateway/gateway.md) component, with the gateway deployed in your environment. The gateway runs AWS's Redshift MCP server as a container, so it must be able to pull images from `public.ecr.aws` and reach the Redshift and Redshift Data API endpoints of each cluster's region.
* For each AWS account that holds a cluster or workgroup:
  * P0's AWS **IAM management** installed for the account. See [Setting up AWS IAM management](https://docs.p0.dev/integrations/resource-integrations/aws/installation-methods.md). P0 invokes the connector, and creates each agent's IAM role, through that integration's role.
  * The [AWS OIDC](https://docs.p0.dev/agentic-access/credential-sources/aws-oidc.md) integration installed for the account. Each agent's MCP server signs in to AWS through it.
  * An AWS admin of the account, to apply the connector's and each cluster's Terraform.
* A Redshift superuser for each cluster or workgroup, to run the one-time setup SQL.
* Clusters and workgroups in commercial AWS regions, not in GovCloud or China. The connector runs in the same AWS account as the clusters it reaches.
* Terraform 1.4 or later, with version 6.0 or later of the `hashicorp/aws` provider and version 3.x of the `kreuzwerker/docker` provider.
* `docker` and the AWS CLI wherever you run the connector's Terraform, with the AWS CLI signed in to the connector's AWS account. The Terraform copies P0's connector image into Amazon ECR with them.

## What P0 holds

P0 stores no Redshift password or secret. The connector signs in to each cluster with its own IAM role, and each agent signs in with the IAM role of its grant.

| Identity                                                           | What it can do                                                                                                                                                              | Where                                                 |
| ------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- |
| The connector's IAM role, `p0-connector-redshift-ai-aws`           | Sign in to the cluster, run statements through the Data API, and look the cluster up                                                                                        | Each cluster or workgroup whose Terraform you apply   |
| The connector's database user, `IAMR:p0-connector-redshift-ai-aws` | Create, change, and drop any user or role (`sys:secadmin`), read every row of the system tables, change the owner of any table, and drop any schema, function, or procedure | Each cluster or workgroup where you run the setup SQL |
| P0's AWS IAM management role                                       | Create and delete each agent's IAM role, under the path `/p0/redshift-ai/`                                                                                                  | Each cluster's AWS account                            |
| An agent's IAM role, `p0-agent-<request ID>`                       | Sign in as the agent's database user, run statements through the Data API, and list clusters and workgroups                                                                 | One cluster or workgroup, for the length of the grant |

The connector creates, changes, and drops only users named `IAMR:p0-agent-<id>` and roles named `p0_agent_<id>`. It never grants Redshift's `sys:` roles, its own `p0_connector_admin` role, or another agent's role.

{% hint style="warning" %}
Anyone who can invoke the connector, change it, or use its role can act as its database user: create any user, grant any role, and change the owner of any table. To limit who can, restrict `lambda:InvokeFunction` and IAM access to the connector in its AWS account, for example with a service control policy.
{% endhint %}

## Install the connector

1. Navigate to **Integrations** on [p0.app](https://p0.app), select **Redshift (AI)**, and choose the **Connector** component.
2. Add a connector, choose the AWS account it runs in, and choose its region. P0 lists only AWS accounts where its AWS IAM management is installed.
3. P0 generates a Terraform configuration in two parts: the providers it needs, and the connector itself. Copy both into your Terraform project, apply them as an AWS admin of the account, then click **Next**.

   The configuration creates, in the connector's AWS account and region:

   * A Lambda function, `p0-connector-redshift-ai-aws`, and its IAM role of the same name. The role gets only its log permissions here. Each cluster's Terraform lets it into that cluster.
   * An ECR repository, `p0-connector-redshift-ai-aws`, with immutable tags and scan on push, and a copy of P0's connector image in it.
   * A CloudWatch log group, `/aws/lambda/p0-connector-redshift-ai-aws`, with 30-day retention.
   * Permission for P0's AWS IAM management role to invoke the connector.
   * Permission for P0's AWS IAM management role to create, change, and delete IAM roles under the path `/p0/redshift-ai/`, one for each agent's grant.
4. P0 checks that it can invoke the connector. A new Lambda can take a few seconds to deploy. If the check fails, wait a minute and click **Next** again.
5. Review the summary and click **Finish**.

One connector reaches every cluster and workgroup in its AWS account, in any region.

## Install a cluster or workgroup

1. On the **Redshift (AI)** integration, choose the **Cluster** component and add a cluster. Enter its key as `<AWS account ID>/<region>/<name>`, such as `123456789012/us-west-2/analytics`. The name is the provisioned cluster's identifier or the Serverless workgroup's name.
2. Choose the connector in the cluster's AWS account, and choose the **Deployment type**: **Provisioned cluster** or **Serverless workgroup**. Click **Next**.
3. P0 checks that the connector is in the cluster's AWS account, and that P0's AWS IAM management and AWS OIDC are installed for it.
4. P0 shows the cluster's setup in two tabs:

   * **Terraform**: the cluster's Terraform, then the setup SQL.
   * **Shell**: the setup SQL alone, for when you apply the cluster's Terraform another way.

   Add the cluster's Terraform to the configuration that holds the connector, and apply it. It adds one inline policy to the connector's role, named `p0-redshift-ai-<region>-<name>`, that lets it sign in to this cluster, run statements through the Data API, and look the cluster up. The connector's own Terraform doesn't change when you add a cluster.
5. **Run the setup SQL** as a superuser, in the Redshift query editor or any SQL client, connected to the database you enter in the next step:

   ```sql
   CREATE USER "IAMR:p0-connector-redshift-ai-aws" PASSWORD DISABLE;
   GRANT ROLE "sys:secadmin" TO "IAMR:p0-connector-redshift-ai-aws";
   CREATE ROLE p0_connector_admin;
   GRANT ACCESS SYSTEM TABLE, ALTER TABLE, DROP SCHEMA, DROP FUNCTION, DROP PROCEDURE TO ROLE p0_connector_admin;
   GRANT ROLE p0_connector_admin TO "IAMR:p0-connector-redshift-ai-aws";
   ```

   It creates the connector's database user, which signs in only through the connector's IAM role, and gives it what it needs to manage agents' users and roles, to find what each agent was granted, and to hand over or drop what an agent created once its access ends. Click **Next**.
6. Fill in the cluster details, then click **Next**:
   * **Database**: the database the connector connects to, such as `dev`. The connector finds the cluster's other databases from it.
   * **Object owner** (optional): the database user that takes over each table and view an agent created once its access ends, such as `etl_owner` or `IAMR:etl-role`. Leave it empty to have the connector's own user take them over. You can change it after install.
7. P0 checks the cluster, in this order, and stops at the first check that fails, with a message that says what to change:

   * The connector can find the cluster or workgroup.
   * The connector can sign in through the Data API.
   * The connector's user holds `sys:secadmin` and the system permissions the setup SQL grants, or is a superuser.
   * The object owner, when you set one, is an existing user.

   Two more checks only warn. The install still finishes, and the cluster shows the warning:

   * The connector's user can grant nothing in a database. See [#let-p0-grant-your-objects](#let-p0-grant-your-objects "mention").
   * `PUBLIC` can create tables in a database's `public` schema. See [#stop-agents-creating-tables-in-public](#stop-agents-creating-tables-in-public "mention").
8. Review the summary and click **Finish**. The cluster now appears with the state **Installed**.

P0 repeats these checks every hour.

### Let P0 grant your objects

The connector can grant a privilege on a table or view only if its user owns it, or holds that privilege `WITH GRANT OPTION`. Agents can request only what the connector can grant: P0 lists nothing else, and refuses anything else.

For each schema agents may use, give the connector's user `USAGE` and the privileges agents may request, with `WITH GRANT OPTION`. Run this in the schema's database, as the owner of its objects or as a superuser. This example uses a schema named `reporting`:

```sql
GRANT USAGE ON SCHEMA reporting TO "IAMR:p0-connector-redshift-ai-aws" WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA reporting TO "IAMR:p0-connector-redshift-ai-aws" WITH GRANT OPTION;
```

`ALL TABLES` covers only the tables and views that exist now. To cover tables created later, also run `ALTER DEFAULT PRIVILEGES` as each user who creates them:

```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "IAMR:p0-connector-redshift-ai-aws" WITH GRANT OPTION;
```

The **Role** access type needs none of this. The connector's user can grant any role because it holds `sys:secadmin`.

### Stop agents creating tables in public

By default, every Redshift user can create tables in the `public` schema of every database. An agent's user is no exception, so an agent can create tables there with no grant. On a Serverless workgroup, IAM can't limit which databases an agent signs in to, so this applies to every database in the workgroup.

To stop it, run this in each database:

```sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```

Then grant `CREATE` on `public` only to the users and roles that need it. P0 warns about each database where `PUBLIC` still has `CREATE` on `public`.

### Install with Terraform

{% hint style="info" %}
The `p0_redshift_ai_*` resources require P0 Terraform provider version 0.87.0 or later.
{% endhint %}

Instead of the installer, you can install the connector and each cluster with the P0 Terraform provider (see [Manage integrations as code with the P0 Terraform provider](https://docs.p0.dev/getting-started/get-started-with-the-p0-terraform-provider.md)). Create the resources in this order:

1. **Stage the connector** with [`p0_redshift_ai_connector_staged`](https://registry.terraform.io/providers/p0-security/p0/latest/docs/resources/redshift_ai_connector_staged). Set `id` to the AWS account the connector runs in, and `region` to its region.
2. **Deploy the connector** in that account and region. Let P0's AWS IAM management role invoke it, and create and delete IAM roles under the path `/p0/redshift-ai/`.
3. **Install the connector** with [`p0_redshift_ai_connector`](https://registry.terraform.io/providers/p0-security/p0/latest/docs/resources/redshift_ai_connector), with the same `id` and `region`. P0 checks that it can invoke the connector.
4. **Stage each cluster** with [`p0_redshift_ai_cluster_staged`](https://registry.terraform.io/providers/p0-security/p0/latest/docs/resources/redshift_ai_cluster_staged). Set `id` to the cluster's key, `connector` to the connector's `id`, and `type` to `provisioned` or `serverless`.
5. **Let the connector's role into the cluster** with an inline policy, and run the setup SQL.
6. **Install the cluster** with [`p0_redshift_ai_cluster`](https://registry.terraform.io/providers/p0-security/p0/latest/docs/resources/redshift_ai_cluster). Set the staged cluster's `id`, `connector`, and `type`, the `database`, and optionally an `object_owner`. P0 runs the checks in [#install-a-cluster-or-workgroup](#install-a-cluster-or-workgroup "mention"). Changing `database` or `object_owner` later updates the cluster in place, and P0 checks it again.

The provider's example for `p0_redshift_ai_connector` does steps 1 to 3 in one configuration. Set `account_id` and `region` in its `locals`:

```terraform
# Installs the Redshift (AI) connector: one Lambda per AWS account, outside any
# VPC, that P0 invokes to give AI agents access to the Redshift clusters and
# Serverless workgroups in that account. It holds no secret: it signs in to
# each cluster with its IAM role. Apply it as an AWS admin of the connector's
# account.
#
# Before you apply this, install P0's AWS IAM management for the connector's
# AWS account (see p0_aws_iam_write). P0 invokes the connector as that
# installation's role. Once the connector is installed, add clusters (see
# p0_redshift_ai_cluster).

terraform {
  # 1.4 added terraform_data.
  required_version = ">= 1.4"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = ">= 6.0"
    }
    docker = {
      source  = "kreuzwerker/docker"
      version = "~> 3.0"
    }
    # 0.87.0 added the p0_redshift_ai resources.
    p0 = {
      source  = "p0-security/p0"
      version = ">= 0.87.0"
    }
  }
}

locals {
  account_id = "123456789012"
  region     = "us-east-1"

  # The connector's name. P0 invokes it by this name, in the account and region
  # above, so keep it as it is. The connector's Lambda function, role, log group
  # and ECR repository all take it.
  connector_name = "p0-connector-redshift-ai-aws"

  # How many days CloudWatch keeps the connector's logs.
  log_retention_days = 30

  # The role that P0's AWS IAM management assumes in this account, which
  # invokes the connector.
  p0_role_name = "P0RoleIamManager"

  # The connector image's version: the sha- tag P0 publishes each release
  # under, and the digest that tag must resolve to. Docker Hub lets a tag be
  # pushed again, so the plan fails if the tag no longer resolves to this
  # digest. To update, take both from the latest version of this example in
  # the Terraform registry: they change together. Changing them copies the
  # image again and redeploys the Lambda.
  connector_image_name   = "p0security/p0-connector-redshift-ai-aws"
  connector_image_tag    = "sha-fc85477"
  connector_image_digest = "sha256:b5e6942eced487dfda13d4f59e8e15c32c73acdbc214c2148a02652fb4d15d1f"

  tags = {
    ManagedBy  = "Terraform"
    ManagedFor = "P0"
  }
}

provider "aws" {
  region              = local.region
  allowed_account_ids = [local.account_id]
}

# Staging records the connector in P0 before the Lambda is deployed.
resource "p0_redshift_ai_connector_staged" "example" {
  id     = local.account_id
  region = local.region
}

# The connector's execution role. Redshift names the database user it signs in
# as IAMR:p0-connector-redshift-ai-aws, which each cluster's setup SQL creates.
# Each cluster's Terraform lets this role into that cluster (see the
# p0_redshift_ai_cluster example).
resource "aws_iam_role" "connector" {
  name = local.connector_name
  tags = local.tags
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect    = "Allow"
      Principal = { Service = "lambda.amazonaws.com" }
      Action    = "sts:AssumeRole"
    }]
  })
}

# The log group Lambda writes the connector's logs to.
resource "aws_cloudwatch_log_group" "connector" {
  name              = "/aws/lambda/${local.connector_name}"
  retention_in_days = local.log_retention_days
  tags              = local.tags
}

# The connector writes its own logs, and no others.
resource "aws_iam_role_policy" "connector_logs" {
  name = "${local.connector_name}-logs"
  role = aws_iam_role.connector.id
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect   = "Allow"
      Action   = ["logs:CreateLogStream", "logs:PutLogEvents"]
      Resource = "${aws_cloudwatch_log_group.connector.arn}:*"
    }]
  })
}

data "docker_registry_image" "connector" {
  name = "${local.connector_image_name}:${local.connector_image_tag}"

  lifecycle {
    postcondition {
      condition     = self.sha256_digest == local.connector_image_digest
      error_message = "The connector image's tag doesn't resolve to the digest pinned here. Take both from the latest p0_redshift_ai_connector example in P0's Terraform registry docs, or contact support@p0.dev."
    }
  }
}

# Lambda only runs images from Amazon ECR, so this copies P0's image into a
# repository in this account. It needs docker and the AWS CLI, signed in to this
# account, wherever Terraform runs.
resource "aws_ecr_repository" "connector" {
  name = local.connector_name
  # Tags can't be overwritten, so no one who can push to this repository can
  # replace the image that administers Redshift as the execution role.
  image_tag_mutability = "IMMUTABLE"
  force_delete         = true
  tags                 = local.tags

  image_scanning_configuration {
    scan_on_push = true
  }
}

# Copies the pinned image again whenever its tag or digest changes, tagged
# with the image's tag and this copy's id. ECR's tags can't be overwritten, so
# each copy needs a tag of its own: copying the image again, to roll back or
# after an interrupted apply, pushes a new one.
resource "terraform_data" "connector_image" {
  triggers_replace = {
    digest         = data.docker_registry_image.connector.sha256_digest
    repository_url = aws_ecr_repository.connector.repository_url
    tag            = local.connector_image_tag
  }

  provisioner "local-exec" {
    # Signs Docker out of ECR at the end, so its login doesn't outlast the copy.
    # A failed sign-out doesn't fail the copy once the image is pushed.
    command = <<-EOT
      set -e
      trap 'docker logout "$REGISTRY" || true' EXIT
      aws ecr get-login-password --region "$REGION" |
        docker login --username AWS --password-stdin "$REGISTRY"
      docker pull --platform linux/amd64 "$SOURCE"
      docker tag "$SOURCE" "$TARGET"
      docker push "$TARGET"
    EOT

    environment = {
      REGION   = local.region
      REGISTRY = split("/", aws_ecr_repository.connector.repository_url)[0]
      SOURCE   = "${local.connector_image_name}@${local.connector_image_digest}"
      TARGET   = "${aws_ecr_repository.connector.repository_url}:${local.connector_image_tag}-${self.id}"
    }
  }
}

# Pulling one platform and pushing it can give ECR a different manifest from
# the one the tag names on Docker Hub, so the digest there can differ from the
# one pinned above. Lambda needs ECR's.
data "aws_ecr_image" "connector" {
  repository_name = aws_ecr_repository.connector.name
  image_tag       = "${local.connector_image_tag}-${terraform_data.connector_image.id}"
}

resource "aws_lambda_function" "connector" {
  # P0 invokes the connector by its name in this account and region, so it must
  # be deployed exactly there. If your other AWS providers have credential
  # settings, such as profile or assume_role, add them to the provider above.
  function_name = local.connector_name
  role          = aws_iam_role.connector.arn
  package_type  = "Image"
  image_uri     = "${aws_ecr_repository.connector.repository_url}@${data.aws_ecr_image.connector.image_digest}"
  architectures = ["x86_64"]
  # A revoke reads and changes each database of the cluster in turn. P0 stops
  # waiting for the connector after 5 minutes, so the timeout stays under that.
  timeout = 270
  tags    = local.tags

  # The connector is staged in P0 before it's deployed. Its role can't create
  # a log group, so its own must exist before it runs.
  depends_on = [
    p0_redshift_ai_connector_staged.example,
    aws_cloudwatch_log_group.connector,
  ]
}

# Lets P0 invoke the connector, through the role P0's AWS IAM management
# assumes in this account.
resource "aws_iam_role_policy" "p0_invoke_connector" {
  name = "${local.connector_name}-invoke"
  role = local.p0_role_name
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect   = "Allow"
      Action   = "lambda:InvokeFunction"
      Resource = aws_lambda_function.connector.arn
    }]
  })
}

# Lets P0 make and remove each grant's own IAM role, on the /p0/redshift-ai/
# path only, through the same role.
resource "aws_iam_role_policy" "p0_manage_agent_roles" {
  name = "${local.connector_name}-agent-roles"
  role = local.p0_role_name
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect = "Allow"
      Action = [
        "iam:CreateRole",
        "iam:TagRole",
        "iam:UpdateAssumeRolePolicy",
        "iam:PutRolePolicy",
        "iam:DeleteRolePolicy",
        "iam:DeleteRole",
      ]
      Resource = "arn:aws:iam::${local.account_id}:role/p0/redshift-ai/*"
    }]
  })
}

# Completes the install. P0 checks that it can invoke the connector, retrying
# for about half a minute while a new IAM policy takes effect. If the first
# apply is denied access, apply again.
resource "p0_redshift_ai_connector" "example" {
  id     = p0_redshift_ai_connector_staged.example.id
  region = p0_redshift_ai_connector_staged.example.region

  depends_on = [
    aws_iam_role_policy.p0_invoke_connector,
    aws_iam_role_policy.p0_manage_agent_roles,
  ]
}
```

The provider's example for `p0_redshift_ai_cluster` does steps 4 to 6 for one provisioned cluster. Set the values in its `locals`, and run the setup SQL before you apply it:

```terraform
# Adds one Redshift provisioned cluster to P0. Apply it as an AWS admin of the
# cluster's account, which is also the connector's.
# Full chain: p0_redshift_ai_connector -> p0_redshift_ai_cluster_staged ->
# the connector role's policy for the cluster and the setup SQL ->
# p0_redshift_ai_cluster.
#
# For a Serverless workgroup, set type = "serverless", look the workgroup up
# with the aws_redshiftserverless_workgroup data source, and in the SignIn
# statement allow redshift-serverless:GetCredentials on the workgroup's ARN.
# The Lookup statement then allows redshift-serverless:GetWorkgroup.

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = ">= 6.0"
    }
    # 0.87.0 added the p0_redshift_ai resources.
    p0 = {
      source  = "p0-security/p0"
      version = ">= 0.87.0"
    }
  }
}

locals {
  account_id = "123456789012"
  # The cluster's region, which may differ from the connector's.
  region = "us-west-2"
  # The provisioned cluster's identifier.
  cluster_identifier = "analytics"
  # The database that the connector's admin statements connect to.
  database = "dev"
}

provider "aws" {
  region              = local.region
  allowed_account_ids = [local.account_id]
}

resource "p0_redshift_ai_cluster_staged" "example" {
  id        = "${local.account_id}/${local.region}/${local.cluster_identifier}"
  connector = local.account_id
  type      = "provisioned"
}

data "aws_redshift_cluster" "this" {
  cluster_identifier = local.cluster_identifier
}

# Lets the connector's role into this cluster: sign in to any of its
# databases, since the connector reads and revokes in each one, run statements
# through the Data API, and look the cluster up. One inline policy per
# cluster, so removing it removes the cluster's access, and the connector's
# own Terraform never changes when a cluster is added.
resource "aws_iam_role_policy" "connector_cluster" {
  name = "p0-redshift-ai-${local.region}-${local.cluster_identifier}"
  role = "p0-connector-redshift-ai-aws"
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Sid      = "SignIn"
        Effect   = "Allow"
        Action   = "redshift:GetClusterCredentialsWithIAM"
        Resource = "arn:aws:redshift:${local.region}:${local.account_id}:dbname:${local.cluster_identifier}/*"
      },
      {
        Sid      = "DataApi"
        Effect   = "Allow"
        Action   = ["redshift-data:ExecuteStatement", "redshift-data:BatchExecuteStatement"]
        Resource = data.aws_redshift_cluster.this.arn
      },
      {
        # The Data API checks that a statement is the caller's own.
        Sid      = "Statements"
        Effect   = "Allow"
        Action   = ["redshift-data:DescribeStatement", "redshift-data:GetStatementResult", "redshift-data:CancelStatement"]
        Resource = "*"
      },
      {
        Sid      = "Lookup"
        Effect   = "Allow"
        Action   = "redshift:DescribeClusters"
        Resource = data.aws_redshift_cluster.this.arn
      },
    ]
  })
}

# Before you apply the resource below, have a superuser run this setup SQL in
# the cluster. It creates the connector's database user, lets it manage users
# and roles, and gives it the permissions revoke needs to find an agent's
# grants and to hand over or drop what an agent created:
#
#   CREATE USER "IAMR:p0-connector-redshift-ai-aws" PASSWORD DISABLE;
#   GRANT ROLE "sys:secadmin" TO "IAMR:p0-connector-redshift-ai-aws";
#   CREATE ROLE p0_connector_admin;
#   GRANT ACCESS SYSTEM TABLE, ALTER TABLE, DROP SCHEMA, DROP FUNCTION, DROP PROCEDURE TO ROLE p0_connector_admin;
#   GRANT ROLE p0_connector_admin TO "IAMR:p0-connector-redshift-ai-aws";

# Completes the install. P0 checks that the connector can find the cluster and
# sign in, that its user holds the setup SQL's roles, and that object_owner
# names an existing user.
resource "p0_redshift_ai_cluster" "example" {
  id        = p0_redshift_ai_cluster_staged.example.id
  connector = p0_redshift_ai_cluster_staged.example.connector
  type      = p0_redshift_ai_cluster_staged.example.type
  database  = local.database
  # Takes over each table and view an agent created once its access ends.
  # Leave it out to have the connector's own user take them over.
  object_owner = "etl_owner"

  depends_on = [aws_iam_role_policy.connector_cluster]
}
```

## Add the Redshift MCP server

Each server covers one installed cluster or workgroup. Add one for every cluster or workgroup agents use.

1. Follow the shared steps in [MCP server](https://docs.p0.dev/agentic-access/agentic-gateway/mcp-server.md#add-an-mcp-server) to name the server and choose its gateway. On the configuration screen, set the following fields, then click **Finish**:
   * **Credential source**: choose **AWS IAM federation**, then select the **Federation provider**. This is the [AWS OIDC](https://docs.p0.dev/agentic-access/credential-sources/aws-oidc.md) identity for the cluster's AWS account.
   * **Definition**: choose **P0**, then select **Amazon Redshift (`<name>`)** for the cluster or workgroup this server reaches.
2. The server now appears with the state **Installed**.

To add the server with P0's Terraform provider instead, use the [`p0_ai_gateway_server`](https://registry.terraform.io/providers/p0-security/p0/latest/docs/resources/ai_gateway_server) resource. Set `definition.id` to `redshift:` followed by the cluster's key, such as `redshift:123456789012/us-west-2/analytics`.

The server has these tools:

* `execute_query`: runs a SQL statement as the agent's database user. It can write.
* `list_clusters`, `list_databases`, `list_schemas`, `list_tables`, and `list_columns`: read the cluster's catalog.
* `review_cluster`: reviews the cluster's configuration. It needs the `sys:monitor` role, which agents don't hold unless a role you grant them has it.

### What agents can see and do

{% hint style="warning" %}
Writes run with no confirmation prompt. AWS's server can ask before it runs a write, but the gateway can't relay that question, so P0 turns the prompt off. An agent's grants are the only limit on what `execute_query` can change: request approval, and the roles and privileges you let agents request.
{% endhint %}

An agent's IAM role can list every provisioned cluster and Serverless workgroup in the region of the cluster it was granted: their names, endpoints, and status, and each workgroup's tags. It can sign in to and query only the one cluster or workgroup it was granted.

## How agents request access

An agent requests access with the server's `access` tool, for the cluster or workgroup that server reaches. Every request names how long the access lasts, which can be at most 365 days. P0 refuses a request with no end time or a longer one.

* **Role**: one or more existing roles in the cluster, by name. Redshift's `sys:` roles, `p0_connector_admin`, and agents' own roles can't be requested.
* **Objects**: one or more grants, each written as `<PRIVILEGE>:<OBJECT>`. The object is a database (`sales`), a schema (`sales.public`), or a table or view (`sales.public.orders`). Grants in one request can name different databases. For example, `SELECT:sales.public` and `INSERT:sales.public.orders`.

A grant on a database or schema covers the tables and views that exist in it when access is granted. Tables and views created later aren't covered. P0 adds `USAGE` on each schema a grant covers.

Agents can request only roles and objects that P0 lists. Use the server's `list` tool to see them. An object appears only if the connector can grant it, as in [#let-p0-grant-your-objects](#let-p0-grant-your-objects "mention"). P0 lowercases names, and lists only names made of letters, digits, `_`, and `$`.

Once a request is approved, access usually works within 10 to 30 seconds, and can take up to a minute. The agent's statements run as its own database user, `IAMR:p0-agent-<request ID>`, so Redshift's system tables show them under that name.

When the grant ends, P0 deletes the agent's IAM role, so the agent can't sign in again. Then, in every database of the cluster, the connector:

1. Revokes every role and privilege it granted the agent.
2. Makes the object owner the owner of each table, view, and materialized view the agent created.
3. Drops each function and procedure the agent created, and each schema it created that is then empty. Only a superuser can change their owner.
4. Drops the agent's user and its role, `p0_agent_<request ID>`.

See [Requesting access](https://docs.p0.dev/agentic-access/using-the-gateway/requesting-access.md) for how agents submit and use requests.

## Write policies for Redshift

Match Redshift requests in an agentic access policy (see [Agentic access policies](https://docs.p0.dev/agentic-access/agentic-access-policies.md)) with `service: redshift-ai`, and `accessType: role` or `accessType: objects`. These filters narrow them:

| Filter      | Key     | What it matches                                                                           | Example                            |
| ----------- | ------- | ----------------------------------------------------------------------------------------- | ---------------------------------- |
| `cluster`   | `id`    | The cluster's key                                                                         | `123456789012/us-west-2/analytics` |
| `role`      | `name`  | Each requested role. Role requests only.                                                  | `analyst_read`                     |
| `object`    | `name`  | Each requested object, as `DB`, `DB.SCHEMA`, or `DB.SCHEMA.TABLE`. Objects requests only. | `sales.public`                     |
| `privilege` | `name`  | Each requested privilege. Objects requests only.                                          | `SELECT`                           |
| `grant`     | `entry` | Each whole grant, as `<PRIVILEGE>:<OBJECT>`. Objects requests only.                       | `SELECT:sales.public`              |

Privileges are in uppercase, and role and object names are in lowercase. Patterns are case-sensitive, so write them the same way.

One policy must cover a whole request. For a request with several roles or grants, a single policy must allow every one of them. An agent that needs access allowed by two different policies makes two requests.

### Anchor your patterns

In a pattern, `.` matches any character, so `^sales.public$` also matches a schema named `sales_public`. Write `\.` to match only a dot, as the examples below do.

Start each pattern with `^`, and end it with `$` or `\.`. A pattern without anchors matches more than it looks like it does. `sales` also matches `presales`, and `^sales` also matches `sales_archive`.

* `^sales\.` matches every schema, table, and view in the `sales` database, but not the database itself.
* `^sales(\.|$)` also matches the `sales` database.
* `^sales\.public\.orders$` matches one table.

To match one cluster name in any account or region, anchor the end of the key: `/analytics$`.

### Grant objects

The `object` and `privilege` filters each check every grant on their own. A policy that allows the objects `^(sales|hr)\.` and the privileges `^(SELECT|DELETE)$` also allows `DELETE` on `sales`. To tie a privilege to an object, use the `grant` filter.

For example, auto-approve read-only access to the `sales` database for one agent, on one cluster:

```yaml
- requestor:
    type: agentic
    actor:
      type: agent-client
      clientId: reporting-agent
    subject:
      type: any
  resource:
    type: integration
    service: redshift-ai
    accessType: objects
    filters:
      cluster:
        effect: keep
        key: id
        pattern: ^123456789012/us-west-2/analytics$
      grant:
        effect: keep
        key: entry
        pattern: ^SELECT:sales(\.|$)
  approval:
    - type: persistent
```

### Grant roles

{% hint style="warning" %}
A role can hold more than its name suggests, including other roles and system permissions. A broad role policy can hand out every role in the cluster. Name each role an agent may get, with a pattern anchored at both ends.
{% endhint %}

For example, auto-approve one read-only role for one agent:

```yaml
- requestor:
    type: agentic
    actor:
      type: agent-client
      clientId: reporting-agent
    subject:
      type: any
  resource:
    type: integration
    service: redshift-ai
    accessType: role
    filters:
      cluster:
        effect: keep
        key: id
        pattern: ^123456789012/us-west-2/analytics$
      role:
        effect: keep
        key: name
        pattern: ^analyst_read$
  approval:
    - type: persistent
```

## Leftover users and roles

If a revoke can't finish, the agent's user stays in the cluster. P0 deletes the agent's IAM role first, so the user can't sign in. The request shows the error in P0, which lists what still stops the drop, such as a table the connector can't hand over, or a privilege someone else granted the agent.

To clean up, as a superuser:

1. Hand each table and view that `IAMR:p0-agent-<request ID>` owns to your object owner, with `ALTER TABLE ... OWNER TO`.
2. Revoke every privilege the user and its role `p0_agent_<request ID>` hold.
3. Drop the user and the role.

Delete one only once its request has ended or shows an error in P0. An IAM role named `p0-agent-<request ID>`, under the path `/p0/redshift-ai/`, can also stay in the cluster's AWS account if a request fails after P0 created it. Delete it once its request has ended.

## Remove the integration

1. **Remove the Redshift MCP servers** from your gateway.
2. **End or revoke every active grant**, so that P0 drops each agent's user and deletes its IAM role. Then clean up any leftovers, as in [#leftover-users-and-roles](#leftover-users-and-roles "mention").
3. **Delete each cluster and the connector** from the **Redshift (AI)** integration in P0.
4. **Destroy the Terraform configuration** with `terraform destroy`. This deletes the connector and its access to each cluster.
5. **Drop the connector's database user and role** in each cluster, as a superuser. If you left the object owner empty, the connector's user owns the tables agents created. Hand them to another user first, or `DROP USER` fails.

   ```sql
   DROP USER "IAMR:p0-connector-redshift-ai-aws";
   DROP ROLE p0_connector_admin;
   ```

## Next steps

* Define the [Agentic access policies](https://docs.p0.dev/agentic-access/agentic-access-policies.md) that decide which agents and users can request Redshift access.
