Integrate Identity Provider (IdP) with Amazon Redshift Query Editor V2 and SQL Client using AWS IAM Identity Center for seamless Single Sign-On

October 2024: This post was reviewed and updated to update SQL Client setup instructions.

AWS IAM Identity Center allows you to manage single sign-on (SSO) access to all your AWS accounts and applications from a single location. We are pleased to announce that Amazon Redshift now integrates with AWS IAM Identity Center, and supports trusted identity propagation, allowing you to use third-party Identity Providers (IdP) such as Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin. This integration simplifies the authentication and authorization process for Amazon Redshift users using Query Editor V2 or Amazon Quicksight, making it easier for them to securely access your data warehouse. Additionally, this integration positions Amazon Redshift as an IAM Identity Center-managed application, enabling you to use database role-based access control on your data warehouse for enhanced security.

IAM Identity Center supports automatic provisioning of user and groups from your preferred identity provider (IdP) into IAM Identity Center using the System for Cross-domain Identity Management (SCIM) v2.0 protocol. This integration allows for seamless synchronization of information between two services, ensuring accurate and up-to-date information in AWS IAM Identity Center.

In this blog post, we will provide a comprehensive guidance on how to integrate IAM identity center with Amazon Redshift to manage your identity provider’s groups using Redshift RBAC strategy. We will also show you how to take advantage of this integration to query Redshift tables using Amazon Redshift Query Editor V2 (QEv2) and with SQL clients like DbVisualizer and DBeaver.

Solution overview

Using IAM Identity Center with Amazon Redshift can benefit your organization in the following ways:

• Users can connect to Amazon Redshift without requiring an administrator to set up AWS IAM roles with complex permissions.
• IAM Identity Center integration allows mapping of IAM Identity Center groups with Amazon Redshift database roles. Administrators can then assign different privileges to different roles and assigning these roles to different users, giving organizations granular control for user access.
• IAM Identity Center provides a central location for your users in AWS. You can create users and groups directly in IAM Identity Center or connect your existing users and groups that you manage in a standards-based identity provider like Okta, Ping Identity, or Microsoft Entra ID (i.e., Azure Active Directory [AD]).
• IAM Identity Center directs authentication to your chosen source of truth for users and groups, and it maintains a directory of users and groups for access by Amazon Redshift.
• You can share one IAM Identity Center instance with multiple Amazon Redshift data warehouses with a simple auto-discovery and connect capability. This makes it fast to add clusters without the extra effort of configuring the IAM Identity Center connection for each, and it ensures that all clusters and workgroups have a consistent view of users, their attributes, and groups. Note: Your organization’s IAM Identity Center instance must be in the same region as the Amazon Redshift data warehouse you’re connecting to.
• Because user identities are known and logged along with data access, it’s easier for you to meet compliance regulations through auditing user access in AWS CloudTrail authorizes access to data.
• IAM Identity Center allows you to make trusted identity propagation between Amazon Redshift with other AWS services such as AWS Lake Formation, Amazon QuickSight and supported third-party BI tools and applications.

Amazon Query Editor V2 workflow with IAM Identity center and an external identity provider:

Figure 1: Amazon Query Editor V2 workflow with IAM Identity center and an external identity provider

1. End user initiates the flow using AWS access portal URL (this URL would be available on IAM Identity Center dashboard console on Management Account). A browser pop-up triggers and takes you to your preferred IdP Login page where you enter IdP credentials. After successful authentication, you’ll be logged into the AWS Console as a federated user. Click on your AWS Account and choose the Amazon Redshift Query Editor V2 application. Once you federate to Query Editor V2, select the IAM Identity Center authentication method.
2. QEV2 invokes browser flow where you re-authenticate, this time with your AWS IAM Identity Center credentials. Since you already entered your IdP credentials, which are already cached in browser. At this step, federation flow with IAM Identity Center initiates and at the end of this flow, the Session token and Access token is available to the QEv2 console in browser as cookies.
3. Amazon Redshift retrieves your authorization details based on session token retrieved and fetches user’s group membership.
4. Upon a successful authentication, you’ll be redirected back to QEV2, but logged in as an IAM Identity Center authenticated user.

SQL Client workflow with IAM Identity center and an external identity provider:

Figure 1.1: SQL Client workflow with IAM Identity center and an external identity provider

1. User configure SQL client to use IAM Identity Center’s issuer url or start url.
2. Redshift driver initiates OAuth 2.0 based authorization grant with Proof Key for Code Exchange (PKCE) to IAM Identity Center.
3. In browser, user enters credentials in the IdP login page, if they have not already signed in to their IdP.
4. Redshift driver obtains an authorization code from IAM Identity Center.
5. Driver calls to IAM Identity Center with authorization code.
6. IAM Identity Center returns an access token to the driver.
7. Driver forwards access token to Redshift in SQL request.
8. Redshift calls IAM Identity Center to obtain the user’s ID from the access token.
9. Redshift uses the user ID to obtain group membership information from IAM Identity Center. Amazon Redshift uses group information to authorize access to requested data.
10. SQL client able to connect with Amazon Redshift and access data based on the user and group membership returned from IAM Identity Center.

This solution covers following steps:

1. Create IAM policy for Amazon Redshift access
2. Create IAM role
3. Setting up IAM Identity Center integration with Amazon Redshift
4. Assign Users or Groups from IAM Identity Center to Amazon Redshift Application.
5. Amazon Redshift integration with IAM Identity Center

5.1 Integrate a new Amazon Redshift provisioned or Amazon Redshift serverless endpoint
5.2 Associating an IAM Identity Center application with an existing provisioned cluster or Serverless endpoint

6. Configure Amazon Redshift role-based access
7. Create a permission set
8. Create account assignments
9. Test your AWS IAM Identity center and Amazon Redshift integration with Redshift Query Editor V2
10. Configure SQL client
11. Troubleshooting

Prerequisites

• An AWS account. If you don’t have one, you can sign up for one.
• Enable IAM Identity Center. For more information, see Enabling AWS IAM Identity Center.
• Connect IAM Identity Center with your preferred IdP and sync users and groups. Follow this link for the IdP setup.
• For SQL Client integration – download Amazon Redshift JDBC SDK driver-dependent libraries 2.1.0.30 onwards and unzip the Amazon Redshift JDBC JAR zipped folder. All prior Amazon Redshift driver versions don’t support this feature.
• A SQL client – DbVisualizer(version 24.2.3  and above) and DBeaver. In this blog, we are using DbVisualizer and DBeaver for demonstration purpose, however, you can use any of your preferred SQL client with Redshift JDBC/ODBC Driver.

Walkthrough

Step 1: Create IAM policy for Amazon Redshift access

To integrate Amazon Redshift with IAM Identity Center, you must create an IAM policy in the account where Amazon Redshift cluster/Serverless instance exists. In this blog post, we would be creating IAM policy in the member account where the Redshift cluster/Serverless instance exist. The specific policy permissions required are the following:

• redshift:DescribeQev2IdcApplications – Used to detect capability for IAM Identity Center authentication from Redshift Query Editor V2.
• redshift-serverless:ListNamespaces – Grants permission to list namespaces in Amazon Redshift Serverless.
• redshift-serverless:ListWorkgroups – Grants permission to list workgroups in Amazon Redshift Serverless.
• redshift-serverless:GetWorkgroup – Grants permission to get information about a specific workgroup.
• sso:DescribeApplication – Required to create an identity provider (IdP) entry in the catalog.
• sso:DescribeInstance – Used to manually create IdP federated roles or users.

To create your IAM policy, complete the following steps:

1. On the IAM console, choose Policies.
2. Choose Create policy.
3. On the Create policy page, choose the JSON tab.
4. For the policy, enter the JSON in following format:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "VisualEditor0",
               "Effect": "Allow",
               "Action": [
                   "redshift:DescribeQev2IdcApplications",
                   "redshift-serverless:ListNamespaces",
                   "redshift-serverless:ListWorkgroups",
                   "redshift-serverless:GetWorkgroup"
               ],
               "Resource": "*"
           },
           {
               "Sid": "VisualEditor1",
               "Effect": "Allow",
               "Action": [
                   "sso:DescribeApplication",
                   "sso:DescribeInstance"
               ],
               "Resource": [
                   "arn:aws:sso:::instance/<IAM Identity Center Instance ID>",
                   "arn:aws:sso::<AWS Account ID>:application/<IAM Identity Center Instance ID>/*"
               ]
           }
       ]
   }

Replace <IAM Identity Center Instance ID> with your Identity Center instance and <AWS Account ID> with the account id where your IAM Identity Center is setup. In this blog post, for <IAM Identity Center Instance ID> , we are using the ID that belongs to the organization instance of IAM Identity Center in the management account and similarly for <AWS Account ID> , we are using the management account ID.

5. On the Review and create page, type a Policy Name and a Description (optional) for the policy that you are creating. Review Permissions defined in this policy to see the permissions that are granted by your policy.
6. (Optional) Add metadata to the policy by attaching tags as key-value pairs. For more information about using tags in IAM, see Tagging IAM resources.
7. Choose Create policy to save your new policy.

Step 2: Create the IAM role

To integrate Amazon Redshift with IAM Identity Center, you must create an IAM Role in the account where the cluster exists. In this blog post, we would be creating IAM role in the member account where the Redshift cluster/Serverless instance exist. Redshift administrator uses this IAM role to configure Redshift for use with IAM Identity Center. To create your IAM role, complete the following steps:

1. On the IAM console, choose Roles in the navigation pane.
2. Choose Create role.
3. Choose AWS service as the trusted entity, and then choose Redshift as the use case.
4. Under Use case for other AWS services, choose Redshift – Customizable and then choose Next.
5. The Add permissions policy page appears. Choose the policy which you created earlier. For e.g. aws-idc-policy. Choose Next.
6. For Role name, enter a name for your role, for example IAMIDCRedshiftRole.
7. Review the information, and then choose Create role.
8. Navigate to the role that you just created and choose Trust Relationships.
9. Choose Edit trust policy and type SetContext under Add actions for STS. Select SetContext and choose Update policy.
10. Below is the code from Trust Relationships:

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Principal": {
                    "Service": "redshift.amazonaws.com"
                },
                "Action": [
                    "sts:AssumeRole",
                    "sts:SetContext"
                ]
            }
        ]
    }

Step 3: Setting up IAM Identity Center integration with Amazon Redshift

Amazon Redshift cluster administrator or Amazon Redshift Serverless administrator must perform steps to configure Redshift as an IAM Identity Center-enabled application in the account where Redshift endpoint/Serverless instance exist. In this blog post, we would be creating Amazon Redshift application in the member account where the Redshift cluster/Serverless instance exist. This enables Amazon Redshift to discover and connect to the IAM Identity Center automatically to receive sign-in and user directory services.

After this, when the Amazon Redshift administrator creates a cluster or workgroup, they can enable the new data warehouse to use IAM Identity Center and its identity-management capabilities. The point of enabling Amazon Redshift as an IAM Identity Center-managed application is so you can control user and group permissions from within the IAM Identity Center, or from a source third-party identity provider that’s integrated with it.

When your database users sign in to an Amazon Redshift database, for example an analyst or a data scientist, it checks their groups in IAM Identity Center and these are mapped to roles in Amazon Redshift. In this manner, a group can map to an Amazon Redshift database role that allows read access to a set of tables.

The following steps show how to make Amazon Redshift an AWS-managed application with IAM Identity Center:

1. Select IAM Identity Center connection from Amazon Redshift console menu.
   

   Figure 2: Redshift-IAM Identity Center connection

2. Choose Create application
   
   

   Figure 3: Create Redshift Application for IAM Identity Center Connection

3. On IAM Identity Center connection, Choose Next.
4. In IAM Identity Center integration setup section, for:
   1. IAM Identity Center display name – Enter a unique name for Amazon Redshift’s IAM Identity Center-managed application. The application display name appears in the IAM Identity Center console and APIs.
   2. Managed application name – You can enter the managed Amazon Redshift application name or use the assigned value as it is. This is a unique identifier for the managed application that’s integrated with IAM Identity Center. You can use it for multi-application identification.
5. In Connection with third-party identity providers section, for:
   1. Identity Provider Namespace – Specify the unique namespace for your organization. This is typically an abbreviated version of your organization’s name. It’s added as a prefix for your IAM Identity Center-managed users and roles in the Amazon Redshift database.
6. In IAM role for IAM Identity Center access – Choose an IAM role which you have created in section (Step 2. Create the IAM role). For e.g. IAMIDCRedshiftRole
7. Select Enable the query editor v2 application to allow user to access the database and appropriate objects in the database through their IAM Identity Center-based, namespace-prefixed identity.
8. If you want to leverage AWS Lake formation then select Enable AWS Lake Formation access grants under Trusted identity propagation. This will enable fine grained access control from AWS Lake Formation. Lake Formation use case is covered in the blog – Simplify access management with Amazon Redshift and AWS Lake Formation for users in an External Identity Provider.
9. Choose Next.Figure 4: Configure Redshift Application for IAM Identity Center connection
10. In Configure client connections that use third-party IdPs section, choose Yes if you want to connect Amazon Redshift with a third-party application. Otherwise, choose No. In this blog post, we are using Amazon Redshift Query Editor V2 so thats why we are choosing option as No.
    

    Figure 5: Configure client connections for third-party IdPs

11. Choose Next.
12. In the Review and create application section, review all the details you have entered before and choose Create application.
    

    Figure 6: Review – Redshift Application for IAM Identity Center connection

    After the Amazon Redshift administrator finishes the steps and saves the configuration, the IAM Identity Center properties appear in the Redshift Console. Completing these tasks makes Redshift an IAM Identity Center-enabled application.

Figure 7: Successful creation of Redshift Application

After you select the managed application name, the properties in the console includes the integration status. It says Success when it’s completed. This status indicates if IAM Identity Center configuration is completed.

Figure 8: General Configuration of Redshift Application

Step 4: Assigning users or groups from IAM Identity Center to Amazon Redshift application

In this step, users or groups synced to your Organization instance of IAM identity center in management account, and are available to assign to your Redshift application. Amazon Redshift administrator can now decide which users or groups from Management Account IAM Identity Center need to be included as part of Amazon Redshift application.

For example, if you have total 20 groups on your organization instance of IAM Identity Center and you don’t want all the groups to include as part of Amazon Redshift application in the member account, then you have options to choose which IAM Identity Center groups to include as part of Amazon Redshift-enabled IAM Identity Center application. In this section, we will be assigning two groups to the Redshift application. Later, you can create two Redshift database roles as part of IAM Identity Center integration in Amazon Redshift.

The following steps assign groups to Amazon Redshift-enabled IAM Identity Center application:

1. On IAM Identity Center properties in the Amazon Redshift Console, select Assign under Groups tab.
2. If this is the first time you’re assigning groups, then you’ll see a notification. Select Get started.
3. Enter which groups you want to synchronize in the application. In this example, we chose the groups awssso-sales and awssso-finance.
4. Choose Done.
   

   Figure 9: Group Assignment in Amazon Redshift’s IAM Identity Center-managed application

Step 5: Amazon Redshift integration with IAM Identity Center

After completing above steps under section (Step 3: Setting up IAM Identity Center integration with Amazon Redshift) — Amazon Redshift database administrator have two options:

• Configure new Redshift cluster/serverless instance to work in alignment with IAM Identity Center by following section (Step 5.1 Integrate a new Amazon Redshift provisioned or Amazon Redshift serverless endpoint)
• Or, Admin can modify existing Redshift cluster or serverless workgroup to enable for IAM Identity Center integration by following section – (Step 5.2 Associating an IAM Identity Center application with an existing provisioned cluster or Serverless endpoint)

Step 5.1:  Integrate a new Amazon Redshift provisioned or Amazon Redshift serverless endpoint

Amazon Redshift database administrator needs to configure new Redshift resources to work in alignment with IAM Identity Center to make sign-in and data access easier. Anyone with permissions to create Amazon Redshift resources can perform these IAM Identity Center integration tasks. When you create a provisioned cluster, you start by choosing Create Cluster in the Amazon Redshift Console. In this blog post, we would be creating a Redshift cluster in the member account.

1. Choose Enable for the cluster (recommended) in the section for IAM Identity Center connection in the create-cluster steps.
2. From the drop down, choose the redshift application which you created in above steps.

Note that when a new data warehouse is created, the IAM role specified for IAM Identity Center integration is automatically attached to the provisioned cluster or Serverless Namespace. After you finish entering the required cluster metadata and create the resource, you can check the status for IAM Identity Center integration in the properties.

Figure 10: Create Redshift Cluster to enable IAM Identity Center connection

For more details on creating a cluster, refer to AWS documentation:

• For Redshift cluster setup instructions, see Create a sample Amazon Redshift cluster.
• For Redshift Serverless endpoint setup instructions, see Getting started with Amazon Redshift Serverless.

Step 5.2: Associating an IAM Identity Center application with an existing provisioned cluster or Serverless endpoint

If you have an existing provisioned cluster or serverless workgroup that you would like to enable for IAM Identity Center integration, then you can do that by running a SQL command. You run the following command to enable integration. It’s required that a database administrator run the query.

CREATE IDENTITY PROVIDER "<IAM Identity Center display name>" TYPE AWSIDC
NAMESPACE '<Identity provider namespace>'
APPLICATION_ARN '<IAM Identity Center managed application ARN>'
IAM_ROLE '<IAM role for IAM Identity Center access>';

For example:

CREATE  IDENTITY PROVIDER "redshift-idc-app" TYPE AWSIDC 
NAMESPACE 'awsidc' APPLICATION_ARN 'arn:aws:sso::123456789012:application/ssoins-12345f67fe123d4/apl-a0b0a12dc123b1a4'
IAM_ROLE 'arn:aws:iam::123456789012:role/IAMIDCRedshiftRole';

Below commands only need to be run if you want to make changes/drop existing Identity Provider.

To alter the IdP, use the following command:

ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
NAMESPACE '<Identity provider namespace>';

ALTER IDENTITY PROVIDER "<IAM Identity Center display name>"
IAM_ROLE default | '<IAM role for IAM Identity Center access>';

Few of the examples are:

ALTER IDENTITY PROVIDER "redshift-idc-app"
NAMESPACE 'awsidctest';

ALTER IDENTITY PROVIDER "redshift-idc-app"
IAM_ROLE 'arn:aws:iam::123456789012:role/administratoraccess';

Note: If you update the IAM Identity Center-namespace value, then all the new cluster created afterwards will be using the updated namespace.

For existing clusters or serverless workgroups, you need to update the namespace manually on each Amazon Redshift cluster using the previous command. Also, all the database roles associated with identity provider will be updated with new namespace value.

You can disable or enable the identity provider using the following command:

ALTER IDENTITY PROVIDER "<IAM Identity Center display name>" disable|enable;

For example:

ALTER IDENTITY PROVIDER "redshift-idc-app" disable;

You can drop an existing identity provider. The following example shows how CASCADE deletes users and roles attached to the identity provider.

DROP IDENTITY PROVIDER "<IAM Identity Center display name>" [ CASCADE ]

Step 6: Configure Amazon Redshift role-based access

In this step, we pre-create the database roles in Amazon Redshift based on the groups that you synced in IAM Identity Center. Make sure the role name matches with the IAM Identity Center Group name.

Amazon Redshift roles simplify managing privileges required for your end-users. In this post, we create two database roles, sales and finance, and grant them access to query tables with sales and finance data, respectively. You can download this sample SQL Notebook and import into Redshift Query Editor v2 to run all cells in the notebook used in this example. Alternatively, you can copy and enter the SQL into your SQL client.

Below is the syntax to create role in Amazon Redshift:

create role "<Identity provider namespace>:<IAM Identity Center groupname>";

For example:

create role "awsidc:awssso-sales";
create role "awsidc:awssso-finance";

Create the sales and finance database schema:

create schema sales_schema;
create schema finance_schema;

Creating the tables:

CREATE TABLE IF NOT EXISTS finance_schema.revenue
(
account INTEGER   ENCODE az64
,customer VARCHAR(20)   ENCODE lzo
,salesamt NUMERIC(18,0)   ENCODE az64
)
DISTSTYLE AUTO
;

insert into finance_schema.revenue values (10001, 'ABC Company', 12000);
insert into finance_schema.revenue values (10002, 'Tech Logistics', 175400);
insert into finance_schema.revenue values (10003, 'XYZ Industry', 24355);
insert into finance_schema.revenue values (10004, 'The tax experts', 186577);

CREATE TABLE IF NOT EXISTS sales_schema.store_sales
(
ID INTEGER   ENCODE az64,
Product varchar(20),
Sales_Amount INTEGER   ENCODE az64
)
DISTSTYLE AUTO
;

Insert into sales_schema.store_sales values (1,'product1',1000);
Insert into sales_schema.store_sales values (2,'product2',2000);
Insert into sales_schema.store_sales values (3,'product3',3000);
Insert into sales_schema.store_sales values (4,'product4',4000);

Now, you can grant relevant permissions to the role as per your requirement. In the following example, we grant usage on a specific schema to a role. We then apply scoped-level permissions to grant select permissions to existing and future objects for the roles sales_schema and finance_schema.

For example:

grant usage on schema sales_schema to role "awsidc:awssso-sales";
grant select for tables in schema sales_schema to role "awsidc:awssso-sales";

grant usage on schema finance_schema to role "awsidc:awssso-finance";
grant select for tables in schema finance_schema to role "awsidc:awssso-finance";

Step 7: Create a permission set

In this step, we will be creating a permission set in the Management Account. This step is required to grant console access to Amazon Redshift Query Editor V2 application. This will allow federated users to access Redshift Query Editor V2 application.

For more information about permission set, please follow this document.

The following steps to create permission set:

1. In the Management account, open the IAM Identity Center Console.
2. In the navigation pane, under Multi-Account permissions, choose Permission sets.
3. Choose Create permission set.
   

   Figure 11: Create Permission Set

4. Choose Custom permission set and then choose Next.
5. Under AWS managed policies, choose AmazonRedshiftQueryEditorV2ReadSharing.
6. Under Inline policy, add the below policy:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "Statement1",
               "Effect": "Allow",
               "Action": [
                   "redshift:DescribeQev2IdcApplications",
                   "redshift-serverless:ListNamespaces",
                   "redshift-serverless:ListWorkgroups",
                   "redshift-serverless:GetWorkgroup"
               ],
               "Resource": "*"
           }
       ]
   }

7. Choose Next.
8. Enter permission set name. For example, Redshift-Query-Editor-V2.
9. Under Relay state – optional – set default relay state to the Query Editor V2 URL, using the format : https://<region>.console.aws.amazon.com/sqlworkbench/home.
   For this post, we use: https://us-east-1.console.aws.amazon.com/sqlworkbench/home.
10. Choose Next.
11. On the Review and create screen, choose Create. The console displays the following message: The permission set Redshift-Query-Editor-V2 was successfully created.
    

    Figure 12: Review – Permission Set

Step 8: Create account assignments

1. In the Management Account, open the IAM Identity Center Console.
2. In the navigation pane, under Multi-account permissions, choose AWS accounts.
3. On the AWS accounts page, select AWS member account that you want to assign single sign-on access to.
   

   Figure 13: Account Assignment

4. Choose Assign users or groups.
5. On the Assign users and groups to AWS-account-name, choose the groups that you want to create the permission set for. Then, choose Next.
6. On the Assign permission sets to AWS-account-name, choose the permission set you created in the section –  Step 7. Create a permission set. Then, choose Next.
7. On the Review and submit assignments to AWS-account-name page, for Review and submit, choose Submit.
   

   Figure 14: Review Account Assignment

   

   Figure 15: Account Assignment Completed for Redshift Query Editor V2

Step 9: Test your AWS IAM Identity center and Amazon Redshift integration with Redshift Query Editor V2

Now you’re ready to connect Amazon Redshift Query Editor V2 with AWS IAM Identity Center authentication:

1. In the Management Account, navigate to the IAM Identity Center console and copy the AWS access portal URL from the dashboard.
2. Now sign out from the management account and paste the AWS access portal URL in a new browser window.
3. A browser pop-up will trigger and takes you to preferred IdP login page where you enter your IdP credentials.
4. After successful authentication, you’ll be logged into the AWS console as a federated user.
5. Select the permission set named Amazon Redshift Query Editor V2.
6. Once you federate to Query Editor V2, choose your Redshift instance (i.e., right-click) and choose Create connection.
7. To authenticate using IAM Identity Center, choose the authentication method IAM Identity Center.
8. It will show a pop-up and since your IdP credentials is already cached, it utilizes the same credentials and connects to Amazon Redshift Query Editor V2 using IAM Identity Center authentication.

The following demonstration shows a federated user (Ethan) from IdP used the AWS access portal URL to access Amazon Redshift using IAM Identity Center authentication. In below demonstration, we are taking Okta as the example for IdP. User Ethan accesses the sales_schema tables. If User Ethan tries to access the tables in finance_schema, then the user gets a permission denied error.

Step 10: Configure the SQL client

In this section, we provide instructions to set up SQL Clients such as DbVisualizer and DBeaver to connect with Amazon Redshift using IAM Identity center authentication. Amazon Redshift introduced a new browser plugin for AWS IAM Identity Center, which will allow user to use their preferred SQL clients to sign in with their user credentials defined in their corporate identity providers. To authenticate with AWS IAM Identity Center, you need to configure the issuer_url, plugin_name, and idc_region fields in the properties of respective SQL clients.

Set up DbVisualizer

To set up DbVisualizer, complete the following steps:

1. Select Tools and then choose Driver Manager.

Figure 16: Driver Manager in DbVisualizer

2. Type redshift and choose plus icon to add Redshift JDBC Driver files(2.1.0.30 or higher version). Keep only one set of the latest driver version and if you see any older versions, delete those files. Close the window after adding driver files.
   

   Figure 17: Updating Redshift JDBC Driver in DbVisualizer

3. Database connections are created using the Database→Create Database Connection from main menu or using the plus icon in the Databases tab toolbar.
   

   Figure 18: Creating Database Connection in DbVisualizer

4. It will open a window in which the driver type needs to be selected. Type redshift followed by double click.
5. On Database Connection, enter:
   Database Server: Enter Redshift endpoint. e.g. test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com
   Database Port: Enter port. e.g. 5439
   Database – Enter Database name which you want to connect. For e.g. dev
   

   Figure 19: Database Connection for Redshift in DbVisualizer

6. Navigate to Properties tab and choose Driver Properties.
7. Using the plus icon, add the following properties:
   1. issuer_url – you can either use the Issuer URL or AWS access portal URL from your IAM Identity center dashboard as shown below. In this example, we will use the issuer url.
      

      Figure 20: IAM Identity Center Dashboard

   2. plugin_name – com.amazon.redshift.plugin.BrowserIdcAuthPlugin
   3. idc_region – enter the region of your IAM Identity center.
      

      Figure 21: Adding Parameter in DbVisualizer

8. Choose Apply.
9. Under Redshift, Select the Physical Connection category and enable Use a Single Shared Physical Connection. Choose Apply.
   

   Figure 22: Single Shared Physical Connection in DbVisualizer

10. From Connection tab, choose Connect to Redshift. You’re redirected to the browser to sign in with your IdP credentials. After entering the credential, It will prompt you with a notification. Choose Allow access.
    

    Figure 23: Allow Amazon Redshift JDBC Driver to access your data

11. If authentication is successful, log in to be redirected to a page showing the connection as successful and on DbVisualizer you will  also will see green tick mark on Redshift connection.
    

    Figure 24: Successful Connection message

12. With this connection, run the following query to test you are logged in as federated user and also to show which role federated user is part of.

    select current_user,* from pg_get_session_roles() eff_ro(name name, roleid integer);

    Below query show ethan logged in as federated user and he is part of awsidc:awssso-sales role.

    

    Figure 25: SQL showing current_user and associated database roles

Congratulations! You have integrated Amazon Redshift using DbVisualizer with AWS IAM Identity Center.

Set up DBeaver

To set up DBeaver, complete the following steps:

1. Go to Database and choose Driver Manager.
2. Search for Redshift, then choose Next.
   

   Figure 26: DBeaver Redshift Connection

3. You can connect by:
   1. Host:
      1. Host/Instance – Enter Redshift endpoint.
      2. Database – Enter Database name which you want to connect.
      3. Port  – Enter port. e.g. 5439
   2. Choose URL and enter jdbc:redshift://<cluster endpoint>:<port>:<databasename>.
      For e.g., jdbc:redshift://test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com:5439/dev
      In this blog post, we are using URL option.
      

      Figure 27: DBeaver Connect using URL

4. Choose Driver Settings and on the Libraries tab, choose Add File. Keep only one set of the latest driver version (2.1.0.30 and upwards) and if you see any older versions, delete those files.
5. Add all the files from the downloaded AWS JDBC driver pack .zip file and choose OK (remember to unzip the .zip file).
6. Click Driver properties tab, add the following properties:
   1. issuer_url – you can either use the Issuer URL or AWS access portal URL from your IAM Identity center dashboard as shown below. In this example, we will use the issuer url.
   2. plugin_name – com.amazon.redshift.plugin.BrowserIdcAuthPlugin
   3. idc_region – enter the region of your IAM Identity center.
      

      Figure 28: DBeaver Driver Properties

7. Click SSL tab and select Use SSL. Otherwise, you may get an error like Authentication must use an SSL connection.
   

   Figure 29: Enable SSL

8. You can verify the connection by choosing Test Connection. You’re redirected to the browser to sign in with your IdP credentials. After entering the credential, It will prompt you with following notification. Choose Allow access.
9. If authentication is successful, you will get similar notification showing the connection as successful.
   

   Figure 30: DBeaver Successful Connection

Congratulations! You have integrated Amazon Redshift using DBeaver with AWS IAM Identity Center.

Note: DBeaver by default uses separate connection for SQL Editor and/or metadata read. You have the option to disable:

• separate connection for metadata read globally in Preferences→Connections→Metadata or per each connection in Connection configuration→Metadata.
• separate connection for each editor in Preferences→Editors→SQL Editor and Connection configuration→SQL Editor and Connection configuration→SQL Editor.

Step 11: Troubleshooting

• If you get the following error:

  ERROR: registered identity provider does not exist for "<idc-namespace:redshift_database_role_name"

  This means that you are trying to create a role with a wrong namespace. Please check current namespace using the command select * from svv_identity_providers;

• If you get below error:

  ERROR: (arn:aws:iam::123456789012:role/<iam_role_name>) Insufficient privileges to access AWS IdC. [ErrorId: 1-1234cf25-1f23ea1234c447fb12aa123e]
  

  This means that an IAM role doesn’t have sufficient privileges to access to the IAM Identity Center. Please follow the above section “Create IAM Amazon Redshift access policy” to update permissions in policy.

• If you get below error:

  FATAL: Invalid scope. User's credentials are not authorized to connect to Redshift. [SQL State=28000]

  Please make sure that the user and group are added to the Amazon Redshift IAM Identity Center application.

• Your internet browser’s security and privacy settings, particularly those that control secure cookie settings, such as Firefox’s Total Cookie Protection feature, can result in blocked connection attempts from Query Editor V2 to your Redshift database. Please follow below steps to remediate this on your respective browser:
  • For Firefox, currently 3rd party cookies are blocked by default. You will have to perform a one-click toggle on the “shield” symbol left of the address bar to disable Enhanced Tracking Protection only for the QEv2 website.
    
  • For Chrome incognito mode, you can click on the “eye” symbol right to the address bar to allow third party cookies for Amazon Redshift Query Editor V2.Note: By default Chrome Incognito mode blocks third party cookies and if you have changed that setting to allow cookies then you may not see the “eye” symbol on the address bar. Below is the default setting of Chrome Incognito mode using which you will see the “eye” symbol on Amazon Redshift Query Editor V2.
    

    Figure 31: Default setting of Chrome Incognito mode

    

Note: If you still see database couldn’t be listed error then:

• Delete your existing connection on Redshift Query Editor V2
• Close all Redshift Query Editor V2 sessions
• Re-open Query EditorV2 from IAM IDC console.
• Authenticate using IAM IDC option. (Make sure cookies are allowed as per above section).

For more troubleshooting guidance on Amazon Redshift Query Editor V2 using AWS IAM Identity Center authentication- refer to  https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-idp-connect-troubleshooting.html

Clean up

Complete the following steps to clean up your resources:

1. Delete the IdP Applications which you have created to integrate with IAM Identity Center.
2. Delete IAM Identity Center configuration.
3. Delete the Redshift application and the Redshift provisioned cluster which you have created for testing.
4. Delete the IAM role which you have created for IAM Identity Center and Redshift integration.
5. Delete the Permission set from IAM Identity Center which you have created for Redshift Query Editor V2 in the management account.

Conclusion

In this post, we showed you a detailed walkthrough of how you can integrate your preferred IdP with the IAM Identity Center and Amazon Redshift Query Editor version 2 and SQL Client to simplify your SSO setup. This integration allows you to use role-based access control with Amazon Redshift. We encourage you to try out this integration. To learn more about IAM Identity Center with Amazon Redshift, see:

• • AWS Documentation: Connect Redshift with IAM Identity Center to give users a single sign-on experience
  • AWS blogs:
    • Simplify access management with Amazon Redshift and AWS Lake Formation for users in an External Identity Provider
    • Integrate Tableau and Okta with Amazon Redshift using AWS IAM Identity Center
    • Integrate Tableau and Microsoft Entra ID with Amazon Redshift using AWS IAM Identity Center
    • Set up cross-account AWS Glue Data Catalog access using AWS Lake Formation and AWS IAM Identity Center with Amazon Redshift and Amazon QuickSight

About the Authors

Debu Panda is a Senior Manager, Product Management at AWS. He is an industry leader in analytics, application platform, and database technologies, and has more than 25 years of experience in the IT world.

Maneesh Sharma is a Senior Database Engineer at AWS with more than a decade of experience designing and implementing large-scale data warehouse and analytics solutions. He collaborates with various Amazon Redshift Partners and customers to drive better integration.

Harshida Patel is a Principal Solutions Architect, Analytics with AWS.

Praveen Kumar Ramakrishnan is a Senior Software Engineer at AWS. He has nearly 20 years of experience spanning various domains including filesystems, storage virtualization and network security. At AWS, he focuses on enhancing the Redshift data security.

Karthik Ramanathan is a Sr. Software Engineer with AWS Redshift and is based in San Francisco. He brings close to two decades of development experience across the networking, data storage and IoT verticals prior to Redshift. When not at work, he is also a writer and loves to be in the water.