Configuring your AWS account - Amazon Redshift

Configuring your AWS account

When you choose the query editor v2 from the Amazon Redshift console, a new tab in your browser opens with the query editor v2 interface. With the proper permissions, you can access data in an Amazon Redshift cluster or workgroup owned by your AWS account that is in the current AWS Region.

The first time an administrator configures query editor v2 for your AWS account, they choose the AWS KMS key that is used to encrypt query editor v2 resources. By default, an AWS owned key is used to encrypt resources. Or 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, AWS 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 AWS KMS customer managed key to use with query editor v2.

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:

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 IAM 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 AWS Resource Groups Tag Editor.

You can set up IAM roles and IAM users with IAM policies to share queries with others in your same AWS account in the AWS Region.

Creating an AWS 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 AWS KMS console or AWS KMS API operations. For instructions about creating a key, see Creating symmetric encryption AWS KMS key in the AWS 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 AWS KMS keys in the AWS 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 AWS KMS policy for AWS 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 AWS account using the key must be 111122223333. In the second section, the root user and key administrators of AWS account 111122223333 can access to the key.

{ "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 AWS KMS keys:

  • For more information about AWS KMS policies, see Specifying permissions in a policy in the AWS Key Management Service Developer Guide.

  • For information about troubleshooting AWS KMS policies, see Troubleshooting key access in the AWS Key Management Service Developer Guide.

  • For more information about keys, see AWS KMS keys in the AWS 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 AWS managed policies to the IAM user or role to grant permission. These AWS managed policies are written with different options that control how tagging resources allows sharing of queries. You can use the IAM console (https://console.aws.amazon.com/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 AWS SQL Workbench in the visual editor and IAM Policy Simulator.

For a principal (an IAM user or IAM role) 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 users or IAM roles that need 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 (an IAM user or IAM role) to turn on the ability in the Account settings page for others in the account to Export result set, they need the sqlworkbench:UpdateAcountExportSettings permission. This permission is included in the AmazonRedshiftQueryEditorV2FullAccess AWS managed policy.

For a principal (an IAM user or IAM role) to use the SQL Notebooks (preview) feature, you must add the following policy to one of the principal's existing query editor v2 managed policies.

{ "Version": "2012-10-17", "Statement": [ { "Sid": "AmazonRedshiftQueryEditorV2NonResourceLevelPermissions", "Effect": "Allow", "Action": [ "sqlworkbench:ListNotebooks" ], "Resource": "*" }, { "Sid": "AmazonRedshiftQueryEditorV2CreateOwnedResourcePermissions", "Effect": "Allow", "Action": [ "sqlworkbench:CreateNotebook" ], "Resource": "*", "Condition": { "StringEquals": { "aws:RequestTag/sqlworkbench-resource-owner": "${aws:userid}" } } }, { "Sid": "AmazonRedshiftQueryEditorV2OwnerSpecificPermissions", "Effect": "Allow", "Action": [ "sqlworkbench:GetNotebook", "sqlworkbench:UpdateNotebook", "sqlworkbench:DeleteNotebook", "sqlworkbench:CreateNotebookCell", "sqlworkbench:DeleteNotebookCell", "sqlworkbench:UpdateNotebookCellContent", "sqlworkbench:UpdateNotebookCellLayout", "sqlworkbench:BatchGetNotebookCell", "sqlworkbench:AssociateNotebookWithTab" ], "Resource": "*", "Condition": { "StringEquals": { "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}" } } }, { "Sid": "AmazonRedshiftQueryEditorV2TeamReadAccessPermissions", "Effect": "Allow", "Action": [ "sqlworkbench:GetNotebook", "sqlworkbench:BatchGetNotebookCell", "sqlworkbench:AssociateNotebookWithTab" ], "Resource": "*", "Condition": { "StringEquals": { "aws:ResourceTag/sqlworkbench-team": "${aws:PrincipalTag/sqlworkbench-team}" } } } ] }

For more information about managed policies in Amazon Redshift, see AWS-managed (predefined) policies for Amazon Redshift.

You can use the IAM console to attach IAM policies to an IAM user or an IAM role. After you attach a policy to a role, you can attach the role to an IAM user.

To attach the IAM policies to an IAM user

  1. Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.

  2. Choose Users.

  3. Choose the user that needs access to the query editor v2.

  4. Choose Add permissions.

  5. Choose Attach existing policies directly.

  6. For Policy names, choose the proper policies as previously described.

  7. Choose Next: Review.

  8. Choose Add permissions.

To attach the IAM policies to an IAM role

  1. Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.

  2. Choose Roles.

  3. Choose the role that needs access to the query editor v2.

  4. Choose Attach policies.

  5. For Policy names, choose the proper policies as previously described.

  6. Choose Attach policy.

For more information about using IAM to manage users and roles, see Changing permissions for an IAM user in the IAM User Guide.

Setting up principal tags to connect to query editor v2 as a federated user

To access the query editor v2 as a federated user, either set up your IAM role or IAM 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 AWS Security Token Service in the IAM User Guide. To set up access using AWS Identity and Access Management, an administrator can add tags using the IAM console (https://console.aws.amazon.com/iam/).

To add principal tags to an IAM role

  1. Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.

  2. Choose Roles in the navigation pane.

  3. Choose the role which needs access to the query editor v2 using a federated user.

  4. Choose the Tags tab.

  5. Choose the Manage tags.

  6. Choose Add tag and enter the Key as RedshiftDbUser and enter a Value of the federated user name.

  7. Optionally choose Add tag and enter the Key as RedshiftDbGroups and enter a Value of the group name to associate to the user.

  8. Choose Save changes to view the list of tags associated with your chosen IAM role. Propagating changes might take several seconds.

  9. To use the federated user, refresh your query editor v2 page after the changes have propagated.

You can similarly add principal tags to IAM users.

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 which is used by the AWS 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.