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

You need to use your most well-liked SQL purchasers to research your knowledge in an Amazon Redshift knowledge warehouse. Join seamlessly by leveraging an id supplier (IdP) or single sign-on (SSO) credentials to connect with the Redshift knowledge warehouse to reuse present consumer credentials and keep away from extra 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 may as well use redshift database roles to outline a set of elevated permissions, equivalent to for a system monitor or database administrator.

Utilizing AWS Id and Entry Administration (IAM) with RBAC, organizations can simplify consumer administration since you not must 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 position, 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 assist 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 steerage to combine a Redshift provisioned cluster with Okta utilizing the Redshift Question Editor v2 and with SQL purchasers like SQL Workbench/J. You need to use this mechanism with different IdP suppliers equivalent to 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 Middle, supporting trusted id propagation, permitting you to make use of third-party Id 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 Id Middle provides 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 knowledge between two providers, making certain correct and up-to-date info in AWS IAM Id Middle. Consult with Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Middle for seamless Single Signal-On weblog publish to study extra about organising single sign-on (SSO) to Amazon Redshift utilizing integration with IAM Id Middle and Okta because the Id Supplier.

In case you are excited about utilizing IAM-based single sign-on with Amazon Redshift database roles then you’ll be able to proceed studying this weblog.

Answer overview

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

The workflow accommodates the next steps:

1. Both the consumer chooses an IdP app of their browser, or the SQL shopper 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 accommodates 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 shopper 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 shopper 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 shopper.
5. Utilizing the database consumer and short-term password, the SQL shopper or Question Editor v2 connects to Amazon Redshift. Upon login, the consumer is permitted primarily based 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. Comply with all of the steps described within the sections Arrange your Okta utility and Arrange AWS configuration within the following publish.
2. For the Amazon Redshift entry IAM coverage, exchange 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 along 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 id.
4. For Database, enter the database title 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 checklist of roles related to that consumer for the present session:

SELECT current_user,* FROM pg_get_session_roles() eff_ro(title title, 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 shopper

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

1. Comply with 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 permits you to authenticate with the IAM credentials for the Redshift provisioned cluster. With out the groupfederation parameter, it won’t 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 shopper (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.
• If you happen to’re getting errors whereas organising the appliance on Okta, be sure you have admin entry.
• If you happen to can authenticate by way of the SQL shopper however get a permission problem or can’t see objects, grant the related permission to the position.

Clear up

Whenever you’re accomplished testing the answer, clear up the assets to keep away from incurring future prices:

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

Conclusion

On this publish, 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 need to use an analogous setup with different SQL purchasers (equivalent to 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’ve got any suggestions or questions, please depart 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 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 knowledge warehousing applied sciences. He’s enthusiastic about 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 varied Amazon Redshift Companions and clients to drive higher integration.