diff options
Diffstat (limited to 'tasks/postgis.yml')
| -rw-r--r-- | tasks/postgis.yml | 158 |
1 files changed, 158 insertions, 0 deletions
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')) }}" |