Single sign-on with Amazon Redshift Serverless with Okta utilizing Amazon Redshift Question Editor v2 and third-party SQL shoppers

Amazon Redshift Serverless makes it simple to run and scale analytics in seconds with out the necessity to arrange and handle information warehouse clusters. With Redshift Serverless, customers comparable to information analysts, builders, enterprise professionals, and information scientists can get insights from information by merely loading and querying information within the information warehouse.

Clients use their most popular SQL shoppers to research their information in Redshift Serverless. They wish to use an identification supplier (IdP) or single sign-on (SSO) credentials to hook up with Redshift Serverless to reuse present utilizing credentials and keep away from further consumer setup and configuration. While you use AWS Id and Entry Administration (IAM) or IdP-based credentials to hook up with a serverless information warehouse, Amazon Redshift mechanically creates a database consumer for the end-user. You may simplify managing consumer privileges through the use of role-based entry management. Admins can use a database-role mapping for SSO with the IAM roles that customers are assigned to get their database privileges mechanically. With this integration, organizations can simplify consumer administration as a result of they 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 position, so Amazon Redshift database roles and customers who’re members of these IdP teams are granted to the database roles mechanically.

On this submit, we give attention to Okta because the IdP and supply step-by-step steering to combine Redshift Serverless with Okta utilizing the Amazon Redshift Question Editor V2 and with SQL shoppers like SQL Workbench/J. You should utilize this mechanism with different IdP suppliers comparable to Azure Energetic Listing or Ping with any purposes or instruments utilizing Amazon’s JDBC/ODBC/Python driver.

Resolution overview

The next diagram illustrates the authentication move of Okta with Redshift Serverless utilizing federated IAM roles and automated database-role mapping.

The workflow incorporates 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 PrincipalTags.
3. The AWS federation endpoint validates the SAML assertion and invokes the AWS Safety Token Service (AWS STS) API AssumeRoleWithSAML. The SAML assertion incorporates the IdP consumer 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 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 shopper or Question Editor v2 to name the Redshift Serverless GetCredentials API. The 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 mechanically. A brief password is returned to the SQL shopper.
5. Utilizing the database consumer and non permanent password, the SQL shopper or Question Editor v2 connects to Redshift Serverless. Upon login, the consumer is permitted primarily based on the Amazon Redshift database roles that have been assigned in Step 4.

To arrange the answer, we full the next steps:

1. Arrange your Okta utility:
   • Create Okta customers.
   • Create teams and assign teams to customers.
   • Create the Okta SAML utility.
   • Accumulate Okta info.
2. Arrange AWS configuration:
   • Create the IAM IdP.
   • Create the IAM position and coverage.
3. Configure Redshift Serverless role-based entry.
4. Federate to Redshift Serverless utilizing the Question Editor V2.
5. Configure the SQL shopper (for this submit, we use SQL Workbench/J).
6. Optionally, implement MFA with SQL Consumer and Question Editor V2.

Stipulations

You want the next conditions to arrange this answer:

Arrange Okta utility

On this part, we offer the steps to configure your Okta utility.

Create Okta customers

To create your Okta customers, full the next steps:

1. Check in to your Okta group as a consumer with administrative privileges.
2. On the admin console, underneath Listing within the navigation pane, select Individuals.
3. Select Add individual.
4. For First Identify, enter the consumer’s first identify.
5. For Final Identify, enter the consumer’s final identify.
6. For Username, enter the consumer’s consumer identify in e-mail format.
7. Choose I’ll set password and enter a password.
8. Optionally, deselect Consumer should change password on first login in the event you don’t need the consumer to vary their password once they first register. Select Save.

Create teams and assign teams to customers

To create your teams and assign them to customers, full the next steps:

1. Check in to your Okta group as a consumer with administrative privileges.
2. On the admin console, underneath Listing within the navigation pane, select Teams.
3. Select Add group.
4. Enter a bunch identify and select Save.
5. Select the not too long ago created group after which select Assign folks.
6. Select the plus signal after which select Executed.
7. Repeat Steps 1–6 so as to add extra teams.

On this submit, we create two teams: gross sales and finance.

Create an Okta SAML utility

To create your Okta SAML utility, full the next steps:

1. Check in to your Okta group as a consumer with administrative privileges.
2. On the admin console, underneath Purposes within the navigation pane, select Purposes.
3. Select Create App Integration.
4. Choose SAML 2.0 because the sign-in technique and select Subsequent.
5. Enter a reputation to your app integration (for instance, redshift_app) and select Subsequent.
6. Enter following values within the app and go away the remaining as is:
7. Select Subsequent.
8. Select I’m an Okta buyer including an inner app adopted by That is an inner app that we’ve created.
9. Select End.
10. Select Assignments after which select Assign.
11. Select Assign to teams after which choose Assign subsequent to the teams that you just wish to add.
12. Select Executed.

Arrange Okta superior configuration

After you create the customized SAML app, full the next steps:

1. On the admin console, navigate to Normal and select Edit underneath SAML settings.
2. Select Subsequent.
3. Set Default Relay State to the Question Editor V2 URL, utilizing the format https://<area>.console.aws.amazon.com/sqlworkbench/dwelling. For this submit, we use https://us-west-2.console.aws.amazon.com/sqlworkbench/dwelling.
4. Below Attribute Statements (elective), add the next properties:
   • Present the IAM position and IdP in comma-separated format utilizing the Function attribute. You’ll create this identical IAM position and IdP in a later step when organising AWS configuration.
   • Set consumer.login for RoleSessionName. That is used as an identifier for the non permanent credentials which might be issued when the position is assumed.
   • Set the DB roles utilizing PrincipalTag:RedshiftDbRoles. This makes use of the Okta teams to fill the principal tags and map them mechanically with the Amazon Redshift database roles. Its worth have to be a colon-separated checklist within the format role1:role2.
   • Set consumer.login for PrincipalTag:RedshiftDbUser. This makes use of the consumer identify within the listing. This can be a required tag and defines the database consumer that’s utilized by Question Editor V2.
   • Set the transitive keys utilizing TransitiveTagKeys. This prevents customers from altering the session tags in case of position chaining.

The previous tags are forwarded to the GetCredentials API to get non permanent credentials to your Redshift Serverless occasion and map mechanically with Amazon Redshift database roles. The next desk summarizes their attribute statements configuration.

5. After you add the attribute claims, select Subsequent adopted by End.

Your attributes ought to be in related format as proven within the following screenshot.

Accumulate Okta info

To collect your Okta info, full the next steps:

1. On the Signal On tab, select View SAML setup directions.
   
2. For Id Supplier Single Signal-on URL, Use this URL when connecting with any third-party SQL shopper comparable to SQL Workbench/J.
3. Use the IdP metadata in block 4 and save the metadata file in .xml format (for instance, metadata.xml).

Arrange AWS configuration

On this part, we offer the steps to configure your IAM sources.

Create the IAM IdP

To create your IAM IdP, full the next steps:

1. On the IAM console, underneath Entry administration within the navigation pane, select Id suppliers.
2. Select Add supplier.
3. For Supplier sort¸ choose SAML.
4. For Supplier identify¸ enter a reputation.
5. Select Select file and add the metadata file (.xml) you downloaded earlier.
6. Select Add supplier.

Create the IAM Amazon Redshift entry coverage

To create your IAM coverage, full the next steps:

1. On the IAM console, select Insurance policies.
2. Select Create coverage.
3. On the Create coverage web page, select the JSON tab.
4. For the coverage, enter the JSON in following format:

   {
       "Model": "2012-10-17",
       "Assertion": [
           {
               "Sid": "VisualEditor0",
               "Effect": "Allow",
               "Action": "redshift-serverless:GetCredentials",
               "Resource": "<Workgroup ARN>"
           },
           {
               "Sid": "VisualEditor1",
               "Effect": "Allow",
               "Action": "redshift-serverless:ListWorkgroups",
               "Resource": "*"
           }
       ]
   }

The workgroup ARN is accessible on the Redshift Serverless workgroup configuration web page.

The next instance coverage consists of solely a single Redshift Serverless workgroup; you possibly can modify the coverage to incorporate a number of workgroups within the Useful resource part:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "redshift-serverless:GetCredentials",
            "Resource": "arn:aws:redshift-serverless:us-west-2:123456789012:workgroup/4a4f12vc-123b-2d99-fd34-a12345a1e87f"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "redshift-serverless:ListWorkgroups",
            "Resource": "*"
        }
    ]
}

5. Select Subsequent: Tags.
6. Select Subsequent: Evaluate.
7. Within the Evaluate coverage part, for Identify, enter the identify of your coverage; for instance, OktaRedshiftPolicy.
8. For Description, you possibly can optionally enter a quick description of what the coverage does.
9. Select Create coverage.

Create the IAM position

To create your IAM position, full the next steps:

1. On the IAM console, select Roles within the navigation pane.
2. Select Create position.
3. For Trusted entity sort, choose SAML 2.0 federation.
4. For SAML 2.0-based supplier, select the IdP you created earlier.
5. Choose Permit programmatic and AWS Administration Console entry.
6. Select Subsequent.
7. Select the coverage you created earlier.
8. Additionally, add the coverage AmazonRedshiftQueryEditorV2ReadSharing.
9. Select Subsequent.
10. Within the Evaluate part, for Function Identify, enter the identify of your position; for instance, oktarole.
11. For Description, you possibly can optionally enter a quick description of what the position does.
12. Select Create position.
13. Navigate to the position that you just simply created and select Belief Relationships.
14. Select Edit belief coverage and select TagSession underneath Add actions for STS.

When utilizing session tags, belief insurance policies for all roles linked to the IdP passing tags should have the sts:TagSession permission. For roles with out this permission within the belief coverage, the AssumeRole operation fails.

15. Select Replace coverage.

The next screenshot exhibits the position permissions.

The next screenshot exhibits the belief relationships.

Replace the superior Okta Function Attribute

Full the next steps:

1. Change again to Okta.com.
2. Navigate to the applying which you created earlier.
3. Navigate to Normal and click on Edit underneath SAML settings.
4. Below Attribute Statements (elective), replace the worth for the attribute – https://aws.amazon.com/SAML/Attributes/Function, utilizing the precise position and identification supplier arn values from the above step. For instance, arn:aws:iam::123456789012:position/oktarole,arn:aws:iam::123456789012:saml-provider/oktaidp.

Configure Redshift Serverless role-based entry

On this step, we create database roles in Amazon Redshift primarily based on the teams that you just created in Okta. Make certain the position identify matches with the Okta Group identify.

Amazon Redshift roles simplify managing privileges required to your end-users. On this submit, we create two database roles, gross sales and finance, and grant them entry to question tables with gross sales and finance information, respectively. You may obtain this pattern SQL Pocket book and import into Redshift Question Editor v2 to run all cells within the pocket book used on this instance. Alternatively, you possibly can copy and enter the SQL into your SQL shopper.

The next is the syntax to create a task in Redshift Serverless:

create position <IdP groupname>;

For instance:

create position gross sales;
create position finance;

Create the gross sales and finance database schema:

create schema sales_schema;
create schema finance_schema;

Create the tables:

CREATE TABLE IF NOT EXISTS finance_schema.income
(
account INTEGER   ENCODE az64
,buyer VARCHAR(20)   ENCODE lzo
,salesamt NUMERIC(18,0)   ENCODE az64
)
DISTSTYLE AUTO
;

insert into finance_schema.income values (10001, 'ABC Firm', 12000);
insert into finance_schema.income values (10002, 'Tech Logistics', 175400);
insert into finance_schema.income values (10003, 'XYZ Trade', 24355);
insert into finance_schema.income values (10004, 'The tax specialists', 186577);

CREATE TABLE IF NOT EXISTS sales_schema.store_sales
(
ID INTEGER   ENCODE az64,
Product varchar(20),
Sales_Amount INTEGER   ENCODE az64
)
DISTSTYLE AUTO
;

Insert into sales_schema.store_sales values (1,'product1',1000);
Insert into sales_schema.store_sales values (2,'product2',2000);
Insert into sales_schema.store_sales values (3,'product3',3000);
Insert into sales_schema.store_sales values (4,'product4',4000);

The next is the syntax to grant permission to the Redshift Serverless position:

GRANT  ALL [ PRIVILEGES ]  ON  ALL TABLES IN SCHEMA schema_name [, ...]  TO position <IdP groupname>;

Grant related permission to the position as per your necessities. Within the following instance, we grant full permission to the position gross sales on sales_schema and solely choose permission on finance_schema to the position finance:

grant utilization on schema sales_schema to position gross sales;
grant choose on all tables in schema sales_schema to position gross sales;

grant utilization on schema finance_schema to position finance;
grant choose on all tables in schema finance_schema to position finance;

Federate to Redshift Serverless utilizing Question Editor V2

The RedshiftDbRoles principal tag and DBGroups are each mechanisms that can be utilized to combine with an IdP. Nonetheless, federating with the RedshiftDbRoles principal has some clear benefits with regards to connecting with an IdP as a result of it supplies automated mapping between IdP teams and Amazon Redshift database roles. Total, RedshiftDbRoles is extra versatile, simpler to handle, and safer, making it the higher possibility for integrating Amazon Redshift along with your IdP.

Now you’re prepared to hook up with Redshift Serverless utilizing the Question Editor V2 and federated login:

1. Use the SSO URL you collected earlier and log in to your Okta account along with your consumer credentials. For this demo, we log in with consumer Ethan.
2. Within the Question Editor v2, select your Redshift Serverless occasion (right-click) and select Create connection.
3. For Authentication, choose Federated consumer.
4. For Database, enter the database identify you wish to connect with.
5. Select Create Connection.

Consumer Ethan will be capable of entry sales_schema tables. If Ethan tries to entry the tables in finance_schema, he’ll get a permission denied error.

Configure the SQL shopper (SQL Workbench/J)

To arrange SQL Workbench/J, full the next steps:

1. Create a brand new connection in SQL Workbench/J and select Redshift Serverless as the motive force.
2. Select Handle drivers and add all of the recordsdata from the downloaded AWS JDBC driver pack .zip file (bear in mind to unzip the .zip file).
3. For Username and Password, enter the values that you just set in Okta.
   
4. Seize the values for app_id, app_name, and idp_host from the Okta app embed hyperlink, which could be discovered on the Normal tab of your utility.
   
5. Set the next prolonged properties:
   • For app_id, enter the worth from app embed hyperlink (for instance, 0oa8p1o1RptSabT9abd0/avc8k7abc32lL4izh3b8).
   • For app_name, enter the worth from app embed hyperlink (for instance, dev-123456_redshift_app_2).
   • For idp_host, enter the worth from app embed hyperlink (for instance, dev-123456.okta.com).
   • For plugin_name, enter com.amazon.redshift.plugin.OktaCredentialsProvider. The next screenshot exhibits the SQL Workbench/J prolonged properties.
     
     6. Select OK.
     7. Select Take a look at from SQL Workbench/J to check the connection.
     8. When the connection is profitable, select OK.
     9. Select OK to register with the customers created.

Consumer 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.

Congratulations! You will have federated with Redshift Serverless and Okta with SQL Workbench/J utilizing RedshiftDbRoles.

[Optional] Implement MFA with SQL Consumer and Question Editor V2

Implementing MFA poses a further problem as a result of the character of multi-factor authentication is an asynchronous course of between initiating the login (the primary issue) and finishing the login (the second issue). The SAML response will probably be returned to the suitable listener in every situation; the SQL Consumer or the AWS console within the case of QEV2. Relying on which login choices you can be giving your customers, you might want a further Okta utility. See beneath for the totally different eventualities:

1. In case you are ONLY utilizing QEV2 and never utilizing another SQL shopper, then you need to use MFA with Question Editor V2 with the above utility. There aren’t any modifications required within the customized SAML utility which we’ve created above.
2. In case you are NOT utilizing QEV2 and solely utilizing third occasion SQL shopper (SQL Workbench/J and so on), then it’s essential modify the above customized SAML app as talked about beneath.
3. If you wish to use QEV2 and third-party SQL Consumer with MFA, you then want create a further customized SAML app as talked about beneath.

Stipulations for MFA

Every identification supplier (IdP) has step for enabling and managing MFA to your customers. Within the case of Okta, see the next guides on how one can allow MFA utilizing the Okta Confirm utility and by defining an authentication coverage.

Steps to create/replace SAML utility which helps MFA for a SQL Consumer

1. If making a second app, comply with all of the steps that are described underneath part 1 (Create Okta SAML utility).
2. Open the customized SAML app and choose Normal.
3. Choose Edit underneath SAML settings
4. Click on Subsequent in Normal Settings
5. Below Normal, replace the Single sign-on URL to http://localhost:7890/redshift/
6. Choose Subsequent adopted by End.

Under is the screenshot from the MFA App after making above modifications:

Configure SQL Consumer for MFA

To arrange SQL Workbench/J, full the next steps:

1. Observe all of the steps that are described underneath (Configure the SQL shopper (SQL Workbench/J))
2. Modify your connection updating the prolonged properties:
   • login_url – Get the Single Signal-on URL as proven in part -Accumulate Okta info. (For instance, https://dev-123456.okta.com/app/dev-123456_redshiftapp_2/abc8p6o5psS6xUhBJ517/sso/saml)
   • plugin_name – com.amazon.redshift.plugin.BrowserSamlCredentialsProvider
     
3. Select OK
4. Select OK from SQL Workbench/J. You’re redirected to the browser to register along with your Okta credentials.
5. After that, you’ll get immediate for MFA. Select both Enter a code or Get a push notification.
   
6. As soon as authentication is profitable, log in to be redirected to a web page displaying the connection as profitable.
   
7. With this connection profile, run the next question to return federated consumer identify.
   

Troubleshooting

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

• Allow logging within the driver. For directions, see Configure logging.
• Make certain to make use of the most recent Amazon Redshift JDBC driver model.
• If you happen to’re getting errors whereas organising the applying 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, as detailed earlier on this submit.

Clear up

While you’re achieved testing the answer, clear up the sources to keep away from incurring future prices:

1. Delete the Redshift Serverless occasion by deleting each the workgroup and the namespace.
2. Delete the IAM roles, IAM IdPs, and IAM insurance policies.

Conclusion

On this submit, we offered step-by-step directions to combine Redshift Serverless with Okta utilizing the Amazon Redshift Question Editor V2 and SQL Workbench/J with the assistance of federated IAM roles and automated database-role mapping. You should utilize an analogous setup with another SQL shopper (comparable to DBeaver or DataGrip) or enterprise intelligence software (comparable to Tableau Desktop). We additionally confirmed how Okta group membership is mapped mechanically with Redshift Serverless roles to make use of role-based authentication seamlessly.

For extra details about Redshift Serverless single sign-on utilizing database roles, see Defining database roles to grant to federated customers in Amazon Redshift Serverless.

In regards to the Authors

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 prospects to drive higher integration.

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.

Mohamed Shaaban is a Senior Software program Engineer in Amazon Redshift and is predicated in Berlin, Germany. He has over 12 years of expertise within the software program engineering. He’s keen about cloud companies and constructing options that delight prospects. Outdoors of labor, he’s an novice photographer who likes to discover and seize distinctive moments.

Rajiv Gupta is Sr. Supervisor of Analytics Specialist Options Architects primarily based out of Irvine, CA. He has 20+ years of expertise constructing and managing groups who construct information warehouse and enterprise intelligence options.

Amol Mhatre is a Database Engineer in Amazon Redshift and works on Buyer & Companion engagements. Previous to Amazon, he has labored on a number of initiatives involving Database & ERP implementations.

Ning Di is a Software program Improvement Engineer at Amazon Redshift, pushed by a real ardour for exploring all facets of expertise.

Harsha Kesapragada is a Software program Improvement Engineer for Amazon Redshift with a ardour to construct scalable and safe programs. Prior to now few years, he has been engaged on Redshift Datasharing, Safety and Redshift Serverless.