<commit_before>- hosts: localhost
  connection: local
  gather_facts: False
  vars:
    instances:
      - aact-dev
      - aact-prod

  tasks:
    - name: create S3 instances
      s3_bucket:
        name: "{{ item }}"
        policy:
          Version: "2012-10-17"
          Statement:
            -
              Sid: "Stmt1470845581678"
              Effect: "Allow"
              Principal: "*"
              Action:
                - "s3:GetBucketLocation"
                - "s3:ListBucket"
              Resource: "arn:aws:s3:::{{ item }}"
            -
              Sid: "Stmt1470845605315"
              Effect: "Allow"
              Principal: "*"
              Action: "s3:GetObject"
              Resource: "arn:aws:s3:::{{ item }}/*"
      with_items: "{{ instances }}"

    - name: create agent IAM accounts
      iam:
        iam_type: user
        name: "{{ item }}-agent"
        state: present
      with_items: "{{ instances }}"

    - name: "create {{ item }}-policy"
      iam_policy:
        state: present
        iam_name: "{{ item }}-agent"
        iam_type: user
        policy_name: "{{ item }}-agent-policy"
        policy_document: "{{ item }}-agent-policy.json"
      with_items: "{{ instances }}"

    - name: Create Security Groups to manage access to rds
      ec2_group:
        name: "postgres-{{ item }}-public"
        description: "PostgreSQL public (internet) access to {{ item }} "
        region: us-east-1
        rules:
          - proto: tcp
            from_port: 5432
            to_port: 5432
            cidr_ip: 0.0.0.0/0
        rules_egress:
          - proto: all
            from_port: All
            cidr_ip: 0.0.0.0/0
        state: present
      register: "rdsgroup"
      with_items: "{{ instances }}"

    - name: Provision RDS Postgres Instances
          # https://github.com/ansible/ansible-modules-core/issues/633
          # rds module will not support storage_type SSD until it is refactored
          # to use boto3
          # rds:
          #   command: create
          #   instance_name: "{{ item.item }}"
          #   instance_type: db.t2.medium
          #   vpc_security_groups: "{{ item.group_id }}"
          #   subnet: default
          #   size: 20
          #   db_engine: postgres
          #   db_name: aact
          #   username: dbadmin
          #   password: "{{ lookup('password', 'rds.' + item.item + '.secret') }}"
          #   publicly_accessible: yes
          #   region: us-east-1
          #   zone: us-east-1e
          #   backup_retention: 7
          #   backup_window: 10:21-10:51
          #   maint_window: wed:08:03-wed:08:33
      command: "aws rds create-db-instance
                --db-instance-identifier {{ item.item }}
                --db-instance-class db.t2.medium
                --vpc-security-group-ids {{ item.group_id }}
                --db-subnet-group-name default
                --engine postgres
                --storage-type gp2
                --allocated-storage 20
                --db-name aact
                --master-username dbadmin
                --master-user-password {{ lookup('password', 'rds.' + item.item + '.secret length=17') }}
                --publicly-accessible
                --region us-east-1
                --availability-zone us-east-1e
                --backup-retention-period 7
                --preferred-backup-window 10:21-10:51
                --preferred-maintenance-window wed:08:03-wed:08:33
                --port 5432
                --tags Key=workload-type,Value=other
                --copy-tags-to-snapshot
                --no-multi-az"
      register: result
      failed_when: >
        result.rc != 0 and ('DBInstanceAlreadyExists' not in result.stderr)
      changed_when: "result.rc == 0"
      with_items: "{{ rdsgroup.results }}"

    - name: Get facts about each RDS instance
      rds:
        command: facts
        instance_name: "{{ item }}"
        region: us-east-1
      register: dbfacts
      with_items: "{{ instances }}"

    - name: create RO Users in RDS postgres instances
      postgresql_user:
        login_host: "{{ item.instance.endpoint }}"
        login_user: dbadmin
        login_password: "{{ lookup('password', '<PASSWORD>.' + item.item + '.<PASSWORD>') }}"
        db: aact
        name: aact
        password: <PASSWORD>
        no_password_changes: yes
      with_items: "{{ dbfacts.results }}"

    - name: REVOKE ALL ON DATABASE aact TO RO User
      postgresql_privs:
        login_host: "{{ item.instance.endpoint }}"
        login_user: dbadmin
        login_password: "{{ lookup('password', '<PASSWORD>.' + item.item + '.<PASSWORD>') }}"
        db: aact
        type: database<commit_msg>revokes create from schema public just after the db is created. This prevents new users, such as the RO user, from being able to create new tables and insert into them<commit_after>- hosts: localhost
  connection: local
  gather_facts: False
  vars:
    instances:
      # - aact-dev
      - aact-prod

  tasks:
    - name: create S3 instances
      s3_bucket:
        name: "{{ item }}"
        policy:
          Version: "2012-10-17"
          Statement:
            -
              Sid: "Stmt1470845581678"
              Effect: "Allow"
              Principal: "*"
              Action:
                - "s3:GetBucketLocation"
                - "s3:ListBucket"
              Resource: "arn:aws:s3:::{{ item }}"
            -
              Sid: "Stmt1470845605315"
              Effect: "Allow"
              Principal: "*"
              Action: "s3:GetObject"
              Resource: "arn:aws:s3:::{{ item }}/*"
      with_items: "{{ instances }}"

    - name: create agent IAM accounts
      iam:
        iam_type: user
        name: "{{ item }}-agent"
        state: present
      with_items: "{{ instances }}"

    - name: "create {{ item }}-policy"
      iam_policy:
        state: present
        iam_name: "{{ item }}-agent"
        iam_type: user
        policy_name: "{{ item }}-agent-policy"
        policy_document: "{{ item }}-agent-policy.json"
      with_items: "{{ instances }}"

    - name: Create Security Groups to manage access to rds
      ec2_group:
        name: "postgres-{{ item }}-public"
        description: "PostgreSQL public (internet) access to {{ item }} "
        region: us-east-1
        rules:
          - proto: tcp
            from_port: 5432
            to_port: 5432
            cidr_ip: 0.0.0.0/0
        rules_egress:
          - proto: all
            from_port: All
            cidr_ip: 0.0.0.0/0
        state: present
      register: "rdsgroup"
      with_items: "{{ instances }}"

    - name: Provision RDS Postgres Instances
          # https://github.com/ansible/ansible-modules-core/issues/633
          # rds module will not support storage_type SSD until it is refactored
          # to use boto3
          # rds:
          #   command: create
          #   instance_name: "{{ item.item }}"
          #   instance_type: db.t2.medium
          #   vpc_security_groups: "{{ item.group_id }}"
          #   subnet: default
          #   size: 20
          #   db_engine: postgres
          #   db_name: aact
          #   username: dbadmin
          #   password: "{{ lookup('password', '<PASSWORD>.' + item.item + '.<PASSWORD>') }}"
          #   publicly_accessible: yes
          #   region: us-east-1
          #   zone: us-east-1e
          #   backup_retention: 7
          #   backup_window: 10:21-10:51
          #   maint_window: wed:08:03-wed:08:33
      command: "aws rds create-db-instance
                --db-instance-identifier {{ item.item }}
                --db-instance-class db.t2.medium
                --vpc-security-group-ids {{ item.group_id }}
                --db-subnet-group-name default
                --engine postgres
                --storage-type gp2
                --allocated-storage 20
                --db-name aact
                --master-username dbadmin
                --master-user-password {{ lookup('password', 'rds.' + item.item + '.secret length=17') }}
                --publicly-accessible
                --region us-east-1
                --availability-zone us-east-1e
                --backup-retention-period 7
                --preferred-backup-window 10:21-10:51
                --preferred-maintenance-window wed:08:03-wed:08:33
                --port 5432
                --tags Key=workload-type,Value=other
                --copy-tags-to-snapshot
                --no-multi-az"
      register: result
      failed_when: >
        result.rc != 0 and ('DBInstanceAlreadyExists' not in result.stderr)
      changed_when: "result.rc == 0"
      with_items: "{{ rdsgroup.results }}"

    - name: Get facts about each RDS instance
      rds:
        command: facts
        instance_name: "{{ item }}"
        region: us-east-1
      register: dbfacts
      with_items: "{{ instances }}"

      # http://dba.stackexchange.com/questions/35316/why-is-a-new-user-allowed-to-create-a-table
    - name: REVOKE CREATE ON SCHEMA public FROM public
      postgresql_privs:
        login_host: "{{ item.instance.endpoint }}"
        login_user: dbadmin
        login_password: "{{ lookup('password', 'rds.' + item.item + '.secret length=17') }}"
        db: aact
        privs: CREATE
        type: schema
        objs: public
        role: public
        state: absent
      with_items: "{{ dbfacts.results }}"

    - name: create RO Users in RDS postgres instances
      postgresql_user:
        login_host: "{{ item.instance.endpoint }}"
        login_user: dbadmin
        login_password: "{{ lookup('password', 'rds.' + item.item + '.secret length=17') }}"
        db: aact
        name: aact
        password: <PASSWORD>
        no_password_changes: yes
      with_items: "{{ dbfacts.results }}"

    - name: REVOKE ALL ON DATABASE aact FROM RO User
      postgresql_privs:
        login_host: "{{ item.instance.endpoint }}"
        login_user: dbadmin
        login_password: "{{ lookup('password', 'rds.' + item.item + '.secret length=17') }}"
        db: aact
        type: database
{'fraction_non_alphanumeric': 0.12093898573692552, 'fraction_numerical': 0.02466323296354992, 'mean_word_length': 1.5764225567746875, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 13, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 4, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5125422', 'n_tokens_mistral': 3201, 'n_tokens_neox': 2992, 'n_words': 687}