AWSTemplateFormatVersion: '2010-09-09'
Description: 'This template deploys the 3decision oracle database from an existing public snapshot (qs-1snm79ff4).'
Metadata:
  cfn-lint: { config: { ignore_checks: [W9002, W9003, W9006] } }
  LintSpellExclude:
    - Discngine
    - 3decision
Parameters:
  ORAVersion:
    AllowedValues:
      - Standard-Edition-Two-19.0-License-Included
    Default: Standard-Edition-Two-19.0-License-Included
    Description: Amazon RDS Oracle version
    Type: String
  DBInstanceClass:
    Description: Amazon RDS instance type for the Oracle Database instance
    Type: String
  DBInstanceIdentifier:
    Default: db3dec
    Description: The database instance name
    Type: String
    MinLength: '1'
    MaxLength: '8'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.
  DBName:
    Default: ORCL
    Description: The database name
    Type: String
    MinLength: '1'
    MaxLength: '8'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.
  DBPort:
    Default: 1521
    Description: Oracle Database listener port number.
    Type: Number
  DBMultiZone:
    Description: High Availability (Multi-AZ) for Oracle RDS.
        More informtion is available here - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html
    Type: String
    AllowedValues: [ 'true', 'false' ]
    Default: 'true'
  DBSubnets:
    Description: 'Required: Subnets to use for the creation of the db subnet group
                  Defaults to the private subnets created in the VPC'
    Type: CommaDelimitedList
  VpcId:
    Description: 'Required: Id of the VPC'
    Type: String
  NodeSecurityGroupId:
    Description: "Optionnal: Id of the EKS SecurityGroup in which we will add the RDS ingress"
    Default: ''
    Type: String
  DBSnapshotIdentifier:
    Description: "Optionnal: the snapshot arn used for the database.
                  If left empty, the default snapshot will be used. 
                  But if you wish to have an encrypted database, you will have to copy the public snapshot locally and select encryption and use that arn"
    Type: String
Mappings:
  OracleDetailsMap:
    Standard-Edition-Two-19.0-License-Included:
      Version: 19.0.0.0.ru-2022-01.rur-2022-01.r1
      Engine: oracle-se2-cdb
      Family: oracle-se2-cdb-19
      License: license-included
      Option: default:oracle-se2-cdb-19
Resources:
  EncryptionKey:
    Metadata:
      cfn-lint:
        config:
          ignore_checks:
            - EIAMPolicyActionWildcard
          ignore_reasons:
            EIAMPolicyActionWildcard: The first statement is the default key policy allowing administrators to administer the CMK. See https://docs.aws.amazon.com/kms/latest/developerguide/key-policies.html#key-policy-default-allow-root-enable-iam.
    UpdateReplacePolicy: Retain
    DeletionPolicy: Delete
    Type: AWS::KMS::Key
    Properties:
      EnableKeyRotation: true
      KeyPolicy:
        Version: 2012-10-17
        Id: !Ref AWS::StackName
        Statement:
          - Effect: Allow
            Principal:
              AWS:
                - !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:root"
            Action: 'kms:*'
            Resource: '*'
          - Effect: Allow
            Principal:
              AWS: '*'
            Action:
              - kms:Encrypt
              - kms:Decrypt
              - kms:ReEncryptTo
              - kms:ReEncryptFrom
              - kms:GenerateDataKeyWithoutPlaintext
              - kms:GenerateDataKey
              - kms:GenerateDataKeyPairWithoutPlaintext
              - kms:GenerateDataKeyPair
              - kms:CreateGrant
              - kms:ListGrants
              - kms:DescribeKey
            Resource: '*'
            Condition:
              StringEquals:
                kms:CallerAccount: !Ref AWS::AccountId
                kms:ViaService: !Sub "rds.${AWS::Region}.${AWS::URLSuffix}"
      Tags:
        - Key: Name
          Value: !Ref AWS::StackName
  EncryptionKeyAlias:
    Type: AWS::KMS::Alias
    Properties:
      AliasName: !Sub "alias/${AWS::StackName}"
      TargetKeyId: !Ref EncryptionKey
  SubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: 'private subnets group'
      SubnetIds: !Ref DBSubnets
  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: "Opens incoming requests on Oracle RDS port 1521"
      VpcId: !Ref VpcId
      SecurityGroupIngress:
      - SourceSecurityGroupId: !Ref NodeSecurityGroupId
        IpProtocol: TCP
        FromPort: 1521
        ToPort: 1521
  AddDBToSecurityEKS:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      IpProtocol: "-1"
      SourceSecurityGroupId: !GetAtt DBSecurityGroup.GroupId
      GroupId: !Ref NodeSecurityGroupId
  PrimaryDBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: "1024"
      MaxAllocatedStorage: 3072
      CharacterSetName: AL32UTF8
      DBInstanceClass: !Ref DBInstanceClass
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      DBName: !Ref DBName
      DBParameterGroupName: !Ref DBParamGroup
      Engine: !FindInMap [OracleDetailsMap, !Ref ORAVersion, Engine]
      EngineVersion: !FindInMap [OracleDetailsMap, !Ref ORAVersion, Version]
      LicenseModel: !FindInMap [OracleDetailsMap, !Ref ORAVersion, License]
      OptionGroupName: !FindInMap [OracleDetailsMap, !Ref ORAVersion, Option]
      Port: !Ref DBPort
      MultiAZ: !Ref DBMultiZone
      StorageEncrypted: true
      KmsKeyId: !GetAtt EncryptionKey.Arn
      DBSubnetGroupName: !Ref SubnetGroup
      VPCSecurityGroups: [ !GetAtt DBSecurityGroup.GroupId ]
      StorageType: gp2
      DBSnapshotIdentifier: !Ref DBSnapshotIdentifier
      PubliclyAccessible: false
  DBParamGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: Oracle Database Parameter Group
      Family: !FindInMap [OracleDetailsMap, !Ref ORAVersion, Family]
      Parameters:
        processes: 'GREATEST({DBInstanceClassMemory/9868951}, 1000)'
      Tags:
        - Key: Name
          Value: "DBParamGroup"
Outputs:
  DbInstanceName:
    Description: Identifier of the db instance
    Value: !Ref PrimaryDBInstance
  PrimaryEndpointAddress:
    Description: Endpoint address for the primary database
    Value: !GetAtt 'PrimaryDBInstance.Endpoint.Address'
  PrimaryEndpointPort:
    Description: Endpoint port for the primary database
    Value: !GetAtt 'PrimaryDBInstance.Endpoint.Port'
  PrimaryConnectionString:
    Description: JDBC connection string for the primary database
    Value: !Join
      - ''
      - - !GetAtt
          - PrimaryDBInstance
          - Endpoint.Address
        - ':'
        - !GetAtt
          - PrimaryDBInstance
          - Endpoint.Port
        - '/'
        - !Ref DBName
  PrimaryHostString:
    Description: JDBC Host string for the primary database
    Value: !Join
      - ''
      - - !GetAtt
          - PrimaryDBInstance
          - Endpoint.Address
        - ':'
        - !GetAtt
          - PrimaryDBInstance
          - Endpoint.Port
        - '/'
  PrimaryDBName:
    Description: JDBC Service Name for the primary database
    Value: !Ref DBName
  DBSecurityGroup:
    Description: DB Security Group id
    Value: !Ref DBSecurityGroup