Redshift Dbeaver

 admin



DBeaver is a universal database management tool for everyone who needs to work with data in a professional way. With DBeaver you are able to manipulate with your data like in a regular spreadsheet, create analytical reports based on records from different data storages, export information in an appropriate format. Amazon Redshift offers drivers for tools that are compatible with the JDBC 4.2 API. For detailed information about how to install the JDBC driver, reference the JDBC driver libraries, and register the driver class, see the following topics. JDBC drivers version 1.2.27.1051 and later support Amazon Redshift stored procedures. Creating an ODBC Data Source to Use Amazon Redshift Data in DBeaver Click the Start menu and select Control Panel. Select Administrative Tools, then click ODBC Data Sources. Click on the System DSN tab if you want to set up a DSN name for all users of the system or select User DSN to configure DSN. Redshift: bug with data types reading was fixed. DBeaver Community Edition is free and open source (ASL) product. DBeaver is designed for use with SAP® ASE (Adaptive Server Enterprise), originally known as Sybase SQL Server, and also commonly known as.

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

Redshift dbeaver

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 thosegenerated 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 accessto the UI of the application it is embedded in. To overcome this problem the driver uses a clever solution.

Dbeaver
  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 thenopens 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 temporarycredentials (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 isstill 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 appsdo not support the flow. DBeaver does not by default, but it can be achieved without much work.

Dbeaver

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 presentedto you by DBeaver means it is not possible to configure for MFA flow.

Redshift Dbeaver

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 pointerscan 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

  2. Search for “Redshift” then select it and click Copy

  3. 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:

    3. In “Libraries”, choose “Add File” and add all the files from the downloaded AWS JDBC driver pack zip

    4. Press OK

  4. 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 Oktaends in /sso/saml.

    5. Press OK

  5. 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 intoDBeaver, but the improvements in security are undeniable.

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

Good luck with your integration!

Iam

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 scopeof this post, but here are a few pointers which may help:

Redshift Driver For Dbeaver

  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 firstrow (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 accessonly” or change the Trust Relationship Policy Document afterwards. It should look like this: