[アップデート]Amazon Athenaで提供されるデータコネクタの内12種類がAWSマネージドになったのでPostgreSQLに接続してみました

AWSTemplateFormatVersion: 2010-09-09
Parameters:
  Env:
    Type: String
  Prefix:
    Type: String
  VpcId:
    Type: String
  SubnetGroupId:
    Type: String
  EndpointSubnetId:
    Type: List<String>
Resources:
  DBParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Family: postgres17
      Description: Enable connection logging
      Parameters:
        log_connections: "1"
        log_disconnections: "1"
  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      Engine: postgres
      EngineVersion: "17.9"
      MasterUsername: postgres
      AllocatedStorage: 20
      StorageType: gp3
      DBInstanceIdentifier: !Sub ${Env}-${Prefix}-db
      MasterUserPassword: !Sub "{{resolve:secretsmanager:${RDSMasterUserCredential}:SecretString:password::}}"
      AvailabilityZone: !Sub ${AWS::Region}a
      DBInstanceClass: db.t3.small
      AutoMinorVersionUpgrade: False
      EnablePerformanceInsights: True
      CACertificateIdentifier: "rds-ca-rsa4096-g1"
      DBSubnetGroupName: !Ref SubnetGroupId
      DBParameterGroupName: !Ref DBParameterGroup
      VPCSecurityGroups:
        - !Ref RdsSg
  RDSMasterUserCredential:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub ${Env}-${Prefix}-master-credential
      GenerateSecretString:
        SecretStringTemplate: '{"username": "postgres"}'
        GenerateStringKey: "password"
        PasswordLength: 16
        ExcludeCharacters: '"@/\'
  RdsSg:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: data-connector-rds-sg
      GroupDescription: String
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          SourceSecurityGroupId: !Ref DataConnectorSg
      VpcId: !Ref VpcId
  SpillBucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub ${Env}-${Prefix}-spill-bucket
      PublicAccessBlockConfiguration:
        BlockPublicAcls: true
        BlockPublicPolicy: true
        IgnorePublicAcls: true
        RestrictPublicBuckets: true
  DataConnectorSg:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: data-connector-client-sg
      GroupDescription: String
      VpcId: !Ref VpcId
  SecretsManagerEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.secretsmanager
      SubnetIds: !Ref EndpointSubnetId
      VpcEndpointType: Interface
      VpcId: !Ref VpcId
      PrivateDnsEnabled: true
      SecurityGroupIds:
        - !Ref EndpointSg
  STSEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.sts
      SubnetIds: !Ref EndpointSubnetId
      VpcEndpointType: Interface
      VpcId: !Ref VpcId
      PrivateDnsEnabled: true
      SecurityGroupIds:
        - !Ref EndpointSg
  GlueEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.glue
      SubnetIds: !Ref EndpointSubnetId
      VpcEndpointType: Interface
      VpcId: !Ref VpcId
      PrivateDnsEnabled: true
      SecurityGroupIds:
        - !Ref EndpointSg
  KMSEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.kms
      SubnetIds: !Ref EndpointSubnetId
      VpcEndpointType: Interface
      VpcId: !Ref VpcId
      PrivateDnsEnabled: true
      SecurityGroupIds:
        - !Ref EndpointSg
  S3Endpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      ServiceName: !Sub com.amazonaws.${AWS::Region}.s3
      SubnetIds: !Ref EndpointSubnetId
      VpcEndpointType: Interface
      VpcId: !Ref VpcId
      PrivateDnsEnabled: true
      DnsOptions:
        PrivateDnsOnlyForInboundResolverEndpoint: "AllResolvers"
      SecurityGroupIds:
        - !Ref EndpointSg
  EndpointSg:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: data-connector-ep-sg
      GroupDescription: for VPC Endpoint
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: 0.0.0.0/0
      VpcId: !Ref VpcId
  AthenaConnectorRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: !Sub ${Env}-${Prefix}-athena-connector-role
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - glue.amazonaws.com
                - lakeformation.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: AthenaConnectorPolicy
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - glue:ManagedConnector
                  - glue:GetConnection
                  - lakeformation:GetDataAccess
                  - ec2:CreateNetworkInterface
                  - ec2:DeleteNetworkInterface
                  - ec2:DescribeNetworkInterfaces
                  - ec2:DescribeSubnets
                  - ec2:DescribeSecurityGroups
                  - ec2:DescribeVpcs
                Resource: "*"
              - Effect: Allow
                Action:
                  - secretsmanager:DescribeSecret
                  - secretsmanager:GetSecretValue
                  - secretsmanager:PutSecretValue
                Resource: !Ref RDSMasterUserCredential
              - Effect: Allow
                Action:
                  - s3:GetObject
                  - s3:PutObject
                  - s3:DeleteObject
                Resource: !Sub arn:aws:s3:::${SpillBucket}/*
              - Effect: Allow
                Action:
                  - s3:ListBucket
                  - s3:GetBucketLocation
                Resource: !GetAtt SpillBucket.Arn

{
      "Version": "2012-10-17",
      "Statement": [
          {
              "Action": [
                  "glue:ManagedConnector",
                  "glue:GetConnection",
                  "lakeformation:GetDataAccess",
                  "ec2:CreateNetworkInterface",
                  "ec2:DeleteNetworkInterface",
                  "ec2:DescribeNetworkInterfaces",
                  "ec2:DescribeSubnets",
                  "ec2:DescribeSecurityGroups",
                  "ec2:DescribeVpcs"
              ],
              "Resource": "*",
              "Effect": "Allow"
          },
          {
              "Action": [
                  "secretsmanager:DescribeSecret",
                  "secretsmanager:GetSecretValue",
                  "secretsmanager:PutSecretValue"
              ],
              "Resource": "arn:aws:secretsmanager:ap-northeast-1:xxxxx:secret:dev-postgres-connector-master-credential-xxxxx",
              "Effect": "Allow"
          },
          {
              "Action": [
                  "s3:GetObject",
                  "s3:PutObject",
                  "s3:DeleteObject"
              ],
              "Resource": "arn:aws:s3:::dev-postgres-connector-spill-bucket-xxxxx/*",
              "Effect": "Allow"
          },
          {
              "Action": [
                  "s3:ListBucket",
                  "s3:GetBucketLocation"
              ],
              "Resource": "arn:aws:s3:::dev-postgres-connector-spill-bucket-xxxxx",
              "Effect": "Allow"
          }
      ]
  }

  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": {
          "Service": [
            "glue.amazonaws.com",
            "lakeformation.amazonaws.com"
          ]
        },
        "Action": "sts:AssumeRole"
      }
    ]
  }

## 初回作成時に発生
Insufficient Lake Formation permission(s) on arn:aws:glue:ap-northeast-1:xxxxx:connection/athena-postgres-test

## ↑でエラーが発生してConnectionのみ削除した場合に発生、CloudTrailから作成はできていたので削除
Resource is already registered