AWSTemplateFormatVersion: '2010-09-09'
Description: "Cloudformation template to launch AWS Aurora PostgreSQL Cluster"
Metadata:
  LICENSE: Apache License Version 2.0
  AWS::CloudFormation::Interface:
    ParameterGroups:
    - Label:
        default: Source Database configuration
      Parameters:
      - DBClusterName
      - DBName
      - DBInstanceClass
      - DBMasterUsername
      - DBMasterUserPassword
      - DBEngineVersion
    ParameterLabels:
      DBClusterName:
        default: Source Database Cluster name
      DBName:
        default: Source Database name
      DBInstanceClass:
        default: Source Database instance class
      DBMasterUsername:
        default: Source Database master username
      DBMasterUserPassword: 
        default: Source Database master password
      DBEngineVersion:
        default: Source Database Engine Version
Outputs: 
  ClusterName:
    Value: !Ref DBClusterName
  StackName:
    Value: !Ref 'AWS::StackName'
  Regionname:
    Value: !Ref 'AWS::Region'
  VPCid:
    Value: !GetAtt 
      - PubSubnet1
      - VpcId
  Cloud9Env:
    Value: !Join 
      - ''
      - - 'https://'
        - !Ref 'AWS::Region'
        - .console.aws.amazon.com/cloud9/home/
        - '?region='
        - !Ref 'AWS::Region'
  SubnetID1: 
    Value: !Ref DBSubnet1
  SubnetID2: 
    Value: !Ref DBSubnet2
  RDSSecurityGrp:
    Value: !GetAtt 
      - AuroraVPCSecurityGroup
      - GroupId
  DBName: 
    Description: "Amazon Aurora database name"
    Value: !Ref DBName
  RDSEndPoint: 
    Description: "Full Amazon Aurora endpoint"
    Value: !Sub ${AuroraDBCluster.Endpoint.Address}:${AuroraDBCluster.Endpoint.Port}/${DBName}
Mappings:
  AuroraVersion:
   "11.11":
     DBClustergroup: aurora-postgresql11
   "12.6":
     DBClustergroup: aurora-postgresql12
Parameters: 
  DBClusterName:
    Default: "aurora-db"
    Description: Specify database cluster name.
    Type: String 
  DBEngineVersion:
    Default: 12.6
    AllowedValues: 
      - "11.11"
      - "12.6"
    Description: "Specify Aurora PostgreSQL database engine version."
    Type: String
  DBInstanceClass: 
    AllowedValues: 
      - db.t3.medium
      - db.r5.large
      - db.r5.xlarge
      - db.r5.2xlarge
      - db.r5.4xlarge
    ConstraintDescription: "Must select a valid database instance type."
    Default: db.r5.large
    Description: "The name of the compute and memory capacity class of the database instance."
    Type: String
  DBMasterUserPassword: 
    Default: auradmin
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: "Min 8 chars. Must include 1 uppercase, 1 lowercase, 1 number, 1 (non / @ \" ') symbol"
    Description: "The database admin account password."
    MaxLength: "64"
    MinLength: "8"
    NoEcho: "True"
    Type: String
  DBMasterUsername: 
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: "Must begin with a letter and contain only alphanumeric characters."
    Default: pgadmin
    Description: "The database admin account username."
    MaxLength: "16"
    MinLength: "1"
    Type: String
  DBName: 
    AllowedPattern: "[a-zA-Z0-9]*"
    Description: "Name of the Amazon Aurora database."
    MaxLength: "64"
    MinLength: "0"
    Default: 'demo'
    Type: String
Resources:
  VPC:
    Type: 'AWS::EC2::VPC'
    Properties:
      CidrBlock: 10.0.0.0/20
      EnableDnsSupport: 'true'
      EnableDnsHostnames: 'true'
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
        - Key: Name
          Value: !Ref 'AWS::StackName'
  DBSubnet1:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.3.0/25
      AvailabilityZone: !Select 
        - '0'
        - !GetAZs ''
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
  DBSubnet2:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.4.0/25
      AvailabilityZone: !Select 
        - '1'
        - !GetAZs ''
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
  PubSubnet1:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.1.0/25
      AvailabilityZone: !Select 
        - '0'
        - !GetAZs ''
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
  PubSubnet2:
    Type: 'AWS::EC2::Subnet'
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.2.0/25
      AvailabilityZone: !Select 
        - '1'
        - !GetAZs ''
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'

  InternetGateway:
    Type: 'AWS::EC2::InternetGateway'
    Properties:
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
  AttachGateway:
    Type: 'AWS::EC2::VPCGatewayAttachment'
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway
  RouteTable:
    Type: 'AWS::EC2::RouteTable'
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Application
          Value: !Ref 'AWS::StackId'
  Route:
    Type: 'AWS::EC2::Route'
    DependsOn: AttachGateway
    Properties:
      RouteTableId: !Ref RouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway
  SubnetRouteTableAssociation:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref PubSubnet1
      RouteTableId: !Ref RouteTable
  SubnetRouteTableAssociation1:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref PubSubnet2
      RouteTableId: !Ref RouteTable
      
  MyprivateRouteTable:
    Type: 'AWS::EC2::RouteTable'
    Properties:
      VpcId: !Ref VPC

  privateSubnet1RouteTableAssociation:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref DBSubnet1
      RouteTableId: !Ref MyprivateRouteTable
  privateSubnet2RouteTableAssociation:
    Type: 'AWS::EC2::SubnetRouteTableAssociation'
    Properties:
      SubnetId: !Ref DBSubnet2
      RouteTableId: !Ref MyprivateRouteTable
  AuroraVPCSecurityGroup:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      GroupDescription: Security group for Aurora PostgreSQL Instances.
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: '5432'
          ToPort: '5432'
          CidrIp: 10.0.0.0/20       
  EncryptionKey:
    DeletionPolicy: Retain
    Type: AWS::KMS::Key
    Properties:
      KeyPolicy:
        Version: 2012-10-17
        Id: !Ref AWS::StackName
        Statement:
          - Effect: Allow
            Principal:
              AWS:
                - !Sub "arn:aws:iam::${AWS::AccountId}:root"
            Action: 'kms:*'
            Resource: '*'
      Tags:
        - Key: Name
          Value: !Ref AWS::StackName
  EncryptionKeyAlias:
    Type: AWS::KMS::Alias
    Properties:
      AliasName: !Sub "alias/${AWS::StackName}"
      TargetKeyId: !Ref EncryptionKey

  RDSDBClusterParameterGroup: 
   Properties: 
     Description: "Aurora PostgreSQL Custom Cluster parameter group"
     Family: !FindInMap [AuroraVersion, !Ref DBEngineVersion, DBClustergroup]
     Parameters: 
      shared_preload_libraries: 'auto_explain, pgaudit, pg_stat_statements, pg_hint_plan'
      log_connections: 1
      log_disconnections: 1
      log_lock_waits: 1
      log_temp_files: 10240
      autovacuum_vacuum_cost_delay: 10
      autovacuum_vacuum_cost_limit: 1000
      log_min_duration_statement: 5000
   Type: "AWS::RDS::DBClusterParameterGroup"


  RDSDBParameterGroup: 
   Properties: 
    Description: "Aurora PostgreSQL Custom DB parameter group"
    Family: !FindInMap [AuroraVersion, !Ref DBEngineVersion, DBClustergroup]
    Parameters: 
      shared_preload_libraries: 'auto_explain, pgaudit, pg_stat_statements, pg_hint_plan'
      log_connections: 1
      log_disconnections: 1
      log_lock_waits: 1
      log_temp_files: 10240
      log_min_duration_statement: 5000
   Type: "AWS::RDS::DBParameterGroup"


  AuroraDBSubnetGroup: 
    Properties: 
      DBSubnetGroupDescription: "Subnets available for the Amazon Aurora database instance"
      SubnetIds: 
       - !Ref DBSubnet1
       - !Ref DBSubnet2
    Type: "AWS::RDS::DBSubnetGroup"

  AuroraDBCluster: 
    Properties: 
      BackupRetentionPeriod: 7
      DBClusterParameterGroupName: !Ref RDSDBClusterParameterGroup 
      DBClusterIdentifier: !Ref DBClusterName
      DBSubnetGroupName: !Ref AuroraDBSubnetGroup
      DatabaseName: !Ref DBName
      Engine: aurora-postgresql
      EngineVersion: !Ref DBEngineVersion
      KmsKeyId: !GetAtt EncryptionKey.Arn
      MasterUserPassword: !Ref DBMasterUserPassword
      MasterUsername: !Ref DBMasterUsername
      Port: 5432
      StorageEncrypted: true
      VpcSecurityGroupIds: 
        - !Ref AuroraVPCSecurityGroup
    Type: "AWS::RDS::DBCluster"
  AuroraDB:
    Properties:
      AutoMinorVersionUpgrade: true
      DBClusterIdentifier: !Ref AuroraDBCluster
      DBInstanceClass: !Ref DBInstanceClass
      DBParameterGroupName: !Ref RDSDBParameterGroup
      Engine: aurora-postgresql
      PubliclyAccessible: false
    Type: "AWS::RDS::DBInstance"

  Cloud9DevIAMRole:
    Type: 'AWS::IAM::Role'
    Properties:
      ManagedPolicyArns:
        - 'arn:aws:iam::aws:policy/SecretsManagerReadWrite'
        - 'arn:aws:iam::aws:policy/AWSCloudFormationFullAccess'
        - 'arn:aws:iam::aws:policy/AmazonRDSFullAccess'
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service: ec2.amazonaws.com
            Action: 'sts:AssumeRole'
      Path: /
  Cloud9Environment:
    Type: 'AWS::Cloud9::EnvironmentEC2'
    Properties:
      ImageId: amazonlinux-2-x86_64
      AutomaticStopTimeMinutes: 60
      InstanceType: t3.medium
      Name: !Sub 'Project-${AWS::StackName}'
      SubnetId: !Ref PubSubnet1