databases.yaml

AWSTemplateFormatVersion: 2010-09-09
Description: |
  AWS Data Wrangler Development Databases Infrastructure Redshift, Aurora PostgreSQL, Aurora MySQL, Microsoft SQL Server
Parameters:
  DatabasesPassword:
    Type: String
    Description: Password for all databases
    NoEcho: true
Resources:
  RedshiftRole:
    Type: AWS::IAM::Role
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - redshift.amazonaws.com
            Action:
              - sts:AssumeRole
      Path: /
      Policies:
        - PolicyName: Root
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - kms:Encrypt
                  - kms:Decrypt
                  - kms:GenerateDataKey
                Resource:
                  - Fn::ImportValue: aws-data-wrangler-base-KmsKeyArn
              - Effect: Allow
                Action:
                  - s3:Get*
                  - s3:List*
                  - s3:Put*
                Resource:
                  - Fn::Sub:
                      - arn:aws:s3:::${Bucket}
                      - Bucket:
                          Fn::ImportValue: aws-data-wrangler-base-BucketName
                  - Fn::Sub:
                      - arn:aws:s3:::${Bucket}/*
                      - Bucket:
                          Fn::ImportValue: aws-data-wrangler-base-BucketName
              - Effect: Allow
                Action:
                  - lakeformation:GrantPermissions
                Resource: '*'
              - Effect: Allow
                Action:
                  - glue:SearchTables
                  - glue:GetConnections
                  - glue:GetDataCatalogEncryptionSettings
                  - glue:GetTables
                  - glue:GetTableVersions
                  - glue:GetPartitions
                  - glue:DeleteTableVersion
                  - glue:BatchGetPartition
                  - glue:GetDatabases
                  - glue:GetTags
                  - glue:GetTable
                  - glue:GetDatabase
                  - glue:GetPartition
                  - glue:GetTableVersion
                  - glue:GetConnection
                  - glue:GetUserDefinedFunction
                  - glue:GetUserDefinedFunctions
                Resource: '*'
  RedshiftSubnetGroup:
    Type: AWS::Redshift::ClusterSubnetGroup
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Description: AWS Data Wrangler Test Arena - Redshift Subnet Group
      SubnetIds:
        - Fn::ImportValue: aws-data-wrangler-base-PublicSubnet1
  DatabaseSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
        - Key: Name
          Value: aws-data-wrangler
      VpcId:
        Fn::ImportValue: aws-data-wrangler-base-VPC
      GroupDescription: AWS Data Wrangler Test Arena - Redshift security group
  DatabaseSecurityGroupIngress:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      Description: Self Referencing
      GroupId:
        Ref: DatabaseSecurityGroup
      IpProtocol: '-1'
      FromPort: 0
      ToPort: 65535
      SourceSecurityGroupId:
        Ref: DatabaseSecurityGroup
  Redshift:
    Type: AWS::Redshift::Cluster
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      DBName: test
      MasterUsername: test
      MasterUserPassword:
        Ref: DatabasesPassword
      NodeType: dc2.large
      ClusterType: single-node
      VpcSecurityGroupIds:
        - Ref: DatabaseSecurityGroup
      ClusterSubnetGroupName:
        Ref: RedshiftSubnetGroup
      PubliclyAccessible: true
      Port: 5439
      IamRoles:
        - Fn::GetAtt:
            - RedshiftRole
            - Arn
  RdsSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      DBSubnetGroupDescription: RDS Database Subnet Group
      SubnetIds:
        - Fn::ImportValue: aws-data-wrangler-base-PublicSubnet1
        - Fn::ImportValue: aws-data-wrangler-base-PublicSubnet2
  RdsRole:
    Type: AWS::IAM::Role
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - rds.amazonaws.com
            Action:
              - sts:AssumeRole
      Path: /
      Policies:
        - PolicyName: S3GetAndList
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - s3:Get*
                  - s3:List*
                  - s3:Put*
                Resource:
                  - Fn::Sub:
                      - arn:aws:s3:::${Bucket}
                      - Bucket:
                          Fn::ImportValue: aws-data-wrangler-base-BucketName
                  - Fn::Sub:
                      - arn:aws:s3:::${Bucket}/*
                      - Bucket:
                          Fn::ImportValue: aws-data-wrangler-base-BucketName
  PostgresqlParameterGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Description: Postgres 11
      Family: aurora-postgresql11
      Parameters:
        apg_plan_mgmt.capture_plan_baselines: 'off'
  AuroraClusterPostgresql:
    Type: AWS::RDS::DBCluster
    DeletionPolicy: Delete
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Engine: aurora-postgresql
      EngineVersion: '11.6'
      DBClusterIdentifier: postgresql-cluster-wrangler
      MasterUsername: test
      MasterUserPassword:
        Ref: DatabasesPassword
      BackupRetentionPeriod: 1
      DBSubnetGroupName:
        Ref: RdsSubnetGroup
      VpcSecurityGroupIds:
        - Ref: DatabaseSecurityGroup
      DBClusterParameterGroupName:
        Ref: PostgresqlParameterGroup
      AssociatedRoles:
        - FeatureName: s3Import
          RoleArn:
            Fn::GetAtt:
              - RdsRole
              - Arn
  AuroraInstancePostgresql:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Engine: aurora-postgresql
      EngineVersion: '11.6'
      DBInstanceIdentifier: postgresql-instance-wrangler
      DBClusterIdentifier:
        Ref: AuroraClusterPostgresql
      DBInstanceClass: db.t3.medium
      DBSubnetGroupName:
        Ref: RdsSubnetGroup
      PubliclyAccessible: true
  MysqlParameterGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Description: Mysql 5.7
      Family: aurora-mysql5.7
      Parameters:
        aurora_load_from_s3_role:
          Fn::GetAtt:
            - RdsRole
            - Arn
        aws_default_s3_role:
          Fn::GetAtt:
            - RdsRole
            - Arn
        aurora_select_into_s3_role:
          Fn::GetAtt:
            - RdsRole
            - Arn
  AuroraClusterMysql:
    Type: AWS::RDS::DBCluster
    DeletionPolicy: Delete
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Engine: aurora-mysql
      EngineVersion: '5.7'
      DBClusterIdentifier: mysql-cluster-wrangler
      MasterUsername: test
      MasterUserPassword:
        Ref: DatabasesPassword
      BackupRetentionPeriod: 1
      DBSubnetGroupName:
        Ref: RdsSubnetGroup
      VpcSecurityGroupIds:
        - Ref: DatabaseSecurityGroup
      DBClusterParameterGroupName:
        Ref: MysqlParameterGroup
      DatabaseName: test
      AssociatedRoles:
        - RoleArn:
            Fn::GetAtt:
              - RdsRole
              - Arn
  AuroraInstanceMysql:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Engine: aurora-mysql
      EngineVersion: '5.7'
      DBInstanceIdentifier: mysql-instance-wrangler
      DBClusterIdentifier:
        Ref: AuroraClusterMysql
      DBInstanceClass: db.t3.small
      DBSubnetGroupName:
        Ref: RdsSubnetGroup
      PubliclyAccessible: true
  SqlServerInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      Tags:
        - Key: Env
          Value: aws-data-wrangler
      Engine: sqlserver-ex
      EngineVersion: '15.00'
      DBInstanceIdentifier: sqlserver-instance-wrangler
      DBInstanceClass: db.t3.small
      AllocatedStorage: '20'
      MasterUsername: test
      MasterUserPassword:
        Ref: DatabasesPassword
      DBSubnetGroupName:
        Ref: RdsSubnetGroup
      VPCSecurityGroups:
        - Ref: DatabaseSecurityGroup
      PubliclyAccessible: true
      AssociatedRoles:
        - RoleArn:
            Fn::GetAtt:
              - RdsRole
              - Arn
          FeatureName: S3_INTEGRATION
  RedshiftGlueConnection:
    Type: AWS::Glue::Connection
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      ConnectionInput:
        Description: Connect to Redshift.
        ConnectionType: JDBC
        PhysicalConnectionRequirements:
          AvailabilityZone:
            Fn::Select:
              - 0
              - Fn::GetAZs: ''
          SecurityGroupIdList:
            - Ref: DatabaseSecurityGroup
          SubnetId:
            Fn::ImportValue: aws-data-wrangler-base-PrivateSubnet
        ConnectionProperties:
          JDBC_CONNECTION_URL:
            Fn::Sub: jdbc:redshift://${Redshift.Endpoint.Address}:${Redshift.Endpoint.Port}/test
          USERNAME: test
          PASSWORD:
            Ref: DatabasesPassword
        Name: aws-data-wrangler-redshift
  PostgresqlGlueConnection:
    Type: AWS::Glue::Connection
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      ConnectionInput:
        Description: Connect to Aurora (PostgreSQL).
        ConnectionType: JDBC
        PhysicalConnectionRequirements:
          AvailabilityZone:
            Fn::Select:
              - 0
              - Fn::GetAZs: ''
          SecurityGroupIdList:
            - Ref: DatabaseSecurityGroup
          SubnetId:
            Fn::ImportValue: aws-data-wrangler-base-PrivateSubnet
        ConnectionProperties:
          JDBC_CONNECTION_URL:
            Fn::Sub: jdbc:postgresql://${AuroraInstancePostgresql.Endpoint.Address}:${AuroraInstancePostgresql.Endpoint.Port}/postgres
          USERNAME: test
          PASSWORD:
            Ref: DatabasesPassword
        Name: aws-data-wrangler-postgresql
  MysqlGlueConnection:
    Type: AWS::Glue::Connection
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      ConnectionInput:
        Description: Connect to Aurora (MySQL).
        ConnectionType: JDBC
        PhysicalConnectionRequirements:
          AvailabilityZone:
            Fn::Select:
              - 0
              - Fn::GetAZs: ''
          SecurityGroupIdList:
            - Ref: DatabaseSecurityGroup
          SubnetId:
            Fn::ImportValue: aws-data-wrangler-base-PrivateSubnet
        ConnectionProperties:
          JDBC_CONNECTION_URL:
            Fn::Sub: jdbc:mysql://${AuroraInstanceMysql.Endpoint.Address}:${AuroraInstanceMysql.Endpoint.Port}/test
          USERNAME: test
          PASSWORD:
            Ref: DatabasesPassword
        Name: aws-data-wrangler-mysql
  MysqlGlueConnectionSSL:
    Type: AWS::Glue::Connection
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      ConnectionInput:
        Description: Connect to Aurora (MySQL) SSL enabled.
        ConnectionType: JDBC
        PhysicalConnectionRequirements:
          AvailabilityZone:
            Fn::Select:
              - 0
              - Fn::GetAZs: ''
          SecurityGroupIdList:
            - Ref: DatabaseSecurityGroup
          SubnetId:
            Fn::ImportValue: aws-data-wrangler-base-PrivateSubnet
        ConnectionProperties:
          JDBC_CONNECTION_URL:
            Fn::Sub: jdbc:mysql://${AuroraInstanceMysql.Endpoint.Address}:${AuroraInstanceMysql.Endpoint.Port}/test
          JDBC_ENFORCE_SSL: true
          CUSTOM_JDBC_CERT: s3://rds-downloads/rds-combined-ca-bundle.pem
          USERNAME: test
          PASSWORD:
            Ref: DatabasesPassword
        Name: aws-data-wrangler-mysql-ssl
  SqlServerGlueConnection:
    Type: AWS::Glue::Connection
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      ConnectionInput:
        Description: Connect to SQL Server.
        ConnectionType: JDBC
        PhysicalConnectionRequirements:
          AvailabilityZone:
            Fn::Select:
              - 0
              - Fn::GetAZs: ''
          SecurityGroupIdList:
            - Ref: DatabaseSecurityGroup
          SubnetId:
            Fn::ImportValue: aws-data-wrangler-base-PrivateSubnet
        ConnectionProperties:
          JDBC_CONNECTION_URL:
            Fn::Sub: jdbc:sqlserver://${SqlServerInstance.Endpoint.Address}:${SqlServerInstance.Endpoint.Port};databaseName=test
          USERNAME: test
          PASSWORD:
            Ref: DatabasesPassword
        Name: aws-data-wrangler-sqlserver
  GlueCatalogSettings:
    Type: AWS::Glue::DataCatalogEncryptionSettings
    Properties:
      CatalogId:
        Ref: AWS::AccountId
      DataCatalogEncryptionSettings:
        ConnectionPasswordEncryption:
          KmsKeyId:
            Fn::ImportValue: aws-data-wrangler-base-KmsKeyArn
          ReturnConnectionPasswordEncrypted: true
        EncryptionAtRest:
          CatalogEncryptionMode: DISABLED
  RedshiftSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: aws-data-wrangler/redshift
      Description: Redshift credentials
      SecretString:
        Fn::Sub: |
          {
            "username": "test",
            "password": "${DatabasesPassword}",
            "engine": "redshift",
            "host": "${Redshift.Endpoint.Address}",
            "port": ${Redshift.Endpoint.Port},
            "dbClusterIdentifier": "${Redshift}"
          }
      Tags:
        - Key: Env
          Value: aws-data-wrangler
  PostgresqlSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: aws-data-wrangler/postgresql
      Description: Postgresql credentials
      SecretString:
        Fn::Sub: |
          {
            "username": "test",
            "password": "${DatabasesPassword}",
            "engine": "postgresql",
            "host": "${AuroraInstancePostgresql.Endpoint.Address}",
            "port": ${AuroraInstancePostgresql.Endpoint.Port},
            "dbClusterIdentifier": "${AuroraInstancePostgresql}",
            "dbname": "postgres"
          }
      Tags:
        - Key: Env
          Value: aws-data-wrangler
  MysqlSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: aws-data-wrangler/mysql
      Description: Mysql credentials
      SecretString:
        Fn::Sub: |
          {
            "username": "test",
            "password": "${DatabasesPassword}",
            "engine": "mysql",
            "host": "${AuroraInstanceMysql.Endpoint.Address}",
            "port": ${AuroraInstanceMysql.Endpoint.Port},
            "dbClusterIdentifier": "${AuroraInstanceMysql}",
            "dbname": "test"
          }
      Tags:
        - Key: Env
          Value: aws-data-wrangler
  SqlServerSecret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: aws-data-wrangler/sqlserver
      Description: SQL Server credentials
      SecretString:
        Fn::Sub: |
          {
            "username": "test",
            "password": "${DatabasesPassword}",
            "engine": "sqlserver",
            "host": "${SqlServerInstance.Endpoint.Address}",
            "port": ${SqlServerInstance.Endpoint.Port},
            "dbClusterIdentifier": "${SqlServerInstance}",
            "dbname": "test"
          }
      Tags:
        - Key: Env
          Value: aws-data-wrangler
  DatabaseSecurityGroupId:
    Type: AWS::SSM::Parameter
    Properties:
      Type: String
      Description: Database Security Group Id
      Name: /Wrangler/EC2/DatabaseSecurityGroupId
      Value:
        Fn::GetAtt:
          - DatabaseSecurityGroup
          - GroupId
Outputs:
  DatabasesPassword:
    Value:
      Ref: DatabasesPassword
    Description: Password.
  RedshiftIdentifier:
    Value:
      Ref: Redshift
  RedshiftAddress:
    Value:
      Fn::GetAtt:
        - Redshift
        - Endpoint.Address
    Description: Redshift address.
  RedshiftPort:
    Value:
      Fn::GetAtt:
        - Redshift
        - Endpoint.Port
    Description: Redshift Endpoint Port.
  RedshiftRole:
    Value:
      Fn::GetAtt:
        - RedshiftRole
        - Arn
    Description: Redshift IAM role.
  PostgresqlAddress:
    Value:
      Fn::GetAtt:
        - AuroraInstancePostgresql
        - Endpoint.Address
    Description: Postgresql Address
  MysqlAddress:
    Value:
      Fn::GetAtt:
        - AuroraInstanceMysql
        - Endpoint.Address
    Description: Mysql Address
  SqlServerAddress:
    Value:
      Fn::GetAtt:
        - SqlServerInstance
        - Endpoint.Address
    Description: SQL Server Address
  DatabaseSecurityGroupId:
    Value:
      Fn::GetAtt:
        - DatabaseSecurityGroup
        - GroupId