site.yaml

---
##############
#   INFRA    #
##############
- name: PROVISION HOSTS AND BUILD ANSIBLE HOSTS INVENTORY
  hosts: localhost
  connection: local
  gather_facts: no
  become: no
  tasks:
    - name: ensure presence of instances and Ansible inventory
      fabiog1901.cockroachdb.cloud_instance:
        state: "{{ state }}"
        deployment_id: "{{ deployment_id }}"
        deployment: "{{ deployment }}"
        defaults:
          instances:
            aws:
              "2":
                default: m6i.large
                "4": c5.large
                "8": m6i.large
                "16": r5.large
              "4":
                default: m6i.xlarge
                "8": c5.xlarge
                "16": m6i.xlarge
                "32": r5.xlarge
              "8":
                default: m6i.2xlarge
                "16": c5.2xlarge
                "32": m6i.2xlarge
                "64": r5.2xlarge
              "16":
                default: m6i.4xlarge
                "32": c5.4xlarge
                "64": m6i.4xlarge
                "128": r5.4xlarge
              "32":
                default: m6i.8xlarge
                "128": m6i.8xlarge
                "256": r5.8xlarge
            azure:
              "2":
                "4": Standard_F2s_v2
                "8": Standard_D2s_v3
                "16": Standard_E2s_v3
                default: Standard_D2s_v3
              "4":
                "8": Standard_F4s_v2
                "16": Standard_D4s_v3
                "32": Standard_E4s_v3
                default: Standard_D4s_v3
              "8":
                "16": Standard_F8s_v2
                "32": Standard_D8s_v3
                "64": Standard_E8s_v3
                default: Standard_D8s_v3
              "16":
                "32": Standard_F16s_v2
                "64": Standard_D16s_v3
                "128": Standard_E16s_v3
                default: Standard_D16s_v3
              "32":
                "64": Standard_F32s_v2
                "128": Standard_D32s_v3
                "256": Standard_E32s_v3
                default: Standard_D32s_v3
            gcp:
              "2":
                default: n2-standard-2
                "2": n2-highcpu-2
                "4": n2-standard-2
                "8": n2-standard-2
                "16": n2-highmem-2
              "4":
                default: n2-standard-4
                "4": n2-highcpu-4
                "8": n2-standard-4
                "16": n2-standard-4
                "32": n2-highmem-4
              "8":
                default: n2-standard-8
                "8": n2-highcpu-8
                "16": n2-standard-8
                "32": n2-standard-8
                "64": n2-highmem-8
              "16":
                default: n2-standard-16
                "16": n2-highcpu-16
                "32": n2-standard-16
                "64": n2-standard-16
                "128": n2-highmem-16
              "32":
                default: n2-standard-32
                "32": n2-highcpu-32
                "64": n2-standard-32
                "128": n2-standard-32
                "256": n2-highmem-32
      register: instances

    - name: quit if state is absent
      fail:
        # msg: "state is absent, will quit here!"
      when: state == 'absent'

    - name: Wait for SSH to come up
      wait_for:
        host: "{{ item.public_ip }}"
        port: 22
        timeout: 180
        sleep: 5
        state: started
      loop: "{{ instances.out }}"

    - name: add instances to inventory groups
      ansible.builtin.add_host:
        # id
        name: "{{ item.private_hostname }}"
        id: "{{ item.id }}"

        # locality
        cloud: "{{ item.cloud }}"
        region: "{{ item.region }}"
        zone: "{{ item.zone }}"
        
        # addresses
        public_hostname: "{{ item.public_hostname }}"
        public_ip: "{{ item.public_ip }}"
        private_hostname: "{{ item.private_hostname }}"
        private_ip: "{{ item.private_ip }}"

        # tags
        ansible_user: "{{ item.ansible_user }}"
        ansible_host: "{{ item.public_ip }}"
        groups: "{{ item.inventory_groups }}"
        cluster_name: "{{ item.cluster_name }}"
        group_name: "{{ item.group_name }}"
        extra_vars: "{{ item.extra_vars }}"

      loop: "{{ instances.out }}"

    - name: save cluster list of hosts
      ansible.builtin.copy:
        content: |
          {% for item in groups %}
          {% if item not in ['all', 'ungrouped'] %}
          [{{item}}]
          {% for entry in groups[item] %}
          {{ entry }} ansible_host={{ hostvars[entry].ansible_host }} cloud={{ hostvars[entry].cloud }} cluster_name={{ hostvars[entry].cluster_name }}  group_name={{ hostvars[entry].group_name }} id={{ hostvars[entry].id }} region={{ hostvars[entry].region }} zone={{ hostvars[entry].zone }} private_hostname={{ hostvars[entry].private_hostname }} private_ip={{ hostvars[entry].private_ip }} public_hostname={{ hostvars[entry].public_hostname }} public_ip={{ hostvars[entry].public_ip }} ansible_user={{ hostvars[entry].ansible_user }} extra_vars="{{ hostvars[entry].extra_vars }}"
          {% endfor %}

          {% endif %}
          {% endfor %}
        dest: "{{ deployment_id }}.ini"

    - name: save simplified list of hosts
      ansible.builtin.copy:
        content: |
          {% for item in groups %}
          {% if item in ['haproxy', 'cockroachdb'] %}
          [{{item}}]
          {% for entry in groups[item] %}
          {{ hostvars[entry].cloud }}   {{ "{:18}".format(hostvars[entry].region) }} {{ hostvars[entry].public_ip }}
          {% endfor %}

          {% endif %}
          {% endfor %}
        dest: "{{ deployment_id }}.simple.ini"

  tags:
    - infra
    - cloud_instance

##############
#  PLATFORM  #
##############
- name: INSTALL MIT KDC CLIENT
  hosts: krb5_client
  gather_facts: yes
  become: yes
  tasks:
    - name: install MIT KDC client
      include_role:
        name: fabiog1901.cockroachdb.krb_client
      when: krb5_realm is defined
  tags:
    - platform
    - mitkdc
    - krb_client

- name: INSTALL MIT KDC SERVER
  hosts: krb5_server
  gather_facts: yes
  become: yes
  tasks:
    - name: install MIT KDC server
      include_role:
        name: fabiog1901.cockroachdb.krb_server
  tags:
    - platform
    - mitkdc
    - krb_server

- name: GENERATE COCKROACHDB CERTS
  hosts: localhost
  gather_facts: yes
  become: no
  tasks:
    - include_role:
        name: fabiog1901.cockroachdb.certificates
      when: cockroachdb_secure | default(no)
  tags:
    - platform
    - certificates

- name: CONFIGURE OS
  hosts: cockroachdb
  gather_facts: yes
  become: yes
  tasks:
    - include_role:
        name: fabiog1901.cockroachdb.config_os
  tags:
    - platform
    - config_os

- name: DEPLOY COCKROACHDB
  hosts: cockroachdb
  gather_facts: yes
  become: yes
  serial: "{{ (cockroachdb_deployment|default('standard') == 'upgrade') | ternary(1, '100%') }}"
  tasks:
    - include_role:
        name: fabiog1901.cockroachdb.cockroachdb
      vars:
        cockroachdb_deployment_type: "{{ cockroachdb_deployment | default('standard') }}"
  tags:
    - platform
    - cockroachdb

- name: INSTALL COCKROACHDB-HAPROXY
  hosts: haproxy
  gather_facts: yes
  become: yes
  tasks:
    - name: install HAProxy
      include_role:
        name: fabiog1901.cockroachdb.haproxy
      vars:
        haproxy_group: "{{ groups[cluster_name] | intersect(groups['cockroachdb']) }}"
        haproxy_port: "{{ cockroachdb_port | default('26257') }}"
        haproxy_checkport: "{{ cockroachdb_http_addr_port | default('8080') }}"
        haproxy_serverprefix: cockroach
        haproxy_httpchk: "/health?ready=1"

    - include_role:
        name: fabiog1901.cockroachdb.cockroachdb
      vars:
        cockroachdb_deployment_type: install only

    # - name: get the standalone workload binary
    #   shell: |
    #     wget https://edge-binaries.cockroachdb.com/cockroach/workload.LATEST -O workload; chmod 755 workload

  tags:
    - platform
    - haproxy

#################
#  APPLICATION  #
#################
- name: CREATE KERBEROS USERS AND KEYTABS
  hosts: krb5_server
  gather_facts: no
  become: yes
  tasks:
    - name: create user and keytab in /etc/krb5.keytab
      shell: |
        kadmin.local addprinc -pw cockroach {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].public_ip }}@{{ krb5_realm }}
        kadmin.local addprinc -pw cockroach {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].public_hostname }}@{{ krb5_realm }}
        kadmin.local addprinc -pw cockroach {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].private_ip }}@{{ krb5_realm }}
        kadmin.local addprinc -pw cockroach {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].private_hostname }}@{{ krb5_realm }}

        kadmin.local ktadd {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].public_ip }}@{{ krb5_realm }}
        kadmin.local ktadd {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].public_hostname }}@{{ krb5_realm }}
        kadmin.local ktadd {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].private_ip }}@{{ krb5_realm }}
        kadmin.local ktadd {{ cockroachdb_krbsrvname | default('postgres') }}/{{ hostvars[item].private_hostname }}@{{ krb5_realm }}

      loop: "{{ groups['haproxy'] }}"

    - name: add principals for the database users
      shell: |
        kadmin.local addprinc -pw {{ item.password }} {{ item.name }}@{{ krb5_realm }}
      loop: "{{ krb5_principals }}"
      register: result
      failed_when: result.rc != 0 and 'Principal or policy already exists while creating' not in result.stderr

    - name: Download keytab
      ansible.builtin.fetch:
        src: /etc/krb5.keytab
        dest: /tmp/cockroach.keytab
        flat: yes
  tags:
    - app

- name: COPY KEYTAB TO NODES
  hosts: cockroachdb
  gather_facts: no
  become: yes
  tasks:
    - name: Copy file with owner and permissions
      ansible.builtin.copy:
        src: /tmp/cockroach.keytab
        dest: /var/lib/cockroach/cockroach.keytab
        mode: "0644"
      when: cockroachdb_krb
  tags:
    - app

- name: COPY CRT+KEY TO APP SERVERS
  hosts: app
  gather_facts: no
  become: yes
  tasks:
    - name: install cockroachdb sql client
      include_role:
        name: fabiog1901.cockroachdb.cockroachdb
      vars:
        cockroachdb_deployment_type: install only

    - name: Copy client key
      ansible.builtin.copy:
        src: "certs/certs/client.{{ item.name }}.key"
        dest: "client.{{ item.name }}.key"
        mode: "0400"
        owner: "{{ ansible_user }}"
        group: "{{ ansible_user }}"
      when: cockroachdb_secure and item.is_cert
      loop: "{{ dbusers }}"

    - name: Copy client cert
      ansible.builtin.copy:
        src: certs/certs/client.{{ item.name }}.crt
        dest: "client.{{ item.name }}.crt"
        mode: "0644"
        owner: "{{ ansible_user }}"
        group: "{{ ansible_user }}"
      when: cockroachdb_secure and item.is_cert
      loop: "{{ dbusers }}"

    - name: Copy ca.crt
      ansible.builtin.copy:
        src: certs/certs/ca.crt
        dest: "ca.crt"
        mode: "0644"
        owner: "{{ ansible_user }}"
        group: "{{ ansible_user }}"
      when: cockroachdb_secure
  tags:
    - app

- name: SET CLUSTER SETTINGS FOR COCKROACHDB
  hosts: cockroachdb
  gather_facts: no
  become: yes
  tasks:
    - name: cockroachdb - set hba.conf setting
      run_once: yes
      shell: |
        cockroach sql \
          {{ (cockroachdb_secure) | ternary('--certs-dir=/var/lib/cockroach/certs', '--insecure') }} \
          --host={{ cockroachdb_advertise_addr }} \
          -e "SET cluster setting server.host_based_authentication.configuration = 'host all app all cert-password
          host all all all gss include_realm=0';"
      when: cockroachdb_krb

    - name: cockroachdb - add locations
      run_once: yes
      shell: |
        cockroach sql \
          --host={{ cockroachdb_advertise_addr }} \
          {{ (cockroachdb_secure) | ternary('--certs-dir=/var/lib/cockroach/certs', '--insecure') }} \
          -e "UPSERT INTO system.locations VALUES 
            ('region', 'us-east-1', 37.478397, -76.453077),
            ('region', 'us-east-2', 40.417287, -76.453077),
            ('region', 'us-west-1', 38.837522, -120.895824),
            ('region', 'us-west-2', 43.804133, -120.554201),
            ('region', 'ca-central-1', 56.130366, -106.346771),
            ('region', 'eu-central-1', 50.110922, 8.682127),
            ('region', 'eu-west-1', 53.142367, -7.692054),
            ('region', 'eu-west-2', 51.507351, -0.127758),
            ('region', 'eu-west-3', 48.856614, 2.352222),
            ('region', 'ap-northeast-1', 35.689487, 139.691706),
            ('region', 'ap-northeast-2', 37.566535, 126.977969),
            ('region', 'ap-northeast-3', 34.693738, 135.502165),
            ('region', 'ap-southeast-1', 1.352083, 103.819836),
            ('region', 'ap-southeast-2', -33.86882, 151.209296),
            ('region', 'ap-south-1', 19.075984, 72.877656),
            ('region', 'sa-east-1', -23.55052, -46.633309),
            ('region', 'eastasia', 22.267, 114.188),
            ('region', 'southeastasia', 1.283, 103.833),
            ('region', 'centralus', 41.5908, -93.6208),
            ('region', 'eastus', 37.3719, -79.8164),
            ('region', 'eastus2', 36.6681, -78.3889),
            ('region', 'westcentralus', 40, -110),
            ('region', 'northcentralus', 41.8819, -87.6278),
            ('region', 'southcentralus', 29.4167, -98.5),       
            ('region', 'westus', 37.783, -122.417),
            ('region', 'westus2', 47, -119),
            ('region', 'westus3', 33, -112),
            ('region', 'northeurope', 53.3478, -6.2597),
            ('region', 'westeurope', 52.3667, 4.9),
            ('region', 'japanwest', 34.6939, 135.5022),
            ('region', 'japaneast', 35.68, 139.77),
            ('region', 'brazilsouth', -23.55, -46.633),
            ('region', 'australiaeast', -33.86, 151.2094),
            ('region', 'australiasoutheast', -37.8136, 144.9631),
            ('region', 'southindia', 12.9822, 80.1636),
            ('region', 'centralindia', 18.5822, 73.9197),
            ('region', 'westindia', 19.088, 72.868),
            ('region', 'canadacentral', 43.653, -79.383),
            ('region', 'canadaeast', 46.817, -71.217),
            ('region', 'uksouth', 50.941, -0.799),
            ('region', 'ukwest', 53.427, -3.084),
            ('region', 'westcentralus', 40.890, -110.234),
            ('region', 'westus2', 47.233, -119.852),
            ('region', 'westus3', 33, -112),
            ('region', 'koreacentral', 37.5665, 126.9780),
            ('region', 'koreasouth', 35.1796, 129.0756),
            ('region', 'francecentral', 46.3772, 2.3730),
            ('region', 'francesouth', 43.8345, 2.1972),
            ('region', 'us-east1', 33.836082, -81.163727),
            ('region', 'us-east4', 37.478397, -76.453077),
            ('region', 'us-central1', 42.032974, -93.581543),
            ('region', 'us-west1', 43.804133, -120.554201),
            ('region', 'us-west4', 36.1955, -115.1538),
            ('region', 'northamerica-northeast1', 56.130366, -106.346771),
            ('region', 'europe-west1', 50.44816, 3.81886),
            ('region', 'europe-west2', 51.507351, -0.127758),
            ('region', 'europe-west3', 50.110922, 8.682127),
            ('region', 'europe-west4', 53.4386, 6.8355),
            ('region', 'europe-west6', 47.3769, 8.5417),
            ('region', 'europe-north1', 60.5702, 27.1877),
            ('region', 'asia-east1', 24.0717, 120.5624),
            ('region', 'asia-northeast1', 35.689487, 139.691706),
            ('region', 'asia-southeast1', 1.352083, 103.819836),
            ('region', 'australia-southeast1', -33.86882, 151.209296),
            ('region', 'asia-south1', 19.075984, 72.877656),
            ('region', 'asia-southeast2', -6.2075, 106.8449),
            ('region', 'asia-northeast2', 34.8172, 135.4976),
            ('region', 'southafricanorth', -27.049240, 29.172484),
            ('region', 'me-west1', 32.075206, 35.030164),
            ('region', 'southamerica-west1', -33.442004, -70.426019),
            ('region', 'southamerica-east1', -23.55052, -46.633309)
            ;"

    - name: cockroachdb - add license
      run_once: yes
      shell: |
        cockroach sql \
          --host={{ cockroachdb_advertise_addr }} \
          {{ (cockroachdb_secure) | ternary('--certs-dir=/var/lib/cockroach/certs', '--insecure') }} \
          -e "SET CLUSTER SETTING cluster.organization = '{{ cockroachdb_cluster_organization }}';
              SET CLUSTER SETTING enterprise.license = '{{ cockroachdb_enterprise_license }}';"

    - name: cockroachdb - create users
      run_once: yes
      shell: |
        cockroach sql \
          --host={{ cockroachdb_advertise_addr }} \
          {{ (cockroachdb_secure) | ternary('--certs-dir=/var/lib/cockroach/certs', '--insecure') }} \
          -e "CREATE USER {{ item.name }} WITH password {{ (item.password == None )| ternary('NULL' ,  "'" + item.password + "'" ) }};"
      loop: "{{ dbusers }}"
      failed_when: result.rc != 0 and 'already exists' not in result.stderr
      register: result
      when: cockroachdb_secure

    - name: cockroachdb - grant admin to users
      run_once: yes
      shell: |
        cockroach sql \
          --host={{ cockroachdb_advertise_addr }} \
          {{ (cockroachdb_secure) | ternary('--certs-dir=/var/lib/cockroach/certs', '--insecure') }} \
          -e "GRANT admin TO {{ item.name }};"
      loop: "{{ dbusers }}"
      when: item.is_admin and cockroachdb_secure
  tags:
    - app