- name: Install PostgreSQL and PostGIS
  apt: pkg={{ packages }}
  vars:
    packages:
    - postgresql
    - postgresql-postgis
    - postgis
    # for ansible
    - python3-psycopg

- name: Generate sv_SE.UTF-8 locales
  locale_gen: name=sv_SE.UTF-8 state=present
  # PostgreSQL needs to be restarted to see the new locale
  notify: Restart PostgreSQL

- name: Configure PostgreSQL
  copy: src=etc/postgresql/postgresql.conf
        dest=/etc/postgresql/{{ postgresql.version }}/{{ postgresql.cluster }}/conf.d/local.conf
        owner=postgres group=postgres
        mode=0644
  notify: Restart PostgreSQL

- name: Start PostgreSQL
  service: name=postgresql@{{ postgresql.version }}-{{ postgresql.cluster }}.service state=started

- meta: flush_handlers

# Usage: \sudo -u postgres psql </usr/local/share/geodata/schema.sql
- name: Copy /usr/local/share/geodata/schema.sql
  copy: src=webmap-tools/schema.sql
        dest=/usr/local/share/geodata/schema.sql
        owner=root group=root
        mode=0644

- name: Create PostgreSQL database
  become: true
  # XXX: this creates /var/lib/postgresql/.ansible/tmp
  become_user: postgres
  community.postgresql.postgresql_db:
    name: geodata
    comment: Backend PostGIS database for KlimatanalysNorr tooling
    encoding: UTF-8
    lc_collate: sv_SE.UTF-8
    lc_ctype: sv_SE.UTF-8
    locale_provider: icu
    icu_locale: sv-SE-x-icu
    template: template0
    owner: postgres

- name: Create 'geodata' and 'guest' PostgreSQL users (roles)
  become: true
  become_user: postgres
  community.postgresql.postgresql_user:
    login_db: geodata
    name: "{{ item }}"
  with_items:
    - geodata
    - guest

- name: Add a rule for 'geodata' user in pg_hba.conf
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgresql.version }}/{{ postgresql.cluster }}/pg_hba.conf
    regexp: '^local\s+geodata\s'
    line: 'local   geodata         all                                     peer map=pgmap_geodata'
    # must come before 'local all all peer', cf.
    # https://dba.stackexchange.com/questions/177142/postgresql-cannot-peer-authenticate-using-usermap-provided-user-name-dbuser
    insertbefore: '^local\s+all\s+all\s'
    create: false
  notify: Reload PostgreSQL

- name: Add a mapping rule for 'geodata' user in pg_ident.conf
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgresql.version }}/{{ postgresql.cluster }}/pg_ident.conf
    regexp: '^pgmap_geodata\s.*\sgeodata\s*$'
    line: 'pgmap_geodata   _geodata                                geodata'
    create: false
  notify: Reload PostgreSQL

- name: Add a mapping rule for 'guest' user in pg_ident.conf
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgresql.version }}/{{ postgresql.cluster }}/pg_ident.conf
    regexp: '^pgmap_geodata\s.*\sguest\s*$'
    line: 'pgmap_geodata   /^_?[a-zA-Z][a-zA-Z0-9_\-]*[a-zA-Z0-9]$ guest'
    create: false
  notify: Reload PostgreSQL

- name: Create PostgreSQL schemas
  become: true
  become_user: postgres
  community.postgresql.postgresql_schema:
    login_db: geodata
    name: "{{ item.name }}"
    owner: postgres
    comment: "{{ item.comment }}"
  with_items: "{{ postgis_schemas }}"

- name: Install 'postgis' PostgreSQL extension to the geodata database
  become: true
  become_user: postgres
  community.postgresql.postgresql_ext:
    name: postgis
    login_db: geodata
    comment: Geographic objects support for PostgreSQL

- name: GRANT CONNECT ON DATABASE geodata TO geodata, guest
  become: true
  become_user: postgres
  community.postgresql.postgresql_privs:
    login_db: geodata
    privs: CONNECT
    type: database
    role: geodata,guest

- name: GRANT USAGE ON SCHEMA * TO geodata, guest
  become: true
  become_user: postgres
  community.postgresql.postgresql_privs:
    login_db: geodata
    privs: USAGE
    type: schema
    objs: "{{ (['public'] + (postgis_schemas | map(attribute='name'))) | join(',') }}"
    role: geodata,guest

# tooling should TRUNCATE existing output layers instead
- name: REVOKE CREATE ON SCHEMA * FROM geodata
  become: true
  become_user: postgres
  community.postgresql.postgresql_privs:
    login_db: geodata
    privs: CREATE
    type: schema
    objs: "{{ (['public'] + (postgis_schemas | map(attribute='name'))) | join(',') }}"
    role: geodata
    state: absent

- name: GRANT SELECT ON TABLES IN SCHEMA * TO guest
  become: true
  become_user: postgres
  community.postgresql.postgresql_privs:
    login_db: geodata
    privs: SELECT
    type: table
    obj: ALL_IN_SCHEMA
    schema: "{{ item }}"
    role: guest
  with_items: "{{ ['public'] + (postgis_schemas | map(attribute='name')) }}"

- name: GRANT USAGE, SELECT ON SEQUENCES IN SCHEMA * TO guest
  become: true
  become_user: postgres
  community.postgresql.postgresql_privs:
    login_db: geodata
    privs: USAGE,SELECT
    type: sequence
    obj: ALL_IN_SCHEMA
    schema: "{{ item }}"
    role: guest
  with_items: "{{ ['public'] + (postgis_schemas | map(attribute='name')) }}"