Lambda RDS Demo CloudFormation Template

lambda-rds-demo-cfn.yml

AWSTemplateFormatVersion: "2010-09-09"

Description: Lambda RDS Demo CloudFormation Template

Metadata: 
  AWS::CloudFormation::Interface: 
    ParameterGroups:
      - Label:
          default: RDS Parameters
        Parameters:
          - DBInstanceId
          - DBName
          - DBMasterUsername
          - DBMasterPassword
          - DBInstanceClass
          - DBStorageSize
          - DBStorageType
          - DBEngineVersion
          - DBPort
          - VpcId
          - SubnetIds
          - AvailabilityZone
          - EnableStorageEncryption
          - EnableMultiAZ
          - EnablePublicAccess
      - Label:
          default: Security Group Parameters
        Parameters:
          - SGProtocol
          - SGFromPort
          - SGToPort
          - SGCidrIp
      - Label:
          default: Lambda Parameters
        Parameters:
          - SchemaLoadFunctionName
          - Architectures
          - LambdaVPCAccessExecRoleName

Parameters:
  DBInstanceId:
    Type: String
    MinLength: '1'
    MaxLength: '63'
    Description: Enter a string for the Database Instance ID.
    Default: my-db-instance
  DBName:
    Type: String
    MinLength: '1'
    MaxLength: '63'
    Description: Enter a string for the Database Instance Name. Must begin with a letter. Subsequent characters can be letters, underscores, or digits (0-9).
    Default: mydatabase
  DBMasterUsername:
    Type: String
    MinLength: '1'
    MaxLength: '16'
    Description: Enter a string for the Database Master Username.
    Default: root
  DBMasterPassword:
    NoEcho: 'true'
    Type: String
    MinLength: '8'
    MaxLength: '41'
    Description: Enter a string for the Database Master Password.
    Default: BadDbPassword
  DBInstanceClass:
    Type: String
    AllowedValues: [db.t3.micro,db.t4g.micro]
    Default: db.t3.micro
  DBStorageSize:
    Type: String
    Default: 20
  DBStorageType:
    Type: String
    Default: gp2
  DBEngineVersion:
    Type: String
    AllowedValues: [14.5,14.6,14.7,15.2]
    Default: 14.6
  DBPort:
    Type: String
    Default: 5432
  VpcId:
    Type: AWS::EC2::VPC::Id
    Description: Select an existing VPC
  SubnetIds:
    Type: List<AWS::EC2::Subnet::Id>
    Description: Select at least two existing subnet(s) for RDS.
  AvailabilityZone:
    Type: AWS::EC2::AvailabilityZone::Name
    Description: Select an Availability Zone.
  EnableStorageEncryption:
    Type: String
    Description: Boolean to enable storage encryption.
    AllowedValues: [true,false]
    Default: false
  EnableMultiAZ:
    Type: String
    Description: Boolean to create a multi-AZ Amazon RDS database instance.
    AllowedValues: [true,false]
    Default: false
  EnablePublicAccess:
    Type: String
    Description: Boolean to enable public access.
    AllowedValues: [true,false]
    Default: true

  SGProtocol:
    Type: String
    Default: tcp
  SGFromPort:
    Type: Number
    Default: 5432
  SGToPort:
    Type: Number
    Default: 5432
  SGCidrIp:
    Type: String
    Default: 1.1.1.1/32

  SchemaLoadFunctionName:
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9_-]*'
    Description: Lambda schema load function name.
    Default: lambda-schema-load
  Architectures:
    Type: String
    Description: arm64 or x86_64.
    AllowedValues: [arm64,x86_64]
    Default: x86_64
  LambdaVPCAccessExecRoleName:
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[\w+=,.@-]+'
    Description: New IAM role name for Lambda execution.
    Default: LambdaVpcExecutionRole

Conditions:
  CreateMultiAZ: !Equals [!Ref EnableMultiAZ, true]

Resources:
  LambdaEC2SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: EC2 Security Group for Lambda.
      VpcId: !Ref VpcId

  DBEC2SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow incoming traffic to PostgreSQL.
      VpcId: !Ref VpcId
      SecurityGroupIngress:
        - IpProtocol: !Ref SGProtocol
          FromPort: !Ref SGFromPort
          ToPort: !Ref SGToPort
          CidrIp: !Ref SGCidrIp
        - IpProtocol: !Ref SGProtocol
          FromPort: !Ref SGFromPort
          ToPort: !Ref SGToPort
          SourceSecurityGroupId: !GetAtt LambdaEC2SecurityGroup.GroupId

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties: 
      DBSubnetGroupDescription: !Sub "DB Subnet group in ${AWS::Region}"
      DBSubnetGroupName: !Ref DBInstanceId
      SubnetIds: !Ref SubnetIds

  RDSInstance:
    Type: AWS::RDS::DBInstance # https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbinstance.html
    Properties:
      DBInstanceIdentifier: !Ref DBInstanceId
      DBName: !Ref DBName
      DBInstanceClass: !Ref DBInstanceClass
      Engine: postgres
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterPassword
      Port: !Ref DBPort
      StorageType: !Ref DBStorageType
      AllocatedStorage: !Ref DBStorageSize
      StorageEncrypted: !Ref EnableStorageEncryption
      BackupRetentionPeriod: 0
      DBSubnetGroupName: !Ref DBSubnetGroup
      MultiAZ: !Ref EnableMultiAZ
      AvailabilityZone: !If [CreateMultiAZ, !Ref 'AWS::NoValue', !Ref AvailabilityZone]
      PubliclyAccessible: !Ref EnablePublicAccess
      VPCSecurityGroups: [!GetAtt DBEC2SecurityGroup.GroupId]
    DeletionPolicy: Delete    # https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-attribute-deletionpolicy.html
    
  LambdaVPCAccessExecRole:
    Type: AWS::IAM::Role # https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-iam-role.html
    Properties:
      RoleName: !Ref LambdaVPCAccessExecRoleName
      Description: Lambda execution role
      ManagedPolicyArns: [arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole]
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
        - Effect: Allow
          Principal:
            Service: lambda.amazonaws.com
          Action: [sts:AssumeRole]

  LambdaSchemaLoadFunction:
    Type: AWS::Lambda::Function # https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-lambda-function.html
    Properties:
      FunctionName: !Ref SchemaLoadFunctionName
      Architectures: [!Ref Architectures]
      Runtime: python3.8
      Handler: index.lambda_handler
      MemorySize: 128
      Timeout: 3
      TracingConfig:
        Mode: "PassThrough"
      Role: !GetAtt LambdaVPCAccessExecRole.Arn
      Environment:
        Variables:
          CONNECTION_URI: !Join [ "", ["postgresql://", !Ref DBMasterUsername, ":", !Ref DBMasterPassword, "@", !GetAtt RDSInstance.Endpoint.Address, ":", !Ref DBPort, "/", !Ref DBName]] 
      VpcConfig:
        SecurityGroupIds: [!GetAtt LambdaEC2SecurityGroup.GroupId]
        SubnetIds: !Ref SubnetIds
      Code:
        ZipFile: |
            # import cfnresponse
            import json
            import psycopg2
            import os

            def lambda_handler(event, context):
                # Get the properties from the event
                # properties  = event['ResourceProperties']
                # resource_id = event['LogicalResourceId']

                # Create a connection to the database
                conn = psycopg2.connect(os.getenv('CONNECTION_URI'))

                try:
                  with conn.cursor() as cur:
                      cur.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
                      cur.execute('DROP TABLE IF EXISTS public.users;')
                      cur.execute('DROP TABLE IF EXISTS public.activities;')
                      cur.execute("""
                          CREATE TABLE public.users (
                            uuid UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
                            display_name text NOT NULL,
                            handle text NOT NULL,
                            email text NOT NULL,
                            cognito_user_id text NOT NULL,
                            created_at TIMESTAMP default current_timestamp NOT NULL
                          );
                      """)
                      cur.execute("""
                          CREATE TABLE public.activities (
                            uuid UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
                            user_uuid UUID NOT NULL,
                            message text NOT NULL,
                            replies_count integer DEFAULT 0,
                            reposts_count integer DEFAULT 0,
                            likes_count integer DEFAULT 0,
                            reply_to_activity_uuid integer,
                            expires_at TIMESTAMP,
                            created_at TIMESTAMP default current_timestamp NOT NULL
                          );
                      """)

                  # Commit the transaction
                  conn.commit() 

                  # Send the success response to CloudFormation
                  # response_data = {'message': 'Table created successfully'}
                  # cfnresponse.send(event, context, cfnresponse.SUCCESS, response_data)

                except (Exception, psycopg2.DatabaseError) as error:
                  print(error)

                  # Roll back the transaction
                  conn.rollback()

                  # Send the failure response to CloudFormation
                  # response_data = {'error': str(error)}
                  # cfnresponse.send(event, context, cfnresponse.FAILED, response_data)

                finally:
                  if conn is not None:
                      cur.close()
                      conn.close()
                      print('Database connection closed.')
                return event
      Layers: 
        - !Sub arn:aws:lambda:${AWS::Region}:898466741470:layer:psycopg2-py38:1

  # need to update python code to handle cfnresponse properly
  # InvokeLambdaSchemaLoad:
  #   Type: AWS::CloudFormation::CustomResource
  #   Properties:
  #     ServiceToken: !GetAtt LambdaSchemaLoadFunction.Arn
  #     Endpoint: !GetAtt RDSInstance.Endpoint.Address 

Outputs:
  DatabaseSecurityGroupId:
    Value: !Ref DBEC2SecurityGroup
    Description: Security Group name.
  DatabaseInstanceId:
    Value: !Ref RDSInstance
    Description: Database InstanceId.
  DatabaseName:
    Value: !Ref DBName
    Description: Database Name.
  DatabaseMasterUsername:
    Value: !Ref DBMasterUsername
    Description: Database master username.
  DatabaseMasterPassword:
    Value: !Ref DBMasterPassword
    Description: Database master password.
  DatabasePort:
    Description: Database port
    Value: !Ref DBPort
  DatabaseConnectionURI:
    Description: Database connection URI.
    Value: !Join [ "", ["postgresql://", !Ref DBMasterUsername, ":", !Ref DBMasterPassword, "@", !GetAtt RDSInstance.Endpoint.Address, ":", !Ref DBPort, "/", !Ref DBName]]
    Export:
      Name: !Sub "${AWS::StackName}-prod-connection-uri"

  LambdaSchemaLoadFunctionArn:
    Description: ARN of Lambda schema-load function
    Value: !GetAtt LambdaSchemaLoadFunction.Arn
    Export:
      Name: !Sub "${AWS::StackName}-${SchemaLoadFunctionName}"
  CLIInvokeLambdaSchemaLoadFunction:
    Description: aws cli to invoke lambda schema-load function
    Value: !Sub "aws lambda invoke --function-name ${SchemaLoadFunctionName} response.json --region ${AWS::Region}"
    Export:
      Name: !Sub "${AWS::StackName}-aws-cli-db-schema-load"