## Set up Amazon Redshift

URL: https://docs.atlan.com/apps/connectors/data-warehouses/amazon-redshift/how-tos/set-up-amazon-redshift

> Grant permissions and configure authentication for Atlan to connect to and crawl metadata from Amazon Redshift.

:::warning Who can do this?
You need your Amazon Redshift administrator to run these commands. You may not have access yourself.
:::

Atlan supports fetching metadata from Amazon Redshift for the following deployment types:

- Provisioned
 - RA3
 - DC2
- Serverless

:::warning
If you're using the DC2 node type, Redshift restricts cross-database joins and metadata access to a single database. For more information, see [Considerations - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/cross-database-use.html). Because of this restriction, you must set up a separate workflow for each database you want to crawl.
:::

Select your deployment type to see the steps that apply to you.

### Provisioned

## Grant permissions

### Create group and user

Run the following commands to create the group and user Atlan uses to connect:

```sql
CREATE GROUP atlan_users;
CREATE USER atlan_user PASSWORD '<pass>' IN GROUP atlan_users;
```

- Replace `<pass>` with the password for `atlan_user`.
- When you [crawl Amazon Redshift](https://docs.atlan.com/llms/connectors/amazon-redshift/crawl-amazon-redshift/llms.txt), enter this username in the **Username** field.

### Grant required permissions to group

Run the following commands to grant the minimum permissions needed for Atlan to crawl metadata:

```sql
GRANT USAGE ON SCHEMA <schema_name> TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.svv_table_info TO GROUP atlan_users;
```

- Replace `<schema_name>` with your schema name.
- Repeat these commands for each database in your schema.

[SVV_TABLE_INFO](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html) is used to retrieve the relationship between table IDs and their schema and database.

### Grant permissions for external schemas

If your Redshift instance uses external schemas, grant permissions for each one.

Run the following command to grant `USAGE` permission:

```sql
GRANT USAGE ON SCHEMA <schema_name> TO GROUP atlan_users;
```

- Replace `<schema_name>` with the external schema name.
- Repeat for all external schemas.

:::info
If your external tables are sourced from Amazon S3 and AWS Glue Catalog, `USAGE` permission is sufficient, provided the IAM role associated with your Redshift cluster has read access to the data.
:::

For Redshift-based external schemas, you must also grant `SELECT` permission to enable metadata crawling:

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO GROUP atlan_users;
```

- Replace `<schema_name>` with the external schema name.
- Repeat for all Redshift-based external schemas.

#### Verify external schema permissions

To verify your permissions are configured correctly:

1. Connect to Redshift using the `atlan_user` credentials.
2. Run the following query using any database viewer tool:

 ```sql
 SELECT * FROM SVV_EXTERNAL_TABLES WHERE schema_name = '<external_schema_name>';
 ```

 Replace `<external_schema_name>` with your external schema name.

If the tables appear in the results, permissions are correctly configured.

#### Cloned schema for restricted access

If you can't grant `USAGE` or `SELECT` permissions on your external schemas, create a cloned schema containing the necessary metadata views, then grant permissions on the clone.

1. Log in as `dbadmin`.
2. Create a new schema. For example, `atlan`.
3. Clone the following views as tables from `pg_catalog` into your new schema:
 - `pg_views`
 - `SVV_TABLES`
 - `SVV_EXTERNAL_TABLES`
 - `SVV_COLUMNS`
4. Clone the following views as tables from `information_schema` into your new schema:
 - `key_column_usage` as `information_schema_key_column_usage`
 - `table_constraints` as `information_schema_table_constraints`
5. Grant access to the `atlan_users` group on the cloned schema:

 ```sql
 GRANT USAGE ON SCHEMA <cloned_schema_name> TO GROUP atlan_users;
 GRANT SELECT ON ALL TABLES IN SCHEMA <cloned_schema_name> TO GROUP atlan_users;
 ```

 Replace `<cloned_schema_name>` with your cloned schema name.

6. Schedule a cron job to refresh the cloned tables periodically, as Atlan relies on these tables to crawl metadata.

:::info
Contact [Atlan support](https://docs.atlan.com/support/submit-request) if you need assistance setting up a cloned schema.
:::

## Grant additional permissions for mining query history

If you plan to use [query history mining](https://docs.atlan.com/llms/connectors/amazon-redshift/mine-amazon-redshift/llms.txt), run the following commands:

```sql
GRANT SELECT ON pg_catalog.stl_ddltext TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.stl_query TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.stl_connection_log TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.stl_undone TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.stl_insert TO GROUP atlan_users;
GRANT SELECT ON pg_catalog.svl_statementtext TO GROUP atlan_users;
ALTER USER atlan_user SYSLOG ACCESS UNRESTRICTED;
```

These permissions are used for:

- [STL_DDLTEXT](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_DDLTEXT.html): DDL queries
- [STL_QUERY](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY.html): DML and regular queries
- [STL_CONNECTION_LOG](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_CONNECTION_LOG.html): session ID for query tracing
- [STL_UNDONE](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_UNDONE.html): rolled-back transactions
- [STL_INSERT](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_INSERT.html): table IDs in insert queries
- [SVL_STATEMENTTEXT](https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STATEMENTTEXT.html): full query text
- [SYSLOG ACCESS UNRESTRICTED](https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_USER.html#alter-user-syslog-access): access to all users' queries in system tables

## Configure AWS authentication

Select your authentication method.

No AWS IAM configuration is needed for basic authentication. Your setup is complete.

### IAM user

### Create IAM policy

Create an IAM policy with the permissions Atlan needs to connect. Follow [the steps in the AWS IAM User Guide](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html) using the following JSON:

```json
{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": [
 "redshift:GetClusterCredentials"
 ],
 "Resource": [
 "arn:aws:redshift:<region>:<account_id>:dbuser:<redshift_cluster_identifier>/atlan_user",
 "arn:aws:redshift:<region>:<account_id>:dbname:<redshift_cluster_identifier>/<database>"
 ]
 }
 ]
}
```

- Replace `<region>` with the AWS region of your Redshift instance.
- Replace `<account_id>` with your AWS account ID.
- Replace `<redshift_cluster_identifier>` with your Redshift cluster identifier.
- Replace `<database>` with your Redshift database name.

### Create IAM user

1. Create an IAM user following [the steps in the AWS IAM User Guide](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_users_create.html).
2. On the **Set permissions** page, attach the policy you created in the previous step.
3. After creating the user, copy the **access key ID** and **secret access key**.

 :::warning
 This is the only opportunity to view or download the access keys. They won't be accessible after you leave this screen.
 :::

### IAM role delegation

### Create IAM policy

Create an IAM policy with the permissions Atlan needs to connect. Follow [the steps in the AWS IAM User Guide](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html) using the following JSON:

```json
{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": [
 "redshift:GetClusterCredentials"
 ],
 "Resource": [
 "arn:aws:redshift:<region>:<account_id>:dbuser:<redshift_cluster_identifier>/atlan_user",
 "arn:aws:redshift:<region>:<account_id>:dbname:<redshift_cluster_identifier>/<database>"
 ]
 }
 ]
}
```

- Replace `<region>` with the AWS region of your Redshift instance.
- Replace `<account_id>` with your AWS account ID.
- Replace `<redshift_cluster_identifier>` with your Redshift cluster identifier.
- Replace `<database>` with your Redshift database name.

### Create IAM role

1. [Raise a support ticket](https://docs.atlan.com/support/submit-request) to get the ARN of the **Node Instance Role** for your Atlan EKS cluster.
2. Create a new IAM role in your AWS account following [the steps in the AWS IAM User Guide](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user.html):
 1. When prompted for policies, attach the policy you created in the previous step.
 2. When prompted, add the following trust policy. Replace `<atlan_nodeinstance_role_arn>` with the ARN from Atlan support.

 ```json
 {
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Principal": {
 "AWS": "<atlan_nodeinstance_role_arn>"
 },
 "Action": "sts:AssumeRole",
 "Condition": {}
 }
 ]
 }
 ```

3. For additional security, you can require an [external ID](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user_externalid.html). Replace the `Condition` block in the trust policy with the following:

 ```json
 "Condition": {
 "StringEquals": {
 "sts:ExternalId": "<atlan_external_id>"
 }
 }
 ```

 Replace `<atlan_external_id>` with the external ID you want to use.

4. [Contact Atlan support](https://docs.atlan.com/support/submit-request) with:
 - The name of the role you created.
 - The ID of the AWS account where the role was created.

:::warning
Wait for the support team to confirm the account is allowlisted to assume the role before running the crawler.
:::

### Serverless (IAM role)

## Grant permissions

### Create role

Run the following command to create the role Atlan uses to connect:

```sql
CREATE ROLE atlan_role;
```

### Grant required permissions to role

Run the following commands to grant the minimum permissions needed for Atlan to crawl metadata:

```sql
GRANT USAGE ON SCHEMA <schema_name> TO ROLE atlan_role;
GRANT SELECT ON pg_catalog.svv_table_info TO ROLE atlan_role;
```

- Replace `<schema_name>` with your schema name.
- Repeat these commands for each database in your schema.

[SVV_TABLE_INFO](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html) is used to retrieve the relationship between table IDs and their schema and database.

### Grant permissions for external schemas

If your Redshift Serverless instance uses external schemas, grant permissions for each one.

Run the following command to grant `USAGE` permission:

```sql
GRANT USAGE ON SCHEMA <schema_name> TO ROLE atlan_role;
```

- Replace `<schema_name>` with the external schema name.
- Repeat for all external schemas.

:::info
If your external tables are sourced from Amazon S3 and AWS Glue Catalog, `USAGE` permission is sufficient, provided the IAM role associated with your Redshift workgroup has read access to the data.
:::

For Redshift-based external schemas, you must also grant `SELECT` permission to enable metadata crawling:

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO ROLE atlan_role;
```

- Replace `<schema_name>` with the external schema name.
- Repeat for all Redshift-based external schemas.

#### Verify external schema permissions

To verify your permissions are configured correctly:

1. Connect to Redshift Serverless using the IAM role.
2. Run the following query using the [Amazon Redshift Data API](https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html):

 ```sql
 SELECT * FROM SVV_EXTERNAL_TABLES WHERE schema_name = '<external_schema_name>';
 ```

 Replace `<external_schema_name>` with your external schema name.

If the tables appear in the results, permissions are correctly configured.

#### Cloned schema for restricted access

If you can't grant `USAGE` or `SELECT` permissions on your external schemas, create a cloned schema containing the necessary metadata views, then grant permissions on the clone.

1. Log in as `dbadmin`.
2. Create a new schema. For example, `atlan`.
3. Clone the following views as tables from `pg_catalog` into your new schema:
 - `pg_views`
 - `SVV_TABLES`
 - `SVV_EXTERNAL_TABLES`
 - `SVV_COLUMNS`
4. Clone the following views as tables from `information_schema` into your new schema:
 - `key_column_usage` as `information_schema_key_column_usage`
 - `table_constraints` as `information_schema_table_constraints`
5. Grant access to `atlan_role` on the cloned schema:

 ```sql
 GRANT USAGE ON SCHEMA <cloned_schema_name> TO ROLE atlan_role;
 GRANT SELECT ON ALL TABLES IN SCHEMA <cloned_schema_name> TO ROLE atlan_role;
 ```

 Replace `<cloned_schema_name>` with your cloned schema name.

6. Schedule a cron job to refresh the cloned tables periodically, as Atlan relies on these tables to crawl metadata.

:::info
Contact [Atlan support](https://docs.atlan.com/support/submit-request) if you need assistance setting up a cloned schema.
:::

## Create IAM policy

Create an IAM policy with the permissions Atlan needs to connect. Follow [the steps in the AWS IAM User Guide](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html) using the following JSON:

```json
{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Effect": "Allow",
 "Action": [
 "redshift-serverless:GetCredentials"
 ],
 "Resource": [
 "arn:aws:redshift-serverless:<region>:<account_id>:workgroup/<workgroup_identifier>"
 ]
 }
 ]
}
```

- Replace `<region>` with the AWS region of your Redshift Serverless instance.
- Replace `<account_id>` with your AWS account ID.
- Replace `<workgroup_identifier>` with your Redshift Serverless workgroup identifier.

## Configure IAM role tag

On the IAM role you'll use to authenticate, add the following tag to map it to the Redshift role you created:

```
RedshiftDbRoles: atlan_role
```

:::info
STL and SVL system views aren't available in Redshift Serverless, so query history mining permissions aren't required for this deployment type.
:::

## Next steps

[Crawl Amazon Redshift assets](https://docs.atlan.com/llms/connectors/amazon-redshift/crawl-amazon-redshift/llms.txt): Configure and run the crawler to extract metadata from Amazon Redshift.

---