Post Syndicated from Ramkumar Nottath original https://aws.amazon.com/blogs/big-data/accelerating-sql-analytics-with-amazon-redshift-mcp-server/

As data analysts and engineers, we often find ourselves switching between multiple tools to explore database schemas, understand table structures, and execute queries across different Amazon Redshift data warehouses. Using natural language to explore metadata and data can simplify this process, but an AI agent often needs the additional context of your Redshift cluster configurations and schemas to successfully discover and build the best execution path.

This is where the Model Context Protocol (MCP) can act as a bridge between the AI agent and your Redshift clusters to provide the necessary information to better support natural language interfaces to your data. MCP is an open standard that enables AI applications to securely connect to external data sources and tools, providing them with rich, real-time context about your specific environment. Unlike static tools, MCP allows AI agents to dynamically discover database structures, understand table relationships, and execute queries with full awareness of your Amazon Redshift setup.

To address these challenges and unlock the full potential of conversational data analysis, Amazon Web Services (AWS) released the Amazon Redshift MCP server, an open source solution that innovates how you interact with Amazon Redshift data warehouses. The Amazon Redshift MCP server integrates seamlessly with Amazon Q Developer command line interface (CLI), Claude Desktop, Kiro, and other MCP-compatible tools. It can enable discover, explore, and analyze your Amazon Redshift metadata and data through natural language conversations with an AI assistant that truly understands your database environment.

In this post, we walk through setting up the Amazon Redshift MCP server and demonstrate how a data analyst can efficiently explore Redshift data warehouses and perform data analysis using natural language queries.

What is the Amazon Redshift MCP Server?

The Amazon Redshift MCP server is a MCP implementation that provides AI agents with safe, structured access to Amazon Redshift resources. It enables:

• Cluster discovery – Automatically discover both provisioned Redshift clusters and serverless workgroups
• Metadata exploration – Browse databases, schemas, tables, and columns through natural language
• Safe query execution – Execute SQL queries in READ ONLY mode with built-in safety protections
• Multi-cluster support – Work with multiple clusters and workgroups simultaneously for data reconciliation tasks

The MCP server acts as a bridge between Amazon Q CLI and your Amazon Redshift infrastructure, translating natural language requests into appropriate API calls and SQL queries. The following diagram illustrates the high-level architecture.

The following video demonstrates the solution outlined in this post.

Prerequisites

Before you begin, ensure you have the following:

System requirements

• Python 3.10 or newer
• uv package manager (installation guide)
• Amazon Q CLI or other tools such as Claude Desktop installed and configured

AWS requirements

• AWS credentials configured using AWS Command Line Interface (AWS CLI), environment variables, or AWS Identity and Access Management (IAM) roles
• Appropriate IAM permissions for Amazon Redshift access
• At least one Redshift cluster or serverless workgroup

Required IAM permissions

The user identity needs the following IAM permissions in your access policies:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeClusters",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:GetWorkgroup",
        "redshift-data:ExecuteStatement",
        "redshift-data:BatchExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult"
      ],
      "Resource": "*"
    }
  ]
}

Installation and configuration

The following section covers the steps required to install and configure Amazon Redshift MCP server.

Install required dependencies

Complete the following steps to install required dependencies:

1. Install the uv package manager if you haven’t already:

# macOS/Linux
curl -LsSf https://astral.sh/uv/install.sh | sh

# Windows
powershell -c "irm https://astral.sh/uv/install.ps1 | iex"

2. Install Python 3.10 or newer:

uv python install 3.10

Configure the MCP server

The MCP server can be configured using several MCP supported clients. In this post we discuss the steps using Amazon Q Developer CLI and Claude Desktop.Complete the following instructions to set up Amazon Q Developer CLI on your host machine and access the Amazon Redshift MCP Server:

1. Install the Amazon Q Developer CLI.
2. Configure the Amazon Redshift MCP server in your Amazon Q CLI configuration. Edit the MCP configuration file at ~/.aws/amazonq/mcp.json:

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "command": "uvx",
      "args": ["awslabs.redshift-mcp-server@latest"],
      "env": {
        "AWS_PROFILE": "default",
        "AWS_REGION": "us-east-1",
        "FASTMCP_LOG_LEVEL": "INFO"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

For further details on installation, refer to the Installation section in the Amazon Redshift MCP server README.md.

3. Start Amazon Q CLI to verify the MCP server is properly configured:

q chat

/tools

You should notice the Amazon Redshift MCP server initialize successfully in the startup logs.To set up Amazon Q Developer CLI on your host machine and access the Amazon Redshift MCP Server using Claude Desktop, complete the following steps:

4. Download and install Claude Desktop for your operating system
5. Open Claude Desktop and in the bottom left, choose the gear icon to navigate to Settings
6. Choose the Developer tab and configure your MCP server by adding the same configuration as step 3 in the Amazon Q CLI setup
7. Restart Claude Desktop to activate the MCP server connection
8. Test the integration by starting a new conversation and asking: Show me all available Redshift clusters

Use case: Customer purchase analysis

Imagine a practical scenario where a data analyst needs to explore customer purchase data across multiple Redshift clusters. The following walkthrough demonstrates how the MCP server simplifies this workflow.As a data analyst at an ecommerce company, you need to:

1. Discover available Redshift clusters
2. Explore the database structure to find customer and sales data
3. Analyze customer purchase patterns
4. Generate insights for the business team

To accomplish these tasks, you follow these steps:

5. Ask Amazon Q to show you available Amazon Redshift resources:

Show me all available Redshift clusters

Amazon Q will use the MCP server to discover your clusters and provide details such as cluster identifiers and types (provisioned or serverless), current status and availability, connection endpoints and configuration, and node types and capacity information.

6. Explore the database structure to understand your data organization:

What databases and tables are available in the analytics-cluster?

Amazon Q will use the MCP server to systematically explore the objects in the cluster:

7. Before analyzing data, understand the table schemas:

Show me the structure of the customers and orders tables in analytics-cluster

Amazon Q will use the MCP server to will examine the table columns and provide detailed schema information.

8. Analyze customer purchase patterns using natural language queries:

Analyze customer purchase pattern from analytics cluster. Show me the top 10 customers by total purchase amount and their buying frequency

Amazon Q will use the MCP server to run the appropriate SQL queries and provide insights.

9. The MCP server supports analyzing data across multiple clusters:

Compare customer acquisition costs between the analytics-cluster and marketing-cluster data.

Amazon Q will use the MCP server to run the appropriate SQL queries compare the data across analytics-cluster and marketing-cluster.

Best Practices

The MCP server comes equipped with several essential safety protections designed to safeguard your data and system performance. The READ ONLY mode serves as a critical safeguard against unintended data modifications, and we recommend enabling this feature when applicable to your use case. To further enhance security, the server implements query validation mechanisms that scrutinize operations for potential harmful impacts, with user-in-loop validation being recommended for optimal safety. For resource management, the server enforces resource limits to prevent performance-impacting runaway queries, again benefiting from user-in-loop validation for best results. In terms of accessibility, the MCP capability maintains broad availability across all AWS Regions where Amazon Redshift Data API is supported, with throttling limits aligned to existing Amazon Redshift Data API service quotas to ensure consistent performance and reliability.For best results, follow these recommendations:

1. Start with discovery – Begin by exploring cluster and database structure and tables
2. Use natural language – Describe what you want to analyze rather than writing SQL directly
3. Iterate gradually – Build complex analyses step by step
4. Verify results – Cross-check important findings with business stakeholders
5. Document insights – Save important queries and results for future reference

Conclusion

The Amazon Redshift MCP server transforms how data analysts interact with Redshift clusters by enabling natural language data exploration and analysis through agentic tooling like Kiro and Amazon Q CLI. By eliminating the need to manually write SQL queries and navigate complex database structures, analysts can focus on generating insights rather than wrestling with syntax and schema discovery.Whether you’re performing a one-time analysis, generating regular reports, or exploring new datasets, the Amazon Redshift MCP server provides a powerful, intuitive interface for your data analysis workflows.Ready to get started? Here’s what to do next:

1. Install the MCP server following the configuration steps in this post
2. Explore your Amazon Redshift environment using natural language queries
3. Start with simple analyses and gradually build complexity
4. Share insights with your team using the natural language summaries
5. Provide feedback to help improve the MCP server capabilities

Check out these blog posts to help you navigate using natural language with your use cases:

• Implementing conversational AI for S3 Tables using Model Context Protocol (MCP)
• Accelerating development with the AWS Data Processing MCP Server and Agent
• Introducing MCP Server for Apache Spark History Server for AI-powered debugging and optimization
• Billing and Cost Management MCP Server
• Introducing enhanced AI assistance in Amazon SageMaker Unified Studio: Agentic chat, Amazon Q Developer CLI, and MCP integration

About the authors

Post Syndicated from Rohit Vashishtha original https://aws.amazon.com/blogs/big-data/integrate-tableau-and-pingfederate-with-amazon-redshift-using-aws-iam-identity-center/

The series of posts on single sign-on to Amazon Redshift with AWS IAM Identity Center (successor to AWS Single Sign-On) integration continues from our prior post.

In this post, we outline a comprehensive guide for setting up single sign-on from Tableau desktop to Amazon Redshift using integration with IAM Identity Center and PingFederate as the identity provider (IdP) with an LDAP based data store, AWS Directory Service for Microsoft Active Directory.

Prerequisites

You should have the following prerequisites:

1. A PingFederate account that has an active subscription. You need an admin role to set up the application on PingFederate. If you’re new to PingFederate, you can reach out to Ping Identity Sales.
2. A working PingFederate server.
3. Amazon Redshift Serverless workgroup or a provisioned Amazon Redshift data warehouse.
4. Download and install the latest Redshift ODBC 2.X driver.
5. Download and install Tableau Desktop 2024.1 or later
6. Install Tableau Server 2023.3.9 or later. For Tableau Server installation, see Install and Configure Tableau Server.

Solution overview

PingFederate instance connects to IAM Identity Center using SAML. The users and groups in PingFederate are synced to IAM Identity Center using an open standard SCIM. After you set up SAML and SCIM, you will be able to enable single sign-on to Amazon Redshift from the AWS Management Console using Amazon Redshift Query Editor v2. This is achieved by creating an Identity Center application in the Amazon Redshift console.

To enable single sign-on to Amazon Redshift from outside of AWS using a third-party client like Tableau, you set up a trusted token issuer token exchange using OIDC standard.

Figure 1 : Solution overview for Tableau integration with Amazon Redshift using IAM Identity Center and Ping Federate

The workflow, shown in the preceding figure, includes the following steps:

1. The user configures Tableau to access Amazon Redshift using IAM Identity Center authentication.
2. On a user sign-in attempt, Tableau initiates a browser-based OAuth flow and redirects the user to the PingFederate sign in page to enter the sign-in credentials. Password validation is done against the AWS Managed Microsoft AD data store.
3. On successful authentication, PingFederate issues an authentication token (ID and access token) to Tableau.
4. The Amazon Redshift driver then makes a call to the Amazon Redshift-enabled Identity Center application and forwards the access token.
5. Amazon Redshift passes the token to Identity Center and requests an access token.
6. Identity Center verifies the token using the OIDC discovery connection to the trusted token issuer and returns an Identity Center-generated access token for the same user. In the preceding figure, trusted token issuer (TTI) is the PingFederate server that Identity Center trusts to provide tokens that third-party applications like Tableau use to call AWS services.
7. Amazon Redshift then uses the token to obtain the user and group membership information from Identity Center.
8. Tableau user will be able to connect with Amazon Redshift and access data based on the user and group membership returned from Identity Center. The user and group settings in the LDAP-based AWS Managed Microsoft AD data store for PingFederate are propagated to identity center using SCIM protocol for outbound provisioning.

Walkthrough

In this walkthrough, you will use the following steps to build the solution:

1. SAML and SCIM set up between PingFederate and IAM Identity Center
2. Connect to Amazon Redshift using Query Editor v2
3. Configure identity federation from a third-party client
   1. Create an access token manager and access token mapping
   2. Create an OIDC policy
   3. Create an OAuth client
   4. Set up a PingFederate Authorization Server
   5. Policy Contract Grant Mapping
   6. Collect PingFederate information
   7. Set up a trusted token issuer in IAM Identity Center
   8. Set up client connections and trusted token issuers in Amazon Redshift
   9. Configure Tableau OAuth config files for PingFederate to integrate with Amazon Redshift using IAM Identity Center
   10. Install a Tableau OAuth config file on a client machine for Tableau Desktop
   11. Install a Tableau OAuth config file for a site on Tableau Server or Tableau Cloud
   12. Federate to Amazon Redshift from Tableau Desktop using Identity Center
   13. Federate to Amazon Redshift from Tableau Server using Identity Center authentication

SAML and SCIM set up between PingFederate and IAM Identity Center

IAM Identity Center integration with PingFederate starts with SAML set up followed by SCIM.

1. Set up SAML 2.0 for SP Connection of type Browser SSO (single sign-on) in PingFederate.
2. Set up SCIM 2.0 for outbound provisioning. It will sync the users and groups created in an LDAP based data store like AWS managed Microsoft AD for PingFederate to the users and groups in IAM Identity Center.

The implementation for the cloud based IdP option PingOne is not in scope of this post and follows steps similar to those described in Integrate IdP with Amazon Redshift Query Editor v2 using AWS IAM Identity Center for seamless Single Sign-On.

Further details of SAML and SCIM set up are as follows.

• 1. Install PingFederate Server.
  2. Set up IAM Identity center integration by following the Ping documentation including the download for Identity Center integration files.
     1. Deploy the integration files to your PingFederate installation.
     2. Enable provisioning and configure IdP Browser SSO (SAML connection). (You can implement Browser SSO connection only using IAM Identity Center metadata file.)
        1. Under System > Server > Protocol Settings > Federation Info BASE_URL field, use the publicly accessible fully qualified domain name of the PingFederate server.
        2. Create an LDAP based data store (the name used in this example is AWSManagedMSAD) because SCIM 2.0 protocol for outbound provisioning only works with LDAP based data stores with PingFederate. If you are using a cloud-based solution like PinOne, you can set up outbound provisioning in PingOne itself. Thus for this writing, we have used AWS Managed Microsoft AD as a data store created using AWS Directory Service.
        3. Create a password credential validator (name used in this example is awsmanagedmsadpassval) and IdP adapters (name used in this example is awsmanagedmsadadapter) for your data store as applicable.
        4. Create an SP connection of type Browser SSO using the sp-saml-metadata.xml file as explained in creating a provisioning connection.
     3. Export SAML metadata from PingFederate.
     4. Register PingFederate as an IdP in Identity Center.
     5. Navigate back to the connection saved in step b, and configure outbound provisioning.
  3. Enable provisioning in IAM Identity Center by following step 1 in the documentation.
  4. Then, configure provisioning in PingFederate by following step 2 in the documentation.
  5. Optionally, you can configure and pass user attributes from PingFederate for access control in Identity Center.

Next, connect to Amazon Redshift using its native query editor, Query Editor v2, to validate AWS services’ connectivity using IAM Identity Center.

Connect to Amazon Redshift using Query Editor v2

Complete the Walkthrough section of IAM Identity Center integration with Amazon Redshift, which will set up your Amazon Redshift connectivity with Query Editor v2.

If you need further help with SAML and SCIM set up, and connecting to Amazon Redshift using Query Editor v2, you can also follow step by step guided demo video single sign-on to Amazon Redshift with IAM IDC integration using PingFederate with AWS Managed MSAD Demo

Configure identity federation from a third-party client

Configure identity federation enabled by IAM Identity Center from IdP PingFederate to the service provider Amazon Redshift using an external client like Tableau. The following steps in the PingFederate admin console and Identity Center guide you through the identity federation process.

Create an access token manager and access token mapping

To map PingFederate attributes to OAuth access tokens and OpenID Connect ID (OIDC) tokens, create an access token manager and token mapping. For complete details and set up based on your security needs, see Token mapping in PingFederate, which explains access token management in detail. Complete the following steps to create a token manager.

1. In the PingFederate administrative console, go to Applications > OAuth > Access Token Management, and choose Create New Instance.
2. In Type tab,
   1. Enter an Instance Name and Instance ID of your choice, for example TrustedTokenIssuerMgr.
   2. Select the Type from drop down list as JSON Web Tokens, commonly called JWT.
   3. Leave Parent instance as None and choose Next.
3. In Instance configuration tab,
   1. Under Certificates, select Add a new row to ‘Certificates’, select the certificate for token manager from the drop-down list, enter a Key ID such as certkey, and choose Update under Action. You can create a new certificate by navigating to Security > Certificate & Key Management > Signing & Decryption Keys & Certificates > Create New.
   2. Select Use Centralized Signing Key.
   3. In JWS Algorithm, select RSA using SHA-256.
   4. Select Enable Token Revocation. Leave everything else as default and choose Next.
4. Under Session Validation tab,
   1. Select Include Session Identifier in Access Token.
   2. Select Check for valid authentication session.
   3. Leave other choices as is and choose Next.
5. In the Access Token Attribute Contract tab, leave the Subject Attribute Name as the e default and proceed to Extend the Contract to add the following attribute and values.
   1. Enter aud, leave multi-value unchecked. Choose Add under Action.
   2. Repeat the same to enter email, exp, iss, sub. When completed, choose Next.
6. On each of Resource URIs and Access Control tabs, leave as is and choose Next.
7. On the Summary tab, review your changes and choose Save. An instance name with the name you provided, like TrustedTokenIssuerMgr appears in Applications > Oauth > Access Token Management.

Figure 2 : Access Token Management Configuration Summary

8. Navigate to Applications > OAuth > Access Token Mappings, select the default Context and Access Token Manager, TrustedTokenIssuerMgr that was created in the previous step. Choose Add Mapping.
9. Leave Attribute Sources & User Lookup as is and choose Next.
10. Under Contract Fulfillment tab,
    1. For Contract aud, select Text from the Source, and enter the Value as AWSIdentityCenter.
    2. For Contract email, select Persistent Grant from the Source, and Value as email.
    3. For Contract exp, select Persistent Grant from the Source, and Value as EXPIRES_AT.
    4. For Contract iss, select Text from the Source, and enter your base URL as the Value, like https://yourwebsite.domain.com, the same as in System > Server > Protocol Settings > BASE URL.
    5. For Attribute Contract sub, select Persistent Grant from the Source, and Value as USER_KEY.
    6. Choose on Next.
11. Leave Issuance Criteria as is and choose Next.
12. On the Summary tab, review all your changes and choose Save. A new default Context with Access Token Manager if TrustedTokenIssuerMgr appears in Applications > OAuth > Access Token Mappings.

Figure 3: Access Token Mappings Summary

Create an OIDC policy

For complete details and set up based on your security needs, see to Open ID connect (OIDC) policy management in PingFederate. Complete the following steps to set up an OIDC policy.

1. In the PingFederate administrative console, go to Applications > OAuth > OpenID Connect Policy Management, and choose Add Policy.
2. In the Manage Policy tab,
   1. Enter the Policy ID and Name of your choice, for example OIDCPolicy.
   2. Select the Access Token Manager from drop down list created in the previous section—TrustedTokenIssuerMgr.
   3. Select Include Session Identifier in ID Token
   4. Select Include User Info in ID Token
   5. Select Return ID Token on Refresh Grant
   6. Leave others as is and choose Next.
3. In the Attribute Contract tab, keep only the required attributes in extended contract and delete the others.
   1. Leave the sub attribute under Attribute Contract as is.
   2. Under Extend the contract, choose delete for all attributes except email. choose Next.
4. In the Attribute Scopes tab,
   1. Select openid from the Scope list.
   2. Select email from Attributes.
   3. Choose Add from Actions. Choose Next.
5. Leave Attribute Sources & User Lookup as is and choose Next.
6. In Contract Fulfillment tab,
   1. For Attribute Contract email, select Persistent Grant from the Source, and Value as email.
   2. For Attribute Contract sub, select Persistent Grant from the Source, and Value as USER_KEY.
   3. Choose Next.
7. Leave Issuance Criteria as is and choose Next.
8. On the Summary tab, review your changes and choose Save. A policy ID with the name you provided, like OIDCPolicy, appears in Applications > Oauth > OpenID Connect Policy Management.

Figure 4 : OpenID Connect Policy Management Summary

Create OAuth client

For complete details and set up based on your security needs, see configure an OAuth client in PingFederate, which explains each field in detail. Complete the following steps to create an OAuth client.

1. In the PingFederate administrative console, go to Applications > OAuth > Clients, and choose Add Client.
2. In the Client ID field, enter a unique, immutable client ID. We use tableauredshiftpingfed as the name in this example.
3. Enter a Name and Description for the client.
4. Select a Client Authentication method. You can select from None, Client TLS Certificate, Private Key JWT, or Client Secret. For this scenario, select Client Secret. Choose Generate Secret to create a new one or use select Change secret to create your own.
5. Leave Request object signing algorithm set to Allow Any. You can override to use the algorithm of your choice if needed.
6. In the Redirect URIs field, add each of the following values.
   1. http://localhost:8080/authorization-code/callback
   2. http://localhost:55556/Callback
   3. http://localhost:55557/Callback
   4. http://localhost:55558/Callback
   5. http://localhost/auth/add_oauth_token
7. Select Restrict common scopes. Restrict scopes by selecting the checkboxes for email, offline_access, openid, and profile as required.
8. In Logo URL, optionally enter the URL for logo you want to display on the User Grant Authorization and Revocation pages.
9. In the Allowed Grant Types list, you can choose from a list of authorization options. In this example, select Authorization code. Optionally, you can select Implicit, Refresh Token, and Client Credentials.
10. Under Default access token manager, select the access token manager TrustedTokenIssuerMgr created in the earlier section.
11. Select the Restrict box for Restrict to default access token manager.
12. Customize Persistent grants max lifetime to match your requirements. Set it to 12 hours for this example by using the third radio button.
13. For Openid connect, choose your preferred ID token signing algorithm. Select RSA using SHA-256 for this example. Optionally, for Policy you can choose the OIDC policy created in the earlier section.
14. Leave the remaining settings as default and choose Save.

Figure 5 : OAuth Client Configuration

The Tableau Desktop redirect URLs should always use localhost. The following example, also use localhost for the Tableau Server hostname to simplify testing in a test environment. For this setup, you should also access the server at localhost in the browser. In a production environment, or Tableau Cloud, you should use the full hostname that your users will use to access Tableau on the web, along with HTTPS. If you already have an environment with HTTPS configured, you can skip the localhost configuration and use the full hostname from the start.

Set up a PingFederate authorization server

For complete details and set up based on your security needs, see PingFederate authorization server settings in PingFederate. Complete the following steps to configure an authorization server.

1. In the PingFederate administrative console, go to System > OAuth Settings > Authorization Server Settings, and make following changes.
2. Leave the initial configurations as default and scroll down to Persistent Grant Extended Attributes, add Attribute email.
3. For OAuth Administrative Web Services Settings, in Password Credential Validator, select awsmanagedmsadpassval that you created in the SAML and SCIM set up section.
4. For Persistent Grant Management API,
   1. In Access Token Manager, select the TrustedTokenIssuerMgr created earlier.
   2. In Required Scope, select openid.
5. Leave remaining the settings as default and choose Save.

Figure 6 : PingFederate Authorization Server Setting

Policy contract grant mapping

For complete details and set up based on your security needs, see Grant contract mapping in PingFederate. For this illustration, we set up a policy contract grant mapping for authentication in a three-step process.

Step 1: Create a policy contract

1. In the PingFederate administrative console, go to Authentication > Policies > Policy Contracts, and choose Create New Contract.
2. In Contract Info tab, enter a name. For this example, we use OIDCPolicyContract.
3. In Contract Attributes tab, choose Extend the Contract to add email attribute.
4. Review and choose Save.

Figure 7 : Policy Contract Summary

Step 2: Add authentication policy

1. In the PingFederate administrative console, go to Authentication > Policies > Policies, and choose Add Policy.
2. Enter a policy name. In this example, we use OAuthOIDCPolicy.
3. In the Policy drop down, select IdP Adapter and select the awsmanagedmsadadapter that you created in the SAML and SCIM set up section.
4. Set FAIL to Done and under SUCCESS, select Policy Contracts from the drop-down menu and select the OIDCPolicyContract created in step 1. Choose Done.

Figure 8 : Authentication Policy Configuration

Step 3: Policy contract grant mapping

1. In the PingFederate administrative console, go to Authentication > OAuth > Policy Contract Grant Mapping, and under Mappings, select OIDCPolicyContract created in Step1 and choose Add Mapping.
2. On the Attribute Sources & User Lookup tab, choose Next.
3. In the Contract Fulfillment tab,
   1. For Contract USER_KEY, pick Authentication Policy Contract from the Source, and Value as subject.
   2. For Contract USER_NAME, pick Authentication Policy Contract from the Source, and Value as subject.
   3. For Contract email, pick Authentication Policy Contract from the Source, and Value as email.
   4. Choose Next.
4. Leave Issuance Criteria as is, review and choose Save.

Figure 9 : Policy Contract Grant Mapping Summary

Collect PingFederate information

To configure your PingFederate with IAM Identity Center and Amazon Redshift, collect the following parameters. If you don’t have these parameters, contact your PingFederate admin.

1. Issuer URL, auth URL (authUri), and token URL (tokenUri).

You can get these values from the OIDC IdP URL: https://pingfedserver.example.com/.well-known/openid-configuration. Open this URL in a web browser, replacing pingfedserver.example.com with your IdP server name.

The following is an example screenshot of IdP attributes using OIDC IdP URL where:

• The issuer URL corresponds to the issuer
• The auth URL (authUri) corresponds to authorization_endpoint
• The token URL (tokenUri) corresponds to token_endpoint

Figure 10 : Screenshot of IdP Attributes

2. Audience value

To get the Audience value from PingFederate, sign in as an admin to PingFederate and navigate to the following path to get the audience value that you created during access token mapping creation in PingFederate:

Applications > OAuth > Access Token Mappings > TrustedTokenIssuerMgr → Summary > aud

Figure 11 : Access Token Mapping

Set up a trusted token issuer in IAM Identity Center

Switch from the PingFederate console to the IAM Identity Center console for the AWS side of configuration. Start by adding a trusted token issuer (TTI), which makes it possible to authorize Tableau to make requests on behalf of their users to access data in Amazon Redshift. A TTI is an OAuth 2.0 authorization server that issues tokens to applications that initiate requests (requesting applications). The tokens authorize these applications to initiate requests on behalf of their users to a receiving application (an AWS service). In this step, you create a TTI in the central management account. To create a TTI,

1. Open the AWS Management Console and navigate to IAM Identity Center, and then to the Settings page.
2. Select the Authentication tab and under Trusted token issuers, choose Create trusted token issuer.
3. On the Set up an external IdP to issue trusted tokens page, under Trusted token issuer details, do the following:
   • For Issuer URL, enter the OIDC discovery URL of the external IdP that will issue tokens for trusted identity propagation. You can get issuer the URL as mentioned in step 1 of the preceding section Collect PingFederate information.
4. For Trusted token issuer name, enter a name to identify this TTI in Identity Center and in the application console.
5. Under Map attributes, do the following:
   1. For the identity provider attribute, select an attribute from the list to map to an attribute in the Identity Center identity store. You can select Email, Object Identifier, Subject, and Other.
   2. For Identity Center attribute, select the corresponding attribute for the attribute mapping.
6. Under Tags (optional), choose Add new tag, enter a value for Key, and optionally for Value. For information about tags, see Tagging AWS IAM Identity Center resources.

The following figure shows the set up for TTI:

Figure 12 : Configuring Trusted Token Issuer

Set up client connections and trusted token issuers in Amazon Redshift

In this step, the Amazon Redshift applications that exchange externally generated tokens must be configured to use the TTI you created in the previous step. Also, the audience claim (or aud claim) from PingFederate must be specified. In this example, you are configuring the Amazon Redshift application in the member account where the Amazon Redshift cluster or serverless instance exists.

1. Select IAM Identity Center connection from the Amazon Redshift console menu.
2. Select the Amazon Redshift application that you created as part of the prerequisites.
3. Select the Client connections tab and choose Edit.
4. Choose Yes under Configure client connections that use third-party IdPs.
5. Select the checkbox for Trusted token issuer that you created in the previous section.
6. Enter the Aud claim value under Configure selected trusted token issuers. For example, AWSIdentityCenter. You can get the audience value from the PingFederate path: Applications > OAuth > Access Token Mappings > TrustedTokenIssuerMgr > Summary > aud.
7. Choose Save.

Figure 13 : Configure Audience Value in Amazon Redshift

At this point, your IAM Identity Center, Amazon Redshift, and PingFederate configuration are complete. Next, you need to configure Tableau.

Configure Tableau OAuth config files for PingFederate to integrate with Amazon Redshift using IAM Identity Center

This XML file used in this section will be used for all the Tableau products like Tableau Desktop, Server and Cloud.

To integrate Tableau with Amazon Redshift using IAM Identity Center, you need to use a custom XML file. In this step, you will use the following XML and replace the values starting with a $ sign and highlighted in bold. The rest of the values can be kept as it is or you can modify them based on your specific needs. For detailed information on each of the elements in the file, see the Tableau documentation on GitHub.

You can get authUri and tokenUri as mentioned in step 1 of preceding section, Collect PingFederate information.

<?xml version="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
  <dbclass>redshift</dbclass>
  <oauthConfigId>custom_redshift_pingfed</oauthConfigId>
  <clientIdDesktop></clientIdDesktop>
  <clientSecretDesktop></clientSecretDesktop>
  <redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
  <redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
  <redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
  <authUri>https://.com/as/authorization.oauth2</authUri>
  <tokenUri>https://.com/as/token.oauth2</tokenUri>
  <scopes>openid</scopes>
  <scopes>email</scopes>
  <scopes>profile</scopes>
  <scopes>offline_access</scopes>
  <capabilities>
    <entry>
      <key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_REQUIRE_PKCE</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_SUPPORTS_STATE</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</key>
      <value>true</value>
    </entry>
  </capabilities>
  <accessTokenResponseMaps>
    <entry>
      <key>ACCESSTOKEN</key>
      <value>access_token</value>
    </entry>
    <entry>
      <key>REFRESHTOKEN</key>
      <value>refresh_token</value>
    </entry>
    <entry>
      <key>id-token</key>
      <value>id_token</value>
    </entry>
    <entry>
      <key>access-token-issue-time</key>
      <value>issued_at</value>
    </entry>
    <entry>
      <key>access-token-expires-in</key>
      <value>expires_in</value>
    </entry>
    <entry>
      <key>username</key>
      <value>email</value>
    </entry>
  </accessTokenResponseMaps>
</pluginOAuthConfig>

The following is the example XML:

<?xml version="1.0" encoding="utf-8"?>
<pluginOAuthConfig>
  <dbclass>redshift</dbclass>
  <oauthConfigId>custom_redshift_pingfed</oauthConfigId>
  <clientIdDesktop>tableauredshiftpingfed</clientIdDesktop>
  <clientSecretDesktop></clientSecretDesktop>
  <redirectUrisDesktop>http://localhost:55556/Callback</redirectUrisDesktop>
  <redirectUrisDesktop>http://localhost:55557/Callback</redirectUrisDesktop>
  <redirectUrisDesktop>http://localhost:55558/Callback</redirectUrisDesktop>
  <authUri>https://pingfedserver.example.com/as/authorization.oauth2</authUri>
  <tokenUri>https://pingfedserver.example.com/as/token.oauth2</tokenUri>
  <scopes>openid</scopes>
  <scopes>email</scopes>
  <scopes>profile</scopes>
  <scopes>offline_access</scopes>
  <capabilities>
    <entry>
      <key>OAUTH_CAP_FIXED_PORT_IN_CALLBACK_URL</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_PKCE_REQUIRES_CODE_CHALLENGE_METHOD</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_REQUIRE_PKCE</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_SUPPORTS_STATE</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_CLIENT_SECRET_IN_URL_QUERY_PARAM</key>
      <value>true</value>
    </entry>
    <entry>
      <key>OAUTH_CAP_SUPPORTS_GET_USERINFO_FROM_ID_TOKEN</key>
      <value>true</value>
    </entry>
  </capabilities>
  <accessTokenResponseMaps>
    <entry>
      <key>ACCESSTOKEN</key>
      <value>access_token</value>
    </entry>
    <entry>
      <key>REFRESHTOKEN</key>
      <value>refresh_token</value>
    </entry>
    <entry>
      <key>id-token</key>
      <value>id_token</value>
    </entry>
    <entry>
      <key>access-token-issue-time</key>
      <value>issued_at</value>
    </entry>
    <entry>
      <key>access-token-expires-in</key>
      <value>expires_in</value>
    </entry>
    <entry>
      <key>username</key>
      <value>email</value>
    </entry>
  </accessTokenResponseMaps>
</pluginOAuthConfig>

Install Tableau OAuth config file on a client machine for Tableau Desktop

After the XML configuration file is created, it should be copied to a specific location to be used by Amazon Redshift Connector from Tableau Desktop. Save the preceding file as .xml and save it under Documents\My Tableau Repository\OAuthConfigs.

Note: Currently this integration is not supported in macOS because the Amazon Redshift ODBC 2.X Driver is not supported yet for MAC.

Install Tableau OAuth config file for a site on Tableau Server or Tableau Cloud

To integrate with Amazon Redshift using IAM Identity Center authentication, you need to install the Tableau OAuth config file in Tableau Server or Tableau Cloud.

1. Sign in to the Tableau Server or Tableau Cloud using admin credentials.
2. Navigate to Settings.
3. Go to OAuth Clients Registry and select Add OAuth Client.
4. Choose the following settings:
   1. Connection type: Select Amazon Redshift.
   2. OAuth Provider: Select Custom_IdP.
   3. Client ID: Enter your IdP client ID value.
   4. Client Secret: Enter your client secret value.
   5. Redirect URL: Enter the value as http://localhost/auth/add_oauth_token. In this post, we are using localhost for testing in the local environment. You should ideally use the full hostname with https.
   6. Choose OAuth Config File: Select the XML file that you configured in Configure Tableau Desktop.
   7. Select Add OAuth Client and choose Save.

Figure 14: Create an OAuth connection in Tableau Server or Cloud

Federate to Amazon Redshift from Tableau Desktop using IAM Identity Center

Now, you’re ready to connect from Tableau and federated sign-in using IAM Identity Center authentication. In this step, you will create a Tableau Desktop report and publish it to Tableau Server.

1. Open Tableau Desktop.
2. Choose Amazon Redshift Connector and enter the following values:
   1. Server: Enter the name of the server that hosts the database and the name of the database you want to connect to.
   2. Port: Enter 5439.
   3. Database: Enter your database name. In this example, we use dev.
   4. Authentication: Select OAuth.
   5. Federation Type: Select Identity Center
   6. Identity Center Namespace: You can leave this blank.
   7. OAuth Provider: This value should automatically be pulled from your configured XML. It will be the value from the element oauthConfigId.
   8. Select checkbox for Require SSL.
3. Choose Sign-In.
4. A browser pop-up will initiate where you will enter your IdP credentials.

Figure 15: Tableau Desktop OAuth connection

5. When authentication is successful, you will see the message Tableau created this window to authenticate. It is now safe to close it.

Figure 16: Successful authentication using Tableau

Congratulations! You are signed in using the IAM Identity Center integration with Amazon Redshift and are ready to explore and analyze your data using Tableau Desktop.

Figure 17: Successful connection using Tableau Desktop

The following is a screenshot from Amazon Redshift system table (sys_query_history) showing that user Ethan from PingFederate is accessing the sales report.

Figure 18: User audit in sys_query_history

Now you can create your own Tableau Report on the desktop version and publish it to your Tableau Server. For the next section, you create and publish a report named Account Level Sales.

Federate to Amazon Redshift from Tableau Server using IAM Identity Center authentication

After you have published the report from Tableau Desktop to Tableau Server, sign in as non-admin user and view the published report using IAM Identity Center authentication.

1. Sign in to the Tableau Server site as a non-admin user.
2. Navigate to Explore and go to the folder where your published report is stored.
3. Select the report and choose Sign In.

Figure 19: Sign In Prompt on Tableau Cloud/Server

4. Enter your PingFederate credentials to the browser pop-up to authenticate.
5. After successful authentication, you can access the data and create reports.

Figure 20: Tableau report

Clean up

Complete the following steps to clean up your resources:

1. Delete the IdP applications that you created to integrate with IAM Identity Center.
2. Delete Identity Center configuration.
3. Delete the Amazon Redshift application and the Amazon Redshift provisioned cluster or Serverless instance that you created for testing.
4. Delete the IAM role and IAM policy that you created for Identity Center and Amazon Redshift integration.
5. Delete the permission set from Identity Center that you created for Amazon Redshift Query Editor v2 in the management account.
6. Clean up resources related to PingFederate.

Conclusion

This post covered streamlining access management for data analytics by using Tableau’s capability to support single sign-on based on the OAuth 2.0 and OIDC protocol. This setup facilitates federated user authentication, where user identities from an external identity provider like PingFederate are trusted and propagated to Amazon Redshift. You walked through the steps to configure Tableau Desktop and Tableau Server to integrate seamlessly with Amazon Redshift using AWS IAM Identity Center for single sign-on. By harnessing this integration of a third-party IdP with IAM Identity Center, analysts can securely access Amazon Redshift data sources within Tableau without managing separate database credentials.

Learn more about Amazon Redshift integration with IAM Identity Center using PingFederate as an identity provider by visiting the following resources.

• Connect Redshift with IAM Identity Center
• IAM Identity Center integration with PingFederate
• Integrate Identity Provider (IdP) with Amazon Redshift Query Editor
• For data lake queries, simplify access management with Amazon Redshift and AWS Lake Formation

About the authors