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 need to use your most well-liked SQL shoppers to research your information in an Amazon Redshift information warehouse. Join seamlessly by leveraging an id supplier (IdP) or single sign-on (SSO) credentials to connect with the Redshift information warehouse to reuse current consumer credentials and keep away from further consumer setup and configuration. Utilizing role-based entry management (RBAC), you may 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 routinely.

Earlier in 2023, we launched help for Okta integration with Amazon Redshift Serverless utilizing database roles. On this put up, we concentrate on 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 need to use this mechanism with different IdP suppliers reminiscent of Azure Lively Listing or Ping with any functions or instruments utilizing Amazon’s JDBC, ODBC, or Python driver.

Not too long ago we additionally introduced Amazon Redshift integration with AWS IAM Id Middle, supporting trusted id 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 Middle affords automated 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 data between two providers, making certain correct and up-to-date data in AWS IAM Id Middle. Seek advice from Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Middle for seamless Single Signal-On weblog put up to be taught extra about organising single sign-on (SSO) to Amazon Redshift utilizing integration with IdC and Okta because the Id Supplier.

If you’re concerned with utilizing IAM-based single sign-on with Amazon Redshift database roles then you may proceed studying this weblog.

Answer overview

The next diagram illustrates the authentication circulate of Okta with a Redshift provisioned cluster utilizing federated IAM roles and automated 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 data that’s saved within the RedshiftDbUser and RedshiftDbRoles principal tags, respectively. Short-term 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 non permanent IAM credentials.
4. The non permanent 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 routinely. A brief password is returned to the SQL consumer.
5. Utilizing the database consumer and non permanent password, the SQL consumer or Question Editor v2 connects to Amazon Redshift. Upon login, the consumer is permitted based mostly on the Redshift database roles that have been assigned in Step 4.

Conditions

You want the next stipulations to arrange this answer:

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. Observe all of the steps described within the sections Arrange your Okta utility and Arrange AWS configuration within the following put up.
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 connect 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 Short-term credentials utilizing your IAM id.
4. For Database, enter the database identify you need 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 be capable of 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 shouldn’t 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. Observe steps 1 and a pair 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 put up.

Person Ethan will be capable of 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, think about the next:

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

Clear up

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

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

Conclusion

On this put up, 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 automated database function mapping. You need to use an identical setup with different SQL shoppers (reminiscent of DBeaver or DataGrip). We additionally confirmed how Okta group membership is mapped routinely with Redshift provisioned cluster roles to make use of role-based authentication seamlessly.

If in case 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, utility 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 makes a speciality of Amazon Redshift and helps clients construct scalable analytical options. He has greater than 16 years of expertise in numerous database and information warehousing applied sciences. He’s obsessed 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 varied Amazon Redshift Companions and clients to drive higher integration.