Password-based access control alone is not considered secure enough, and many organizations are adopting multi-factor authentication (MFA) and single sign-on (SSO) as a de facto standard to prevent unauthorized access to systems and data. SSO frees up time and resources for both administrators and end users from the painful process of password-based credential management. MFA capability adds an extra layer of protection, improving organizational security and confidence.
Amazon Redshift is a fast, fully-managed cloud data warehouse that provides browser-based plugins for JDBC/ODBC drivers, which helps you easily implement SSO capabilities added with MFA to secure your data warehouse, and also helps automation and enforcement of data access policies across the organization through directory federation.
You can integrate Amazon Redshift with different identity providers (IdPs) like Microsoft Azure Active Directory, Ping, Okta, OneLogin, and more. For more information, see Using a credentials provider plugin. You may already have pre-existing integrations for federating to AWS using industry standard Security Assertion Markup Language (SAML) with these IdPs. In this post, we explain the steps to integrate the Amazon Redshift browser-based SAML plugin to add SSO and MFA capability with your federation IdP.
With this integration, users get authenticated to Amazon Redshift using the SSO and MFA credentials of the IdP application, which uses SAML tokens to map the IdP user identity (like login name or email address) as the database user in Amazon Redshift. It can also map users’ directory group memberships to corresponding database groups in Amazon Redshift, which allows you to control authorization grants for database objects in Amazon Redshift.
The following diagram illustrates our solution architecture.
High-level steps for SSO-MFA integration
The following diagram illustrates the authentication flow with the browser SAML plugin.
We complete the following high-level steps to set up the SSO and MFA authentication capability to an Amazon Redshift data warehouse using a browser-based SAML plugin with our IdP:
- Create a custom SAML 2.0 application with the IdP with the following configurations:
- A redirect URI (for example,
- MFA capability enabled.
- Relevant SAML claim attributes.
- Appropriate directory groups and users with the IdP.
- A redirect URI (for example,
- Add appropriate AWS Identity and Access Management (IAM) permissions:
- Add an IdP.
- Add appropriate IAM roles for the IdP.
- Use IAM policies to add appropriate permissions to the roles to access the Amazon Redshift cluster.
- Set up Amazon Redshift with group-level access control:
- Connect to Amazon Redshift using superuser credentials.
- Set up appropriate database groups in Amazon Redshift.
- Grant access permissions appropriate to relevant groups.
- Connect to Amazon Redshift with your JDBC/ODBC SQL client:
- Configure connection attributes for the IdP.
- Enable browser-based MFA.
- Connect to Amazon Redshift.
Create a custom SAML 2.0 application with the IdP
The first step of this setup is to create a SAML 2.0 application with your IdP. The various directory groups and users that need to log in to the Amazon Redshift cluster should have access to this SAML application. Provide an appropriate
redirect_uri (for example,
http://localhost:7890/redshift/) in the SAML application configuration, so that the IdP can seamlessly redirect SAML responses over HTTPS to this URI, which then allows the Amazon Redshift JDBC/ODBC driver to authenticate and authorize the user.
The following screenshot shows a SAML application configuration with PingOne as the IdP (for more details on PingOne Amazon Redshift SSO federation, see Federating single sign-on access to your Amazon Redshift cluster with PingIdentity).
You need to download the metadata XML file from the provider (as shown in the preceding screenshot) and use it in a later step to create a SAML IdP in IAM.
Next, you can enable MFA for this application so that users are authorized to access Amazon Redshift only after they pass the two-factor authentication with MFA.
The following screenshot shows the MFA configuration settings with PingOne as the IdP.
As part of the IdP application setup, map the following claim attributes so that Amazon Redshift can access them using the SAML response.
|Claim Attribute||Namespace||Description||Example Value|
||https://aws.amazon.com/SAML/Attributes/RoleSessionName||Identification for the user session, which in most cases is the
||https://redshift.amazon.com/SAML/Attributes/AutoCreate||If this parameter is set, new users authenticated by the IdP are automatically created in Amazon Redshift.||
||https://redshift.amazon.com/SAML/Attributes/DbUser||Identification for the user session, which in most cases is the
||https://redshift.amazon.com/SAML/Attributes/DbGroups||Amazon Redshift database group names for the user, which in most cases is the same as the directory groups the user belongs to.||
The following screenshot is an example of these claim attributes set up for PingOne as IdP.
Apart from setting up the SAML application, you also need to set up appropriate directory groups and users with your IdP, which you will use to grant SSO and MFA access to users for different applications like AWS Single Sign-On and Amazon Redshift application single sign-on.
The following screenshot is an example of this user group set up for PingOne as IdP.
Add appropriate permissions using IAM
After you complete the configuration settings with the IdP, the next step is to configure appropriate permissions in IAM in your AWS account for identity federation using IAM.
The first step is to add an IdP using the SAML metadata XML file downloaded from the IdP’s SAML application you created in the previous step.
After you add the IdP, you need to create an IAM role with that IdP as a trusted entity.
Set the value of the
SAML:aud attribute to the same redirect URI defined in your IdP’s SAML application setup (for example,
Create a new IAM policy with the necessary permissions needed by the users to access Amazon Redshift, and attach it to the IAM role you created earlier. See the following sample policy:
You can also use an AWS CloudFormation template to automate this IAM setup by uploading the IdP- specific SAML metadata XML file from the SAML application you created.
The following template takes care of creating the IAM resources required for this setup. You need to enter the following parameters to the template:
- FederationProviderName – Enter a suitable name for the IAM IdP.
- FederationXmlS3Location – Enter the Amazon Simple Storage Service (Amazon S3) URI where you uploaded the SAML metadata XML file from your IdP’s SAML application.
- RedshiftClusterEndpoint – Enter the endpoint URI of your Amazon Redshift cluster. You can get this URI via the Amazon Redshift console. If you have multiple Amazon Redshift clusters, you may need to modify this CloudFormation template to add permissions for all the clusters in your account.
Grant group-level access control with Amazon Redshift
If you haven’t set up an Amazon Redshift cluster yet, see Getting started with Amazon Redshift for a step-by-step guide to create a new cluster in your AWS account.
If you already have an Amazon Redshift cluster, note the primary user credentials for that cluster and refer to the following resources to connect to that cluster using a SQL client like SQL Workbench/J and the latest Amazon Redshift JDBC driver with AWS SDK:
When you’re logged in, you need to set up the appropriate groups in Amazon Redshift. The following example code sets up three database groups for business intelligence (BI) users, analysts, and a cross-user group in Amazon Redshift:
You can then set up database objects and appropriate access permissions for them. In the following code, we set up two schemas for analysts and BI users and then grant access on them to the relevant groups:
These group-level grants allow federated users to access Amazon Redshift objects based on their associated permissions. As explained earlier in this post, you can map your IdP directory groups to their respective database groups in Amazon Redshift, which allows you to control both authentication and authorization to Amazon Redshift based on the IdP credentials.
However, you may choose to control the authorization part within the database itself instead of relying on IdP directory groups. In this case, you use the IdP only to facilitate system authentication to Amazon Redshift, but for data authorization, you map the users and groups manually using alter group statements, as in the following code:
In the preceding example, we create a new user,
exampleuser, with password disabled. We can use the IdP credentials for this user to authenticate and therefore it doesn’t need a password. But to provide authorization, we added this user to the
cross_user groups, so that it can inherit the permissions granted to these groups and can work seamlessly with SSO and MFA federation.
Configure your JDBC/ODBC SQL client to use the browser-based plugin to connect to Amazon Redshift
In this step, you can test Amazon Redshift connectivity through your IdP using a SQL client like SQL Workbench/J.
You need to provide the following configurations in the SQL client.
Additionally, you need to set up the following extended properties.
|login_url||This is the SAML application’s login page|
|idp_response_timeout||Number of seconds to allow for SSO authentication to complete before timing out|
The following screenshot shows the configurations to connect SQLWorkbench/J client with PingOne as IdP.
The following table summarizes our property values.
When you choose Test or OK, a new web browser window opens that shows the SAML application’s login page.
If this is the first time you’re logging in to PingOne, and haven’t set up MFA before, you can download and pair the PingID mobile app on iOS or Android.
After the PingID app is installed and paired, it pushes a notification to your phone to approve or deny the MFA authorization. When the MFA succeeds, the browser displays a success message on the redirect page.
After the connection is successful, let’s run a SQL query to confirm that the correct user identification was passed and also confirm that the correct database group was mapped for this SQL user session, based on the user’s directory group. In this case, the user
manish was mapped to the
bi_users_group directory group in PingOne. We should see the SQL session reflect the corresponding database group in Amazon Redshift.
We were able to successfully accomplish MFA-based SSO identity federation with PingOne using the browser-based plugin that Amazon Redshift provides.
As mentioned earlier, the first step of this process is to set up SSO for Amazon Redshift with your IdP. The setup steps for that may vary depending on the provider. For more information, see the following resources:
- Federating single sign-on access to your Amazon Redshift cluster with PingIdentity
- Enabling multi-factor authentication for an Amazon Redshift cluster using Okta as an identity provider
- Federate Amazon Redshift access with Microsoft Azure AD single sign-on
- Federate access to Amazon Redshift with Active Directory Federation Services (AD FS)
- Federating Amazon Redshift access from OneLogin
The following videos also cover these details if you want to view them in action:
- Federating Amazon Redshift with PingOne Single Sign-On and Multi-Factor Authentication
- Federating Amazon Redshift Access with Microsoft Azure AD
Amazon Redshift makes it easy to integrate identity federation with your existing third-party identity providers, allowing you to centralize user and access management in a single corporate directory. In this post, we showed how the Amazon Redshift browser-based plugin works with popular SAML-based IdPs to provide an additional security layer with MFA authentication. You can also use the instructions in this post to set up various SAML-based IdPs (like Ping, Okta, JumpCloud, and OneLogin) to implement SSO and MFA with Amazon Redshift.
About the Authors
Manash Deb is a Senior Analytics Specialist Solutions Architect at AWS. He has worked on building end-to-end data driven solutions in different database and data warehousing technologies for over fifteen years. He loves to learn new technologies and solving, automating and simplifying customer problems with easy-to-use cloud data solutions on AWS.
Manish Vazirani is an Analytics Specialist Solutions Architect at Amazon Web Services.