Prerequisites

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Before you install the Amazon Redshift OCF connector, make sure that you have performed the following:

Configure Network Connectivity

  • Open inbound TCP port 5439 on Redshift to receive requests from Alation.

Create a Service Account

Sample SQL to create an account:

CREATE USER alation WITH PASSWORD 'password';

Grant Required Permissions

Permissions for Metadata extraction

Grant Access on External Schema or Table

GRANT USAGE ON SCHEMA <external_schema_name> TO <user_name>;

Grant Access on one of the following:

Either Grant Access on All Views in Schema PG_CATALOG

GRANT USAGE ON SCHEMA PG_CATALOG TO alation;
GRANT SELECT ON ALL TABLES IN SCHEMA PG_CATALOG TO alation;

Or, Grant Access to Service Account for All Views in the list

GRANT USAGE ON SCHEMA PG_CATALOG TO alation;
GRANT SELECT ON PG_CATALOG.PG_NAMESPACE TO alation;
GRANT SELECT ON PG_CATALOG.PG_TABLES TO alation;
GRANT SELECT ON PG_CATALOG.PG_DESCRIPTION TO alation;
GRANT SELECT ON PG_CATALOG.PG_STATIO_ALL_TABLES TO alation;
GRANT SELECT ON PG_CATALOG.SVV_EXTERNAL_TABLES TO alation;
GRANT SELECT ON PG_CATALOG.SVV_EXTERNAL_SCHEMAS TO alation;
GRANT SELECT ON PG_CATALOG.SVV_COLUMNS TO alation;
GRANT SELECT ON PG_CATALOG.PG_VIEWS TO alation;
GRANT SELECT ON PG_CATALOG.PG_CONSTRAINT TO alation;
GRANT SELECT ON PG_CATALOG.PG_ATTRIBUTE TO alation;
GRANT SELECT ON PG_CATALOG.PG_CLASS TO alation;
GRANT SELECT ON PG_CATALOG.PG_PROC TO alation;
GRANT SELECT ON PG_CATALOG.PG_LANGUAGE TO alation;

Views Used for Extracting Metadata Types

Metadata type

System views being used

Catalog

PG_NAMESPACE

Schema

PG_NAMESPACE

Table

PG_TABLES, PG_VIEWS, PG_CATALOG.PG_STATIO_ALL_TABLES, PG_CATALOG.PG_DESCRIPTION SVV_EXTERNAL_SCHEMAS, SVV_EXTERNAL_TABLES

View

PG_VIEWS

Column

SVV_COLUMNS

Primary Key

PG_CONSTRAINT, PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE

Foreign Key

PG_CONSTRAINT, PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE

Function

PG_PROC, PG_NAMESPACE, PG_LANGUAGE

Function Definition

PG_PROC, PG_NAMESPACE, PG_LANGUAGE

Permissions for Table or View Profiling

GRANT SELECT ON <schema_name.view_name> TO alation;
GRANT SELECT ON <schema_name.table_name> TO alation;

Permissions for Query Log Ingestion

GRANT USAGE ON SCHEMA <qli_schema_name> TO alation;
GRANT SELECT ON <qli_schema_name>.<qli_table_name> TO alation;

STS Authentication with an AWS IAM User

You can choose to authenticate through the STS service and an AWS IAM user.

To set up STS authentication with an AWS IAM user:

  1. In AWS IAM, create a user.

  2. Write down the access key, secret access key, and the user ARN of the user.

  3. Create an IAM policy (for example, <read_resources_policy>) that grants access to the AWS resources you are going to catalog. For an Amazon Redshift data source, we recommend granting the SELECT privileges on schemas and tables you wish to catalog, as well as on a number of system tables. See Service Account for information on what permissions are required.

    Note

    You can use an existing policy that fulfills these access requirements.

  4. Create an IAM role (for example, <read_resources_role>) selecting the Type of Trusted Entity to be AWS Service and Use Case to be EC2. To this role, attach the policy you created in step 3 above (<read_resources_policy>) or an existing policy that fulfills the requirements for MDE, profiling, and QLI. This role will be assumed by Alation when performing MDE, profiling, and QLI from your Amazon Redshift data source.

  5. Save the ARN of the role (<read_resources_role>). It will be required for the next configuration steps in AWS and later during the configuration in Alation.

  6. Create an IAM policy (for example, <assume_role_policy>) that allows the AssumeRole action for the IAM role created in step 4. When creating this policy:

    • Select STS as Service.

    • Under Actions > Access Level > Write, select AssumeRole.

    • Under Resources, specify the ARN of the role that gives access to the AWS resources (your <read_resources_role> created in step 4).

  7. Open the properties page of your <read_resources_role> role. Edit the Trust Relationship of this role by adding your user ARN.

When configuring your Amazon Redshift data source in Alation, you will need to specify:

  • AWS access key ID of the IAM user

  • AWS access key secret

  • ARN of your <read_resources_role> role.

Have this information available when performing the configuration on the Alation side.

STS Authentication with an AWS IAM Role

This type of authentication can be used to authenticate across two AWS accounts when the Alation instance runs on EC2 under one account and the Amazon Redshift cluster runs under the other. STS Authentication with the AWS IAM role is available for both on-premise and Alation Cloud Service instances from connector version 1.3.2.6521.

To configure STS authentication with an AWS IAM role, use the steps in Configure Authentication via AWS STS and an IAM Role. To provide access to Redshift, create policies that provide a level of access similar to the Service Account permissions.