> ## Documentation Index
> Fetch the complete documentation index at: https://backstage.spotify.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Integration: Redshift

> Configure Redshift as a Data Experience source — set up IAM authentication and ingest metadata from your clusters.

### IAM Policy Requirements

Authentication with Redshift is handled with IAM User credentials. You can configure these credentials to allow access to all of your Redshift clusters or a subset of clusters.

<Tabs>
  <Tab title="Allow access to all clusters">
    To ingest *all* of your AWS account's Redshift clusters and databases into the data registry, the following IAM Policy can be used which grants the required actions on all resources.

    ```json theme={"theme":{"light":"github-light","dark":"dracula"}}
    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "VisualEditor0",
          "Effect": "Allow",
          "Action": [
            "redshift:DescribeClusters",
            "redshift:GetClusterCredentialsWithIAM",
            "redshift-data:ListSchemas",
            "redshift-data:ListDatabases",
            "redshift-data:ExecuteStatement",
            "redshift-data:DescribeStatement",
            "redshift-data:GetStatementResult"
          ],
          "Resource": "*"
        }
      ]
    }
    ```

    Taking advantage of this option simplifies configuration as you will only need to configure the [AWS Account ID](https://docs.aws.amazon.com/IAM/latest/UserGuide/console-account-alias.html) and the regions your clusters are in, and the integration will discover the rest. Use the 'option 1' array under sources:

    <Frame>
      <img src="https://mintcdn.com/spotify-89f50c35/30KNAI5WzZFwyWnx/portal/core-features-and-plugins/data-experience/assets/data-exp-redshift-source.jpg?fit=max&auto=format&n=30KNAI5WzZFwyWnx&q=85&s=9daee6ae1be1d6c9799743bc15f5c295" alt="Redshift Source All Clusters" width="1869" height="790" data-path="portal/core-features-and-plugins/data-experience/assets/data-exp-redshift-source.jpg" />
    </Frame>
  </Tab>

  <Tab title="Limit access to specific clusters">
    If you wish to limit the clusters which the data registry has access to, you will need to specify multiple policy statements. For example, the following policy will grant access to only `my-cluster` in the `us-east-1` region along with all of its databases:

    ```json theme={"theme":{"light":"github-light","dark":"dracula"}}
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "VisualEditor0",
                "Effect": "Allow",
                "Action": [
                    "redshift:DescribeClusters",
                    "redshift-data:ListSchemas",
                    "redshift-data:ListDatabases",
                    "redshift-data:ExecuteStatement"
                ],
                "Resource": "arn:aws:redshift:us-east-1:my-account-id:cluster:my-cluster"
            },
            {
                "Sid": "VisualEditor1",
                "Effect": "Allow",
                "Action": [
                    "redshift:GetClusterCredentialsWithIAM"
                ],
                "Resource": "arn:aws:redshift:us-east-1:my-account-id:dbname:my-cluster/*"
            },
            {
              "Sid": "VisualEditor2",
              "Effect": "Allow",
              "Action": [
                  "redshift-data:DescribeStatement",
                  "redshift-data:GetStatementResult"
              ],
              "Resource": "*",
              "Condition": {
                  "StringEquals": {
                      "redshift-data:statement-owner-iam-userid": "${aws:userid}"
                  }
              }
          }
        ]
    }
    ```

    Taking advantage of this option means you will need to configure each cluster in Config Manager manually by specifying the [AWS Account ID](https://docs.aws.amazon.com/IAM/latest/UserGuide/console-account-alias.html) along with a list of cluster identifier/region pairs. Use the 'option 2' array under sources:

    <Frame>
      <img src="https://mintcdn.com/spotify-89f50c35/30KNAI5WzZFwyWnx/portal/core-features-and-plugins/data-experience/assets/data-exp-redshift-source-limited-clusters.jpg?fit=max&auto=format&n=30KNAI5WzZFwyWnx&q=85&s=21a75b1f71ce8f63347babe59bf70a15" alt="Redshift Source Limited Clusters" width="1468" height="926" data-path="portal/core-features-and-plugins/data-experience/assets/data-exp-redshift-source-limited-clusters.jpg" />
    </Frame>
  </Tab>
</Tabs>

#### Privileges for IAM user

By default in redshift, information\_schema (and other cluster-generated tables and views the integration reads from) only shows objects/rows that the connected user has privileges to see. [Additionally, only superusers and the creators of objects can query objects](https://docs.aws.amazon.com/redshift/latest/dg/r_Privileges.html). Because of this, if there are tables or views created by a user other than the IAM service account that you want ingested, you can grant permissions to ensure their metadata will be pulled in.

The following commands can be executed programmatically, or in the redshift query editor as a superuser. Ensure you use double quotes around the verbatim username associated with the IAM account you [configured](#iam-policy-requirements) for data registry.

```sql theme={"theme":{"light":"github-light","dark":"dracula"}}
-- these two commands ensure for a given schema 'schema_name', all tables are ingested into data registry
GRANT USAGE ON SCHEMA schema_name TO "IAM:DataExperienceRedshiftUser";

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO "IAM:DataExperienceRedshiftUser";

-- this command will ensure any new tables created in the schema moving forward are accessible / ingestable into data registry
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
GRANT SELECT ON TABLES TO "IAM:DataExperienceRedshiftUser";

-- these commands can help confirm/show if your IAM user has access to schemas or tables
SHOW GRANTS ON SCHEMA schema_name;
SELECT HAS_TABLE_PRIVILEGE('IAM:DataExperienceRedshiftUser', 'schema_name.table-name', 'select');

```

### Configuration

Each source in the Redshift integration requires an [accountId](https://docs.aws.amazon.com/IAM/latest/UserGuide/console-account-alias.html); make sure to set up [authentication](#authentication) for each account. Each region in the associated config must be [enabled for the account](https://docs.aws.amazon.com/accounts/latest/reference/manage-acct-regions.html).

While Redshift doesn't natively support table or column descriptions, some organizations use the [COMMENT sql operation](https://docs.aws.amazon.com/redshift/latest/dg/r_COMMENT.html) to store relevant information. The Redshift module can optionally be configured to ingest these comments as descriptions, by setting the `pullRedshiftComments` config value to true. The setting is optional, and defaults to false.

### Authentication

The Redshift integration uses the `@backstage/integration-aws-node` package to create a credential provider which is then passed into
the AWS client SDKs. Since it is handled by a separate plugin, the set up for the authentication for it is found under the `App`
config as seen below:

<Frame>
  <img
    src="https://mintcdn.com/spotify-89f50c35/30KNAI5WzZFwyWnx/portal/core-features-and-plugins/data-experience/assets/data-exp-aws-auth.jpg?fit=max&auto=format&n=30KNAI5WzZFwyWnx&q=85&s=622012d539d878c029154aac4eb8b713"
    alt="Redshift
Auth"
    width="1198"
    height="1078"
    data-path="portal/core-features-and-plugins/data-experience/assets/data-exp-aws-auth.jpg"
  />
</Frame>

<Info>
  Every accountID included in the data registry config must have an associated
  auth config like above.
</Info>

### Naming

The naming structure for Datasets created from Redshift is as follows: `[database].[schema].[table]`.

### Tags & Labels

[Tags](https://docs.aws.amazon.com/redshift/latest/APIReference/API_TaggedResource.html) for redshift resources are not currently supported
for the Redshift connector since they cannot be applied to resources at the database granularity or below.

[//]: # "Commented out until on data entity page"

[//]: # "## Soundcheck
\[Soundcheck](https://backstage.spotify.com/marketplace/spotify/plugin/soundcheck/) is a plugin included in Portal that helps teams
ensure quality, reliability, and alignment of software developed with codified checks and tracks.
The Data Experience enables you to use Soundcheck with your dataset entities. For more information,
read the [Soundcheck documentation](/portal/core-features-and-plugins/soundcheck)."

[//]: # "## Recommended Check Filters
A [Dataset](./key-concepts#dataset-api) in the Data Experience plugin is defined as an entity with a kind of `API` and a type of `dataset`.
When creating Checks in Soundcheck targeting a Dataset, it is recommended to filter for entities of kind `API` and type `dataset`."

### Troubleshooting

* **The redshift ingestion is failing to ingest anything?** Double check the [IAM user has the correct permissions](#iam-policy-requirements), the [client secret and token placed in the aws config](#authentication) belong to the IAM user, and the [account ID used in the data registry config](#configuration) matches the one in the [aws config](#authentication).
* **Some redshift tables are missing from data registry?** Verify the IAM user has *privileges* on all the schemas and tables/views you want represented in data registry. Use sql statements [here](#privileges-for-iam-user) to check.