Home » Big Data

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

Amazon Redshift Serverless makes it simple to run and scale analytics in seconds without the requirement to establish and handle information storage facility clusters. With Redshift Serverless, users such as information experts, designers, company experts, and information researchers can get insights from information by merely packing and querying information in the information storage facility.

Clients utilize their favored SQL customers to examine their information in Redshift Serverless. They wish to utilize an identity supplier (IdP) or single sign-on (SSO) qualifications to link to Redshift Serverless to recycle existing utilizing qualifications and prevent extra user setup and setup. When you utilize AWS Identity and Gain Access To Management (IAM) or IdP-based qualifications to link to a serverless information storage facility, Amazon Redshift immediately produces a database user for the end-user. You can streamline handling user advantages by utilizing role-based gain access to control. Admins can utilize a database-role mapping for SSO with the IAM functions that users are appointed to get their database advantages immediately. With this combination, companies can streamline user management due to the fact that they no longer require to develop users and map them to database functions by hand. You can specify the mapped database functions as a primary tag for the IdP groups or IAM function, so Amazon Redshift database functions and users who are members of those IdP groups are given to the database functions immediately.

In this post, we concentrate on Okta as the IdP and supply detailed assistance to incorporate Redshift Serverless with Okta utilizing the Amazon Redshift Question Editor V2 and with SQL customers like SQL Workbench/J. You can utilize this system with other IdP suppliers such as Azure Active Directory Site or Ping with any applications or tools utilizing Amazon’s JDBC/ODBC/Python motorist.

Option summary

The following diagram shows the authentication circulation of Okta with Redshift Serverless utilizing federated IAM functions and automated database-role mapping.

The workflow consists of the following actions:

  1. Either the user picks an IdP app in their web browser, or the SQL customer starts a user authentication demand to the IdP (Okta).
  2. Upon an effective authentication, Okta sends a demand to the AWS federation endpoint with a SAML assertion consisting of the PrincipalTags.
  3. The AWS federation endpoint verifies the SAML assertion and conjures up the AWS Security Token Service (AWS STS) API AssumeRoleWithSAML. The SAML assertion consists of the IdP user and group details that is kept in the RedshiftDbUser and RedshiftDbRoles primary tags, respectively. Short-lived IAM qualifications are gone back to the SQL customer or, if utilizing the Question Editor v2, the user’s web browser is rerouted to the Question Editor v2 console utilizing the short-term IAM qualifications.
  4. The short-term IAM qualifications are utilized by the SQL customer or Question Editor v2 to call the Redshift Serverless GetCredentials API. The API utilizes the primary tags to identify the user and database functions that the user comes from. An associated database user is produced if the user is checking in for the very first time and is given the matching database functions immediately. A short-term password is gone back to the SQL customer.
  5. Utilizing the database user and short-term password, the SQL customer or Question Editor v2 links to Redshift Serverless. Upon login, the user is licensed based upon the Amazon Redshift database functions that were appointed in Action 4.

To establish the option, we finish the following actions:

  1. Establish your Okta application:
    • Produce Okta users.
    • Produce groups and designate groups to users.
    • Produce the Okta SAML application.
    • Collect Okta details.
  2. Establish AWS setup:
    • Produce the IAM IdP.
    • Produce the IAM function and policy.
  3. Configure Redshift Serverless role-based gain access to.
  4. Federate to Redshift Serverless utilizing the Question Editor V2.
  5. Set up the SQL customer (for this post, we utilize SQL Workbench/J).
  6. Additionally, carry out MFA with SQL Customer and Question Editor V2.

Requirements

You require the following requirements to establish this option:

Establish Okta application

In this area, we supply the actions to configure your Okta application.

Produce Okta users

To develop your Okta users, finish the following actions:

  1. Check In to your Okta company as a user with administrative advantages.
  2. On the admin console, under Directory Site in the navigation pane, pick Individuals
  3. Select Include individual
  4. For First Call, get in the user’s given name.
  5. For Surname, get in the user’s surname.
  6. For Username, get in the user’s user name in e-mail format.
  7. Select I will set password and get in a password.
  8. Additionally, deselect User should alter password on very first login if you do not desire the user to alter their password when they initially check in. Select Conserve

Produce groups and designate groups to users

To develop your groups and designate them to users, finish the following actions:

  1. Check In to your Okta company as a user with administrative advantages.
  2. On the admin console, under Directory Site in the navigation pane, pick Groups
  3. Select Include group
  4. Go into a group name and pick Conserve
  5. Select the just recently produced group and after that pick Appoint individuals
  6. Select the plus indication and after that pick Done
  7. Repeat Steps 1– 6 to include more groups.

In this post, we develop 2 groups: sales and financing.

Produce an Okta SAML application

To develop your Okta SAML application, finish the following actions:

  1. Check In to your Okta company as a user with administrative advantages.
  2. On the admin console, under Applications in the navigation pane, pick Applications
  3. Select Produce App Combination
  4. Select SAML 2.0 as the sign-in approach and pick Next
  5. Go into a name for your app combination (for instance, redshift_app) and pick Next
  6. Go into following worths in the app and leave the rest as is:
  7. Select Next
  8. Select I’m an Okta consumer including an internal app followed by This is an internal app that we have actually produced
  9. Select End Up
  10. Select Tasks and after that pick Assign
  11. Select Appoint to groups and after that choose Assign beside the groups that you wish to include.
  12. Select Done

Establish Okta advanced setup

After you develop the customized SAML app, finish the following actions:

  1. On the admin console, browse to General and pick Edit under SAML settings
  2. Select Next
  3. Set Default Relay State to the Question Editor V2 URL, utilizing the format https://.console.aws.amazon.com/sqlworkbench/home For this post, we utilize https://us-west-2.console.aws.amazon.com/sqlworkbench/home
  4. Under Associate Declarations (optional), include the following homes:
    • Supply the IAM function and IdP in comma-separated format utilizing the Function characteristic. You’ll develop this exact same IAM function and IdP in a later action when establishing AWS setup.
    • Set user.login for RoleSessionName This is utilized as an identifier for the short-term qualifications that are released when the function is presumed.
    • Set the DB functions utilizing PrincipalTag: RedshiftDbRoles This utilizes the Okta groups to fill the primary tags and map them immediately with the Amazon Redshift database functions. Its worth should be a colon-separated list in the format role1: role2.
    • Set user.login for PrincipalTag: RedshiftDbUser This utilizes the user name in the directory site. This is a needed tag and specifies the database user that is utilized by Question Editor V2.
    • Set the transitive secrets utilizing TransitiveTagKeys This avoids users from altering the session tags in case of function chaining.

The preceding tags are forwarded to the GetCredentials API to get short-term qualifications for your Redshift Serverless circumstances and map immediately with Amazon Redshift database functions. The following table summarizes their characteristic declarations setup.

Call Call Format Format Example
https://aws.amazon.com/SAML/Attributes/Role Undefined arn: aws: iam::<< yourAWSAccountID>>: role/role-name, arn: aws: iam:: << yourAWSAccountID>>: saml-provider/provider-name arn: aws: iam::112034567890: role/oktarole, arn: aws: iam::112034567890: saml-provider/oktaidp
https://aws.amazon.com/SAML/Attributes/RoleSessionName Undefined user.login user.login
https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbRoles Undefined String.join(":", isMemberOfGroupName(" group1")? 'group1': ", isMemberOfGroupName(" group2")? 'group2': ") String.join(":", isMemberOfGroupName(" sales")? 'sales': ", isMemberOfGroupName(" financing")? 'financing': ")
https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbUser Undefined user.login user.login
https://aws.amazon.com/SAML/Attributes/TransitiveTagKeys Undefined Arrays.flatten(" RedshiftDbUser", "RedshiftDbRoles") Arrays.flatten(" RedshiftDbUser", "RedshiftDbRoles")
  1. After you include the characteristic claims, pick Next followed by End Up.

Your characteristics must remain in comparable format as displayed in the following screenshot.

Collect Okta details

To collect your Okta details, finish the following actions:

  1. On the Indication On tab, pick View SAML setup guidelines
  2. For Identity Service Provider Single Sign-on URL, Utilize this URL when getting in touch with any third-party SQL customer such as SQL Workbench/J.
  3. Utilize the IdP metadata in block 4 and conserve the metadata file in.xml format (for instance, metadata.xml).

Establish AWS setup

In this area, we supply the actions to configure your IAM resources.

Produce the IAM IdP

To develop your IAM IdP, finish the following actions:

  1. On the IAM console, under Gain access to management in the navigation pane, pick Identity suppliers
  2. Select Include supplier
  3. For Company type ¸ choose SAML
  4. For Company name ¸ get in a name.
  5. Select Select file and submit the metadata file (. xml) you downloaded previously.
  6. Select Include supplier

Produce the IAM Amazon Redshift gain access to policy

To develop your IAM policy, finish the following actions:

  1. On the IAM console, pick Policies
  2. Select Produce policy
  3. On the Produce policy page, pick the JSON tab.
  4. For the policy, get in the JSON in following format: 
     {
" Variation": "2012-10-17",.
" Declaration":[
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "redshift-serverless:GetCredentials",
            "Resource": "<Workgroup ARN>"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "redshift-serverless:ListWorkgroups",
            "Resource": "*"
        }
    ]
}

The workgroup ARN is offered on the Redshift Serverless workgroup setup page.

The copying policy consists of just a single Redshift Serverless workgroup; you can customize the policy to consist of several workgroups in the Resource area: 

 {
" Variation": "2012-10-17",.
" Declaration":[
        {
            "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": "*"
        }
    ]
}

  1. Select Next: Tags
  2. Select Next: Evaluation
  3. In the Evaluation policy area, for Call, get in the name of your policy; for instance, OktaRedshiftPolicy.
  4. For Description, you can additionally get in a quick description of what the policy does.
  5. Select Produce policy

Produce the IAM function

To develop your IAM function, finish the following actions:

  1. On the IAM console, pick Functions in the navigation pane.
  2. Select Produce function
  3. For Relied on entity type, choose SAML 2.0 federation
  4. For SAML 2.0-based supplier, pick the IdP you produced previously.
  5. Select Enable programmatic and AWS Management Console gain access to
  6. Select Next
  7. Select the policy you produced previously.
  8. Likewise, include the policy AmazonRedshiftQueryEditorV2ReadSharing
  9. Select Next
  10. In the Evaluation area, for Function Call, get in the name of your function; for instance, oktarole
  11. For Description, you can additionally get in a quick description of what the function does.
  12. Select Produce function
  13. Browse to the function that you simply produced and pick Trust Relationships
  14. Select Modify trust policy and pick TagSession under Include actions for STS

When utilizing session tags, trust policies for all functions linked to the IdP passing tags need to have the sts: TagSession approval. For functions without this approval in the trust policy, the AssumeRole operation stops working.

  1. Select Update policy

The following screenshot reveals the function authorizations.

The following screenshot reveals the trust relationships.

Update the sophisticated Okta Function Quality

Total the following actions:

  1. Change back to Okta.com
  2. Browse to the application which you produced previously.
  3. Browse to General and click Edit under SAML settings.
  4. Under Quality Declarations (optional), upgrade the worth for the characteristic– https://aws.amazon.com/SAML/Attributes/Role, utilizing the real function and identity supplier arn worths from the above action. For instance, arn: aws: iam::123456789012: role/oktarole, arn: aws: iam::123456789012: saml-provider/oktaidp

Configure Redshift Serverless role-based gain access to

In this action, we develop database functions in Amazon Redshift based upon the groups that you produced in Okta. Make certain the function name matches with the Okta Group name.

Amazon Redshift functions streamline handling advantages needed for your end-users. In this post, we develop 2 database functions, sales and financing, and approve them access to query tables with sales and financing information, respectively. You can download this sample SQL Note pad and import into Redshift Question Editor v2 to run all cells in the note pad utilized in this example. Additionally, you can copy and get in the SQL into your SQL customer.

The following is the syntax to develop a function in Redshift Serverless: 

 develop function << IdP groupname>>;

For instance: 

 develop function sales;.
develop function financing;.

Produce the sales and financing database schema: 

 develop schema sales_schema;.
develop schema finance_schema;.

Produce the tables: 

 DEVELOP TABLE IF NOT EXISTS finance_schema. income.
(.
account INTEGER ENCODE az64.
, consumer VARCHAR( 20) ENCODE lzo.
, salesamt NUMERIC( 18,0) ENCODE az64.
).
DISTSTYLE CAR.
;.

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

DEVELOP TABLE IF NOT EXISTS sales_schema. store_sales.
(.
ID INTEGER ENCODE az64,.
Item varchar( 20 ),.
Sales_Amount INTEGER ENCODE az64.
).
DISTSTYLE CAR.
;.

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

The following is the syntax to approve approval to the Redshift Serverless function: 

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

Grant appropriate approval to the function according to your requirements. In the copying, we approve complete approval to the function sales on sales_schema and just choose approval on finance_schema to the function financing: 

 grant use on schema sales_schema to function sales;.
grant choose on all tables in schema sales_schema to function sales;.

grant use on schema finance_schema to function financing;.
grant choose on all tables in schema finance_schema to function financing;

Federate to Redshift Serverless utilizing Question Editor V2

The RedshiftDbRoles primary tag and DBGroups are both systems that can be utilized to incorporate with an IdP. Nevertheless, federating with the RedshiftDbRoles principal has some clear benefits when it pertains to getting in touch with an IdP due to the fact that it supplies automated mapping in between IdP groups and Amazon Redshift database functions. In general, RedshiftDbRoles is more versatile, much easier to handle, and more safe and secure, making it the much better choice for incorporating Amazon Redshift with your IdP.

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

  1. Utilize the SSO URL you gathered earlier and log in to your Okta account with your user qualifications. For this demonstration, we visit with user Ethan.
  2. In the Question Editor v2, pick your Redshift Serverless circumstances (right-click) and pick Produce connection.
  3. For Authentication, choose Federated user
  4. For Database, get in the database name you wish to link to.
  5. Select Produce Connection.

User Ethan will have the ability to gain access to sales_schema tables. If Ethan attempts to access the tables in finance_schema, he will get an approval rejected mistake.

Set up the SQL customer (SQL Workbench/J)

To establish SQL Workbench/J, total the following actions:

  1. Produce a brand-new connection in SQL Workbench/J and pick Redshift Serverless as the motorist.
  2. Select Manage motorists and include all the files from the downloaded AWS JDBC motorist pack.zip file (keep in mind to unzip the.zip file).
  3. For Username and Password, get in the worths that you embed in Okta.
  4. Record the worths for app_id, app_name, and idp_host from the Okta app embed link, which can be discovered on the General tab of your application.
  5. Set the following extended homes:.
    • For app_id, get in the worth from app embed link (for instance, 0oa8p1o1RptSabT9abd0/avc8k7abc32lL4izh3b8).
    • For app_name, get in the worth from app embed link (for instance, dev-123456_redshift_app_2).
    • For idp_host, get in the worth from app embed link (for instance, dev-123456. okta.com).
    • For plugin_name, get in com.amazon.redshift.plugin.OktaCredentialsProvider The following screenshot reveals the SQL Workbench/J extended homes.
      1. Select OKAY
      2. Select Test from SQL Workbench/J to evaluate the connection.
      3. When the connection achieves success, pick OKAY
      4. Select OKAY to check in with the users produced.

User Ethan will have the ability to access the sales_schema tables. If Ethan attempts to access the tables in the finance_schema, he will get an approval rejected mistake.

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

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

Carrying out MFA postures an extra obstacle due to the fact that the nature of multi-factor authentication is an asynchronous procedure in between starting the login (the very first element) and finishing the login (the 2nd element). The SAML reaction will be gone back to the proper listener in each situation; the SQL Customer or the AWS console when it comes to QEV2. Depending upon which login choices you will be providing your users, you might require an extra Okta application. See listed below for the various situations:

  1. If you are just utilizing QEV2 and not utilizing any other SQL customer, then you can utilize MFA with Question Editor V2 with the above application. There are no modifications needed in the customized SAML application which we have actually produced above.
  2. If you are NOT utilizing QEV2 and just utilizing 3rd party SQL customer (SQL Workbench/J and so on), then you require to customize the above customized SAML app as pointed out listed below.
  3. If you wish to utilize QEV2 and third-party SQL Customer with MFA, then you require develop an extra customized SAML app as pointed out listed below.

Requirements for MFA

Each identity supplier (IdP) has action for making it possible for and handling MFA for your users. When it comes to Okta, see the following guides on how to allow MFA utilizing the Okta Verify application and by specifying an authentication policy.

Steps to create/update SAML application which supports MFA for a SQL Customer

  1. If producing a 2nd app, follow all the actions which are explained under area 1 (Produce Okta SAML application).
  2. Open the customized SAML app and choose General
  3. Select Edit under SAML settings
  4. Click Next in General Settings
  5. Under General, upgrade the Single sign-on URL to http://localhost:7890/redshift/
  6. Select Next followed by End Up.

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

Configure SQL Customer for MFA

To establish SQL Workbench/J, total the following actions:

  1. Follow all the actions which are explained under (Set up the SQL customer (SQL Workbench/J))
  2. Customize your connection upgrading the extended homes:.
    • login_url — Get the Single Sign-on URL as displayed in area -Collect Okta details. (For instance, https://dev-123456.okta.com/app/dev-123456_redshiftapp_2/abc8p6o5psS6xUhBJ517/sso/saml)
    • plugin_name– com.amazon.redshift.plugin.BrowserSamlCredentialsProvider
  3. Select OKAY
  4. Select OKAY from SQL Workbench/J. You’re rerouted to the web browser to check in with your Okta qualifications.
  5. After that, you will get timely for MFA. Select either Go into a code or Get a push notice
  6. As soon as authentication achieves success, log in to be rerouted to a page revealing the connection as effective.
  7. With this connection profile, run the following inquiry to return federated user name.

Troubleshooting

If your connection didn’t work, think about the following:

  • Enable visiting the motorist. For guidelines, see Configure logging
  • Make certain to utilize the most recent Amazon Redshift JDBC motorist variation.
  • If you’re getting mistakes while establishing the application on Okta, ensure you have admin gain access to.
  • If you can confirm by means of the SQL customer however get an approval problem or can’t see things, approve the appropriate approval to the function, as detailed previously in this post.

Tidy Up

When you’re done evaluating the option, tidy up the resources to prevent sustaining future charges:

  1. Erase the Redshift Serverless circumstances by erasing both the workgroup and the namespace.
  2. Erase the IAM functions, IAM IdPs, and IAM policies.

Conclusion

In this post, we supplied detailed guidelines to incorporate Redshift Serverless with Okta utilizing the Amazon Redshift Question Editor V2 and SQL Workbench/J with the aid of federated IAM functions and automated database-role mapping. You can utilize a comparable setup with any other SQL customer (such as DBeaver or DataGrip) or company intelligence tool (such as Tableau Desktop). We likewise demonstrated how Okta group subscription is mapped immediately with Redshift Serverless functions to utilize role-based authentication flawlessly.

To learn more about Redshift Serverless single sign-on utilizing database functions, see Specifying database functions to approve to federated users in Amazon Redshift Serverless

About the Authors

Maneesh Sharma is a Senior Database Engineer at AWS with more than a years of experience creating and carrying out massive information storage facility and analytics options. He works together with different Amazon Redshift Partners and consumers to drive much better combination.

Debu-Panda Debu Panda is a Senior Supervisor, Item Management at AWS. He is a market leader in analytics, application platform, and database innovations, and has more than 25 years of experience in the IT world.

Mohamed Shaaban Mohamed Shaaban is a Senior Software Application Engineer in Amazon Redshift and is based in Berlin, Germany. He has more than 12 years of experience in the software application engineering. He is enthusiastic about cloud services and developing options that thrill consumers. Beyond work, he is an amateur professional photographer who enjoys to check out and record distinct minutes.

Rajiv Gupta is Sr. Supervisor of Analytics Expert Solutions Architects based out of Irvine, CA. He has 20+ years of experience structure and handling groups who develop information storage facility and company intelligence options.

Amol Mhatre is a Database Engineer in Amazon Redshift and deals with Client & & Partner engagements. Prior to Amazon, he has actually dealt with several tasks including Database & & ERP executions.

Ning Di is a Software Application Advancement Engineer at Amazon Redshift, driven by a real enthusiasm for checking out all elements of innovation.

Harsha Kesapragada is a Software Application Advancement Engineer for Amazon Redshift with an enthusiasm to develop scalable and safe and secure systems. In the previous couple of years, he has actually been dealing with Redshift Datasharing, Security and Redshift Serverless.

Like this post? Please share to your friends:
Leave a Reply

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: