Enabling multi-factor authentication for an Amazon Redshift cluster using Okta as an identity provider

December 2022: This post was reviewed and updated for accuracy.

Many organizations have started using single sign-on (SSO) with multi-factor authentication (MFA) for enhanced security. This additional authentication factor is the new normal, which enhances the security provided by the user name and password model. Using SSO reduces the effort needed to maintain and remember multiple login credentials.

In the past, you needed to manage Amazon Redshift database users separate from the SSO mechanism. Database administrators had to manage authorizations based on user groups and with a complex set of database grants. These authorizations were maintained within the database and could easily get disjointed from the corporate directory.

Amazon Redshift now supports a browser-based authentication workflow that works with any MFA configured by the identity federation provider. This browser-based authentication workflow also makes integration with any Security Assertion Markup Language (SAML)-based identity federation easier.

As an Amazon Redshift administrator, you can manage users and groups within the enterprise identity provider (IdP) and pass them to Amazon Redshift at login using additional authentication factors. Amazon Redshift supports a browser-based authentication workflow with any MFA configured by the identity federation provider.

Okta is one of the most commonly used SSO service providers using SAML 2.0 authentication. Although setting up Okta is mostly the same as configuring any SAML provider, this post specifically describes the steps to set up identity federation for Amazon Redshift using MFA. I cover the steps to set up an Okta.com account, build users and groups within your organization’s directory, and enable SSO into Amazon Redshift with MFA. You can do all this while also maintaining group-level access controls within your data warehouse.

Solution overview

The steps in this post are structured into the following sections:

Okta configuration – You set up Okta, which contains your users organized into logical groups.
AWS configuration – You set up an AWS Identity and Access Management (IAM) role that establishes a trust relationship between your IdP and AWS and a role that Okta uses to access Amazon Redshift.
Okta advanced configuration – You finalize the Okta configuration by inputting the roles that you just created. You also inform Okta of which groups are allowed to be passed to Amazon Redshift.
Amazon Redshift server and client setup – You set up groups within the Amazon Redshift database to match the Okta groups. You also authorize these groups to access certain schemas and tables. Finally, you set up your client tools to use your enterprise credentials and sign in to Amazon Redshift.

Configuring Okta

In this first step, you set up Okta, add users, and organize them into logical groups. You then add the Amazon Web Services Redshift Okta application.

For your Okta users to connect, you need to create or use an existing Amazon Redshift cluster.

You need the identifier property of the cluster when you configure the connection.

Create an Okta account.

If you don’t already have access to an Okta account, you can start a 30-day free trial.

Sign in to Okta.com using the following URL, where <prefix> is specific to your account and was created at account setup: https://<prefix>-admin.okta.com/admin/dashboard.
Navigate to the Directory page to add people and groups into Okta that match your organization.

Be sure to use lowercase group names (Amazon Redshift expects the group names to be lowercase).

For this use case, I added three users and two groups, where one of the users (Jorge) belongs to both the sales and marketing groups.

Choose Admin.
Choose Add Person.

After you add your users, you can add your groups.

Choose Add Group.

The following screenshot shows our new groups: marketing and sales.

To add a new application, navigate to the Applications.
Choose Create New App.
On the Create a New Application Integration page, for Platform, choose Web.
For Sign on method, select SAML 2.0.
Choose Create.
On the General Settings page, for App name, enter a name that identifies your application.
Choose Next.
On the Configure SAML page, for Single sign on URL, enter http://localhost:7890/redshift/.
Select Use this for Recipient URL and Destination URL.
For Audience URI (SP Entity ID), enter urn:amazon:webservices.
In the Attribute Statements section, set up the following four attribute mappings:

Name	Name Format	Value
https://thinkwithwp.com/SAML/Attributes/Role	Unspecified	arn:aws:iam::<aws_account_id>:saml-provider/<name_for_your_iam_identity_provider>,arn:aws:iam::<aws_account_id>:role/<name_for_your_sso_iam_role>
https://thinkwithwp.com/SAML/Attributes/RoleSessionName	Unspecified	user.email
https://redshift.amazon.com/SAML/Attributes/AutoCreate	Unspecified	true
https://redshift.amazon.com/SAML/Attributes/DbUser	Unspecified	user.email

Replace the placeholders with the following information:

<aws_account_id> – Account ID of the Amazon Redshift cluster
<name_for_your_sso_iam_role> – IAM role for access to the Amazon Redshift cluster
<aws_account_id> – Descriptive name for the IdP

In the Group Attribute Statement section, add the following attribute:

Name	Name Format	Value
https://redshift.amazon.com/SAML/UnspecifiedAttributes/DbGroups	Unspecified	marketing

In the Group Attribute Statements section, you can add optional group attribute statements. The following filter conditions are supported:

Starts with – Matches groups that start with a specified string. If you want to set up permissions for multiple groups, you can choose a common prefix. For example, you could prefix your group names as aws_marketing and aws_sales. Specifying a filter that starts with aws_ ensures that permissions apply to both groups that start with that prefix.
Equals – In our use case, we use the Equals filter condition to specify the group named marketing.
Contains – Matches text contained in the group name.
Matches Regex – For more complex scenarios, you could use a regular expression. The expression (aws_\S*_retail) matches group names that begin with aws_ and end with _retail.

If you have set up SAML correctly, you should have a SAML assertion with the following attributes:

<saml2:AttributeStatement xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion">
<saml2:Attribute Name="https://thinkwithwp.com/SAML/Attributes/Role" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml2:AttributeValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">arn:aws:iam::<accountid>:saml-provider/redshiftmfa,arn:aws:iam::<accountid>:role/rs-okta-mfa-role</saml2:AttributeValue>
</saml2:Attribute>
<saml2:Attribute Name="https://thinkwithwp.com/SAML/Attributes/RoleSessionName" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml2:AttributeValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">john@example.com</saml2:AttributeValue>
</saml2:Attribute>
<saml2:Attribute Name="https://redshift.amazon.com/SAML/Attributes/AutoCreate" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml2:AttributeValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">true</saml2:AttributeValue>
</saml2:Attribute>
<saml2:Attribute Name="https://redshift.amazon.com/SAML/Attributes/DbUser" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml2:AttributeValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string"> john@example.com </saml2:AttributeValue>
</saml2:Attribute>
<saml2:Attribute Name="https://redshift.amazon.com/SAML/Attributes/DbGroups" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml2:AttributeValue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">marketing</saml2:AttributeValue>
</saml2:Attribute>
</saml2:AttributeStatement>
</saml2:Assertion>
</saml2p:Response>

In the Feedback section, keep the options at their default and choose Finish.

You can now download the Okta application metadata.

Navigate to the Amazon Web Services Redshift application’s Settings page.
Choose Sign On.
Choose Identity Provider metadata to download the metadata file in XML format (for example, metadata.xml).

Configuring AWS

In this section, you set up a role that establishes a trust relationship between the IdP and AWS. You also create a role that Okta uses to access Amazon Redshift.

Sign in to the AWS Management Console with your AWS credentials.
On the IAM console, choose Identity providers.
Choose Create Provider.
For Provider Type, choose SAML.
For Provider name, enter a name for your IdP.
For Metadata Document, upload the metadata file that you downloaded in the previous step.
On the IAM console, navigate to Roles and choose Create role.
For Select type of trusted entity, choose SAML 2.0 federation.
For SAML provider, choose the IdP you created in the previous step.
Select Allow programmatic access and AWS Management Console access.
Choose Next: Permissions.
Choose Create policy.

Create the following custom policy, replacing the region, account, cluster and unique-role-identifier parameters. These permissions allow the role to use Amazon Redshift to query data, create users, and allow users to join groups. The Condition block enforces that the AWS user ID should match unique-role-identifier:${redshift:DbUser}, so that individual users can authenticate only as themselves.Note: Leave unique-role-identifier as is; you will to update the policy after the role is created.

{
    "Version": "2012-10-17",
    "Statement": [{
        "Effect": "Allow",
           "Action": [
                "redshift:CreateClusterUser",
                "redshift:JoinGroup",
                "redshift:GetClusterCredentials",
                "redshift:ListSchemas",
                "redshift:ListTables",
                "redshift:ListDatabases",
                "redshift:ExecuteQuery",
                "redshift:FetchResults",
                "redshift:CancelQuery",
                "redshift:DescribeClusters",
                "redshift:DescribeQuery",
                "redshift:DescribeTable"],
           "Resource": [
             "arn:aws:redshift:<region>:<account>:cluster:<cluster>",
             "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/${redshift:DbUser}",
             "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/marketing",
             "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/sales",
             "arn:aws:redshift:<region>:<account>:dbname:<cluster>/${redshift:DbName}"],
            "Condition": {
                "StringEquals": {
                    "aws:userid": "unique-role-identifier:${redshift:DbUser}"
                }
     }]
}

There are a few important things to note:

The group membership lasts only for the duration of the user session
There is no CreateGroup permission because groups need to be manually created and granted DB privileges

The following screenshot shows the summary page for the role.

We use the AWS Command Line Interface (AWS CLI) to fetch the unique role identifier for the role you just created.

Configure the AWS CLI with the necessary access token and secret access key prior to running the following command:

aws iam get-role --role-name <value>

Replace <value> with the role-name that you just created. So, in this example, the command is:

aws iam get-role --role-name oktasso

From the output JSON, note the value of RoleId.
On the IAM console, open the policy you created earlier.
Choose Edit policy.

Choose the JSON tab.

Replace unique-role-identifier with the RoleId fetched earlier.

Choose Review policy and Save changes

Okta advanced configuration

In this section, you finalize the Okta configuration by adding the roles that you just created. You also tell Okta which groups are allowed to be passed to Amazon Redshift.

You authorize users to use the Amazon Web Services Redshift application by selecting their respective groups or individual user accounts.

For this use case, I authorize users by group. See the following screenshot.

Setting up the Amazon Redshift server and client

Next, you set up groups in the Amazon Redshift database to match the Okta groups. You also authorize these groups to access certain schemas and tables. Finally, you set up your client tools to use your enterprise credentials and sign in to Amazon Redshift.

Log in to your Amazon Redshift cluster with an admin account.
Create groups that match the IdP group names, and grant the appropriate permissions to tables and schemas:

CREATE GROUP marketing;
ALTER DEFAULT PRIVILEGES IN SCHEMA marketing
GRANT SELECT on TABLES to GROUP marketing;
GRANT USAGE on SCHEMA sales to GROUP marketing;
GRANT SELECT on ALL TABLES in SCHEMA sales to GROUP marketing;

Now you’re ready to connect to Amazon Redshift using a SQL client application. Make sure to install the Amazon Redshift driver. For instructions, see Download an Amazon Redshift JDBC driver. Also, IAM authentication requires using the JDBC driver with AWS SDK included or that you ensure the AWS SDK is within your Java classpath.

Assuming that the Amazon Redshift JDBC driver is installed, set up a new connection to your cluster using your IdP credentials.

For this use case, I use SQLWorkbenchJ.

For URL, be sure to enter iam to instruct the driver to authenticate using IAM.
For Username and Password, enter the values that you set in Okta.
Choose OK.
For Edit extended properties, enter the following properties and values:
1. login_url – Use the application URL from OKTA. E.g., https://amazonmohitmehta.okta.com/app/amazonorg426936_redshiftmfa_1/exkai5axi2ep4hF6C4x6/sso/saml
2. plugin_name – Enter com.amazon.redshift.plugin.BrowserSamlCredentialsProvider
Choose OK.
When prompted, enter your Okta user name and password to sign in.
When prompted, enter your MFA token.
Retrieve your MFA token from your MFA device.

You should see a message that you successfully connected to Amazon Redshift. See the following screenshot.

You should now be able to sign in with the users created. In our use case, john@example.com has access to the tables in the marketing schema only. Using the john@example.com user, you get the following results when trying to query data from each the schema:

select campaignid From marketing.campaign


campaignid	
-------
1234
2345
3456
[…]


select * From sales.storeid


An error occurred when executing the SQL command:

[Amazon](500310) Invalid operation: permission denied for schema sales;
1 statement failed.

Execution time: 0.16s

Summary

In this post, you learned how to configure Okta as your IdP with multi-factor authentication to enable single sign-on to an Amazon Redshift cluster. You also configured group membership within your IdP that can be passed along, which enables you to manage user access to Amazon Redshift resources from within your IdP.

If you have questions or suggestions, please leave a comment.

About the Author

Mohit Mehta is a Senior Consultant for AWS Professional Services. He works with our customers to provide leadership on a variety of projects, helping them shorten their time to value when using AWS.

AWS Big Data Blog