From 1af347391f9f54b370dfc7395464b8ed637a79ca Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Fri, 6 Mar 2026 13:50:24 +0100
Subject: Rename "webmap" to the less generic "geodata".

The database has uses beyond the webmap.
Cf. ca91a579770c89d25aefae220079bf336fa88dc9 in tools.
---
 tasks/postgis.yml | 158 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 158 insertions(+)
 create mode 100644 tasks/postgis.yml

(limited to 'tasks/postgis.yml')

diff --git a/tasks/postgis.yml b/tasks/postgis.yml
new file mode 100644
index 0000000..3e156a9
--- /dev/null
+++ b/tasks/postgis.yml
@@ -0,0 +1,158 @@
+- 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')) }}"
-- 
cgit v1.2.3