Federate IAM-based single sign-on to Amazon Redshift role-based entry management with Okta

Amazon Redshift accelerates your time to insights with quick, straightforward, and safe cloud information warehousing at scale. Tens of hundreds of consumers depend on Amazon Redshift to research exabytes of information and run complicated analytical queries.

You should utilize your most well-liked SQL shoppers to research your information in an Amazon Redshift information warehouse. Join seamlessly by leveraging an identification supplier (IdP) or single sign-on (SSO) credentials to hook up with the Redshift information warehouse to reuse present consumer credentials and keep away from further consumer setup and configuration. Utilizing role-based entry management (RBAC), you’ll be able to simplify managing consumer privileges, database permissions and administration of safety permissions in Amazon Redshift. You can too use redshift database roles to outline a set of elevated permissions, reminiscent of for a system monitor or database administrator.

Utilizing AWS Id and Entry Administration (IAM) with RBAC, organizations can simplify consumer administration since you not have to create customers and map them to database roles manually. You may outline the mapped database roles as a principal tag for the IdP teams or IAM function, so Redshift database roles and customers who’re members of these IdP teams are granted to the database roles robotically.

Earlier in 2023, we launched help for Okta integration with Amazon Redshift Serverless utilizing database roles. On this publish, we deal with Okta because the IdP and supply step-by-step steering to combine a Redshift provisioned cluster with Okta utilizing the Redshift Question Editor v2 and with SQL shoppers like SQL Workbench/J. You should utilize this mechanism with different IdP suppliers reminiscent of Azure Energetic Listing or Ping with any purposes or instruments utilizing Amazon’s JDBC, ODBC, or Python driver.

Lately we additionally introduced Amazon Redshift integration with AWS IAM Id Heart, supporting trusted identification propagation, permitting you to make use of third-party Id Suppliers (IdP) reminiscent of Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin. This integration simplifies the authentication and authorization course of for Amazon Redshift customers utilizing Question Editor V2 or Amazon Quicksight, making it simpler for them to securely entry your information warehouse. AWS IAM Id Heart gives computerized consumer and group provisioning from Okta to itself by using the System for Cross-domain Id Administration (SCIM) 2.0 protocol. This integration permits for seamless synchronization of knowledge between two companies, making certain correct and up-to-date info in AWS IAM Id Heart. Check with Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Heart for seamless Single Signal-On weblog publish to study extra about organising single sign-on (SSO) to Amazon Redshift utilizing integration with IAM Id Heart and Okta because the Id Supplier.

If you’re enthusiastic about utilizing IAM-based single sign-on with Amazon Redshift database roles then you’ll be able to proceed studying this weblog.

Resolution overview

The next diagram illustrates the authentication movement of Okta with a Redshift provisioned cluster utilizing federated IAM roles and computerized database function mapping.

The workflow comprises the next steps:

1. Both the consumer chooses an IdP app of their browser, or the SQL consumer initiates a consumer authentication request to the IdP (Okta).
2. Upon a profitable authentication, Okta submits a request to the AWS federation endpoint with a SAML assertion containing the principal tags.
3. The AWS federation endpoint validates the SAML assertion and invokes the AWS Safety Token Service (AWS STS) API AssumeRoleWithSAML. The SAML assertion comprises the IdP consumer and group info that’s saved within the RedshiftDbUser and RedshiftDbRoles principal tags, respectively. Non permanent IAM credentials are returned to the SQL consumer or, if utilizing the Question Editor v2, the consumer’s browser is redirected to the Question Editor v2 console utilizing the short-term IAM credentials.
4. The short-term IAM credentials are utilized by the SQL consumer or Question Editor v2 to name the Redshift API GetClusterCredentialsWithIAM. This API makes use of the principal tags to find out the consumer and database roles that the consumer belongs to. An related database consumer is created if the consumer is signing in for the primary time and is granted the matching database roles robotically. A short lived password is returned to the SQL consumer.
5. Utilizing the database consumer and short-term password, the SQL consumer or Question Editor v2 connects to Amazon Redshift. Upon login, the consumer is allowed based mostly on the Redshift database roles that have been assigned in Step 4.

Conditions

You want the next stipulations to arrange this resolution:

Join with a Redshift provisioned cluster as a federated consumer utilizing Question Editor v2

To attach utilizing Question Editor v2, full the next steps:

1. Comply with all of the steps described within the sections Arrange your Okta software and Arrange AWS configuration within the following publish.
2. For the Amazon Redshift entry IAM coverage, change the coverage with the next JSON to make use of the GetClusterCredentialsWithIAM API:

   {
   	"Model": "2012-10-17",
   	"Assertion": [
   					{
   						"Sid": "VisualEditor0",
   						"Effect": "Allow",
   						"Action": "redshift:GetClusterCredentialsWithIAM",
   						"Resource": "arn:aws:redshift:us-west-2:123456789012:dbname:redshift-cluster-1/dev"
   					}
   				]
   }

Now you’re prepared to hook up with your Redshift provisioned cluster utilizing Question Editor v2 and federated login.

1. Use the SSO URL from Okta and log in to your Okta account together with your consumer credentials. For this demo, we log in with consumer Ethan.
2. In Question Editor v2, select your Redshift provisioned cluster (right-click) and select Create connection.
3. For Authentication, choose Non permanent credentials utilizing your IAM identification.
4. For Database, enter the database identify you wish to hook up with.
5. Select Create connection.
6. Run the next command to validate that you’re logged in as a federated consumer and in addition to get the record of roles related to that consumer for the present session:

SELECT current_user,* FROM pg_get_session_roles() eff_ro(identify identify, roleid integer);

As a result of Ethan is a part of the gross sales group and has been granted permissions to entry tables within the sales_schema, he ought to have the ability to entry these tables with none points. Nonetheless, if he tries to entry tables within the finance_schema, he would obtain a permission denied error as a result of Ethan will not be a part of the finance group in Okta.

Join with a Redshift provisioned cluster as a federated consumer by way of a third-party consumer

To attach as a federated consumer by way of a third-party consumer, full the next steps:

1. Comply with steps 1 and a couple of that are described in above part (Join with a Redshift provisioned cluster as a federated consumer utilizing Question Editor v2).
2. Use the Redshift JDBC driver v2.1.0.18 and above as a result of it helps authentication with IAM group federation. For the URL, enter jdbc:redshift:iam://<cluster endpoint>:<port>:<databasename>?groupfederation=true. For instance, jdbc:redshift:iam://redshift-cluster-1.abdef0abc0ab.us-west-2.redshift.amazonaws.com:5439/dev?groupfederation=true

Within the previous URL, groupfederation is a compulsory parameter that means that you can authenticate with the IAM credentials for the Redshift provisioned cluster. With out the groupfederation parameter, it is not going to use Redshift database roles.

3. For Username and Password, enter your Okta credentials.

4. To arrange prolonged properties, observe Steps 4–9 within the part Configure the SQL consumer (SQL Workbench/J) within the following publish.

Person Ethan will have the ability to entry the sales_schema tables. If Ethan tries to entry the tables within the finance_schema, he’ll get a permission denied error.

Troubleshooting

In case your connection didn’t work, contemplate the next:

• Allow logging within the driver. For directions, see Configure logging.
• Make sure that to make use of the newest Amazon Redshift JDBC driver model.
• In the event you’re getting errors whereas organising the appliance on Okta, be sure to have admin entry.
• In the event you can authenticate by way of the SQL consumer however get a permission problem or can’t see objects, grant the related permission to the function.

Clear up

While you’re carried out testing the answer, clear up the assets to keep away from incurring future fees:

1. Delete the Redshift provisioned cluster.
2. Delete the IAM roles, IAM IdPs, and IAM insurance policies.

Conclusion

On this publish, we supplied step-by-step directions to combine a Redshift provisioned cluster with Okta utilizing the Redshift Question Editor v2 and SQL Workbench/J with the assistance of federated IAM roles and computerized database function mapping. You should utilize the same setup with different SQL shoppers (reminiscent of DBeaver or DataGrip). We additionally confirmed how Okta group membership is mapped robotically with Redshift provisioned cluster roles to make use of role-based authentication seamlessly.

When you have any suggestions or questions, please go away them within the feedback.

Concerning the Authors

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an trade chief in analytics, software platform, and database applied sciences, and has greater than 25 years of expertise within the IT world.

Ranjan Burman is an Analytics Specialist Options Architect at AWS. He focuses on Amazon Redshift and helps clients construct scalable analytical options. He has greater than 16 years of expertise in several database and information warehousing applied sciences. He’s captivated with automating and fixing buyer issues with cloud options.

Maneesh Sharma is a Senior Database Engineer at AWS with greater than a decade of expertise designing and implementing large-scale information warehouse and analytics options. He collaborates with numerous Amazon Redshift Companions and clients to drive higher integration.