Mercurial > gemma

annotate schema/updates/1308/01.add_section_country.sql @ 5267:aca4bf7af270

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: remove mapState from import statement
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 10 Jun 2020 16:33:10 +0200
parents 2440d2f86f4e
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE FUNCTION users.user_country(user_name name DEFAULT current_user)
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 RETURNS internal.user_profiles.country%TYPE
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 AS $$
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 SELECT country FROM users.list_users
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 WHERE username = user_name
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 $$
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 LANGUAGE SQL
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 STABLE PARALLEL SAFE;
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 ALTER TABLE waterway.sections ADD country char(2) REFERENCES countries
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 DEFAULT users.user_country();
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 -- Try to find out the country of existing sections:
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 UPDATE waterway.sections s SET country = (
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 SELECT COALESCE(
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 users.user_country((
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 SELECT i.username
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 FROM import.imports i
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 JOIN import.import_logs ON i.id = import_id
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 WHERE i.kind = 'sec'
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 AND (state = 'accepted' AND s.staging_done
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 OR state = 'pending' AND NOT s.staging_done)
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 AND msg = 'Storing section ''' || s.name || ''''
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 ORDER BY i.enqueued DESC FETCH FIRST ROW ONLY)),
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 '--'));
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 ALTER TABLE waterway.sections ALTER country SET NOT NULL;
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 DROP POLICY responsibility_area ON waterway.sections;
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 CREATE POLICY same_country ON waterway.sections
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 FOR ALL TO waterway_admin
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 USING (country = (
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 SELECT country FROM users.list_users WHERE username = current_user));