# Redshift Integration

This page provides an overview of the Redshift integration in Immuta. For a tutorial detailing how to enable this integration, see the [installation guide](/2024.2/data-and-integrations/redshift/how-to-guides/redshift.md).

## Overview

Redshift is a policy push integration that allows Immuta to apply policies directly in Redshift. This allows data analysts to query Redshift views directly instead of going through a proxy and have per-user policies dynamically applied at query time.

## Architecture

The Redshift integration will create views from the tables within the database specified when configured. Then, the user can choose the name for the schema where all the Immuta generated views will reside. Immuta will also create the schemas `immuta_system`, `immuta_functions`, and `immuta_procedures` to contain the tables, views, UDFs, and stored procedures that support the integration. Immuta then creates a system role and gives that system account the following privileges:

* `ALL PRIVILEGES ON DATABASE IMMUTA_DB`
* `ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE IMMUTA_DB`
* `USAGE ON FUTURE PROCEDURES IN SCHEMA IMMUTA_DB.IMMUTA_PROCEDURES`
* `USAGE ON LANGUAGE PLPYTHONU`

Additionally the `PUBLIC` role will be granted the following privileges:

* `USAGE ON DATABASE IMMUTA_DB`
* `TEMP ON DATABASE IMMUTA_DB`
* `USAGE ON SCHEMA IMMUTA_DB.IMMUTA_PROCEDURES`
* `USAGE ON SCHEMA IMMUTA_DB.IMMUTA_FUNCTIONS`
* `USAGE ON FUTURE FUNCTIONS IN SCHEMA IMMUTA_DB.IMMUTA_FUNCTIONS`
* `USAGE ON SCHEMA IMMUTA_DB.IMMUTA_SYSTEM`
* `SELECT ON TABLES TO public`

### Integration Type

Immuta supports the Redshift integration as both multi-database and single-database integrations. In either integration type, Immuta supports a single integration with secure views in a single database per cluster.

#### Multi-Database Integration

If using a multi-database integration, you must use a Redshift cluster with an RA3 node because Immuta requires cross-database views.

#### Single-Database Integration

If using a single-database integration, all Redshift cluster types are supported. However, because cross-database queries are not supported in any types other than RA3, Immuta's views must exist in the same database as the raw tables. Consequently, the steps for configuring the integration for Redshift clusters with external tables differ slightly from those that don't have external tables. Allow Immuta to create secure views of your external tables through one of these methods:

* [configure the integration with an existing database that contains the external tables](/2024.2/data-and-integrations/redshift/how-to-guides/redshift-spectrum.md#use-an-existing-database): Instead of creating an `immuta` database that manages all schemas and views created when Redshift data is registered in Immuta, the integration adds the Immuta-managed schemas and views to an existing database in Redshift.
* [configure the integration by creating a new `immuta` database](/2024.2/data-and-integrations/redshift/how-to-guides/redshift-spectrum.md#create-a-new-immuta-database) and re-create all of your external tables in that database.

## Policy Enforcement

SQL statements are used to create all views, including a join to the secure view: `immuta_system.user_profile`. This secure view is a select from the `immuta_system.profile` table (which contains all Immuta users and their current groups, attributes, projects, and a list of valid tables they have access to) with a constraint `immuta__userid = current_user()` to ensure it only contains the profile row for the current user. The `immuta_system.user_profile` view is readable by all users, but will only display the data that corresponds to the user executing the query.

The Redshift integration uses webhooks to keep views up-to-date with Immuta data sources. When a data source or policy is created, updated, or disabled, a webhook will be called that will create, modify, or delete the dynamic view. The `immuta_system.profile` table is updated through webhooks when a user's groups or attributes change, they switch projects, they acknowledge a purpose, or when their data source access is approved or revoked. The profile table can only be read and updated by the Immuta system account.

## Data Flow

1. An Immuta Application Administrator [configures the Redshift integration](/2024.2/data-and-integrations/redshift/how-to-guides/redshift.md) and registers Redshift warehouse and databases with Immuta.
2. Immuta creates a database inside the configured Redshift ecosystem that contains Immuta policy definitions and user entitlements.
3. A Data Owner registers Redshift tables in Immuta as [data sources](/2024.2/data-and-integrations/registering-metadata/register-data-sources/query-backed-tutorial.md).
4. A Data Owner, Data Governor, or Administrator [creates or changes a policy](/2024.2/secure-your-data/authoring-policies-in-secure.md) or user in Immuta.
5. Data source metadata, tags, user metadata, and policy definitions are stored in Immuta's Metadata Database.
6. The Immuta Web Service calls a stored procedure that modifies the user entitlements or policies.
7. A Redshift user who is subscribed to the data source in Immuta [queries the corresponding table](/2024.2/secure-your-data/data-consumers/query-data/redshift.md) directly in Redshift through the immuta database and sees policy-enforced data.

<figure><img src="/files/fLZPy2djHZmXxpDcPjpi" alt=""><figcaption></figcaption></figure>

## Redshift Spectrum

Redshift Spectrum ([Redshift external tables](https://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum.html)) allows Redshift users to query external data directly from files on Amazon S3. Because cross-database queries are not supported in Redshift Spectrum, Immuta's views must exist in the same database as the raw tables. Consequently, the steps for configuring the integration for Redshift clusters with external tables differ slightly from those that don't have external tables. Allow Immuta to create secure views of your external tables through one of these methods:

* [configure the integration with an existing database that contains the external tables](/2024.2/data-and-integrations/redshift/how-to-guides/redshift-spectrum.md#use-an-existing-database): Instead of creating an `immuta` database that manages all schemas and views created when Redshift data is registered in Immuta, the integration adds the Immuta-managed schemas and views to an existing database in Redshift
* [configure the integration by creating a new `immuta` database](/2024.2/data-and-integrations/redshift/how-to-guides/redshift-spectrum.md#create-a-new-immuta-database) and re-create all of your external tables in that database.

Once the integration is configured, Data Owners must [register Redshift Spectrum data sources using the Immuta CLI or V2 API](/2024.2/developer-guides/api-intro/immuta-v2-api/request-payload-examples.md#redshift-spectrum-data-sources).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://documentation.immuta.com/2024.2/data-and-integrations/redshift/reference-guides/redshift.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.