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.
See the Amazon Redshift ECP Plugin for JDBC topic for this discussion, it applies here as well.
The following steps are needed to get a client ready for use:
- Install the ODBC driver/client itself.
- Build the Visual Studio project available at https://git.shibboleth.net/git/cpp-ecp.git to produce the ecp.exe executable file.
- 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.
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:
- Point this wherever you successfully installed curl.exe and managed to make it work in the step above.
- 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.
- Obviously this is where your IdP lives
- 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.
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.
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.
Minimum Required Properties
You MUST define at least these:
|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|
Note that unlike the JDBC case, you fully control the command line, which means
|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|
Path to IdP's ECP endpoint
|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|