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

Many purchasers already handle consumer identities by identification suppliers (IdPs) for single sign-on entry. With an IdP similar to Lively Listing Federation Companies (AD FS), you possibly can arrange federated entry to Amazon Redshift clusters as a mechanism to manage 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 info, check with the weblog put up collection “Federate entry to your Amazon Redshift cluster with Lively Listing Federation Companies (AD FS)” (half 1, half 2).

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

This put up introduces a step-by-step process to arrange federated entry to Amazon Redshift in AWS China Areas. It pinpoints the important thing variations it’s best to take note of and supplies a troubleshooting information for widespread 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 title 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 well-liked position with SAML.
4. AWS STS authenticates the SAML response based mostly on the mutual belief and returns short-term credentials if profitable.
5. The SQL shopper communicates with Amazon Redshift to get again a database consumer with short-term credentials, then makes use of it to hitch database teams and connect with the desired database.

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 primary declare guidelines.
2. Provision an AWS Id and Entry Administration (IAM) identification supplier and roles.
3. Full the remaining relying social gathering belief’s declare guidelines based mostly on the IAM assets.
4. Connect with Amazon Redshift with federated entry through a JDBC-based SQL shopper.

Stipulations

This put up assumes that you’ve the next stipulations:

• Home windows Server 2016
• The flexibility to create customers and teams in AD
• The flexibility to configure a relying social gathering belief and outline declare guidelines in AD FS
• An AWS account
• Adequate permissions to provision IAM identification suppliers, roles, Amazon Digital Non-public Cloud (Amazon VPC) associated assets, and an Amazon Redshift cluster through 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 potential to configure two relying social gathering trusts for each AWS China Areas and AWS Areas in the identical AD FS on the similar time. For AWS China Areas, we have to use a unique 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. Observe down this identifier to make use of later on this put up.

Add AD teams and customers

With SAML-based federation, end-users assume an IAM position and use it to hitch a number of database (DB) teams. The permissions on such roles and DB teams may 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 necessary to tell apart appropriately the 2 varieties 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: knowledge scientist and knowledge engineer, and two DB teams: oncology and pharmacy. Information scientists can be a part of each teams, and knowledge engineers can solely be a part of the pharmacy group. On the AD facet, we outline one AD group for every position and group. On the AWS facet, we outline one IAM position for every position 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.

It’s possible 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 determine 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. Reserve it domestically at /tmp/FederationMetadata.xml.
3. Try the AWS CDK code on GitHub.
4. Use AWS CDK to deploy the stack named redshift-cn:

export AWS_ACCOUNT=YOUR_AWS_ACCOUNT
export AWS_DEFAULT_REGION=cn-north-1
export AWS_PROFILE=YOUR_PROFILE

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

The AWS CDK model needs to be 2.0 or newer. For testing functions, you might 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 package deal provisions.

On this instance, the Publicly Accessible setting of the Amazon Redshift cluster is ready to Enabled for simplicity. Nevertheless, in a manufacturing setting, it’s best to disable this setting and place the cluster inside a non-public subnet group. Discuss with How can I entry a non-public Amazon Redshift cluster from my native machine for extra info.

Configure a safety group

Add an inbound rule in your IP deal with to permit connection to the Amazon Redshift cluster.

1. Discover the safety group named RC Default Safety Group.
2. Receive the pubic IP deal with of your machine.
3. Add an inbound rule for this IP deal with 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 title 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. Change AWS_ACCOUNT together with your AWS account ID. The next desk demonstrates how the chosen AD teams are reworked to IAM position ARNs.

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 Social gathering 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 title, enter Roles.
5. Within the Customized rule part, enter the next:

c:[Type == "http://temp/variable", Value =~ "(?i)^role_"]
=> situation(Sort = "https://aws.amazon.com/SAML/Attributes/Position",
Worth = RegExReplace(c.Worth, "role_",
"arn:aws-cn:iam::AWS_ACCOUNT:saml-provider/rc-provider,arn:aws-cn:iam::AWS_ACCOUNT:position/rc_"));

The elective parameters of DbUser, AutoCreate, and DbGroups may be supplied through both JDBC connection parameters or SAML attribute values. The good thing about consumer federation is to handle customers in a single place centrally. Subsequently, the DbUser worth needs to be routinely 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 might be supplied by SAML attributes supplied that such relationship is outlined in AD.

To summarize, we advocate to offer at the 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 through SAML attributes.

6. Add a buyer declare rule named DbUser. We use an electronic mail deal with 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);

You too can select a Safety Accounts Supervisor (SAM) account title, which is often the consumer title of the e-mail deal with. Utilizing an electronic mail deal with performs an necessary position in IAM position coverage setting. We revisit this situation later.

7. Add the customized declare rule named AutoCreate:

=> situation(sort = "https://redshift.amazon.com/SAML/Attributes/AutoCreate", worth = "true");

8. 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(Sort = "https://redshift.amazon.com/SAML/Attributes/DbGroups", Worth = c.Worth);

You may check the previous setting is right by acquiring the SAML response through your browser.

9. Go to https://yourcompany.com/adfs/ls/IdpInitiatedSignOn.aspx in your Home windows Server, log in with consumer clement, and test that the next SAML attributes exist. For consumer jimmy, the position is rc_data_engineer and the DB group accommodates solely group_pharmacy.

<AttributeStatement>
    <Attribute Title="https://aws.amazon.com/SAML/Attributes/RoleSessionName">
        <AttributeValue>clement@yourcompany.com</AttributeValue>
    </Attribute>
    <Attribute Title="https://aws.amazon.com/SAML/Attributes/Position">
        <AttributeValue>arn:aws-cn:iam::AWS_ACCOUNT:saml-provider/rc-provider,arn:aws-cn:iam::AWS_ACCOUNT:position/rc_data_scientist</AttributeValue>
    </Attribute>
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/DbUser">
        <AttributeValue>clement@yourcompany.com</AttributeValue>
    </Attribute>
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/AutoCreate">
        <AttributeValue>true</AttributeValue>
    </Attribute>
    <Attribute Title="https://redshift.amazon.com/SAML/Attributes/DbGroups">
        <AttributeValue>group_pharmacy</AttributeValue>
        <AttributeValue>group_oncology</AttributeValue>
    </Attribute>
</AttributeStatement>

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.

Connect with Amazon Redshift with a SQL shopper

We use JDBC-based SQL Workbench/J (SQL shopper) to connect 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 put up, we don’t dive deep into privilege administration. Nevertheless, you must create the previous two DB teams.

1. Connect with the provisioned cluster and create the teams. You may join on the AWS Administration Console through the question editor with short-term credentials, or through a SQL shopper with database consumer admin and password. The password is saved in AWS Secrets and techniques Supervisor. It’s possible you’ll want correct permissions for the above operations.

create group group_oncology;
create group group_pharmacy;

2. Observe the directions in Connect with your cluster through the use of 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.

3. Check that the cluster is connectable through its endpoint. The first consumer title is admin. You retrieve the key worth of the cluster’s password through Secrets and techniques Supervisor. Specify DSILogLevel and LogPath to acquire driver logs and assist prognosis. The connection string seems to be like the next code. Change CLUSTER_ENDPOINT with the right worth and delete all line breakers. We cut up the road for readability.

jdbc:redshift://CLUSTER_ENDPOINT.cn-north-1.redshift.amazonaws.com.cn:5439/fundamental
;ssl_insecure=true
;DSILogLevel=3
;LogPath=/tmp

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 a couple of AWS position, within the connection string, use preferred_role to specify the precise position to imagine for federated entry.

4. Copy the position ARN straight and take note of the aws-cn partition.

If the consumer is mapped to solely one position, this selection may be omitted.

5. Change CLUSTER_ID with the right cluster identifier. For the consumer title, enter yourcompanyclement; for the password, enter the credential from AD:

jdbc:redshift:iam://CLUSTER_ID:cn-north-1/fundamental
;ssl_insecure=true
;DSILogLevel=3
;LogPath=/tmp
;loginToRp=urn:amazon:webservices:cn-north-1
;plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider
;idp_host=adfsserver.yourcompany.com
;preferred_role=arn:aws-cn:iam::AWS_ACCOUNT:position/rc_data_scientist

6. Whenever you’re linked, run the SQL assertion as proven within the following screenshot.

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

7. 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 least DbUser and AutoCreate through SAML attributes. If it’s tough to handle DB teams in AD customers otherwise you need flexibility, specify DbGroups within the connection string. See the next code:

jdbc:redshift:iam://CLUSTER_ID:cn-north-1/fundamental
;ssl_insecure=true
;DSILogLevel=3
;LogPath=/tmp
;loginToRp=urn:amazon:webservices:cn-north-1
;plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider
;idp_host=adfsserver.yourcompany.com
;preferred_role=arn:aws-cn:iam::AWS_ACCOUNT:position/rc_data_scientist
;DbUser=clement@yourcompany.com
;AutoCreate=true
;DbGroups=group_oncology

Use an electronic mail or SAM account title as DB consumer

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

{"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 accommodates the RoleSessionName, which is the e-mail deal with. The SAM account title is the string earlier than @ within the electronic mail deal with. As a result of the connection string parameter is earlier than the SAML attribute parameter, we summarize the potential instances as follows:

• If SAML attributes include DbUser:
  • If the situation worth accommodates a site suffix:
    • If the DbUser SAML attribute worth is an electronic mail deal with, DbUser have to be within the connection string with out the area suffix.
    • If the DbUser SAML attribute worth is a SAM account title, DbUser may be omitted within the connection string. In any other case, the worth should not include a site suffix.
  • If the situation worth doesn’t include a site suffix:
    • If the DbUser SAML attribute worth is an electronic mail deal with, DbUser may be omitted within the connection string. In any other case, the worth should include a site suffix.
    • If the DbUser SAML attribute worth is a SAM account title, DbUser have to be within the connection string with a site suffix.
• If SAML attributes don’t include DbUser:
  • If the situation worth accommodates a site suffix, DbUser have to be within the connection string with out a area suffix.
  • If the situation worth doesn’t include a site suffix, DbUser may be omitted within the connection string, as a result of RoleSessionName worth which is the e-mail deal with acts as DbUser. In any other case, the worth should include a site suffix.

Troubleshooting

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. Discuss with the entry diagram within the resolution overview. We are able to cut up the method into three phrases:

1. Is SAML-based federation profitable? Confirm this by visiting the only sign-on web page of AD FS and ensure you can register to the console with the federated position. Do you configure the relying social gathering with the AWS China particular metadata doc? Receive the SAML response and test if the vacation spot is https://signin.amazonaws.cn/saml. Are the SAML supplier ARN and IAM position ARNs right? Examine if the position’s belief relationship accommodates the right worth for SAML:aud. For different potential checkpoints, check with Troubleshooting SAML 2.0 federation with AWS.
2. Are the position insurance policies right? If SAML-based federation is profitable, test the position insurance policies are right. Evaluate yours with these supplied by this put up. Did you employ aws the place aws-cn needs to be used? If the coverage situation accommodates a site suffix, is it the right area suffix? You may receive the area suffix in use if you happen to get the error that the assumed position isn’t licensed to carry out an motion.
3. Is the SQL shopper connecting efficiently? Is the cluster identifier right? Make it possible for your connection string accommodates the loginToRp possibility and factors to the AWS China relying social gathering. If a number of IAM roles are mapped, ensure preferred_role is one in every of them with the right position ARN. You will get the listing of roles within the SAML response. Attempt to set ssl_insecure to true briefly for debugging. Examine the earlier subsection and ensure the DbUser is correctly used or set in keeping with the DbUser SAML attribute and situation worth for aws:consumer. Activate the driving force logs and get debug hints there. Typically you might must restart the SQL shopper to clear the cache and retry.

Safety considerations

In a manufacturing setting, we advise making use of the next safety settings, which aren’t used on this put up.

For the Amazon Redshift cluster, full the next:

• Disable the publicly accessible possibility and place the cluster inside a non-public 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, ensure it doesn’t allow undesirable DB teams.
• Examine StringEquals for aws:consumer in opposition to the position ID together with the Amazon Redshift DB consumer. This situation may be checked for GetClusterCredentials, CreateClusterUser, and JoinGroup actions. Discuss 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. Subsequently, to restrict the privileges a DB consumer may be granted, the IAM position 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 exhibits such a danger. At all times observe the least privilege precept when defining permission insurance policies.

{
    "Impact": "Enable",
    "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 if you happen to don’t want it anymore.

Conclusion

On this put up, we walked you thru how to connect with Amazon Redshift in China with federated entry based mostly on AD FS. AWS China Areas are in a partition totally different from different AWS Areas, so you could pay particular consideration throughout the configuration. In abstract, you must test 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 put up additionally discusses totally different utilization situations for the redshift:Dbuser parameter and supplies widespread troubleshooting strategies.

For extra info, check with the Amazon Redshift Cluster Administration Information. Discover the code used for this put up within the following GitHub repository.

In regards to the Authors

Wenjun Yuan is a Cloud Infra Architect in AWS Skilled Companies based mostly 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 around the globe in his spare time.

Khoa Nguyen is a Large Information Architect in AWS Skilled Companies. He works with massive enterprise prospects and AWS companions to speed up prospects’ enterprise outcomes by offering experience in Large Information and AWS companies.

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