Connecting DBeaver to AWS Redshift Using SAML Federation and MFA

How to connect to an AWS Redshift cluster using DBeaver and federated credentials over SAML

Posted by Harry Lascelles on January 19, 2021

When connecting to a DB from your machine it is tempting to hardcode credentials. However, as we are finding, correctly securing your systems with SSO (combined with MFA) is no longer just best practice, it is becoming mandatory.

In this post we will see how to configure the multi-platform DBeaver database tool to connect to AWS Redshift using a SAML-based SSO provider. I will use URLs that are similar to those generated by Okta, but any SAML provider should operate the same way.

The login sequence

The authentication required for a JDBC connection is usually provided by environment variables, saved credentials in a file, or a UI window that is native to the application being used. The AWS JDBC driver, however, needs to challenge the user for an MFA token without having access to the UI of the application it is embedded in. To overcome this problem the driver uses a clever solution.

  1. The user initiates the login sequence by using the driver to connect to Redshift.
  2. The AWS Redshift JDBC driver starts a server listening on a local port (7890 by default) and then opens a browser window (pointing to the SSO service, eg Okta) so that the user can log in.
  3. When the user has logged in, the SSO service communicates with Redshift to generate temporary credentials (and set any groups), then redirects the user's browser to POST to a URL of the form http://localhost:7890/redshift/, together with a large SAMLResponse payload.
  4. The browser follows that redirect, and in doing so it sends that data to the local driver, which is still listening on port 7890.
  5. The JDBC driver now has a set of temporary login credentials, and it uses them to connect to the Redshift cluster.

The fact that the driver needs to open a browser means that many traditional login scripts and apps do not support the flow. DBeaver does not by default, but it can be achieved without much work.

Prerequisites

DBeaver does come with a "Redshift Driver" included, but it is not configured to allow SAML integration that requires a browser to open. Note, under the hood this does use the JDBC driver, but the UI presented to you by DBeaver means it is not possible to configure for MFA flow.

We need to create a new "Redshift (MFA) Driver" in DBeaver. To do that we must download the AWS Redshift JDBC driver with supporting libraries. You can find the JDBC Java libraries at this link.

Thus, you will need:

  1. The Redshift JDBC driver
  2. A running Redshift instance
  3. An Identity Provider (IdP) like Okta
  4. A configured SAML 2.0 application in that provider

Setting up the SSO Redshift application is beyond the scope of this article, but some pointers can be found at the end of this post.

Configuring the connection

To create a new DBeaver Driver (using the AWS Redshift JDBC Driver):

  1. Go to Database > Driver Manager
  1. Search for "Redshift" then select it and click Copy

  1. Fill out the form as below. The changes will be:

    1. Change the name to "Redshift (Okta MFA)" or similar
    2. Change the URL Template to: 
      jdbc:redshift:iam://{host}:{port}/{database}?plugin_name=com.amazon.redshift.plugin.BrowserSamlCredentialsProvider
    3. In "Libraries", choose "Add File" and add all the files from the downloaded AWS JDBC driver pack zip
    1. Press OK

  2. To create a connection:

    1. Choose "Database" -> "New Database Connection"

    2. Search for Okta (or the name used in step 3i), select it and hit "Next"

    3. Fill in the hostname of your Redshift instance and the database to connect to. Leave the username and password as they are. They are ignored.

    4. Configure it to use your pre-setup Okta app by clicking on the Driver Properties tab and adding a new property. To add a new property, click the tiny button with a green plus on it. The name of the new property is login_url, and the value is the SAML target URL, which for Okta ends in /sso/saml.

    5. Press OK

  3. Finally, you can activate the connection. You will see a browser window open that will take you to Okta. If you are not yet logged in it will prompt you to do so, including MFA.

    Once done, it will show the following message. You can close the browser window. You are connected.

That's it!

You may find it is initially off-putting, the way a browser window suddenly opens when you log into DBeaver, but the improvements in security are undeniable.

Overall we've been very happy with the new setup.

Good luck with your integration!

Appendix: Setting up an Okta Redshift application with MFA

As mentioned above, the full set of steps to set up an Okta connection to Redshift is beyond the scope of this post, but here are a few pointers which may help:

  1. Do not use the Okta "pre-prepared" Redshift app. It is not configured to allow MFA connectivity. Instead, create a new (custom) "SAML 2.0 app".

  2. The main guide for how to set up the custom app is provided by AWS.

    However, it has a few mistakes:

    1. The picture of the "Attribute Statements" shows a single "arn" on the right of the first row (in the Value column). It should be two arns, separated by a colon. The guidance text is correct though.

    2. During the Okta setup at step 19 there is an example Group statement which is invalid. If it is not corrected then user groups do not get applied and the user cannot access any tables.

      It says: https://redshift.amazon.com/SAML/UnspecifiedAttributes/DbGroups

      It should be: https://redshift.amazon.com/SAML/Attributes/DbGroups

    3. The AWS SAML 2.0 provider setup forces the wrong "value" to https://signin.aws.amazon.com/saml. It should be http://localhost:7890/redshift. You can either choose "programmatic access only" or change the Trust Relationship Policy Document afterwards. It should look like this: