Authorizing connections from Amazon QuickSight to Amazon Redshift clusters - Amazon QuickSight
Enabling trusted identity propagation with Amazon RedshiftManually enabling access to an Amazon Redshift cluster in a VPCEnabling access to Amazon Redshift Spectrum

Important: We've redesigned the Amazon QuickSight analysis workspace. You might encounter screenshots or procedural text that doesn't reflect the new look in the QuickSight console. We're in the process of updating screenshots and procedural text.

To find a feature or item, use the Quick search bar.

For more information on QuickSight's new look, see Introducing new analysis experience on Amazon QuickSight.

Authorizing connections from Amazon QuickSight to Amazon Redshift clusters

   Applies to: Enterprise Edition and Standard Edition 
   Intended audience: System administrators 

You can provide access to Amazon Redshift data using three authentication methods: trusted identity propagation, run-as IAM role, or Amazon Redshift database credentials.

With trusted identity propagation, a user's identity is passed to Amazon Redshift with single sign-on that is managed by IAM Identity Center. A user that accesses a dashboard in QuickSight has their identity propagated to Amazon Redshift. In Amazon Redshift, fine grained data permissions are applied on the data before the data is presented in a QuickSight asset to the user. QuickSight authors can also connect to Amazon Redshift data sources without a password input or IAM role. If Amazon Redshift Spectrum is used, all permission management is centralized in Amazon Redshift. Trusted identity propagation is supported when QuickSight and Amazon Redshift use the same organization instance of IAM Identity Center. Trusted identity propagation is not currently supported for the following features.

  • SPICE datasets

  • Custom SQL on data sources

  • Alerts

  • Email reports

  • Amazon QuickSight Q

  • CSV, Excel, and PDF exports

  • Anomaly detection

For Amazon QuickSight to connect to an Amazon Redshift instance, you must create a new security group for that instance. This security group contains an inbound rule that authorizes access from the appropriate IP address range for the Amazon QuickSight servers in that AWS Region. To learn more about authorizing Amazon QuickSight connections, see Manually enabling access to an Amazon Redshift cluster in a VPC.

Enabling connection from Amazon QuickSight servers to your cluster is just one of several prerequisites for creating a data set based on an AWS database data source. For more information about what is required, see Creating datasets from new database data sources.

Enabling trusted identity propagation with Amazon Redshift

Trusted identity propagation authenticates the end user in Amazon Redshift when they access QuickSight assets that leverage a trusted identity propagation enabled data source. When an author creates a data source with trusted identity propagation, the identity of the data source consumers in QuickSight is propagated and logged in CloudTrail. This allows database administrators to centrally manage data security in Amazon Redshift and automatically apply all data security rules to data consumers in QuickSight. With other authentication methods, the data permissions of the author who created the data source are applied to all data source consumers. The data source author can choose to apply additional row and column level security to the data sources that they create in Amazon QuickSight.

Trusted identity propagation data sources are supported only in Direct Query datasets. SPICE datasets do not currently support trusted identity propagation.

Prerequisites

Before you get started, make sure that you have all of the required prerequisites ready.

  • Trusted identity propagation is only supported for QuickSight accounts that are integrated with IAM Identity Center. For more information, see Configure your Amazon QuickSight account with IAM Identity Center.

  • An Amazon Redshift application that is integrated with IAM Identity Center. The Amazon Redshift cluster that you use must be in the same organization in AWS Organizations as the QuickSight account that you want to use. The cluster must also be configured with the same organization instance in IAM Identity Center that your QuickSight account is configured to. For more information about configuring a Amazon Redshift cluster, see Integrating IAM Identity Center.

Enabling trusted identity propagation in QuickSight

To configure QuickSight to connect to Amazon Redshift data sources with trusted identity propagation, configure Amazon Redshift OAuth scopes to your QuickSight account.

To add a scope that allows QuickSight to authorize identity propagation to Amazon Redshift, specify the AWS account ID of the QuickSight account and the service that you want to authorize identity propagation with, in this case 'REDSHIFT'.

Specify the IAM Identity Center application ARN of the Amazon Redshift cluster that you are authorizing Amazon QuickSight to propagate user identities to. This information can be found in the Amazon Redshift console. If you don't specify authorized targets for the Amazon Redshift scope, QuickSight authorizes users from any Amazon Redshift cluster that share the same IAM Identity Center instance. The example below configures QuickSight to connect to Amazon Redshift data sources with trusted identity propagation.

aws quicksight update-identity-propagation-config --aws-account-id "AWSACCOUNTID" --service "REDSHIFT" --authorized-targets "arn:aws:sso::XXXXXXXXXXXX:application/ssoins-XXXXXXXXXXXX/apl-XXXXXXXXXXXX" "arn:aws:sso::XXXXXXXXXXXX:application/ssoins-XXXXXXXXXXXX/apl-XXXXXXXXXXXX"

The following example deletes OAuth scopes from a QuickSight account.

aws quicksight delete-identity-propagation-config --aws-account-id "AWSACCOUNTID" --service "REDSHIFT" --authorized-targets "arn:aws:sso::XXXXXXXXXXXX:application/ssoins-XXXXXXXXXXXXapl-XXXXXXXXXXXX "arn:aws:sso::XXXXXXXXXXXX:application/ssoins-XXXXXXXXXXXX/apl-XXXXXXXXXXXX"

The following example lists all OAuth scopes that are currently on a QuickSight account.

aws quicksight list-identity-propagation-configs --aws-account-id "AWSACCOUNTID"

Connecting to Amazon Redshift with trusted identity propagation

Use the procedure below to connect to Amazon Redshift trusted identity propagation.

To connect to Amazon Redshift with trusted identity propagation

  1. Create a new dataset in Amazon QuickSight. For more information about creating a dataset, see Creating datasets.

  2. Choose Amazon Redshift as the data source for the new dataset.

    Note

    The authentication type of an existing data source can't be changed to trusted identity propagation

  3. Choose IAM Identity Center as the identity option for the data source, and then choose Create data source.

Manually enabling access to an Amazon Redshift cluster in a VPC

 Applies to: Enterprise Edition 

Use the following procedure to enable Amazon QuickSight access to an Amazon Redshift cluster in a VPC.

To enable Amazon QuickSight access to an Amazon Redshift cluster in a VPC

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshiftv2/.

  2. Navigate to the cluster that you want to make available in Amazon QuickSight.

  3. In the Cluster Properties section, find Port. Note the Port value.

  4. In the Cluster Properties section, find VPC ID and note the VPC ID value. Choose VPC ID to open the Amazon VPC console.

  5. On the Amazon VPC console, choose Security Groups in the navigation pane.

  6. Choose Create Security Group.

  7. On the Create Security Group page, enter the security group information as follows:

    • For Security group name, enter redshift-security-group.

    • For Description, enter redshift-security-group.

    • For VPC, choose the VPC for your Amazon Redshift cluster. This is the VPC with the VPC ID that you noted.

  8. Choose Create security group.

    Your new security group should appear on the screen.

  9. Create a second security group with the following properties.

    • For Security group name, enter quicksight-security-group.

    • For Description, enter quicksight-security-group.

    • For VPC, choose the VPC for your Amazon Redshift cluster. This is the VPC with the VPC ID that you noted.

  10. Choose Create security group.

  11. After you create the new security groups, create inbound rules for the new groups.

    Choose the new redshift-security-group security group, and input the following values.

    • For Type, choose Amazon Redshift.

    • For Protocol, choose TCP.

    • For Port Range, enter the port number of the Amazon Redshift cluster to which you are providing access. This is the port number that you noted in an earlier step.

    • For Source, enter the security group ID of quicksight-security-group.

  12. Choose Save rules to save your new inbound rule.

  13. Repeat the previous step for quicksight-security-group and enter the following values.

    • For Type, choose All traffic.

    • For Protocol, choose All.

    • For Port Range, choose All.

    • For Source, enter the security group ID of redshift-security-group.

  14. Choose Save rules to save your new inbound rule.

  15. In QuickSight, navigate to the Manage QuickSight menu.

  16. Choose Manage VPC connections, and then choose Add VPC connection.

  17. Configure the new VPC connection with the following values.

    • For VPC connection name, choose a meaningful name for the VPC connection.

    • For VPC ID, choose the VPC in which the Amazon Redshift cluster exists.

    • For Subnet ID, choose the subnet for the Availability Zone (AZ) that is used for Amazon Redshift.

    • For Security group id, copy and paste the security group ID for quicksight-security-group.

  18. Choose Create. It might take several minutes for the new VPC to generate.

  19. In the Amazon Redshift console, navigate to the Amazon Redshift cluster that redshift-security-group is configured to. Choose Properties. underNetwork and security settings, enter the name of the security group.

  20. In QuickSight, choose Datasets, and then choose New dataset. Create a new dataset with the following values.

    • For Data source, choose Amazon Redshift Auto-discovered.

    • Give the data source a meaningful name.

    • The instance ID should auto populate with the VPC connection that you created in QuickSight. If the instance ID doesn't auto populate, choose the VPC that you created from the dropdown list.

    • Enter the database credentials. If your QuickSight account uses trusted identity propagation, choose Single sign-on.

  21. Validate the connection, and then choose Create data source.

If you want to restrict the default outbound rules further, update the outbound rule of quicksight-security-group to allow only Amazon Redshift traffic to redshift-security-group. You can also delete the outbound rule that's located in the redshift-security-group.

Enabling access to Amazon Redshift Spectrum

Using Amazon Redshift Spectrum, you can connect Amazon QuickSight to an external catalog with Amazon Redshift. For example, you can access the Amazon Athena catalog . You can then query unstructured data on your Amazon S3 data lake using an Amazon Redshift cluster instead of the Athena query engine.

You can also combine data sets that include data stored in Amazon Redshift and in S3. Then you can access them using the SQL syntax in Amazon Redshift.

After you've registered your data catalog (for Athena) or external schema (for a Hive metastore), you can use Amazon QuickSight to choose the external schema and Amazon Redshift Spectrum tables. This process works just as for any other Amazon Redshift tables in your cluster. You don't need to load or transform your data.

For more information on using Amazon Redshift Spectrum, see Using Amazon Redshift Spectrum to query external data in the Amazon Redshift Database Developer Guide.

To connect using Redshift Spectrum, do the following:

  • Create or identify an IAM role associated with the Amazon Redshift cluster.

  • Add the IAM policies AmazonS3ReadOnlyAccess and AmazonAthenaFullAccess to the IAM role.

  • Register an external schema or data catalog for the tables that you plan to use.

Redshift Spectrum lets you separate storage from compute, so you can scale them separately. You only pay for the queries that you run.

To connect to Redshift Spectrum tables, you don't need to grant Amazon QuickSight access to Amazon S3 or Athena. Amazon QuickSight needs access only to the Amazon Redshift cluster. For full details on configuring Redshift Spectrum, see Getting started with Amazon Redshift Spectrum in the Amazon Redshift Database Developer Guide.