Integrate with Amazon Redshift

Use JumpCloud SAML Single Sign On (SSO) to give your users convenient but secure access to all their web applications with a single set of credentials.

Read this article to learn how to configure the Amazon Redshift connector. 

Prerequisites

• A JumpCloud administrator account
• JumpCloud SSO Package or higher or SSO add-on feature.
• AWS Admin account (AWS root user)
• AWS organization

Important Considerations

• This connector supports additional Constant Attributes that are sent in the assertion. E.g., Amazon supports SessionDuration in order to allow up to 12 hour sessions before logout. By default, the connector template contains this additional attribute where the Name is  https://aws.amazon.com/SAML/Attributes/SessionDuration and the Value in seconds must be between 15 minutes and 12 hours; e.g., for 15 minutes, enter a value of 900. 

Additional Considerations

Before you begin to set up SSO with Amazon Redshift, decide on names for the resources involved in setup. 

• In JumpCloud, you should have a name(s) for:
  • Users who need access to Redshift. 
  • A User Group Name.

• In AWS, prepare a name for:
  • The IAM IdP Name. 
• In Redshift, plan names for:
  • The Cluster Name. 
  • DB Name. 
  • A master user name (some_admin)
  • A master user password (save creds so we can login to the db directly to setup the dbgroup)
  • A DB Group Name. 

Learn more about the AWS side of this process.

Creating a new JumpCloud Application Integration

1. Log in to the JumpCloud Admin Portal.
2. Navigate to USER AUTHENTICATION > SSO Applications.
3. Click + Add New Application.
4. Type the name of the application in the Search field and select it.
5. Click Next.
6. In the Display Label, type your name for the application. Optionally, you can enter a Description, adjust the User Portal Image and choose to hide or Show in User Portal.

7. Optionally, expand Advanced Settings to specify a value for the SSO IdP URL. If no value is entered, it will default to https://sso.jumpcloud.com/saml2/<applicationname>.

8. Click Save Application.
9. If successful, click:
   • Configure Application and go to the next section
   • Close to configure your new application at a later time

Configuring the SSO Integration

To configure JumpCloud 1 

Create a Group of Users

1. Log in to the JumpCloud Admin Portal.
2. Select Groups.
3. Click (+).
4. In the Details tab, enter a name for the user group. It needs to match the RedShift DB group name.
5. Select the Users tab to add users to the group. 
6. Click save and keep the Admin Portal open. 

Set Up the SAML Application

1. Create a new application or select it from the Configured Applications list.
2. Select the SSO tab.
3. Add or change any attributes.
4. In the User Groups tab, select the User Group you created in the previous section.
5. Click save. 

Download the JumpCloud metadata file

1. Find your application in the Configured Applications list and click anywhere in the row to reopen its configuration window.
2. Select the SSO tab and click Export Metadata.
3. The JumpCloud-<applicationname>-metadata.xml will be exported to your local Downloads folder.

To configure AWS 1

1. Log in to the Amazon Web Services console for your organization as an administrator.
2. In the main console, go to All Services, under Security, Identity & Compliance select IAM.
3. On the left hand side nav, select Identity Providers.
4. Select Create Provider.
5. In Provider Type, select SAML.
6. For Provider Name, enter a name of your choice.
7. Select Choose File, then upload the metadata file you downloaded from JumpCloud.
8. Select Next Step.
9. On the next screen, select Create.
10. Select the identity provider you created in steps 5-6. 
11. Download the metadata XML file to the local machine. 

To configure JumpCloud 2

1. In the JumpCloud Admin Portal, open the AWS Redshift SSO connector you configured. 
2. For Service Provider Metadata File, upload the file you downloaded from AWS. 
3. Click save.

To configure AWS 2

Create a Security Group

1. Go to the AWS management console.
2. In the main console, go to All Services, under Compute, select EC2.
3. In the left hand navigation, under Network & Security, select Security Groups.
4. Click Create security group. 
5. For Security group name, enter a name of your choice. 
6. Under Inbound rules, click Add rule.
7. For Type, select Redshift.
8. For source, enter the internal IP of the VPN you’re connected to, or its subnet address. 
9. Skip Outbound rules and Tags, then click Create security group. 

Create an Access Policy

1. In the AWS Management console, click Services, then select IAM under Security, Identity, & Compliance. 
2. Under Access Management on the left-hand side, select Policies.
3. Click Create policy. 
4. Select the JSON tab, and overwrite it with the following:

5. Click Review policy. 
6. For name, enter the name of your choice.
7. Click, Create policy. 

Create a Role 

1. In the AWS Management console, click Services, then select IAM under Security, Identity, & Compliance. 
2. Under Access Management on the left-hand side, select Roles.
3. Click Create role.
4. For Select type of trust entity, select SAML 2.0 federation. 
5. In SAML provider, select the identity provider you created in Configure AWS 1.
6. Choose Allow programmatic access only.
7. For Attribute, select SAML:aud.
8. For Value, enter http://localhost:7890/redshift/. The resulting Trusted Entity should look like:

{

          "Version": "2012-10-17",

          "Statement": [

            {

              "Effect": "Allow",

              "Principal": {

                "Federated": "arn:aws:iam::ACCOUNT_NUMBER:saml-provider/redshift-sso"

              },

              "Action": "sts:AssumeRoleWithSAML",

              "Condition": {

                "StringEquals": {

                  "SAML:aud": "http://localhost:7890/redshift/""

                }

              }

            }

          ]

        }

9. Click Next: Permissions. 
10. Attach the policy you created in Create an Access Policy.
11. Select Next: Review.
12. Define a Role Name. 
13. Select Create role.

Create a Subnet Group in Redshift

1. In the AWS Management console, click Services, then select Amazon Redshift under Database. 
2. In the left-hand navigation, select CONFIG, then select Subnet groups.
3. Click Create cluster subnet group.
4. For name, enter the name of your choice.
5. For VPC, select the VPC that contains the subnets that you want to include.
6. Select AZ and associated subnets. Make sure you select the same subnet that’s associated with the VPN. You can also select to add all the subnets for the VPC.
7. Click Create cluster subnet group. 

Create a Cluster in Redshift

1. In the AWS Management console, click Services, then select Amazon Redshift under Database. 
2. In the left-hand navigation, select CLUSTERS, then click Create cluster. 
3. For cluster identifier, enter the name of your choice.
4. Select the Node type, then enter the number of nodes. 
5. Enter the following information for Database Configurations:
   1. Database name: the name of your choice.
   2. Database port: 5493
   3. Master username: a master username of your choice
   4. Master password: a master password of your choice. Note: Save these credentials for Create the DB Group, in Configure the SQL Client. 
6. In Additional configurations, turn off Use defaults, then select Network and security.
7. For Virtual private cloud, select the VPC you selected for the Redshift Subnet Group.
8. For VPC security group, select the security group you created in Create a Security Group. 
9. For Cluster subnet group, select the subnet group you created in Create a Subnet Group. 
10. Select AZ or No Preference. 
11. For Enhanced VPC routing, select disabled, then for Publicly accessible select No.
12. Expand Database configurations.
13. Select a Parameter group and the Encryption. 
14. Configure settings for Maintenance, Monitoring, and Backup according to your preferences. 
15. Click Create cluster. 
16. In the Clusters list view, select the cluster you just created. 
17. Under General information, copy the JDBC URL. It looks like this:  jdbc:redshift://testcluster.XXXXXXXXXX.REGION.redshift.amazonaws.com:5439/testdb

Configure the SQL Client 

In the SQL Client, you have three phases to complete:

Set up Redshift with the SQL Client

1. Download the Redshift JDBC driver. It needs to be 1.2.41 or higher with SDK: https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.41.1065/RedshiftJDBC42-1.2.41.1065.jar
2. Download and run SQL Workbench. It requires Java 8 or later and you can use any JDBC tool: https://www.sql-workbench.eu/downloads.html
3. Unzip and run sqlworkbench.jar.
4. In the Connection Window, select Manage Drivers, then select Amazon Redshift.
5. Select RedshiftJDBC42-1.2.41.1065.jar for the driver. 
6. For Class Name, select com.amazon.redshift.jdbc.Driver. 

7. Click OK. 

Create the DB Group

1. Connect directly to the Cluster with the master username and password you created in Create a Cluster in Redshift to create the DB Group.
2. In the Connection Window, enter the following information:
   1. For Driver, select Amazon Redshift.
   2. For URL, enter the JDBC URL you copied in step 17 of Create a Cluster in Redshift.
   3. For username, enter the master username that you created in steps 5c-5d of Create a Cluster in Redshift. 
   4. For password, enter the master password that you created in steps 5c-5d of Create a Cluster in Redshift. 
3. For Extended Properties, enter:
   1. For login_url enter https://sso.jumpcloud.com/saml2/redshift-sso.
   2. For Plugin_name enter com.amazon.redshift.plugin.BrowserSamlCredentialsProvider.
   3. For idp_response_timeout, put 60. 
   4. Select the option for Copy to system properties before connecting. 
   5. Click OK. 
4. Click OK again. Then you’re logged into the Redshift DB directly and the top right shows the connection:
5. “Catalog=testdb, URL=jdbc:redshift://testcluster.XXXXXXXXXX.REGION.redshift.amazonaws.com:5439/testdb”.
6. Execute the following SQL commands to create a DB group:
   1. CREATE GROUP redshift-dbgroup;
   2. Commit;
      1. Notes:
         1. Use GRANT with the GROUP command to add any other specific DB permissions other than the default.
         2. If there are already users that exist that you want to only connect via SSO and not direct, then also execute the following command: alter user existing_user password disable. 
7.  Disconnect from DB. 

Connect the Redshift DB with JumpCloud SSO

1. In the Connection Window enter the following information:
   1. Driver: Amazon Redshift
   2. URL: jdbc:redshift:iam://testcluster.XXXXXXXXXX.REGION.redshift.amazonaws.com:5439/testdb
   3. Note: Notice the addition of :iam: into the original JDBC URL string
   4. Username: <clear any user creds>
   5. Password: <clear any user creds>
2. Extended Properties stay the same from when you configured them in Step 3 of Create the DB Group. 
3. Click OK.
4. You’re redirected to the JumpCloud User Login screen. Complete the auth workflow. 
5. After you log in, you’re redirected to a local page that says, “Thank you for using Amazon Redshift! You can now close this window.”
6. Go back to the SQL Application. You find that you are logged in to the Redshift DB with JumpCloud SSO. The top right shows your connection:  “Catalog=testdb, URL=jdbc:redshift:iam://testcluster.XXXXXXXXXX.REGION.redshift.amazonaws.com:5439/testdb”.

Authorizing User SSO Access

Users are implicitly denied access to applications. After you connect an application to JumpCloud, you need to authorize user access to that application. You can authorize user access from the Application Configuration panel or from the Groups Configuration panel. 

To authorize user access from the Application Configuration panel

1. Log in to the JumpCloud Admin Portal.
2. Go to USER AUTHENTICATION > SSO Applications, then select the application to which you want to authorize user access.
3. Select the User Groups tab. If you need to create a new group of users, see Get Started: User Groups.
4. Select the check box next to the group of users you want to give access.
5. Click save. 

To learn how to authorize user access from the Groups Configuration panel, see Authorize Users to an SSO Application.

Validating SSO authentication workflow(s)

IdP-initiated

• Access the JumpCloud User Console
• Select the application’s tile
• The application will launch and login the user

SP-initiated

• Navigate to your Service Provider application URL
• You will be redirected to log in to the JumpCloud User Portal
• The browser will be redirected back to the application and be automatically logged in

Removing the SSO Integration

To deactivate the SSO Integration

1. Log in to the JumpCloud Admin Portal.
2. Go to USER AUTHENTICATION > SSO Applications.
3. Search for the application that you’d like to deactivate and click to open its details panel. 
4. Select the SSO tab.
5. Scroll to the bottom of the configuration.
6. Click Deactivate SSO. 
7. Click save. 
8. If successful, you will receive a confirmation message.

To delete the application

1. Log in to the JumpCloud Admin Portal.
2. Go to USER AUTHENTICATION > SSO Applications.
3. Search for the application that you’d like to delete.
4. Check the box next to the application to select it.
5. Click Delete.
6. Enter the number of the applications you are deleting
7. Click Delete Application.
8. If successful, you will see an application deletion confirmation notification.