Amazon Redshift ECP Plugin for ODBC

Amazon Redshift is a data warehousing service available through AWS. Like most AWS services, there are a variety of security models available and like most databases, it supports built-in user and group management features. Unlike most services and unlike essentially all databases, it also supports a mechanism for leveraging AWS' SAML support for federated login by database clients using Amazon's JDBC and ODBC drivers.

This article describes an open source WIndows console utility written by a member of the Shibboleth team that interfaces to the Amazon ODBC driver and to the Shibboleth IdP (or any SAML compliant IdP) using the ECP (Enhanced Client or Proxy) profile designed for non-browser SAML authentication. There are some advanced features that are designed in part to support some Shibboleth features, but it's largely a vanilla Windows excutable ECP client that relies on some parameters passed via the command line from the Amazon ODBC driver and passes back a SAML response from the IdP to stdout for use by AWS.

The program is actually just a Windows command line wrapper around (by default) an execution of the curl utility to do the heavy lifting, though it's heavily customizeable. It doesn't have any real dependencies on the Amazon driver as a starting point, but happens to work with the inputs and outputs that driver uses. It may be useful in other situations in which an ECP client might be useful.

It is Windows-only but could theoretically be copied and converted into a Linux/Mac fork/exec wrapper that would be similarly usable with the ODBC driver on those platforms.

See https://docs.aws.amazon.com/redshift/latest/mgmt/generating-user-credentials.html for a large amount of documentation on the general topic of using IAM credentials in AWS together with SAML, the drivers, and a lot of configuration glue to make all this fit together.

Prerequisite Setup

See the Amazon Redshift ECP Plugin for JDBC topic for this discussion, it applies here as well.

Client Setup

The following steps are needed to get a client ready for use:

1. Install the ODBC driver/client itself.

2. Build the Visual Studio project available at https://git.shibboleth.net/git/cpp-ecp.git to produce the ecp.exe executable file.

3. Install a usable SSL-capable version of the curl.exe utility. It's overkill, but one option is to install the Shibboleth SP software for Windows, which includes a copy and places the necessary libraries on the system path for you.

Validating Install

The ODBC interactions can get very tricky to debug, so it's best to do some testing from the command line without involving it.

First, make sure you can run the chosen implementation of curl.exe from an arbitrary directory from a command line Window. For example if it lives in "C:\Utilities", open a prompt and make sure "C:\Utilities\curl.exe" works.

Next, try to run a transaction with the ecp.exe utility built in step 2 above against your IdP. You'll need several parameters passed on the command line, and they're passed to the utility in name=value pairs. At least the following need to be set:

• ecp_curl=C:\Utilities\curl.exe

  • Point this wherever you successfully installed curl.exe and managed to make it work in the step above.

• ecp_issuer=urn:amazon:webservices

  • The is the SP entityID of Amazon. It's not valid, it should be noted, but there's not much we can do about that.

• idp_host=idp.example.org

  • Obviously this is where your IdP lives

• ecp_nobase64=1

  • This helps with testing by echoing the raw response XML from the IdP and not the encoded form.

You'll also need user and password to provide the credentials to use.

For example:

Barring a need for other options or a non-Shibboleth IdP, the goal is to get back an XML dump from the IdP that contains a valid signed Assertion wrapped in a Response message. It's actually returning SOAP but the client auto-extracts the response unless you also give it an ecp_soap=1 parameter.

If you get back something like "ERROR: SAML Response was unsuccessful", you should have logs at the IdP to help debug the problem.

If you get back something else, you're in trouble and have some debugging to do and I don't have a lot of good suggestions. It may mean a problem with the TLS certificate verification step. Try some generic curl commands to get a working interaction with the IdP web server.

Driver Configuration

Data source connections with ODBC require using an AWS "profile" in an INI file that's generally stored in your home directory in a file named .aws/config

Many options and approaches not specific to this plugin are covered in https://docs.aws.amazon.com/redshift/latest/mgmt/options-for-providing-iam-credentials.html

Because most of the options are in the profile configuration file, the ODBC Driver configuration itself is less specific to this use case. What you do in the ODBC Driver Manager to use this is to select an Auth Type of "AWS Profile" from the drop-down and then fill in the profile section name under "Profile Name". This would be awstest in the example shown below.

A lot of the necessary settings specific to the ECP plugin are baked into the plugin for use with Shibboleth and are only needed for unusual cases, or to allow use with other ECP-supporting IdPs.

What you're doing with the INI file is baking in a handful of the properties that will be passed to the ecp.exe program so the driver knows how to call it. The INI file has a section named with some kind of profile string that you'll use later to identify the configuration the driver should use.

.aws/config

Minimum Required Properties

You MUST define at least these:

Other Properties

Note that unlike the JDBC case, you fully control the command line, which means

Related articles