Mercurial > gemma
annotate schema/updates/1308/01.add_section_country.sql @ 4998:bb2123358bd8 wmst-config
Configure fairway marks layers with time support
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 09 Mar 2020 12:19:06 +0100 |
parents | 2440d2f86f4e |
children |
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)); |