How Swisscom automated Amazon Redshift as a part of their One Information Platform answer utilizing AWS CDK – Half 2

On this sequence, we discuss Swisscom’s journey of automating Amazon Redshift provisioning as a part of the Swisscom One Information Platform (ODP) answer utilizing the AWS Cloud Improvement Equipment (AWS CDK), and we offer code snippets and the opposite helpful references.

In Half 1, we did a deep dive on provisioning a safe and compliant Redshift cluster utilizing the AWS CDK and one of the best practices of secret rotation. We additionally defined how Swisscom used AWS CDK {custom} sources to automate the creation of dynamic consumer teams which are related for the AWS Identification and Entry Administration (IAM) roles matching totally different job features.

On this publish, we discover utilizing the AWS CDK and a number of the key subjects for self-service utilization of the provisioned Redshift cluster by end-users in addition to different managed providers and functions. These subjects embody federation with the Swisscom identification supplier (IdP), JDBC connections, detective controls utilizing AWS Config guidelines and remediation actions, value optimization utilizing the Redshift scheduler, and audit logging.

Scheduled actions

To optimize cost-efficiency for provisioned Redshift cluster deployments, Swisscom carried out a scheduling mechanism. This performance is pushed by the consumer configuration of the cluster, as described in Half 1 of this sequence, whereby the consumer could allow dynamic pausing and resuming of clusters primarily based on specified cron expressions:

redshift_options:
...
  use_scheduler: true                                         # Whether or not to make use of Redshift scheduler
  scheduler_pause_cron: "cron(00 18 ? * MON-FRI *)"           # Cron expression for scheduler pause
  scheduler_resume_cron: "cron(00 08 ? * MON-FRI *)"          # Cron expression for scheduler resume
...

This characteristic permits Swisscom to scale back operational prices by suspending cluster exercise throughout off-peak hours. This results in important value financial savings by pausing and resuming clusters at applicable occasions. The scheduling is achieved utilizing the AWS CloudFormation motion CfnScheduledAction. The next code illustrates how Swisscom carried out this scheduling:

if config.use_scheduler:
    cfn_scheduled_action_pause = aws_redshift.CfnScheduledAction(
        scope, "schedule-pause-action",
        # ...
        schedule=config.scheduler_pause_cron,
        # ...
        target_action=aws_redshift.CfnScheduledAction.ScheduledActionTypeProperty(
                         pause_cluster=aws_redshift.CfnScheduledAction.ResumeClusterMessageProperty(
                            cluster_identifier="cluster-identifier"
                         )
                      )
    )

    cfn_scheduled_action_resume = aws_redshift.CfnScheduledAction(
        scope, "schedule-resume-action",
        # ...
        schedule=config.scheduler_resume_cron,
        # ...
        target_action=aws_redshift.CfnScheduledAction.ScheduledActionTypeProperty(
                         resume_cluster=aws_redshift.CfnScheduledAction.ResumeClusterMessageProperty(
                            cluster_identifier="cluster-identifier"
                         )
                      )
    )

JDBC connections

The JDBC connectivity for Amazon Redshift clusters was additionally very versatile, adapting to user-defined subnet varieties and safety teams within the configuration:

redshift_options:
...
  subnet_type: "routable-private"         # 'routable-private' OR 'non-routable-private'
  security_group_id: "sg-test_redshift"   # Safety Group ID for Amazon Redshift (referenced group should exists in Account)
...

As illustrated within the ODP structure diagram in Half 1 of this sequence, a substantial a part of extract, rework, and cargo (ETL) processes is anticipated to function outdoors of Amazon Redshift, inside the serverless AWS Glue surroundings. Given this, Swisscom wanted a mechanism for AWS Glue to connect with Amazon Redshift. This connectivity to Redshift clusters is supplied by way of JDBC by creating an AWS Glue connection inside the AWS CDK code. This connection permits ETL processes to work together with the Redshift cluster by establishing a JDBC connection. The subnet and safety group outlined within the consumer configuration information the creation of JDBC connectivity. If no safety teams are outlined within the configuration, a default one is created. The connection is configured with particulars of the info product from which the Redshift cluster is being provisioned, like ETL consumer and default database, together with community parts like cluster endpoint, safety group, and subnet to make use of, offering safe and environment friendly knowledge switch. The next code snippet demonstrates how this was achieved:

jdbc_connection = glue.Connection(
    scope, "redshift-glue-connection",
    kind=ConnectionType("JDBC"),
    connection_name="redshift-glue-connection",
    subnet=connection_subnet,
    security_groups=connection_security_groups,
    properties={
        "JDBC_CONNECTION_URL": f"jdbc:redshift://{cluster_endpoint}/{database_name}",
        "USERNAME": etl_user.username,
        "PASSWORD": etl_user.password.to_string(),
        "redshiftTmpDir": f"s3://{data_product_name}-redshift-work"
    }
)

By doing this, Swisscom made positive that serverless ETL workflows in AWS Glue can securely talk with newly provisioned Redshift cluster operating inside a secured digital personal cloud (VPC).

Identification federation

Identification federation permits a centralized system (the IdP) for use for authenticating customers as a way to entry a service supplier like Amazon Redshift. A extra basic overview of the subject might be present in Identification Federation in AWS.

Identification federation not solely enhances safety resulting from its centralized consumer lifecycle administration and centralized authentication mechanism (for instance, supporting multi-factor authentication), but additionally improves the consumer expertise and reduces the general complexity of identification and entry administration and thereby additionally its governance.

In Swisscom’s setup, Microsoft Lively Listing Providers are used for identification and entry administration. On the preliminary construct phases of ODP, Amazon Redshift provided two totally different choices for identification federation:

In Swisscom’s context, in the course of the preliminary implementation, Swisscom opted for IAM-based SAML 2.0 IdP federation as a result of it is a extra basic strategy, which will also be used for different AWS providers, equivalent to Amazon QuickSight (see Establishing IdP federation utilizing IAM and QuickSight).

At 2023 AWS re:Invent, AWS introduced a new connection possibility to Amazon Redshift primarily based on AWS IAM Identification Heart. IAM Identification Heart gives a single place for workforce identities in AWS, permitting the creation of customers and teams immediately inside itself or by federation with normal IdPs like Okta, PingOne, Microsoft Entra ID (Azure AD), or any IdP that helps SAML 2.0 and SCIM. It additionally gives a single sign-on (SSO) expertise for Redshift options and different analytics providers equivalent to Amazon Redshift Question Editor V2 (see Combine Identification Supplier (IdP) with Amazon Redshift Question Editor V2 utilizing AWS IAM Identification Heart for seamless Single Signal-On), QuickSight, and AWS Lake Formation. Furthermore, a single IAM Identification Heart occasion might be shared with a number of Redshift clusters and workgroups with a easy auto-discovery and join functionality. It makes positive all Redshift clusters and workgroups have a constant view of customers, their attributes, and teams. This complete setup suits nicely with ODP’s imaginative and prescient of offering self-service analytics throughout the Swisscom workforce with crucial safety controls in place. On the time of writing, Swisscom is actively working in the direction of utilizing IAM Identification Heart as the usual federation answer for ODP. The next diagram illustrates the high-level structure for the work in progress.

Audit logging

Amazon Redshift audit logging is helpful for auditing for safety functions, monitoring, and troubleshooting. The logging gives info, such because the IP tackle of the consumer’s laptop, the kind of authentication utilized by the consumer, or the timestamp of the request. Amazon Redshift logs the SQL operations, together with connection makes an attempt, queries, and modifications, and makes it easy to trace the modifications. These logs might be accessed by way of SQL queries in opposition to system tables, saved to a safe Amazon Easy Storage Service (Amazon S3) location, or exported to Amazon CloudWatch.

Amazon Redshift logs info within the following log recordsdata:

• Connection log – Gives info to watch customers connecting to the database and associated connection info like their IP tackle.
• Person log – Logs details about modifications to database consumer definitions.
• Person exercise log – Tracks details about the forms of queries that each the customers and the system carry out within the database. It’s helpful primarily for troubleshooting functions.

With the ODP answer, Swisscom wished to write down all of the Amazon Redshift logs to CloudWatch. That is at the moment indirectly supported by the AWS CDK, so Swisscom carried out a workaround answer utilizing the AWS CDK {custom} sources possibility, which invokes the SDK on the Redshift motion enableLogging. See the next code:

    custom_resources.AwsCustomResource(self, f"{self.cluster_identifier}-custom-sdk-logging",
           on_update=custom_resources.AwsSdkCall(
               service="Redshift",
               motion="enableLogging",
               parameters={
                   "ClusterIdentifier": self.cluster_identifier,
                   "LogDestinationType": "cloudwatch",
                   "LogExports": ["connectionlog","userlog","useractivitylog"],
               },
               physical_resource_id=custom_resources.PhysicalResourceId.of(
                   f"{self.account}-{self.area}-{self.cluster_identifier}-logging")
           ),
           coverage=custom_resources.AwsCustomResourcePolicy.from_sdk_calls(
               sources=[f"arn:aws:redshift:{self.region}:{self.account}:cluster:{self.cluster_identifier}"]
           )
        )

AWS Config guidelines and remediation

After a Redshift cluster has been deployed, Swisscom wanted to guarantee that the cluster meets the governance guidelines outlined in each cut-off date after creation. For that, Swisscom determined to make use of AWS Config.

AWS Config gives an in depth view of the configuration of AWS sources in your AWS account. This contains how the sources are associated to at least one one other and the way they had been configured prior to now so you’ll be able to see how the configurations and relationships change over time.

An AWS useful resource is an entity you’ll be able to work with in AWS, equivalent to an Amazon Elastic Compute Cloud (Amazon EC2) occasion, Amazon Elastic Block Retailer (Amazon EBS) quantity, safety group, or Amazon VPC.

The next diagram illustrates the method Swisscom carried out.

If an AWS Config rule isn’t compliant, a remediation might be utilized. Swisscom outlined the pause cluster motion as default in case of a non-compliant cluster (primarily based in your necessities, different remediation actions are doable). That is coated utilizing an AWS Programs Supervisor automation doc (SSM doc).

Automation, a functionality of Programs Supervisor, simplifies widespread upkeep, deployment, and remediation duties for AWS providers like Amazon EC2, Amazon Relational Database Service (Amazon RDS), Amazon Redshift, Amazon S3, and plenty of extra.

The SSM doc is predicated on the AWS doc AWSConfigRemediation-DeleteRedshiftCluster. It seems to be like the next code:

description: | 
  ### Doc title - PauseRedshiftCluster-WithCheck 

  ## What does this doc do? 
  This doc pauses the given Amazon Redshift cluster utilizing the [PauseCluster](https://docs.aws.amazon.com/redshift/newest/APIReference/API_PauseCluster.html) API. 

  ## Enter Parameters 
  * AutomationAssumeRole: (Required) The ARN of the function that enables Automation to carry out the actions in your behalf. 
  * ClusterIdentifier: (Required) The identifier of the Amazon Redshift Cluster. 

  ## Output Parameters 
  * PauseRedshiftClusterWithoutSnapShot.Response: The usual HTTP response from the PauseCluster API. 
  * PauseRedshiftClusterWithSnapShot.Response: The usual HTTP response from the PauseCluster API. 
schemaVersion: '0.3' 
assumeRole: '{{ AutomationAssumeRole }}' 
parameters: 
  AutomationAssumeRole: 
    kind: String 
    description: (Required) The ARN of the function that enables Automation to carry out the actions in your behalf. 
    allowedPattern: '^arn:aws[a-z0-9-]*:iam::d{12}:function/[w-/.@+=,]{1,1017}$' 
  ClusterIdentifier: 
    kind: String 
    description: (Required) The identifier of the Amazon Redshift Cluster. 
    allowedPattern: '[a-z]{1}[a-z0-9_.-]{0,62}' 
mainSteps: 
  - title: GetRedshiftClusterStatus 
    motion: 'aws:executeAwsApi' 
    inputs: 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
      Service: redshift 
      Api: DescribeClusters 
    description: |- 
      ## GetRedshiftClusterStatus 
      Will get the standing for the given Amazon Redshift Cluster. 
    outputs: 
      - Title: ClusterStatus 
        Selector: '$.Clusters[0].ClusterStatus' 
        Kind: String 
    timeoutSeconds: 600 
  - title: Situation 
    motion: 'aws:department' 
    inputs: 
      Decisions: 
        - NextStep: PauseRedshiftCluster 
          Variable: '{{ GetRedshiftClusterStatus.ClusterStatus }}' 
          StringEquals: out there 
      Default: End 
  - title: PauseRedshiftCluster 
    motion: 'aws:executeAwsApi' 
    description: | 
      ## PauseRedshiftCluster 
      Makes PauseCluster API name utilizing Amazon Redshift Cluster identifier and pauses the cluster with out taking any ultimate snapshot. 
      ## Outputs 
      * Response: The usual HTTP response from the PauseCluster API. 
    timeoutSeconds: 600 
    isEnd: false 
    nextStep: VerifyRedshiftClusterPause 
    inputs: 
      Service: redshift 
      Api: PauseCluster 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
    outputs: 
      - Title: Response 
        Selector: $ 
        Kind: StringMap 
  - title: VerifyRedshiftClusterPause 
    motion: 'aws:assertAwsResourceProperty' 
    timeoutSeconds: 600 
    isEnd: true 
    description: | 
      ## VerifyRedshiftClusterPause 
      Verifies the given Amazon Redshift Cluster is paused. 
    inputs: 
      Service: redshift 
      Api: DescribeClusters 
      ClusterIdentifier: '{{ ClusterIdentifier }}' 
      PropertySelector: '$.Clusters[0].ClusterStatus' 
      DesiredValues: 
        - pausing 
  - title: End 
    motion: 'aws:sleep' 
    inputs: 
      Length: PT1S 
    isEnd: true

The SSM automations doc is deployed with the AWS CDK:

from aws_cdk import aws_ssm as ssm  

ssm_document_content = #learn yaml doc as dict  

document_id = 'automation_id'   
document_name="automation_name" 

doc = ssm.CfnDocument(scope, id=document_id, content material=ssm_document_content,  
                           document_format="YAML", document_type="Automation", title=document_name) 

To run the automation doc, AWS Config wants the suitable permissions. You'll be able to create an IAM function for this objective:

from aws_cdk import iam 

#Create function for the automation 
role_name="role-to-pause-redshift"
automation_role = iam.Position(scope, 'role-to-pause-redshift-cluster', 
                           assumed_by=iam.ServicePrincipal('ssm.amazonaws.com'), 
                           role_name=role_name) 

automation_policy = iam.Coverage(scope, "policy-to-pause-cluster", 
                               policy_name="policy-to-pause-cluster", 
                               statements=[ 
                                   iam.PolicyStatement( 
                                       effect=iam.Effect.ALLOW, 
                                       actions=['redshift:PauseCluster', 
                                                'redshift:DescribeClusters'], 
                                       sources=['*'] 
                                   ) 
                               ]) 

automation_role.attach_inline_policy(automation_policy) 

Swisscom outlined the principles to be utilized following AWS greatest practices (see Safety Greatest Practices for Amazon Redshift). These are deployed as AWS Config conformance packs. A conformance pack is a group of AWS Config guidelines and remediation actions that may be rapidly deployed as a single entity in an AWS account and AWS Area or throughout a corporation in AWS Organizations.

Conformance packs are created by authoring YAML templates that include the checklist of AWS Config managed or {custom} guidelines and remediation actions. You may as well use SSM paperwork to retailer your conformance pack templates on AWS and immediately deploy conformance packs utilizing SSM doc names.

This AWS conformance pack might be deployed utilizing the AWS CDK:

from aws_cdk import aws_config  
  
conformance_pack_template = # learn yaml file as str 
conformance_pack_content = # substitute `role_arn_for_substitution` and `document_for_substitution` in conformance_pack_template

conformance_pack_id = 'conformance-pack-id' 
conformance_pack_name="conformance-pack-name" 


conformance_pack = aws_config.CfnConformancePack(scope, id=conformance_pack_id, 
                                                 conformance_pack_name=conformance_pack_name, 
                                                 template_body=conformance_pack_content) 

Conclusion

Swisscom is constructing its next-generation data-as-a-service platform by way of a mixture of automated provisioning processes, superior safety features, and user-configurable choices to cater for numerous knowledge dealing with and knowledge merchandise’ wants. The mixing of the Amazon Redshift assemble within the ODP framework is a big stride in Swisscom’s journey in the direction of a extra related and data-driven enterprise panorama.

In Half 1 of this sequence, we demonstrated the best way to provision a safe and compliant Redshift cluster utilizing the AWS CDK in addition to the best way to cope with one of the best practices of secret rotation. We additionally confirmed the best way to use AWS CDK {custom} sources in automating the creation of dynamic consumer teams which are related for the IAM roles matching totally different job features.

On this publish, we confirmed, by way of the utilization of the AWS CDK, the best way to tackle key Redshift cluster utilization subjects equivalent to federation with the Swisscom IdP, JDBC connections, detective controls utilizing AWS Config guidelines and remediation actions, value optimization utilizing the Redshift scheduler, and audit logging.

The code snippets on this publish are supplied as is and can must be tailored to your particular use circumstances. Earlier than you get began, we extremely suggest chatting with an Amazon Redshift specialist.

Concerning the Authors

Asad bin Imtiaz is an Knowledgeable Information Engineer at Swisscom, with over 17 years of expertise in architecting and implementing enterprise-level knowledge options.

Jesús Montelongo Hernández is an Knowledgeable Cloud Information Engineer at Swisscom. He has over 20 years of expertise in IT methods, knowledge warehousing, and knowledge engineering.

Samuel Bucheli is a Lead Cloud Architect at Zühlke Engineering AG. He has over 20 years of expertise in software program engineering, software program structure, and cloud structure.

Srikanth Potu is a Senior Advisor in EMEA, a part of the Skilled Providers group at Amazon Net Providers. He has over 25 years of expertise in Enterprise knowledge structure, databases and knowledge warehousing.