Securing sensitive Data in amazon RDS

Chabane R. - Mar 20 '21 - - Dev Community

In the previous part we created our EKS cluster. In this part we will configure the Amazon RDS Instance.

The following resources will be created:

  • A Private Multi-AZ RDS PostgreSQL Instance.
  • VPC Subnet Security Group.
  • (Optional) Network load balancer to expose private RDS to a specific range of IP addresses.
  • (Optional) A Lambda to populate NLB Target Group with RDS private IP.

Alt Text

Amazon RDS

  • The Amazon RDS Instance used is a PostgreSQL database server.
  • A multi-az option is enabled to ensure high-availability.
  • The Instance is not publicly accessible and it's hosted in private subnets.
  • All authentication is done through the IAM database authentication.
  • Automated backup is enabled.

Create a terraform file infra/plan/rds.tf

resource "random_string" "db_suffix" {
  length = 4
  special = false
  upper = false
}

resource "random_string" "root_username" {
  length = 12
  special = false
  upper = true
}

resource "random_password" "root_password" {
  length = 12
  special = true
  upper = true
}

resource "aws_db_instance" "postgresql" {

  # Engine options
  engine                              = "postgres"
  engine_version                      = "12.5"

  # Settings
  name                                = "postgresql${var.env}"
  identifier                          = "postgresql-${var.env}"

  # Credentials Settings
  username                            = "u${random_string.root_username.result}"
  password                            = "p${random_password.root_password.result}"

  # DB instance size
  instance_class                      = "db.m5.large"

  # Storage
  storage_type                        = "gp2"
  allocated_storage                   = 100
  max_allocated_storage               = 200

  # Availability & durability
  multi_az                            = true

  # Connectivity
  db_subnet_group_name                = aws_db_subnet_group.sg.id

  publicly_accessible                 = false
  vpc_security_group_ids              = [aws_security_group.sg.id]
  port                                = var.rds_port

  # Database authentication
  iam_database_authentication_enabled = true 

  # Additional configuration
  parameter_group_name                = "default.postgres12"

  # Backup
  backup_retention_period             = 14
  backup_window                       = "03:00-04:00"
  final_snapshot_identifier           = "postgresql-final-snapshot-${random_string.db_suffix.result}" 
  delete_automated_backups            = true
  skip_final_snapshot                 = false

  # Encryption
  storage_encrypted                   = true

  # Maintenance
  auto_minor_version_upgrade          = true
  maintenance_window                  = "Sat:00:00-Sat:02:00"

  # Deletion protection
  deletion_protection                 = false

  tags = {
    Environment = var.env
  }
}
Enter fullscreen mode Exit fullscreen mode

Add the following outputs

output "rds-username" {
    value = "u${random_string.root_username.result}"
}

output "rds-password" {
    value = "p${random_password.root_password.result}"
}

output "private-rds-endpoint" {
    value = aws_db_instance.postgresql.address
}
Enter fullscreen mode Exit fullscreen mode

DB Subnet Group

We deploy the Amazon RDS Instance on private subnets.

resource "aws_db_subnet_group" "sg" {
  name       = "postgresql-${var.env}"
  subnet_ids = [aws_subnet.private["private-rds-1"].id, aws_subnet.private["private-rds-2"].id]

  tags = {
    Environment = var.env
    Name        = "postgresql-${var.env}"
  }
}
Enter fullscreen mode Exit fullscreen mode

VPC Security Group

In the VPC Security group we allow:

  • inbound / outbound traffic on port 5432 with RDS public subnets.
  • inbound / outbound TCP network traffic between RDS private subnets. 
resource "aws_security_group" "sg" {
  name        = "postgresql-${var.env}"
  description = "Allow inbound/outbound traffic"
  vpc_id      = aws_vpc.main.id

  ingress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.private["private-rds-1"].cidr_block]  
  }

  ingress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.private["private-rds-2"].cidr_block]  
  }

  ingress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.public["public-rds-1"].cidr_block]  
  }

  ingress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.public["public-rds-2"].cidr_block]  
  }

  egress {
    from_port       = 0
    to_port         = 65535
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.private["private-rds-1"].cidr_block]  
  }

  egress {
    from_port       = 0
    to_port         = 65535
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.private["private-rds-2"].cidr_block]  
  }

  egress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.public["public-rds-1"].cidr_block]  
  }

  egress {
    from_port       = var.rds_port
    to_port         = var.rds_port
    protocol        = "tcp"
    cidr_blocks = [aws_subnet.public["public-rds-2"].cidr_block]  
  }

  tags = {
    Name        = "postgresql-${var.env}"
    Environment = var.env
  }
}
Enter fullscreen mode Exit fullscreen mode

(Optional) Exposing the RDS instance

If you want to access the RDS instance databases from your local machine or through an external CI / CD tool, you can create an external network load balancer and target the private IP address of the RDS instance. As the private IP address in the network interface can change if an instance fails, a Lambda function can be deployed to continuously check the current private IP address, unregister the old IP address, and register a new target with the new private IP address.

Network Load Balancer

In order to reach the RDS private IP address, the RDS instance and the external network load balancer must be in the same Availability Zones. Thus, the NLB will be deployed in the same subnet as the primary RDS instance.

We create a target group with a target type of IP address. A Cloud Watch alarm has been added to monitor connectivity between NLB and RDS.

Create the file infra/plan/nlb.tf

locals {
    subnet_id = aws_subnet.public["public-rds-1"].availability_zone == aws_db_instance.postgresql.availability_zone ? aws_subnet.public["public-rds-1"].id : aws_subnet.public["public-rds-2"].id
}

resource "aws_lb" "rds" {
  name               = "nlb-expose-rds-${var.env}"
  internal           = false
  load_balancer_type = "network"
  subnets            = [local.subnet_id]

  enable_deletion_protection = false

  tags = {
    Environment = var.env
  }
}

resource "aws_lb_listener" "rds" {
  load_balancer_arn = aws_lb.rds.id
  port              = var.rds_port
  protocol          = "TCP"

  default_action {
    target_group_arn = aws_lb_target_group.rds.id
    type             = "forward"
  }
}

resource "aws_lb_target_group" "rds" {
  name                 = "expose-rds-${var.env}"
  port                 = var.rds_port
  protocol             = "TCP"
  target_type          = "ip"
  vpc_id               = aws_vpc.main.id
  health_check {
    enabled             = true
    protocol            = "TCP"
  }
  tags = {
    Environment = var.env
  }
}

resource "aws_cloudwatch_metric_alarm" "rds-access" {
  alarm_name                = "rds-external-access-status"
  comparison_operator       = "GreaterThanOrEqualToThreshold"
  evaluation_periods        = "1"
  metric_name               = "UnHealthyHostCount"
  namespace                 = "AWS/NetworkELB"
  period                    = "60"
  statistic                 = "Maximum"
  threshold                 = 1
  alarm_description         = "Monitoring RDS External Access"
  treat_missing_data        = "breaching"

  dimensions = {
    TargetGroup  = aws_lb_target_group.rds.arn_suffix
    LoadBalancer = aws_lb.rds.arn_suffix
  }
}
Enter fullscreen mode Exit fullscreen mode

Complete the file infra/plan/output

output "public-rds-endpoint" {
    value = "${element(split("/", aws_lb.rds.arn), 2)}-${element(split("/", aws_lb.rds.arn), 3)}.elb.${var.region}.amazonaws.com"
}
Enter fullscreen mode Exit fullscreen mode

Now we need to register a target. A Lambda function can be used to perform the task. An Amazon CloudWatch event rule is added to invoke the Lambda function every 15 minutes.

Lambda function

Create the file infra/plan/lambda.tf

data "archive_file" "lambda_zip" {
    type          = "zip"
    source_file   = "${path.module}/lambda/populate-nlb-tg-with-rds-private-ip.py"
    output_path   = "lambda_function_payload.zip"
}

resource "aws_lambda_function" "rds" {
  filename      = "lambda_function_payload.zip"
  function_name = "populate-nlb-tg-with-rds-private-ip"
  role          = aws_iam_role.iam_for_lambda.arn
  handler       = "populate-nlb-tg-with-rds-private-ip.handler"

  source_code_hash = data.archive_file.lambda_zip.output_base64sha256

  runtime = "python3.8"

  timeout = 300

  environment {
    variables = {
      RDS_PORT = var.rds_port
      NLB_TG_ARN = aws_lb_target_group.rds.arn
      RDS_SG_ID = aws_security_group.sg.id
      RDS_ID = aws_db_instance.postgresql.id
    }
  }

  tags = {
    Environment = var.env
  }
}

resource "aws_iam_role" "iam_for_lambda" {
  name = "iam_for_lambda"

  assume_role_policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Effect": "Allow",
      "Sid": ""
    }
  ]
}
EOF
}

resource "aws_iam_role_policy" "lambda_nlb" {
  name = "nlb-tg-access"
  role = aws_iam_role.iam_for_lambda.id

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = [
            "ec2:DescribeNetworkInterfaces",
            "elasticloadbalancing:DeregisterTargets",
            "elasticloadbalancing:DescribeTargetHealth",
            "elasticloadbalancing:RegisterTargets",
            "rds:DescribeDBInstances"
        ]
        Effect   = "Allow"
        Resource = "*"
      },
    ]
  })
}

resource "aws_iam_role_policy" "lambda_logging" {
  name        = "lambda_logging"
  role = aws_iam_role.iam_for_lambda.id

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Action = [
            "logs:CreateLogGroup",
            "logs:CreateLogStream",
            "logs:PutLogEvents"
        ]
        Effect   = "Allow"
        Resource = "arn:aws:logs:*:*:*"
      },
    ]
  })
}

resource "aws_cloudwatch_log_group" "lambda" {
  name              = "/aws/lambda/${aws_lambda_function.rds.function_name}"
  retention_in_days = 1
}

resource "aws_cloudwatch_event_rule" "lambda" {
  name        = "populate-nlb-tg-with-rds-private-ip"
  description = "Populate NLB tg with RDS private IP"
  schedule_expression = "rate(15 minutes)"
}

resource "aws_cloudwatch_event_target" "lambda" {
  rule      = aws_cloudwatch_event_rule.lambda.name
  target_id = "Lambda"
  arn       = aws_lambda_function.rds.arn
}

resource "aws_lambda_permission" "cloudwatch" {
  statement_id  = "AllowExecutionFromCloudWatch"
  action        = "lambda:InvokeFunction"
  function_name = aws_lambda_function.rds.function_name
  principal     = "events.amazonaws.com"
  source_arn    = aws_cloudwatch_event_rule.lambda.arn
}
Enter fullscreen mode Exit fullscreen mode

The lambda function is written in python. The process is as follows:

  • Getting the current registered IPs using describe_target_health function.
  • Getting the current RDS Instance availability zone using describe_db_instances function.
  • Searching the current RDS private IP using describe_network_interfaces function.
  • If no Registry Target has already been assigned, we create a new one. If the current register target is old, we deregister it and create a new register target with the new RDS private IP address.

Create the file infra/plan/lambda/populate-nlb-tg-with-rds-private-ip.py

import json
import os
import random
import sys
import boto3
import logging

from datetime import datetime
from botocore.exceptions import ClientError

logger = logging.getLogger()
logger.setLevel(logging.INFO)

'''
This function populates a Network Load Balancer's target group with RDS IP addresses

Configure these environment variables in your Lambda environment

1. NLB_TG_ARN - The ARN of the Network Load Balancer's target group
2. RDS_PORT
3. RDS_SG_ID - RDS VPC Security Group Id
4. RDS_ID - RDS Identifier
'''

NLB_TG_ARN = os.environ['NLB_TG_ARN']
RDS_PORT = int(os.environ['RDS_PORT'])
RDS_SG_ID = os.environ['RDS_SG_ID']
RDS_ID = os.environ['RDS_ID']

try:
    elbv2client = boto3.client('elbv2')
except ClientError as e:
    logger.error(e.response['Error']['Message'])
    sys.exit(1)

try:
    rdsclient = boto3.client('rds')
except ClientError as e:
    logger.error(e.response['Error']['Message'])
    sys.exit(1)

try:
    ec2client = boto3.client('ec2')
except ClientError as e:
    logger.error(e.response['Error']['Message'])
    sys.exit(1)


def register_target(tg_arn, new_target_list):

    logger.info(f"INFO: Register new_target_list:{new_target_list}")

    try:
        elbv2client.register_targets(
            TargetGroupArn=tg_arn,
            Targets=new_target_list
        )
    except ClientError as e:
        logger.error(e.response['Error']['Message'])


def deregister_target(tg_arn, new_target_list):

    try:
        logger.info(f"INFO: Deregistering targets: {new_target_list}")
        elbv2client.deregister_targets(
            TargetGroupArn=tg_arn,
            Targets=new_target_list
        )
    except ClientError as e:
        logger.error(e.response['Error']['Message'])


def target_group_list(ip_list):

    target_list = []
    for ip in ip_list:
        target = {
            'Id': ip,
            'Port': RDS_PORT,
        }
        target_list.append(target)
    return target_list


def get_registered_ips(tg_arn):

    registered_ip_list = []
    try:
        response = elbv2client.describe_target_health(
            TargetGroupArn=tg_arn)
        registered_ip_count = len(response['TargetHealthDescriptions'])
        logger.info(f"INFO: Number of currently registered IP: {registered_ip_count}")
        for target in response['TargetHealthDescriptions']:
            registered_ip = target['Target']['Id']
            registered_ip_list.append(registered_ip)
    except ClientError as e:
        logger.error(e.response['Error']['Message'])
    return registered_ip_list


def get_rds_private_ips(rds_az):

    resp = ec2client.describe_network_interfaces(Filters=[{
        'Name': 'group-id',
        'Values': [RDS_SG_ID]
    }, {
        'Name': 'availability-zone',
        'Values': [rds_az]
    }])
    private_ip_address = []
    for interface in resp['NetworkInterfaces']:
        private_ip_address.append(interface['PrivateIpAddress'])
    return private_ip_address


def get_rds_az():

    logger.info(f"INFO: Get RDS current AZ: {RDS_ID}")
    az = None
    try:
        response = rdsclient.describe_db_instances(
            DBInstanceIdentifier=RDS_ID
        )
        if len(response['DBInstances']) > 0:
            az = response['DBInstances'][0]['AvailabilityZone']
            logger.info(f"INFO: RDS AZ is: {az}")

    except ClientError as e:
        logger.error(e.response['Error']['Message'])

    return az


def handler(event, context):

    registered_ip_list = get_registered_ips(NLB_TG_ARN)
    current_rds_az = get_rds_az()
    new_active_ip_set = get_rds_private_ips(current_rds_az)

    registration_ip_list = []
    # IPs that have not been registered
    if len(registered_ip_list) == 0 or registered_ip_list != new_active_ip_set:
        registration_ip_list = new_active_ip_set

    if registration_ip_list:
        registerTarget_list = target_group_list(registration_ip_list)
        register_target(NLB_TG_ARN, registerTarget_list)
        logger.info(f"INFO: Registering {registration_ip_list}")
    else:
        logger.info(f"INFO: No new target registered")

    deregistration_ip_list = []
    if registered_ip_list != new_active_ip_set:
        for ip in registered_ip_list:
            deregistration_ip_list.append(ip)
            logger.info(f"INFO: Deregistering IP: {ip}")
            deregisterTarget_list = target_group_list(deregistration_ip_list)
            deregister_target(NLB_TG_ARN, deregisterTarget_list)
    else:
        logger.info(f"INFO: No old target deregistered")
Enter fullscreen mode Exit fullscreen mode

Complete the file infra/plan/variable.tf:

variable "rds_port" {
  type    = number
  default = 5432
}
Enter fullscreen mode Exit fullscreen mode

Let's deploy our RDS instance

cd infra/envs/dev

terraform apply ../../plan/
Enter fullscreen mode Exit fullscreen mode

Before going to the next part, we will need to create the metabase database on Amazon RDS instance:

PGPASSWORD=$(terraform output rds-password) psql --host $(terraform output public-rds-endpoint) --port 5432 --user $(terraform output rds-username) --dbname postgres
CREATE USER metabase;
GRANT rds_iam TO metabase;
CREATE DATABASE metabase;
GRANT ALL ON DATABASE metabase TO metabase;
Enter fullscreen mode Exit fullscreen mode

Let's check if all the resources have been created and are working correctly

RDS Instance

Alt Text

VPC Security Group

Alt Text

Alt Text

Lambda

Alt Text

NLB Target Group

Alt Text

Conclusion

Our RDS instance is now available. In the next part, we'll establish a connection between a container deployed in Amazon EKS and a database created in an Amazon RDS instance.

Image
10 Tips For Asbestos Settlement That Are Unexpected
Image
URL Parts and Parameters in Javascript
Image
A Provocative Remark About Asbestos Compensation Payouts
Image
Mesothelioma Asbestos Claim: The Secret Life Of Mesothelioma Asbestos Claim
Image
Unveiling Spread Out AI: Automating Social Media Audience Targeting for Your Ads

ai, socialmedia, ads, webdev
Image
How to Stand Out in Your AWS Community Builders Application (And Re-Application)

aws, cloud, community, tips
Image
Introduction to Microsoft Entra ID

entraid, activedirectory, microsoft
Image
15 Secretly Funny People Work In How To Get Spare Car Key
Image
Fixing File Renaming Issues in Git: Handling Case Sensitivity and core.ignorecase

git, github, beginners, webdev
Image
Stylowe Meble do Sypialni: Jak Stworzyć Idealną Przestrzeń do Relaksu
Image
sedikit kehebohan bermain judi slots online
Image
What is a chicken? An introduction to distroless containers

dotnet, distroless, docker
Image
What To Look For To Determine If You're Ready To Electric Fireplace That Hangs On Wall
Image
Why We Do We Love Gas Boiler Engineer Near Me (And You Should Also!)
Image
Which are unique gifts for men for all the occasions?
Image
The History Of Asbestos Claims Payout
Image
Azure Network Security Groups (NSGs): Securing Your Azure Virtual Network

azure, activedirectory
Image
Cat Flap Installation - How to Choose and Install a Cat Flap
Image
Think You're Cut Out For Doing Lawsuit Asbestos? Check This Quiz
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .