blob: 3e156a9e0530ef74015ffab2a6938833fbceced1 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
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')) }}"
|
