Amazon Redshift ECP Plugin for ODBC

This documentation refers to an unsupported open source code made available by the Shibboleth Project as a service to the community. It is not an official software product of the project and does not have formal releases, and is generally untested by the development team. It is available under the standard Apache 2.0 software license.

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:

C:\Users\me> C:\Utilities\ecp.exe ecp_curl=C:\Utilities\curl.exe ecp_issuer=urn:amazon:webservices idp_host=idp.example.org ecp_nobase64=1 user=me password=whatever

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
[awstest] plugin_name=C:\Utilities\ecp.exe ecp_curl=C:\Utilities\curl.exe #ecp_hidden=1 ecp_issuer=urn:amazon:webservices idp_host=idp.example.org preferred_role=arn:aws:iam::12345678901:role/Redshift_test

Minimum Required Properties

You MUST define at least these:

Name

Description

Example

Name

Description

Example

plugin_name

This points the ODBC driver at the ecp.exe utility so it knows what to launch.

C:\Utilities\ecp.exe

idp_host

Hostname of the IdP

idp.example.org

ecp_issuer

Name of Amazon SP

urn:amazon:webservices

preferred_role

This isn't just "preferred", it's literally required to tell the system which AWS role you're using. You could omit this if you only operate in one role ever, but if the IdP ever includes more than one you'll end up with a failure, so it's best to specify it.

arn:aws:iam::12345678901:role/Redshift_test

Other Properties

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

Name

Description

Example

Name

Description

Example

idp_port

Port for the IdP

443

ecp_cacert

Path to a file containing one or more CA certificates, used to verify the IdP certificate if the curl default behavior does not allow implicit validation.

C:\Utilities\curl-ca-bundle.crt

ecp_path

Path to IdP's ECP endpoint

/idp/profile/SAML2/SOAP/ECP

ecp_curl

Path to curl.exe utility, if it's not in the path

C:\Utilities\curl.exe

ecp_command

Allows total control of the command to talk to the IdP if you want to get creative, add custom curl parameters, etc. This is the hook to allow setting HTTP headers and other weirdness.



ecp_hidden

Value irrelevant. If set, this tells the utility to hide its console window. Use once you've tested and things are working.

1

ecp_nobase64

Value irrelevant. If set, it sends the raw XML to stdout instead of base64-encoded. Just for debugging command line or for other use cases, the ODBC client requires the encoding.

1

ecp_soap

Value irrelevant. If set, it sends the whole SOAP XML envelope back instead of trimming the response out. Just for debugging command line or for other use cases, the ODBC client requires the response only.

1

ecp_tag

XML Element tagname used by IdP for SAML Response

saml2:Response

Related articles

Â