Resources Amazon KMS key Accessing the query editor v2 Setting up principal tags to connect a cluster or workgroup

Configuring your Amazon Web Services account

You can perform this set of tasks to configure the query editor v2 to query an Amazon Redshift database.With the proper permissions, you can access data in an Amazon Redshift cluster or workgroup owned by your Amazon Web Services account that is in the current Amazon Web Services Region.

The first time an administrator configures query editor v2 for your Amazon Web Services account, they choose the Amazon KMS key that is used to encrypt query editor v2 resources. By default, an Amazon owned key is used to encrypt resources. Alternatively, an administrator can use a customer managed key by choosing the Amazon Resource Name (ARN) for the key in the configuration page.

After configuring an account, Amazon KMS encryption settings can't be changed. For more information about creating and using a customer managed key with query editor v2, see Creating an Amazon KMS customer managed key to use with query editor v2. The administrator can also optionally choose an S3 bucket and path that is used for some features, such as loading data from a file. For more information, see Loading data from a local file setup and workflow.

Amazon Redshift query editor v2 supports authentication, encryption, isolation, and compliance to keep your data at rest and data in transit secure. For more information about data security and query editor v2, see the following:

Amazon CloudTrail captures API calls and related events made by or on behalf of your Amazon Web Services account and delivers the log files to an Amazon S3 bucket that you specify. You can identify which users and accounts called Amazon, the source IP address from which the calls were made, and when the calls occurred. To learn more about how query editor v2 runs on Amazon CloudTrail, see Logging with CloudTrail. For more information about CloudTrail, see the Amazon CloudTrail User Guide.

The query editor v2 has adjustable quotas for some of its resources. For more information, see Quotas for Amazon Redshift objects.

Resources created with query editor v2

Within query editor v2, you can create resources such as saved queries and charts. All resources in query editor v2 are associated with an IAM role or with a user. We recommend attaching policies to an IAM role and assigning the role to a user.

In the query editor v2, you can add and remove tags for saved queries and charts. You can use these tags when setting up custom IAM policies or to search for resources. You can also manage tags by using the Amazon Resource Groups Tag Editor.

You can set up IAM roles with IAM policies to share queries with others in your same Amazon Web Services account in the Amazon Web Services Region.

Creating an Amazon KMS customer managed key to use with query editor v2

To create a symmetric encryption customer managed key:

You can create a symmetric encryption customer managed key to encrypt query editor v2 resources using the Amazon KMS console or Amazon KMS API operations. For instructions about creating a key, see Creating symmetric encryption Amazon KMS key in the Amazon Key Management Service Developer Guide.

Key policy

Key policies control access to your customer managed key. Every customer managed key must have exactly one key policy, which contains statements that determine who can use the key and how they can use it. When you create your customer managed key, you can specify a key policy. For more information, see Managing access to Amazon KMS keys in the Amazon Key Management Service Developer Guide.

To use your customer managed key with Amazon Redshift query editor v2, the following API operations must be allowed in the key policy:

kms:GenerateDataKey – Generates a unique symmetric data key to encrypt your data.
kms:Decrypt – Decrypts data that was encrypted with the customer managed key.
kms:DescribeKey – Provides the customer managed key details to allow the service to validate the key.

The following is a sample Amazon KMS policy for Amazon Web Services account 111122223333. In the first section, the kms:ViaService limits use of the key to the query editor v2 service (which is named sqlworkbench.region.amazonaws.com in the policy). The Amazon Web Services account using the key must be 111122223333. In the second section, the root user and key administrators of Amazon Web Services account 111122223333 can access to the key.

When you create an Amazon Web Services account, you begin with one sign-in identity that has complete access to all Amazon Web Services services and resources in the account. This identity is called the Amazon Web Services account root user and is accessed by signing in with the email address and password that you used to create the account. We strongly recommend that you don't use the root user for your everyday tasks. Safeguard your root user credentials and use them to perform the tasks that only the root user can perform. For the complete list of tasks that require you to sign in as the root user, see Tasks that require root user credentials in the IAM User Guide.


{
    "Version": "2012-10-17",
    "Id": "key-consolepolicy",
    "Statement": [
        {
            "Sid": "Allow access to principals authorized to use Amazon Redshift Query Editor V2",
            "Effect": "Allow",
            "Principal": {
                "AWS": "*"
            },
            "Action": [
                "kms:GenerateDataKey",
                "kms:Decrypt",
                "kms:DescribeKey"
            ],
            "Resource": "*",
            "Condition": {
                "StringEquals": {
                    "kms:ViaService": "sqlworkbench.region.amazonaws.com",
                    "kms:CallerAccount": "111122223333"
                }
            }
        },
        {
            "Sid": "Allow access for key administrators",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::111122223333:root"
            },
            "Action": [
                "kms:*"
            ],
            "Resource": "arn:aws:kms:region:111122223333:key/key_ID"
        }
    ]
}

The following resources provide more information about Amazon KMS keys:

For more information about Amazon KMS policies, see Specifying permissions in a policy in the Amazon Key Management Service Developer Guide.
For information about troubleshooting Amazon KMS policies, see Troubleshooting key access in the Amazon Key Management Service Developer Guide.
For more information about keys, see Amazon KMS keys in the Amazon Key Management Service Developer Guide.

Accessing the query editor v2

To access the query editor v2, you need permission. An administrator can attach one of the following Amazon managed policies to the role to grant permission. (We recommend attaching policies to an IAM role and assigning the role to a user.) These Amazon managed policies are written with different options that control how tagging resources allows sharing of queries. You can use the IAM console (https://console.amazonaws.cn/iam/) to attach IAM policies.

AmazonRedshiftQueryEditorV2FullAccess – Grants full access to the Amazon Redshift query editor v2 operations and resources. This policy also grants access to other required services.
AmazonRedshiftQueryEditorV2NoSharing – Grants the ability to work with Amazon Redshift query editor v2 without sharing resources. This policy also grants access to other required services.
AmazonRedshiftQueryEditorV2ReadSharing – Grants the ability to work with Amazon Redshift query editor v2 with limited sharing of resources. The granted principal can read the resources shared with its team but can’t update them. This policy also grants access to other required services.
AmazonRedshiftQueryEditorV2ReadWriteSharing – Grants the ability to work with Amazon Redshift query editor v2 with sharing of resources. The granted principal can read and update the resources shared with its team. This policy also grants access to other required services.

You can also create your own policy based on the permissions allowed and denied in the provided managed policies. If you use the IAM console policy editor to create your own policy, choose SQL Workbench as the service for which you create the policy in the visual editor. The query editor v2 uses the service name Amazon SQL Workbench in the visual editor and IAM Policy Simulator.

For a principal (a user with an IAM role assigned) to connect to an Amazon Redshift cluster, they need the permissions in one of the query editor v2 managed policies. They also need the redshift:GetClusterCredentials permission to the cluster. To get this permission, someone with administrative permission can attach a policy to the IAM roles used to connect to the cluster by using temporary credentials. You can scope the policy to specific clusters or be more general. For more information about permission to use temporary credentials, see Create an IAM role or user with permissions to call GetClusterCredentials.

For a principal (typically a user with an IAM role assigned) to turn on the ability in the Account settings page for others in the account to Export result set, they need the sqlworkbench:UpdateAccountExportSettings permission attached the role. This permission is included in the AmazonRedshiftQueryEditorV2FullAccess Amazon managed policy.

As new features are added to query editor v2, the Amazon managed policies are updated as needed. If you create your own policy based on the permissions allowed and denied in the provided managed policies, edit your policies to keep them up to date with changes to the managed policies. For more information about managed policies in Amazon Redshift, see Amazon managed policies for Amazon Redshift.

To provide access, add permissions to your users, groups, or roles:

Users managed in IAM through an identity provider:

Create a role for identity federation. Follow the instructions in Creating a role for a third-party identity provider (federation) in the IAM User Guide.
IAM users:
- Create a role that your user can assume. Follow the instructions in Creating a role for an IAM user in the IAM User Guide.
- (Not recommended) Attach a policy directly to a user or add a user to a user group. Follow the instructions in Adding permissions to a user (console) in the IAM User Guide.

Note

If an Amazon IAM Identity Center administrator removes all permission set associations for a particular permission set in the entire account, access to any query editor resources originally associated with the removed permission set are no longer accessible. If later the same permissions are recreated, a new internal identifier is created. Because the internal identifier has changed, access to query editor resources previously owned by a user cannot be accessed. We recommend that before administrators delete a permission set, that users of that permission set export query editor resources such as notebooks and queries as a backup.

Setting up principal tags to connect a cluster or workgroup from query editor v2

To connect to your cluster or workgroup using the federated user option, either set up your IAM role or user with principal tags. Or, set up your identity provider (IdP) to pass in RedshiftDbUser and (optionally) RedshiftDbGroups. For more information about using IAM to manage tags, see Passing session tags in Amazon Security Token Service in the IAM User Guide. To set up access using Amazon Identity and Access Management, an administrator can add tags using the IAM console (https://console.amazonaws.cn/iam/).

To add principal tags to an IAM role

Sign in to the Amazon Web Services Management Console and open the IAM console at https://console.amazonaws.cn/iam/.
Choose Roles in the navigation pane.
Choose the role that needs access to the query editor v2 using a federated user.
Choose the Tags tab.
Choose the Manage tags.
Choose Add tag and enter the Key as RedshiftDbUser and enter a Value of the federated user name.
Optionally choose Add tag and enter the Key as RedshiftDbGroups and enter a Value of the group name to associate to the user.
Choose Save changes to view the list of tags associated with your chosen IAM role. Propagating changes might take several seconds.
To use the federated user, refresh your query editor v2 page after the changes have propagated.

Setup your identity provider (IdP) to pass principal tags

The procedure to set up tags using an identity provider (IdP) varies by IdP. See your IdP documentation for instructions on how to pass user and group information to SAML attributes. When configured correctly, the following attributes appear in your SAML response that is used by the Amazon Security Token Service to populate in the principal tags for RedshiftDbUser and RedshiftDbGroups.


<Attribute Name="https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbUser">
    <AttributeValue>db-user-name</AttributeValue>
</Attribute>
<Attribute Name="https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbGroups">
    <AttributeValue>db-groups</AttributeValue>
</Attribute>

The optional db_groups must be a colon-separated list such as group1:group2:group3.

Additionally, you can set the TransitiveTagKeys attribute to persist the tags during role chaining.


<Attribute Name="https://aws.amazon.com/SAML/Attributes/TransitiveTagKeys">
  <AttributeValue>RedshiftDbUser</AttributeValue>
  <AttributeValue>RedshiftDbGroups</AttributeValue>
</Attribute>

For more information about setting up query editor v2, see Permissions required to use the query editor v2 .

For information about how to set up Active Directory Federation Services (AD FS), see the blog post: Federate access to Amazon Redshift query editor v2 with Active Directory Federation Services (AD FS).

For information about how to set up Okta, see the blog post: Federate single sign-on access to Amazon Redshift query editor v2 with Okta.

Note

When you connect to your cluster or workgroup using the Federated user connection option of the query editor v2, the Identity Provider (IdP) can supply custom principal tags for RedshiftDbUser and RedshiftDbGroups. Currently, Amazon IAM Identity Center dosesn't support the passing custom principal tags directly to the query editor v2.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Querying a database using the Amazon Redshift query editor v2

Opening query editor v2