Using a federated identity to manage Amazon Redshift access to local resources and Amazon Redshift Spectrum external tables

Create an IAM identity. This can be a user or an IAM role. Use any name supported by IAM.

Attach permissions policies to the identity. Specify either of the following:

You can add permissions with the policy editor, using the IAM console.

The IAM identity also needs permissions to access external data. Grant access to Amazon S3 by adding the following AWS managed policies directly:

The last managed policy is required if you're using AWS Glue to prepare your external data. For more information about the steps for granting access to Amazon Redshift Spectrum, see Create an IAM role for Amazon Redshift, which is part of the getting-started guide for Amazon Redshift and Redshift Spectrum. It shows the steps for adding IAM policies to access Redshift Spectrum.

Set up your SQL client to connect to Amazon Redshift. Use the Amazon Redshift JDBC driver, and add your user's credentials to the tool's credential properties. A client like SQL Workbench/J works well for this. Set the following client-connection extended properties:

Set the URL to the JDBC endpoint found in the Amazon Redshift or Amazon Redshift Serverless console. Replace your URL schema with jdbc:redshift:iam: and use this formatting:

After you connect to the database for the first time, using an IAM identity, Amazon Redshift automatically creates an Amazon Redshift identity with the same name, prefixed with IAM: for a user or IAMR: for an IAM role. The remaining steps in this topic show examples for a user.

If a Redshift user isn't automatically created, you can create one by running a CREATE USER statement, using an admin account, specifying the user name in the format IAM:<user name>.

As your Amazon Redshift cluster administrator, grant the Redshift user the required permissions to access the external schema.

GRANT ALL ON SCHEMA my_schema to "IAM:my_user";

To grant the ability to your Redshift user to create tables in the external schema, they must be a schema owner. For example:

ALTER SCHEMA my_schema owner to "IAM:my_user";

To verify the configuration, run a query as the user, using the SQL client, after permissions are granted. This query sample retrieves data from an external table.

SELECT * FROM my_schema.my_table;

Create local tables and external tables. External tables catalogued with AWS Glue work for this.

Connect to Amazon Redshift with your IAM identity. As mentioned in the previous section, when the identity connects to Amazon Redshift, a Redshift database user is created. The user is created if they didn't previously exist. If the user is new, the administrator must grant them permissions to perform tasks in Amazon Redshift, like querying and creating tables.

Connect to Redshift with your admin account. Run the command to create an external schema, using the SESSION value.

create external schema spectrum_schema from data catalog
database '<my_external_database>' 
region '<my_region>'
iam_role 'SESSION'
catalog_id '<my_catalog_id>';

Note that catalog_id is set in this case. This is a new setting added with the feature, because SESSION replaces a specific role.

In this example, values in the query mimic how real values appear.

create external schema spectrum_schema from data catalog
database 'spectrum_db' 
region 'us-east-1'
iam_role 'SESSION'
catalog_id '123456789012'

The catalog_id value in this case is your AWS account ID.

Run queries to access your external data, using the IAM identity you connected with in step 2. For example:

select * from spectrum_schema.table1;

In this case, table1 can be, for example, JSON-formatted data in a file, in an Amazon S3 bucket.

If you already have an external schema that uses a cluster-attached IAM role, pointing to your external database or schema, you can either replace the existing schema and use a federated identity as detailed in these steps, or create a new one.