Skip to main content
Last updated on

Run federated queries on Amazon Redshift (IAM authentication)

Configure Databricks Lakehouse Federation to run federated queries on Amazon Redshift using AWS Identity and Access Management (IAM) authentication instead of a username and password. Authentication uses a Unity Catalog service credential that references an AWS IAM role. For general Redshift federation setup with username and password, see Run federated queries on Amazon Redshift.

note

IAM authentication for Amazon Redshift is available on Databricks on AWS only, because it relies on an AWS IAM role referenced by a service credential.

Before you begin​

Before you run federated queries on Amazon Redshift using IAM authentication, you must meet the following requirements.

Databricks requirements​

  • A workspace enabled for Unity Catalog.
  • Databricks compute that uses Databricks Runtime 19 or above and Standard or Dedicated access mode.
  • The CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
  • The CREATE SERVICE CREDENTIAL privilege on the metastore.

AWS requirements​

Choose an authentication mode​

Redshift IAM authentication supports three modes. Choose the one that matches your Redshift deployment, because it determines the IAM action you grant and whether you create a database user:

  • DB User (redshift:GetClusterCredentials): For provisioned clusters. Requires an explicit database user that you create in Redshift.
  • Group Federation (redshift:GetClusterCredentialsWithIAM): For provisioned clusters. Databricks derives the database identity from the IAM role, so you don't create a user.
  • Serverless (redshift-serverless:GetCredentials): For Redshift Serverless. The database identity is derived automatically, like Group Federation.

Configure AWS IAM authentication​

Complete the following steps in AWS to create the database user, IAM policy, and IAM role that Unity Catalog uses to connect.

Step 1: Set up the Redshift database identity​

For DB User mode, connect to your Redshift cluster and create a database user with password authentication disabled, then grant it the privileges it needs. You can run these commands from the cluster's Query data > Query in query editor page.

SQL
CREATE USER iam_user PASSWORD DISABLE;
GRANT ALL ON DATABASE dev TO iam_user;
GRANT USAGE ON SCHEMA public TO iam_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO iam_user;

For Group Federation and Serverless modes, skip the CREATE USER statement. Databricks derives the database identity automatically. The derived identity still needs in-database privileges so grant the privileges your queries require to that identity, or to a Redshift group or role it belongs to. Without them, the connection authenticates but federated queries fail with a permission error.

Step 2: Set up the AWS IAM policy​

Create an IAM policy that authorizes the credential-fetching action for your authentication mode.

For provisioned clusters, authorize redshift:GetClusterCredentials on the cluster, database user, and database:

JSON
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "RedshiftDbUser",
      "Effect": "Allow",
      "Action": ["redshift:GetClusterCredentials"],
      "Resource": [
        "arn:aws:redshift:<region>:<account-id>:cluster:<cluster-name>",
        "arn:aws:redshift:<region>:<account-id>:dbuser:<cluster-name>/<db-user>",
        "arn:aws:redshift:<region>:<account-id>:dbname:<cluster-name>/<database>"
      ]
    }
  ]
}

For more information, see Using IAM authentication to generate database user credentials and Security and connections in Amazon Redshift Serverless in the AWS documentation.

Step 3: Create the AWS IAM role​

Create an IAM role that Unity Catalog can assume to authenticate to Redshift:

  1. In the AWS IAM console, go to Roles and click Create role.

  2. For Trusted entity type, select Custom trust policy.

  3. In the Custom trust policy editor, paste the following policy. This is a placeholder. After you create the service credential in Databricks, replace it with the trust policy that Databricks generates. The Principal and Action are required. The Condition block is optional at this stage.

    JSON
    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Effect": "Allow",
          "Principal": {
            "AWS": ["arn:aws:iam::414351767826:role/unity-catalog-prod-UCMasterRole-14S5ZJVKOTYTL"]
          },
          "Action": "sts:AssumeRole",
          "Condition": {
            "StringEquals": {
              "sts:ExternalId": "0000"
            }
          }
        }
      ]
    }

  4. Click Next. On the Add permissions page, select the IAM policy you created in Set up the AWS IAM policy.

  5. Click Next, enter a Role name, and click Create role.

  6. Note the role's ARN. You provide it when you create the service credential.

For more information about IAM roles with custom trust policies, see Creating a role using custom trust policies in the AWS documentation.

Step 4: Create the Databricks service credential​

Create a Unity Catalog service credential that references the IAM role ARN from the previous step. For detailed steps, see Create service credentials.

After you create the service credential, finish configuring the IAM role's trust policy:

  1. In the Service credential created dialog, copy the entire trust policy shown, then click Done.
  2. Return to your IAM role in the AWS console, open the Trust relationships tab, and replace the placeholder trust policy with the one you copied. This adds your service credential's external ID and makes the role self-assuming.

Note the name of the service credential. You reference it when you create the connection.

Create a connection​

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in a Databricks notebook or the Databricks SQL query editor.

note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

  1. In your Databricks workspace, click Data icon. Catalog.
  2. At the top of the Catalog pane, click the Add or plus icon Add icon and select Create a connection from the menu.
  3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.
  4. Select a Connection type of Redshift.
  5. For Auth type, select Service Credential.
  6. (Optional) Add a comment.
  7. Click Next.
  8. On the Authentication page, enter the following connection properties for your Redshift cluster or workgroup:
    • Host: For example, redshift-demo.us-west-2.redshift.amazonaws.com
    • Port: For example, 5439
    • User: For DB User mode, the database user you created. For example, iam_user. Leave this empty for Group Federation and Serverless modes.
  9. Click Next.
  10. On the Connection details page, select the Authentication mode (DB User, Group Federation, or Serverless), and for Service credential, select the service credential you created in Create the service credential.
  11. Click Create connection.
  12. On the Catalog basics page, enter a name for the foreign catalog. A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog.
  13. (Optional) Click Test connection to confirm that it works.
  14. Click Create catalog.
  15. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.
  16. Change the Owner who can manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
  17. Grant Privileges on the catalog. Click Grant:
    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
      • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
      • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
      • Manually select the privileges to grant.
    3. Click Grant.
  18. Click Next.
  19. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.
  20. (Optional) Add a comment.
  21. Click Save.

Create a foreign catalog​

note

If you use the UI to create a connection to the data source, foreign catalog creation is included and you can skip this step.

note

If you use the UI to create a connection to the data source, foreign catalog creation is included and you can skip this step.

A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in a Databricks notebook or the SQL query editor. You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

  1. In your Databricks workspace, click Data icon. Catalog to open Catalog Explorer.

  2. At the top of the Catalog pane, click the Add or plus icon Add icon and select Add a catalog from the menu.

    Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.

  3. Follow the instructions for creating foreign catalogs in Create catalogs.

Additional resources​

On this page