AWS Redshift Data Access Management using On-premise Active Directory and connecting over JDBC

By Abhijit Patil and Hukumchand Shah

Problem Statement: When you don’t have Identity provider exposed to AWS, but you have a custom Identify provider which is integrated with corporate Active Directory.

Ex: You want to connect to Redshift from On-premise (ex. SQL workbench, SQL Squirrel, Java API’s, Qlick View, Tableau, etc.) tools and use your corporate Active Directory based authentication.

Resolution:

The below diagram represents sequence of interaction with various components involved in the Federated Access Management. This mechanism will enable user to login/access Redshift using corporate AD credentials.

Federated Access

Steps to configure on AWS

Data Access Management

1. Create the Redshift DB groups and assign required permissions for the groups. You can give GRANT access to Tables or Schema for the DB Group. Refer the link for GRANT syntax https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html
2. Create IAM Policy and restrict access to the specific DBGroup(s). Refer the below sample Policy

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"GetClusterCredsStatement",
         "Effect":"Allow",
         "Action":[
            "redshift:GetClusterCredentials"
         ],
         "Resource":[
            "arn:aws:redshift:us-west-2:123456789012:dbuser:examplecluster/${redshift:DbUser}",
            "arn:aws:redshift:us-west-2:123456789012:dbname:examplecluster/<dbname>",
            "arn:aws:redshift:us-west-2:123456789012:dbgroup:examplecluster/<DBgroup1>"
         ],
         "Condition":{
            "StringEquals":{
               "aws:userid":"AIDIODR4TAW7CSEXAMPLE:${redshift:DbUser}@YOURDOMAIN.COM"
            }
         }
      },
      {
         "Sid":"CreateClusterUserStatement",
         "Effect":"Allow",
         "Action":[
            "redshift:CreateClusterUser"
         ],
         "Resource":[
            "arn:aws:redshift:us-west-2:123456789012:dbuser:examplecluster/${redshift:DbUser}"
         ],
         "Condition":{
            "StringEquals":{
               "aws:userid":"AIDIODR4TAW7CSEXAMPLE:${redshift:DbUser}@YOURDOMAIN.COM"
            }
         }
      },
      {
         "Sid":"RedshiftJoinGroupStatement",
         "Effect":"Allow",
         "Action":[
            "redshift:ListDatabases",
            "redshift:DescribeClusterTracks",
            "redshift:DescribeEvents",
            "redshift:DescribeClusterDbRevisions",
            "redshift:DescribeNodeConfigurationOptions",
            "redshift:DescribeQuery",
            "redshift:DescribeClusters",
            "redshift:DescribeTable",
            "redshift:ViewQueriesInConsole",
            "redshift:ViewQueriesFromConsole",
            "redshift:FetchResults",
            "redshift:ListTables",
            "redshift:ListSavedQueries",
            "redshift:ListSchemas",
            "redshift:CancelQuery",
            "redshift:CancelQuerySession",
            "redshift:ExecuteQuery",
            "redshift:JoinGroup"
         ],
         "Resource":[
            "arn:aws:redshift:us-west-2:123456789012:dbgroup:examplecluster/<common_DBgroup>"
         ]
      }
   ]
}

3. Create federated IAM role and assign the policy which is created as above.

NOTE: We don’t need to create individual users. The user who is part of AD group and accessing the Redshift cluster for first time then the user will get created automatically with its AD user name.

Follow the steps to connect to Redshift using JDBC and Active Directory credentials.

1. Custom JDBC Driver:
   Download Redshift latest JDBC driver. Use the link to download the Driver https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html

• Set up new JAVA maven/gradle project
• Add Redshift JDBC driver dependency in pom/gradle file
• Only 3 classes needs to be developed
  a) AD based authenticator as per your corporate policy to user authentication.
  b) Security Credential Provider to retrieve the STS token
  - Use AWS SDK to call IAM STS(Simple Token Service) Endpoint to assume role with SAML and get security credentials. Follow the details on this link https://docs.aws.amazon.com/STS/latest/APIReference/API_AssumeRoleWithSAML.html
  c) Custom driver class which extends java.sql.Driver and override the method

public Connection connect(String url, Properties prop) throws SQLException

• Call ADBasedAuthenticator API based on corporate AD API
• Call SecurityCredentialProvider method to get security credentials which should return below details

1. SessionToken
2. SecretAccessKey
3. AccessKeyID

• Call super.getConection(properties) and return the connection object by passing the below properties

+------------------+-----------------------------------------+
|     Property     |               Description               |
+------------------+-----------------------------------------+
| Awsaccountnumber | user needs to pass from JDBC client     |
| Awsrolename      | user needs to pass from JDBC client     |
| roleADgroup      | user needs to pass from JDBC client     |
| DbGroups         | user needs to pass from JDBC client     |
| AutoCreate       | true                                    |
| SessionToken     | Retrieved from ADBasedAuthenticator API |
| SecretAccessKey  | Retrieved from ADBasedAuthenticator API |
| AccessKeyID      | Retrieved from ADBasedAuthenticator API |
+------------------+-----------------------------------------+

Note: Property value 1,2 & 3 would be required for AD authentication and Account validation as per your corporate AD API if you have custom IDP

2. Redshift JDBC Driver:
Download Redshift latest JDBC driver. Use the link to download the Driver https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html

Pass the following property to the Driver.

+------------------+-----------------------------------------+
|     Property     |               Description               |
+------------------+-----------------------------------------+
| AutoCreate       | true                                    |
| SessionToken     | Retrieved from ADBasedAuthenticator API |
| SecretAccessKey  | Retrieved from ADBasedAuthenticator API |
| AccessKeyID      | Retrieved from ADBasedAuthenticator API |
+------------------+-----------------------------------------+

Conclusion: With the given approach one can establish On-Premise connectivity with AWS Redshift using AD credentials and manage authorisation.

Authors:

Abhijit Patil: Director-Cloud Data Engineering and Architecture, expertise over building enterprise scale products and services with 20+ years of diverse experience in various technologies and finance domains.

Hukumchand Shah: VP of Engineering, specialised Cloud Data Engineering, Big Data, Microservice, Leadership, and Health Enthusiast.