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

December 12, 2023

1

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

You should use your most well-liked SQL purchasers to research your knowledge in an Amazon Redshift knowledge warehouse. Join seamlessly by leveraging an identification supplier (IdP) or single sign-on (SSO) credentials to connect with the Redshift knowledge warehouse to reuse present person credentials and keep away from extra person setup and configuration. Utilizing role-based entry management (RBAC), you possibly can simplify managing person privileges, database permissions and administration of safety permissions in Amazon Redshift. It’s also possible to use redshift database roles to outline a set of elevated permissions, equivalent to for a system monitor or database administrator.

Utilizing AWS Identification and Entry Administration (IAM) with RBAC, organizations can simplify person administration since you now not must create customers and map them to database roles manually. You’ll be able to outline the mapped database roles as a principal tag for the IdP teams or IAM position, so Redshift database roles and customers who’re members of these IdP teams are granted to the database roles mechanically.

Earlier in 2023, we launched assist for Okta integration with Amazon Redshift Serverless utilizing database roles. On this put up, we give attention to 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 purchasers like SQL Workbench/J. You should use this mechanism with different IdP suppliers equivalent to Azure Lively 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 Identification Heart, supporting trusted identification propagation, permitting you to make use of third-party Identification Suppliers (IdP) equivalent to 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 knowledge warehouse. AWS IAM Identification Heart gives automated person and group provisioning from Okta to itself by using the System for Cross-domain Identification Administration (SCIM) 2.0 protocol. This integration permits for seamless synchronization of data between two companies, guaranteeing correct and up-to-date info in AWS IAM Identification Heart. Check with Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Identification Heart 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 Identification Supplier.

If you’re inquisitive about utilizing IAM-based single sign-on with Amazon Redshift database roles then you possibly can proceed studying this weblog.

Answer overview

The next diagram illustrates the authentication move of Okta with a Redshift provisioned cluster utilizing federated IAM roles and automated database position mapping.

The workflow comprises the next steps:

Both the person chooses an IdP app of their browser, or the SQL shopper initiates a person authentication request to the IdP (Okta).
Upon a profitable authentication, Okta submits a request to the AWS federation endpoint with a SAML assertion containing the principal tags.
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 person and group info that’s saved within the RedshiftDbUser and RedshiftDbRoles principal tags, respectively. Short-term IAM credentials are returned to the SQL shopper or, if utilizing the Question Editor v2, the person’s browser is redirected to the Question Editor v2 console utilizing the non permanent IAM credentials.
The non permanent IAM credentials are utilized by the SQL shopper or Question Editor v2 to name the Redshift API GetClusterCredentialsWithIAM. This API makes use of the principal tags to find out the person and database roles that the person belongs to. An related database person is created if the person is signing in for the primary time and is granted the matching database roles mechanically. A brief password is returned to the SQL shopper.
Utilizing the database person and non permanent password, the SQL shopper or Question Editor v2 connects to Amazon Redshift. Upon login, the person is allowed based mostly on the Redshift database roles that have been assigned in Step 4.

Conditions

You want the next conditions to arrange this resolution:

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

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

Observe all of the steps described within the sections Arrange your Okta utility and Arrange AWS configuration within the following put up.

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.

Use the SSO URL from Okta and log in to your Okta account along with your person credentials. For this demo, we log in with person Ethan.
In Question Editor v2, select your Redshift provisioned cluster (right-click) and select Create connection.
For Authentication, choose Short-term credentials utilizing your IAM identification.
For Database, enter the database identify you wish to hook up with.
Select Create connection.
Run the next command to validate that you’re logged in as a federated person and likewise to get the record of roles related to that person 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 to entry these tables with none points. Nevertheless, if he tries to entry tables within the finance_schema, he would obtain a permission denied error as a result of Ethan is just not a part of the finance group in Okta.

Join with a Redshift provisioned cluster as a federated person through a third-party shopper

To attach as a federated person through a third-party shopper, full the next steps:

Observe steps 1 and a pair of that are described in above part (Join with a Redshift provisioned cluster as a federated person utilizing Question Editor v2).
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 won’t use Redshift database roles.

For Username and Password, enter your Okta credentials.

To arrange prolonged properties, comply with Steps 4–9 within the part Configure the SQL shopper (SQL Workbench/J) within the following put up.

Consumer Ethan will be capable 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.
Be certain to make use of the most recent Amazon Redshift JDBC driver model.
When you’re getting errors whereas organising the applying on Okta, ensure you have admin entry.
When you can authenticate through the SQL shopper however get a permission challenge or can’t see objects, grant the related permission to the position.

Clear up

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

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

Conclusion

On this put up, we offered 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 position mapping. You should use an analogous setup with different SQL purchasers (equivalent to DBeaver or DataGrip). We additionally confirmed how Okta group membership is mapped mechanically with Redshift provisioned cluster roles to make use of role-based authentication seamlessly.

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

In regards to the Authors

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an business 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 focuses on Amazon Redshift and helps clients construct scalable analytical options. He has greater than 16 years of expertise in several database and knowledge 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 knowledge warehouse and analytics options. He collaborates with numerous Amazon Redshift Companions and clients to drive higher integration.