Redshift Dbeaver

Posted on by admin



  • SQL editor:
    • “Open SQL console” action was added to the main toolbar and context menu
    • Support of very long sql scripts was improved
    • Parser hanging was fixed (partially) for queries with a lot of parenthesis
    • Auto-completion performance was improved
  • Data editor:
    • Drop-down inline editor was fixed (Linux)
    • Epoch time transformer now supports timezones
    • Copy As SQL: now uses correct SQL dialect
  • Metadata editor: support of pure unicode identifiers was added
  • Content assistant was fixed for drop-down selectors
  • Connection configuration import now supports target folders
  • GIS (spatial) data viewer:
    • MapBox tile configuration was fixed
    • 3D geometry objects now can be displayed on map
    • Geometry transformer for binary format (WKB) was added
  • Trino (ex-PrestoSQL) driver was added (thanks to @mosabua)
  • MariaDB: default driver version upgraded to 2.7.1
  • SQLite:
    • Spatial (gis) data viewer was added
    • Metadata refresh was fixed (wrong foreign key name)
  • PostgreSQL:
    • SQL formatting for custom data types was fixed (geometry and others)
    • Backup/restore now support non-standard authentication (e.g. IAM)
    • Redundant data types removed from navigator tree (arrays)
    • Nested multiline comments support was added
    • Database template list now contains all databases
  • Redshift: driver version was upgraded (bug with “session is read-only”)
  • Presto/Trino: GIS transformers support was added
  • Firebird:
    • SQL parser was fixed (code blocks)
    • Table/view type recognition was improved (for older FB versions)
  • Number of errors with wrong focus tracking fixed on MacOS
  • Clear message for Java version mismatch errors was added
  • Now DBeaver can be installed without bundled Java (Windows and Linux)
  • Problem with Eclipse Marketplace extension was fixed
  • Several minor UI bugs were fixed

Access Amazon Redshift databases from BI, analytics, and reporting tools, through easy-to-use bi-directional data drivers. Our Drivers make integration a snap, providing an easy-to-use relational interface for working with Amazon Redshift data.

  • DBeaver is certainly an ultimate Universal client which incorporates RDBMS and NoSQL Databases. The GUI is very useful and easy to manipulate all kind of DB queries.DBeaver is remarkably fast and stable. It loads quickly and responds instantaneously. Especially, It is the only client tool for Apache Cassandra NoSQL Database in market.
  • JackDB is an SQL IDE that works with PostgreSQL, Amazon Redshift and more. You can find it here. Documentation is here. Great all around SQL Client. RazorSQL ships with the JDBC drivers necessary to connect to Amazon Redshift. You can get it from here. Redshift instructions here. Instructions to connect here.
  • Free universal database tool and SQL client. Contribute to dbeaver/dbeaver development by creating an account on GitHub.
Posted in Releases

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.

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.

  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.

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.

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!

Appendix: Setting up an Okta Redshift application with MFA

Dbeaver Redshift Spectrum

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:

Amazon Redshift Odbc Driver 64

  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: