Federated entry to Amazon Redshift clusters in AWS China Areas with Lively Listing Federation Providers

0/5 No votes

Report this app



Many shoppers already handle consumer identities by means of identification suppliers (IdPs) for single sign-on entry. With an IdP equivalent to Lively Listing Federation Providers (AD FS), you possibly can arrange federated entry to Amazon Redshift clusters as a mechanism to regulate permissions for the database objects by enterprise teams. This supplies a seamless consumer expertise, and centralizes the governance of authentication and permissions for end-users. For extra data, discuss with the weblog submit collection “Federate entry to your Amazon Redshift cluster with Lively Listing Federation Providers (AD FS)” (half 1, half 2).

Because of the variations within the implementation of Amazon Internet Providers in China, prospects have to regulate the configurations accordingly. For instance, AWS China Areas (Beijing and Ningxia) are in a separate AWS partition, subsequently all of the Amazon Useful resource Names (ARNs) embody the suffix -cn. AWS China Areas are additionally hosted at a special area: www.amazonaws.cn.

This submit introduces a step-by-step process to arrange federated entry to Amazon Redshift in AWS China Areas. It pinpoints the important thing variations you need to take note of and supplies a troubleshooting information for frequent errors.

Resolution overview

The following diagram illustrates the method of Safety Assertion Markup Language 2.0 (SAML)-based federation entry to Amazon Redshift in AWS China Areas. The workflow consists of the next main steps:

  1. The SQL shopper supplies a consumer identify and password to AD FS.
  2. AD FS authenticates the credential and returns a SAML response if profitable.
  3. The SQL shopper makes an API name to AWS Safety Token Service (AWS STS) to assume a most popular function with SAML.
  4. AWS STS authenticates the SAML response primarily based on the mutual belief and returns non permanent credentials if profitable.
  5. The SQL shopper communicates with Amazon Redshift to get again a database consumer with non permanent credentials, then makes use of it to hitch database teams and hook up with the required database.

The architecture

We arrange the walkthrough within the following high-level steps:

  1. Configure an AD FS relying social gathering belief for AWS China Areas and outline fundamental declare guidelines.
  2. Provision an AWS Identification and Entry Administration (IAM) identification supplier and roles.
  3. Full the remaining relying social gathering belief’s declare guidelines primarily based on the IAM assets.
  4. Hook up with Amazon Redshift with federated entry by way of a JDBC-based SQL shopper.


This submit assumes that you’ve got the next conditions:

  • Home windows Server 2016
  • The power to create customers and teams in AD
  • The power to configure a relying social gathering belief and outline declare guidelines in AD FS
  • An AWS account
  • Ample permissions to provision IAM identification suppliers, roles, Amazon Digital Personal Cloud (Amazon VPC) associated assets, and an Amazon Redshift cluster by way of AWS Cloud Improvement Equipment (AWS CDK)

Configure an AD FS relying social gathering belief and outline declare guidelines

A relying social gathering belief permits AWS and AD FS to speak with one another. It’s attainable to configure two relying social gathering trusts for each AWS China Areas and AWS Areas in the identical AD FS on the identical time. For AWS China Areas, we have to use a special SAML metadata doc at https://signin.amazonaws.cn/static/saml-metadata.xml. The relying social gathering’s identifier for AWS China Areas is urn:amazon:webservices:cn-north-1, whereas that for AWS International Areas is urn:amazon:webservices. Be aware down this identifier to make use of later on this submit.

Relying party metadata XML

Add AD teams and customers

With SAML-based federation, end-users assume an IAM function and use it to hitch a number of database (DB) teams. The permissions on such roles and DB teams will be successfully managed by AD teams. We use totally different prefixes in AD group names to tell apart them, which assist map to roles and DB group declare guidelines. It’s essential to tell apart appropriately the 2 forms of AD teams as a result of they’re mapped to totally different AWS assets.

We proceed our walkthrough with an instance. Suppose in enterprise there are two roles: information scientist and information engineer, and two DB teams: oncology and pharmacy. Knowledge scientists can be a part of each teams, and information engineers can solely be a part of the pharmacy group. On the AD facet, we outline one AD group for every function and group. On the AWS facet, we outline one IAM function for every function and one Amazon Redshift DB group for every DB group. Suppose Clement is a knowledge scientist and Jimmy is a knowledge engineer, and each are already managed by AD. The next diagram illustrates this relationship.

AD group and user mapping to IAM role and DB group

Chances are you’ll create the AD teams and customers with both the AWS Command Line Interface (AWS CLI) or the AWS Administration Console. We offer pattern instructions within the README file in the GitHub repo.

Observe substeps a to o of Step 2 in Establishing JDBC or ODBC Single Signal-on authentication with AD FS to arrange the relying social gathering with the right SAML metadata doc for AWS China Areas and outline the primary three declare guidelines (NameId, RoleSessionName, and Get AD Teams). We resume after the IAM identification supplier and roles are provisioned.

Provision an IAM identification supplier and roles

You identify the belief for AD with AWS by provisioning an IAM identification supplier. The IAM identification supplier and assumed roles needs to be in a single AWS account, in any other case you get the next error message throughout federated entry: “Principal exists exterior the account of the Position being assumed.” Observe these steps to provision the assets:

  1. Obtain the metadata file at https://yourcompany.com/FederationMetadata/2007-06/FederationMetadata.xml out of your AD FS server.
  2. Put it aside regionally at /tmp/FederationMetadata.xml.
  3. Take a look at the AWS CDK code on GitHub.
  4. Use AWS CDK to deploy the stack named redshift-cn:
export AWS_DEFAULT_REGION=cn-north-1

cdk deploy redshift-cn --require-approval by no means

The AWS CDK model needs to be 2.0 or newer. For testing functions, it’s possible you’ll use the AdministratorAccess managed coverage for the deployment. For manufacturing utilization, use a profile with least privilege.

The next desk summarizes the assets that the AWS CDK bundle provisions.

Service Useful resource Depend Notes
Amazon VPC VPC 1 .
Subnet 2 .
Web gateway 1 .
Route desk 1 .
Safety group 1 .
IAM SAML Identification supplier 1 .
Position 3 1 service function for cluster
2 federated roles
Amazon Redshift Cluster 1 1 node, dc2.massive
AWS Secrets and techniques Supervisor Secret 1 .

On this instance, the Publicly Accessible setting of the Amazon Redshift cluster is ready to Enabled for simplicity. Nevertheless, in a manufacturing atmosphere, you need to disable this setting and place the cluster inside a personal subnet group. Confer with How can I entry a personal Amazon Redshift cluster from my native machine for extra data.

Configure a safety group

Add an inbound rule to your IP handle to permit connection to the Amazon Redshift cluster.

  1. Discover the safety group named RC Default Safety Group.
  2. Receive the pubic IP handle of your machine.
  3. Add an inbound rule for this IP handle and the default port for Amazon Redshift 5439.

Full the remaining declare guidelines

After you provision the IAM identification supplier and roles, add a declare rule to outline SAML roles. We add a buyer declare rule with the identify Roles. It finds AD teams with the prefix role_ and replaces it with a mixed ARN string. Take note of the ARNs of the assets the place the partition is aws-cn. Substitute AWS_ACCOUNT together with your AWS account ID. The next desk demonstrates how the chosen AD teams are remodeled to IAM function ARNs.

Chosen AD Group Reworked IAM Position ARN
role_data_scientist arn:aws-cn:iam::AWS_ACCOUNT:function/rc_data_scientist
role_data_engineer arn:aws-cn:iam::AWS_ACCOUNT:function/rc_data_engineer

Relying party claim rule

So as to add the declare rule, open the AD FS administration console in your Home windows Server and full the next steps:

  1. Select Relying Celebration Trusts, then select the relying social gathering for AWS China.
  2. Select Edit Declare Issuance Coverage, then select Add Position.
  3. On the Declare rule template menu, select Ship Claims Utilizing a Customized Rule.
  4. For Declare rule identify, enter Roles.
  5. Within the Customized rule part, enter the next:
c:[Type == "http://temp/variable", Value =~ "(?i)^role_"]
=> situation(Kind = "https://aws.amazon.com/SAML/Attributes/Position",
Worth = RegExReplace(c.Worth, "role_",

The elective parameters of DbUser, AutoCreate, and DbGroups will be supplied by way of both JDBC connection parameters or SAML attribute values. The advantage of consumer federation is to handle customers in a single place centrally. Due to this fact, the DbUser worth needs to be robotically supplied by the SAML attribute. The AutoCreate parameter ought to all the time be true, in any other case you need to create DB customers beforehand. Lastly, the DbGroups parameter could possibly be supplied by SAML attributes supplied that such relationship is outlined in AD.

To summarize, we advocate to offer at the very least DbUser and AutoCreate in SAML attributes, such that the end-user can save time by composing shorter connection strings. In our instance, we offer all three parameters by way of SAML attributes.

  1. Add a buyer declare rule named DbUser. We use an e-mail handle as the worth for DbUser:
c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", 
 Issuer == "AD AUTHORITY"]
=> situation(retailer = "Lively Listing",
sorts = ("https://redshift.amazon.com/SAML/Attributes/DbUser"),
question = ";mail;{0}", param = c.Worth);

It’s also possible to select a Safety Accounts Supervisor (SAM) account identify, which is normally the consumer identify of the e-mail handle. Utilizing an e-mail handle performs an essential function in IAM function coverage setting. We revisit this situation later.

  1. Add the customized declare rule named AutoCreate:
=> situation(kind = "https://redshift.amazon.com/SAML/Attributes/AutoCreate", worth = "true");

  1. Add a buyer declare rule named DbGroups. It finds all AD teams with the prefix group_ and lists them as values for DbGroups:
c:[Type == "http://temp/variable", Value =~ "(?i)^group_"]
=> situation(Kind = "https://redshift.amazon.com/SAML/Attributes/DbGroups", Worth = c.Worth);

You possibly can check the previous setting is right by acquiring the SAML response by way of your browser.

  1. Go to https://yourcompany.com/adfs/ls/IdpInitiatedSignOn.aspx in your Home windows Server, log in with consumer clement, and examine that the next SAML attributes exist. For consumer jimmy, the function is rc_data_engineer and the DB group incorporates solely group_pharmacy.
    <Attribute Title="https://aws.amazon.com/SAML/Attributes/RoleSessionName">
    <Attribute Title="https://aws.amazon.com/SAML/Attributes/Position">
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/DbUser">
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/AutoCreate">
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/DbGroups">

The previous SAML attribute names are verified legitimate for AWS China Areas. The URLs finish with amazon.com. It’s incorrect to alter them to amazonaws.cn or amazon.cn.

Hook up with Amazon Redshift with a SQL shopper

We use JDBC-based SQL Workbench/J (SQL shopper) to hook up with the Amazon Redshift cluster. Amazon Redshift makes use of a DB group to gather DB customers. The database privileges are managed collectively at group stage. On this submit, we don’t dive deep into privilege administration. Nevertheless, you’ll want to create the previous two DB teams.

  1. Hook up with the provisioned cluster and create the teams. You possibly can join on the AWS Administration Console by way of the question editor with non permanent credentials, or by way of a SQL shopper with database consumer admin and password. The password is saved in AWS Secrets and techniques Supervisor. Chances are you’ll want correct permissions for the above operations.
create group group_oncology;
create group group_pharmacy;

  1. Observe the directions in Hook up with your cluster by utilizing SQL Workbench/J to obtain and set up the SQL shopper and the Amazon Redshift JDBC driver.

We advocate the JDBC driver model 2.1 with AWS SDK driver-dependent libraries.

  1. Check that the cluster is connectable by way of its endpoint. The first consumer identify is admin. You retrieve the key worth of the cluster’s password by way of Secrets and techniques Supervisor. Specify DSILogLevel and LogPath to acquire driver logs and assist analysis. The connection string appears like the next code. Substitute CLUSTER_ENDPOINT with the proper worth and delete all line breakers. We break up the road for readability.

For AWS China Areas, one additional JDBC driver possibility loginToRp have to be set as you arrange a separate relying social gathering belief for the AWS China Areas. If an AD consumer is mapped to multiple AWS function, within the connection string, use preferred_role to specify the precise function to imagine for federated entry.

  1. Copy the function ARN immediately and take note of the aws-cn partition.

If the consumer is mapped to solely one function, this feature will be omitted.

  1. Substitute CLUSTER_ID with the proper cluster identifier. For the consumer identify, enter yourcompanyclement; for the password, enter the credential from AD:

  1. Once you’re related, run the SQL assertion as proven within the following screenshot.

The consumer prefixed with IAMA signifies that the consumer related with federated entry and was auto-created.

SQL Workbench to query users

  1. As an elective step, within the connection string, you possibly can set the DbUser, AutoCreate, and DbGroups parameters.

Parameters from the connection string are earlier than these from SAML attributes. We advocate you set at the very least DbUser and AutoCreate by way of SAML attributes. If it’s troublesome to handle DB teams in AD customers otherwise you need flexibility, specify DbGroups within the connection string. See the next code:


Use an e-mail or SAM account identify as DB consumer

The function coverage follows the instance coverage for utilizing GetClusterCredentials. It additional permits redshift:DescribeClusters on the cluster as a result of the function queries the cluster endpoint and port primarily based on its identifier and Area. To make it possible for the DB consumer is similar because the AD consumer, on this submit, we use the next situation to examine, the place ROLE_ID is the distinctive identifier of the function:

{"StringEqualsIgnoreCase": {"aws:userid": "ROLD_ID:${redshift:DbUser}"}}

The instance coverage makes use of the next situation:

{"StringEqualsIgnoreCase": {"aws:userid": "ROLD_ID:${redshift:DbUser}@yourcompany.com"}}

The distinction is obvious. The aws:userid incorporates the RoleSessionName, which is the e-mail handle. The SAM account identify is the string earlier than @ within the e-mail handle. As a result of the connection string parameter is earlier than the SAML attribute parameter, we summarize the attainable instances as follows:

  • If SAML attributes comprise DbUser:
    • If the situation worth incorporates a website suffix:
      • If the DbUser SAML attribute worth is an e-mail handle, DbUser have to be within the connection string with out the area suffix.
      • If the DbUser SAML attribute worth is a SAM account identify, DbUser will be omitted within the connection string. In any other case, the worth should not comprise a website suffix.
    • If the situation worth doesn’t comprise a website suffix:
      • If the DbUser SAML attribute worth is an e-mail handle, DbUser will be omitted within the connection string. In any other case, the worth should comprise a website suffix.
      • If the DbUser SAML attribute worth is a SAM account identify, DbUser have to be within the connection string with a website suffix.
  • If SAML attributes don’t comprise DbUser:
    • If the situation worth incorporates a website suffix, DbUser have to be within the connection string and not using a area suffix.
    • If the situation worth doesn’t comprise a website suffix, DbUser will be omitted within the connection string, as a result of RoleSessionName worth which is the e-mail handle acts as DbUser. In any other case, the worth should comprise a website suffix.

IAM policy condition judgment workflow


Federated entry to Amazon Redshift is a non-trivial course of. Nevertheless, it consists of smaller steps that we will divide and conquer when issues happen. Confer with the entry diagram within the resolution overview. We are able to break up the method into three phrases:

  1. Is SAML-based federation profitable? Confirm this by visiting the one sign-on web page of AD FS and ensure you can check in to the console with the federated function. Do you configure the relying social gathering with the AWS China particular metadata doc? Receive the SAML response and examine if the vacation spot is https://signin.amazonaws.cn/saml. Are the SAML supplier ARN and IAM function ARNs right? Examine if the function’s belief relationship incorporates the proper worth for SAML:aud. For different attainable checkpoints, discuss with Troubleshooting SAML 2.0 federation with AWS.
  2. Are the function insurance policies right? If SAML-based federation is profitable, examine the function insurance policies are right. Evaluate yours with these supplied by this submit. Did you utilize aws the place aws-cn needs to be used? If the coverage situation incorporates a website suffix, is it the proper area suffix? You possibly can acquire the area suffix in use for those who get the error that the assumed function isn’t approved to carry out an motion.
  3. Is the SQL shopper connecting efficiently? Is the cluster identifier right? Be sure that your connection string incorporates the loginToRp possibility and factors to the AWS China relying social gathering. If a number of IAM roles are mapped, make sure that preferred_role is one among them with the proper function ARN. You may get the record of roles within the SAML response. Attempt to set ssl_insecure to true quickly for debugging. Examine the earlier subsection and ensure the DbUser is correctly used or set based on the DbUser SAML attribute and situation worth for aws:consumer. Activate the driving force logs and get debug hints there. Typically it’s possible you’ll must restart the SQL shopper to clear the cache and retry.

Safety issues

In a manufacturing atmosphere, we propose making use of the next safety settings, which aren’t used on this submit.

For the Amazon Redshift cluster, full the next:

  • Disable the publicly accessible possibility and place the cluster inside a personal or remoted subnet group
  • Encrypt the cluster, for instance, with a buyer managed AWS Key Administration Service (AWS KMS) key
  • Allow enhanced VPC routing such that the community doesn’t depart your VPC
  • Configure the cluster to require Safe Sockets Layer (SSL) and use one-way SSL authentication

For the IAM federated roles:

  • Specify the precise DB teams for motion redshift:JoinGroup. If you wish to use a wildcard, make sure that it doesn’t allow undesirable DB teams.
  • Examine StringEquals for aws:consumer in opposition to the function ID together with the Amazon Redshift DB consumer. This situation will be checked for GetClusterCredentials, CreateClusterUser, and JoinGroup actions. Confer with the pattern code for detailed codes.

In Amazon Redshift, the DB group is used to handle privileges for a group of DB customers. A DB consumer joins some DB teams throughout a login session and is granted the privileges related to the teams. As we mentioned earlier than, you need to use both the SAML attribute worth or the connection property to specify the DB teams. The Amazon Redshift driver prefers the worth from the connection string to that from the SAML attribute. Because of this, the end-user can override the DB teams within the connection string. Due to this fact, to restrict the privileges a DB consumer will be granted, the IAM function coverage should prohibit which DB teams the DB consumer is allowed to hitch safely, in any other case there is perhaps a safety danger. The next coverage snippet reveals such a danger. All the time comply with the least privilege precept when defining permission insurance policies.

    "Impact": "Permit",
    "Motion": "redshift:JoinGroup",
    "Useful resource": "*"

Clear up

Run the next command to destroy the assets and cease incurring fees:

cdk destroy redshift-cn --force

Take away the customers and teams created within the AD FS. Lastly, take away the relying social gathering belief for AWS China Areas in your AD FS for those who don’t want it anymore.


On this submit, we walked you thru how to hook up with Amazon Redshift in China with federated entry primarily based on AD FS. AWS China Areas are in a partition totally different from different AWS Areas, so it’s essential to pay particular consideration throughout the configuration. In abstract, you’ll want to examine AWS assets ARNs with the aws-cn partition, SAML-based federation with the AWS China particular metadata doc, and an Amazon Redshift JDBC driver with additional connecting choices. This submit additionally discusses totally different utilization situations for the redshift:Dbuser parameter and supplies frequent troubleshooting ideas.

For extra data, discuss with the Amazon Redshift Cluster Administration Information. Discover the code used for this submit within the following GitHub repository.

Concerning the Authors

Clement YuanWenjun Yuan
is a Cloud Infra Architect in AWS Skilled Providers primarily based in Chengdu, China. He works with numerous prospects, from startups to worldwide enterprises, serving to them construct and implement options with state-of-the-art cloud applied sciences and obtain extra of their cloud explorations. He enjoys studying poetry and touring all over the world in his spare time.

Khoa NguyễnKhoa Nguyen is a Large Knowledge Architect in AWS Skilled Providers. He works with massive enterprise prospects and AWS companions to speed up prospects’ enterprise outcomes by offering experience in Large Knowledge and AWS companies.

Yewei Li is a Knowledge Architect in AWS Skilled Providers primarily based in Shanghai, China. He works with numerous enterprise prospects to design and construct information warehousing and information lake options on AWS. In his spare time, he loves studying and doing sports activities.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.