airbods.yaml

---
# Airbods deployment script
# Ansible playbook
# This playbook defines tasks to deploy the application stack.
# It installs several services and configures them.
#
# Table of contents:
#  * Install PostgreSQL database
#  * Install Redis database
#  * Install RabbitMQ message broker
#  * Install Apache Airflow
#  * Install pgAdmin

- name: Install miscellaneous packages
  hosts: all
  become: yes
  pre_tasks:
    - name: Install utilities
      apt:
        update_cache: yes
        name:
          - ca-certificates
          - curl
          - wget
          - ncdu
          - htop

- name: Install PostgreSQL database
  hosts: database
  become: yes
  pre_tasks:
    - name: Install postgresql package
      apt:
        name:
          - postgresql-12
          - python3-pip
    - name: Create Airbods system user
      user:
        name: airbods
        system: yes
    # Python library that will allow us to use Ansible PostgreSQL modules
    # Pre-requisite for Ansible PostgreSQL tasks
    - name: Install psycopg2
      pip:
        name:
          - psycopg2-binary
  tasks:
    - name: Install PostgreSQL auth config
      copy:
        src: pg_hba.conf
        dest: /etc/postgresql/12/main/pg_hba.conf
      notify:
        - Restart PostgreSQL

    - name: Create postgres user
      user:
        name: postgres
        password: "{{ lookup('file', 'secrets/postgres.txt') | password_hash('sha512') }}"

    - name: Install PostgreSQL configuration
      copy:
        src: postgresql.conf
        dest: /etc/postgresql/12/main/postgresql.conf
      notify:
        - Restart PostgreSQL

    # postgresql.conf ssl_cert_file
    - name: Install certificate
      copy:
        src: airbods_my_domain_com_cert.cer
        dest: /var/lib/postgresql/12/main/server.crt
        owner: postgres
        group: postgres

    # postgresql.conf ssl_key_file
    - name: Install private key
      copy:
        src: secrets/airbods_my_domain_com.key
        dest: /var/lib/postgresql/12/main/server.key
        owner: postgres
        group: postgres
        mode: 0600

    - name: Create postgres home directory
      file:
        path: /home/postgres
        state: directory
        owner: postgres
        group: postgres
        mode: 0755

    # postgresql.conf ssl_passphrase_command
    - name: Install SSL key password
      copy:
        content: "{{ lookup('file', 'secrets/database.txt') }}"
        dest: /home/postgres/ssl_key.txt
        owner: postgres
        group: postgres
        mode: 0600

    - name: Start PostgreSQL service
      service:
        name: postgresql
        state: started

    - name: Create Airbods database user
      become_user: postgres
      postgresql_user:
        user: airbods
        password: "{{ lookup('file', 'secrets/database.txt') }}"
        # If this doesn't work, run:
        # sudo -u postgres psql -c "\password airbods"

    - name: Create Airbods database
      become_user: postgres
      postgresql_db:
        name: airbods
        owner: airbods

    - name: Create Airbods database structure
      become_user: airbods
      postgresql_query:
        db: airbods
        query: "{{ lookup('file', item) }}"
        login_user: airbods
        login_password: "{{ lookup('file', 'secrets/database.txt') }}"
      with_fileglob:
        - "database/*.sql"

    - name: Create researcher database role
      become_user: postgres
      postgresql_user:
        db: airbods
        user: researcher
        role_attr_flags: NOLOGIN

    - name: Set researcher permissions
      become_user: postgres
      postgresql_privs:
        role: researcher
        db: airbods
        schema: public
        privs: SELECT
        type: table
        objs: ALL_IN_SCHEMA

    # Apache Airflow credentials
    - name: Create airflow database user
      become_user: postgres
      postgresql_user:
        user: airflow
        password: "{{ lookup('file', 'secrets/airflow.txt') }}"

    - name: Create airflow database
      become_user: postgres
      postgresql_db:
        name: airflow
        owner: airflow

  handlers:
    - name: Restart PostgreSQL
      service:
        name: postgresql
        state: restarted

- name: Install Redis database
  hosts: scheduler
  become: yes
  tasks:
    - name: Install Redis package
      apt:
        name: redis
    # Listen on localhost and network interface (see ifconfig)
    - name: Redis listen externally
      replace:
        path: /etc/redis/redis.conf
        regexp: '^bind.*'
        replace: 'bind 127.0.0.1 172.30.16.201'
      notify:
        - Restart Redis
  handlers:
    - name: Restart Redis
      service:
        name: redis
        state: restarted

# Why You Should use Celery with RabbitMQ
# https://www.section.io/engineering-education/why-you-should-use-celery-with-rabbitmq/
- name: Install RabbitMQ message broker
  hosts: scheduler
  become: yes
  vars:
    node: rabbit@localhost
  tasks:
    - name: Install RabbitMQ package
      apt:
        name: rabbitmq-server=3.*
    # https://stackoverflow.com/a/45475646
    - name: RabbitMQ set HOSTNAME
      lineinfile:
        path: /etc/rabbitmq/rabbitmq-env.conf
        regexp: '^HOSTNAME='
        line: HOSTNAME=localhost
      notify:
        - Restart RabbitMQ
    - name: Start RabbitMQ service
      service:
        name: rabbitmq-server
        state: started
    - name: Enable RabbitMQ management console
      shell:
        cmd: "rabbitmq-plugins enable rabbitmq_management"
    - name: Create RabbitMQ vhost
      rabbitmq_vhost:
        name: airflow
        node: "{{ node }}"
    # This task seems to be problematic with older versions of Ansible
    # https://github.com/ansible-collections/community.rabbitmq/issues/52
    - name: Create RabbitMQ user
      rabbitmq_user:
        node: "{{ node }}"
        user: airflow
        password: "{{ lookup('file', 'secrets/rabbitmq.txt') }}"
        permissions:
          - vhost: airflow
            read_priv: .*
            write_priv: .*
            configure_priv: .*
    - name: Create RabbitMQ management user
      rabbitmq_user:
        node: "{{ node }}"
        user: admin
        password: "{{ lookup('file', 'secrets/rabbitmq_admin.txt') }}"
        tags: administrator
        # assign full access control
        permissions:
          - vhost: /
            configure_priv: .*
            read_priv: .*
            write_priv: .*
    - name: Delete RabbitMQ guest user
      rabbitmq_user:
        node: "{{ node }}"
        user: guest
        state: absent
  handlers:
    - name: Restart RabbitMQ
      systemd:
        name: rabbitmq-server
        state: restarted

- name: Install Airflow scheduler
  hosts: scheduler
  become: yes
  roles:
    - airflow
  vars:
    service:
      - scheduler
      - webserver
      - flower
  tasks:
    - name: Initialise Airflow database
      become_user: airflow
      # "initdb is also idempotent, so this can be run as often as you
      # choose to, without needing to worry about the database changing."
      # https://stackoverflow.com/a/59560731
      shell:
        cmd: "{{ airflow_bin_path }}/airflow db init"

    - name: Update database schema
      become_user: airflow
      shell:
        cmd: "{{ airflow_bin_path }}/airflow db upgrade"

    - name: Create Airflow admin user
      # Don't show password
      no_log: true
      become_user: airflow
      command:
        argv:
          - "{{ airflow_bin_path }}/airflow"
          - "users"
          - "create"
          - "--username"
          - "airflow"
          - "--password"
          - "{{ lookup('file', 'secrets/airflow.txt') }}"
          - "--firstname"
          - "admin"
          - "--lastname"
          - "user"
          - "--role"
          - "Admin"
          - "--email"
          - "airbods@my-domain.com"

    - name: Create Datacake pool
      # https://airflow.apache.org/docs/apache-airflow/stable/concepts/pools.html
      become_user: airflow
      shell:
        # https://airflow.apache.org/docs/apache-airflow/stable/cli-and-env-variables-ref.html#pools
        cmd: "{{ airflow_bin_path }}/airflow pools set datacake 8 Datacake"

- name: Install Airflow worker
  hosts: workers
  become: yes
  roles:
    - airflow
  vars:
    service:
      - worker
  tasks:
    - name: Create raw data directory
      file:
        path: /mnt/airbods/raw_data
        state: directory
        owner: airflow
        group: airflow
        mode: 0755

# pgAdmin (PostgreSQL database administration browser application)
- name: Install pgAdmin
  # pgAdmin docs
  # https://www.pgadmin.org/docs/pgadmin4/latest
  hosts: database
  become: yes
  tasks:
    # pgAdmin 4 (APT) download and installation instructions
    # https://www.pgadmin.org/download/pgadmin-4-apt/
    - name: Install pgAdmin APT public key
      apt_key:
        url: "https://www.pgadmin.org/static/packages_pgadmin_org.pub"
    - name: Install pgAdmin APT respository
      apt_repository:
        # Use lsb_release -cs to get the Ubuntu version
        repo: "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main"
    - name: Install pgAdmin
      apt:
        update_cache: yes
        name:
          # Server mode (web app) without Apache HTTPD
          - pgadmin4
          # https://docs.gunicorn.org/en/latest/install.html#ubuntu
          - gunicorn
    # We don't need Apache HTTPD
    - name: Stop Apache2
      service:
        name: apache2
        state: stopped
        enabled: no
    # Configure Gunicorn
    - name: Create Gunicorn config directory
      file:
        state: directory
        path: /etc/gunicorn.d/pgadmin4
    - name: Grant gunicorn priviledged ports
      shell:
        # Grant privileges to the Python interpreter used by Gunicorn
        # sudo getcap /usr/bin/python3.8
        cmd: "setcap cap_net_bind_service=+eip /usr/bin/python3.8"
    - name: Install Gunicorn service
      copy:
        src: gunicorn/gunicorn.service
        dest: /etc/systemd/system/gunicorn.service
      notify:
        - Restart Gunicorn
    - name: Install Gunicorn configuration file
      copy:
        src: gunicorn/gunicorn.conf.py
        dest: /etc/gunicorn.d/pgadmin4/gunicorn.conf.py
      notify:
        - Restart Gunicorn
    - name: Install Gunicorn environment variables
      copy:
        src: gunicorn/gunicorn.env
        dest: /etc/gunicorn.d/pgadmin4/gunicorn.env
    - name: Create pgAdmin role
      become_user: postgres
      postgresql_user:
        user: pgadmin
        password: "{{ lookup('file', 'secrets/pgadmin.txt') }}"
        role_attr_flags: LOGIN,CREATEROLE
    - name: Start Gunicorn service
      service:
        name: gunicorn
        state: started
        enabled: yes
        daemon_reload: yes
  handlers:
    - name: Restart Gunicorn
      service:
        name: gunicorn
        state: restarted
        daemon_reload: yes

# NGINX is used as a reverse proxy to handle requests to the PgAdmin app
# https://www.pgadmin.org/docs/pgadmin4/6.5/server_deployment.html#nginx-configuration-with-gunicorn
# https://docs.gunicorn.org/en/stable/deploy.html
- name: Install NGINX
  # pgAdmin docs
  # https://www.pgadmin.org/docs/pgadmin4/latest
  hosts: database
  become: yes
  vars:
    site_path: /etc/nginx/sites-available/gunicorn.conf
  tasks:
    - name: Install NGINX
      apt:
        name: nginx
    - name: Install NGINX site configuration
      copy:
        src: gunicorn/gunicorn.conf
        dest: "{{ site_path }}"
      notify:
        - Restart NGINX
    - name: Enable NGINX site
      file:
        state: link
        src: "{{ site_path }}"
        path: /etc/nginx/sites-enabled/gunicorn.conf
    - name: Disable default site
      file:
        state: absent
        path: /etc/nginx/sites-enabled/default
    # Encryption
    - name: Make www-data dir
      file:
        state: directory
        path: /home/www-data
        owner: www-data
        group: www-data
        mode: 0700
    - name: Install SSL certificate
      copy:
        src: airbods_my_domain_com_cert.cer
        dest: /home/www-data/airbods_my_domain_com_cert.cer
    - name: Install SSL private key
      copy:
        src: secrets/airbods_my_domain_com.key
        dest: /home/www-data/airbods_my_domain_com.key
        mode: 0600
        owner: www-data
        group: ssl-cert
  handlers:
    - name: Restart NGINX
      service:
        name: gunicorn
        state: restarted
        daemon_reload: yes
...