Federate access to Amazon Redshift query editor V2 with Active Directory Federation Services (AD FS): Part 3
In the first post of this series, Federate access to your Amazon Redshift cluster with Active Directory Federation Services (AD FS): Part 1, you set up Microsoft Active Directory Federation Services (AD FS) and Security Assertion Markup Language (SAML) based authentication and tested the SAML federation using a web browser.
In this post, we walk through the steps to configure Amazon Redshift query editor v2 to work with AD FS federation SSO.
Organizations want to enable their end-users such as data analysts, data scientists, and database developers to use the query editor v2 to accelerate self-service analytics. Amazon Redshift query editor v2 lets users explore, analyze, and collaborate on data. You can use the query editor to create databases, schemas, tables, and load data from Amazon Simple Storage Service (Amazon S3) using the COPY command or by using a wizard. You can browse multiple databases and run queries on your Amazon Redshift data warehouse or data lake, or run federated queries to operational databases such as Amazon Aurora.
In this post, we show how you can use your corporate Active Directory (AD) and the SAML 2.0 AD FS identity provider (IdP) to enable your users to easily access Amazon Redshift clusters through query editor v2 using corporate user names without managing database users and passwords. We also demonstrate how you can limit the access for your users to use only the query editor without giving them access to perform any admin functions on the AWS Management Console.
After you follow the steps explained in Part 1, you set up a deep link for federated users via the SAML 2.0
RelayState parameter in AD FS. You use the user you set up in your AD in Part 1 (
Bob) to authenticate using AD FS and control access to database objects based on the group the user is assigned to. You also test if user
Bob is integrated with Amazon Redshift database groups as controlled in AD groups.
By the end of this post, you will have created a unique deep link that authenticates the user
Bob using AD FS and redirects them directly to the query editor v2 console, where they’re authenticated using the federation SSO option.
The sign-in process is as follows:
- The user chooses a deep link that redirects to the IdP for authentication with the information about the destination (query editor v2, in our case) URL embedded in the
RelayStateparameter. The user enters their credentials on the login page.
- Your IdP (AD FS in the case) verifies the user’s identity in your organization.
- Your IdP generates a SAML authentication response that includes assertions that identify the user and attributes about the user. The IdP sends this response to the user’s browser.
- The user’s browser is redirected to the AWS Single Sign-On endpoint and posts the SAML assertion and the
- The endpoint calls the
AssumeRoleWithSAMLAPI action to request temporary credentials from the AWS Identity and Access Management (IAM) role specified in the SAML assertion and creates a query editor v2 console sign-in URL that uses those credentials. The IAM role trusts the SAML federation entity and also has a policy that has access to query editor V2. If the SAML authentication response includes attributes that map to multiple IAM roles, the user is first prompted to choose the role to use for access to the query editor v2 console. The sign-in URL is the one specified by the
- AWS sends the sign-in URL back to the user’s browser as a redirect.
- The user’s browser is redirected to the Amazon Redshift query editor v2 console defined by the
The following diagram illustrates this flow.
In this post, we walk you through the following steps:
- Set up the
Salesgroup in AD and set up the
PrincipalTagclaim rules in AD FS.
- Update the IAM roles.
- Construct the SSO URL to authenticate and redirect users to the Amazon Redshift query editor v2 console.
- Set up Amazon Redshift database groups and permissions on the Amazon Redshift cluster.
- Set up Amazon Redshift query editor v2 to use federated authentication with AD FS to connect directly from the query editor interface.
- Query Amazon Redshift objects to validate your authorization.
For this walkthrough, complete the following prerequisite steps:
- Create an Amazon Redshift cluster. For instructions, refer to Create a sample Amazon Redshift cluster or complete the steps in Part 2 of this series.
- Complete the steps in Part 1 to set up SAML federation with AD FS:
- Set up an AD domain controller using an AWS CloudFormation template on a Windows 2016 Amazon Elastic Compute Cloud (Amazon EC2) instance.
- Configure federation in AD FS.
- Configure AWS as the relying party with AD FS using an IAM SAML provider and SAML roles with an attached policy to allow access to the Amazon Redshift cluster.
- Configure claim rules.
- Test the SAML authentication using a web browser.
- Verify that your IdP supports
RelayStateand is enabled. If you’re using AD FS 2.0, you need to download and install either Update Rollup 3 or Update Rollup 2 from Microsoft to enable the
Configure AD and AD FS
After you configure your AD FS and AD services by following the instructions in Part 1, you can set up the following AD group and claim rules.
In this post, you use the user
Bob to log in to Amazon Redshift and check if
Bob can access the
Marketing schemas on the Amazon Redshift cluster. To create the
sales group and assign the user
Bob@adfsredshift.com to it, log in to your AD FS server (Amazon EC2 machine) that you created in Part 1 and use the Windows command tool to run the following command:
Now you’re ready to create your custom claim rules:
The custom claim rule
PrincipalTag:RedshiftDbUser is mapped to the universal principal name in AD FS. When a user authenticates through federated SSO, this claim rule is mapped to the user name. If user doesn’t exist in the Amazon Redshift database, then the user is automatically created. The auto create option is granted through an IAM policy that is attached to the IAM role. The
CreateClusterUser permission allows for auto creation of the user (you set this up as part of Part 1 as a prerequisite).
Complete the following steps to create your custom claim rule:
- On the AD FS management console, choose Relying Party Trusts.
- Choose Edit Claim Issuance Policy.
- Choose Choose Rule Type.
- For Claim rule template, choose Send Claims Using a Custom Rule.
- Choose Next.
- For Claims rule name, enter
- Add the following custom rule:
- Choose Finish.
- Capture the claim rules sent in a SAML assertion response through your browser. For instructions, refer to How to view a SAML response in your browser for troubleshooting.
In my example, I use the following SAML attribute for the
The custom claim rule
PrincipalTag:RedshiftDbGroup is built out of AD groups that the user is a member of. This rule is mapped to the Amazon Redshift database groups. The AD groups and Amazon Redshift database group names should match.
JoinGroup permission set in the IAM policy allows the user to assume a database group and is session based. If the user is mapped to multiple groups in the AD group, the SAML assertion response should send those groups in : separated values and not as multiple value claims. The following steps demonstrate how to send AD groups as : separated values.
In this example, the user
Bob is assigned to the
sales groups. The following code shows how to send multiple groups through the SAML response when the user is in multiple groups, and also how to handle the situation when a user doesn’t exist in any particular group.
- Follow the same steps as in the previous section to create the rule
Marketing, using the following code for the custom rule:
- Create the rule
MarketingNotExistsusing the following code:
- Create the rule
salesusing the following code:
- Create the rule
SalesNotExistsusing the following code:
- Create the rule
RedshiftDbGroupsusing the following code:
If you see a similar SAML response for
RedshiftDbGroups, your setup is good:
If a user doesn’t exist in one of the groups, an empty value is passed to the claim rule. For example, if user bob is removed from the
marketing group, the SAML response for
PrincipalTag:RedshiftDbGroup would be
Update IAM roles
In Part 1 of this series, you created two IAM roles:
ADFZ-Production. These two roles aren’t yet set up with grants on the query editor. In this section, you update these roles with query editor permissions.
Amazon Redshift query editor v2 provides multiple managed policies to access the query editor. For a list of all the managed policies, refer to Configuring your AWS account. For this post, we attach the
AmazonRedshiftQueryEditorV2ReadSharing managed policy to the roles.
- On the IAM console, choose Roles in the navigation pane.
- Choose the role
- Choose Add permissions and then Attach policies.
- Under Other permission policies, search for the
- Select the policy and choose Attach policies.
- Modify the trust relationships for your role and add
sts:TagSession. While in role select Trust relationships and click on Edit trust policy.When using session tags, trust policies for all roles connected to the IdP passing tags must have the sts:TagSession permission. For roles without this permission in the trust policy, the
- Choose Update policy.
- Repeat these steps to attach the
AmazonRedshiftQueryEditorV2ReadSharingmanaged policy to the ADFZ-Production role.
Limiting User access only to Query Editor
If you would like to limit users only access Query Editor then update the policy
redshift-marketing that you have created in Part 1 blog post as below.
Note: once updated, users will lose admin privileges such as create cluster.
Replace the region, account, and cluster parameters. This custom policy grants access to Amazon Redshift to get cluster credentials, create users, and allow users to join groups.
There are a few important things to note:
- The group membership lasts only for the duration of the user session.
- There is no
CreateGrouppermission because groups need to be manually created and granted DB privileges.
Generate the SSO destination URL as the Amazon Redshift query editor v2 console
In this step, you construct the sign-in URL for the AD FS IdP to redirect users to the Amazon Redshift query editor v2 console. For instructions, refer to How to Use SAML to Automatically Direct Federated Users to a Specific AWS Management Console Page.
To provide a full SSO experience for the end-users, the SAML response can include an optional parameter called
RelayState. This parameter contains the destination URL.
Microsoft provides a tool to help generate these SSO URLs for AD FS called the AD FS 2.0 RelayState Generator.
To build this URL, you need three pieces of information:
- IdP URL string – The string is in the format
https://ADFSSERVER/adfs/ls/idpinitiatedsignon.aspx. For this post, we use
- Relying party identifier – For AWS, this is
- Relay state or target app – This is the AWS Management Console URL you want your authenticated users redirect to. In this case, it’s
https://eu-west-1.console.aws.amazon.com/sqlworkbench/home?. For this post, we use the
eu-west-1Region, but you can adjust this as needed.
I followed the instructions in How to Use SAML to Automatically Direct Federated Users to a Specific AWS Management Console Page and used the AD FS 2.0 RelayState Generator to generate the URL shown in the following screenshot.
The following is an example of the final URL that you use to get authenticated and also get redirected to Amazon Redshift query editor v2 (this URL won’t work in your setup because it has been created specifically for an AD FS server in my account):
You can now save this URL and use it from anywhere you can reach your AD FS server. After you enter the URL in a browser, you first authenticate to AD FS, then you’re redirected to the Amazon Redshift query editor v2 console.
Set up DB groups on Amazon Redshift cluster
In this step, you set up your Amazon Redshift database group. This step is necessary because when the user is authenticated, they have to be part of an Amazon Redshift DB group with proper permissions set on a schema or table (or view).
In Active Directory, the user
Bob is part of two groups:
Marketing. In your Amazon Redshift database, you have three database groups:
Bob logs in via federated authentication, the user assumes the
Marketing database groups, so this user can query tables in both the
Marketing schemas. Because the user
Bob isn’t part of the
Finance group, when they try to access the
Finance schema, they receive a permission denied error.
The following diagram illustrates this configuration.
Complete the following steps to set up your DB groups:
- Connect as
- Create three database groups:
- Create three schemas:
- Create a table in each schema:
- Insert sample data into the three tables:
- Validate the data is available in the tables:
You can now set up appropriate privileges for the
marketing groups. Groups are collections of users who are all granted privileges associated with the group. You can use groups to assign privileges by job function. For example, you can create different groups for sales, administration, and support, and give the users in each group the appropriate access to the data they require for their work. You can grant or revoke privileges at the group level, and those changes apply to all members of the group, except for superusers.
- Enter the following SQL queries to grant access to all tables in the
salesschema to the sales group, access to all tables in the
marketingschema to the
marketinggroup, and access to all tables in the
financeschema to the
Access Amazon Redshift query editor v2 through federated authentication
Now that you have completed your SAML integration, deep link setup, and DB groups and access rights configuration, you can set up Amazon Redshift query editor v2 to use federated authentication with AD FS to connect from directly from the query editor interface.
- Navigate to the deep link URL you created earlier.
You’re redirected to the AD FS login page.
- Sign in as
For this post, I accessed this URL from an Amazon EC2 machine, but you can access it from any location where you can reach the AD FS IdP.
After AD FS successfully authenticates you, it redirects to the AWS SSO endpoint and posts the SAML assertion and
RelayStateparameter. Because you configured two IAM roles on the AWS side, you’re prompted to select a role.
- Select a role (for this example,
ADFZ-Dev) and choose Sign In.
AWS sends the sign-in URL that is based on the
RelayStatevalue back to your browser as a redirect. Your browser is redirected to the query editor v2 console automatically.
- Right-click your Amazon Redshift cluster (for this post,
redshift-cluster-1) and choose Edit connection.
The value for User name is automatically populated, and Federated Access is automatically selected.
- Choose Edit connection to save the connection and log in to the database.
After you’re successfully logged in, you can browse the database objects in the left pane.
- Test the connection by running the following query:
The output shows that the user
firstname.lastname@example.org was authenticated using AD FS. The user also joined the
Sales groups as enforced by the AD FS
PrincipalTag:RedshiftDbGroups claim rule and the policy associated with the
ADFZ-Dev role, which the user assumes during this session.
Run queries to validate authorization through federated groups
In this final step, you validate how the groups and membership configured in AD are seamlessly integrated with Amazon Redshift database groups.
Run the following query against the
The following screenshots shows the output:
The preceding images show that AD user
Bob is part of the AD group
RSDB-sales, which are mapped to the DB groups
sales. These DB groups have select access to the schemas marketing and sales and all tables in those schemas. Therefore, the user can successfully query the tables.
To run a query against the
finance schema, enter the following code:
The following screenshot shows the output.
The output shows that
Bob is only part of the AD groups
RSDB-sales. Due to the way the claim rule is set up,
Bob doesn’t have access to the database group
finance, and therefore the query returns with a permission denied error.
To avoid incurring future charges, delete the resources by deleting the CloudFormation stack. This cleans up all the resources from your AWS account that you set up in Part 1.
In this post, we demonstrated how to set up an AD FS server, configure different
PrincipalTag attributes used for Amazon Redshift query editor v2, and generate an SSO URL with the query editor as the destination location. You then connected to the Amazon Redshift DB cluster using a database user with administrator privileges to set up DB groups and permissions, and used a federated user authentication with AD FS to run several queries. This solution enables you to control access to your Amazon Redshift database objects by using AD groups and memberships seamlessly.
If you have any feedback or questions, please leave them in the comments.
About the Authors
Sumeet Joshi is an Analytics Specialist Solutions Architect based out of New York. He specializes in building large-scale data warehousing solutions. He has over 16 years of experience in the data warehousing and analytical space.
Bhanu Pittampally is an Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytical solutions. His background is in data and analytics for over 14 years. His LinkedIn profile can be found here.
Erol Murtezaoglu, a Technical Product Manager at AWS, is an inquisitive and enthusiastic thinker with a drive for self-improvement and learning. He has a strong and proven technical background in software development and architecture, balanced with a drive to deliver commercially successful products. Erol highly values the process of understanding customer needs and problems, in order to deliver solutions that exceed expectations.
Yanis Telaoumaten is a Software Development Engineer at AWS. His passions are building reliable software and creating tools to allow other engineers to work more efficiently. In the past years, he worked on identity, security and reliability of Redshift services