Mercurial > gemma

annotate schema/updates/1427/01.fix_rls_policies.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 4c658a8f34da
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5025
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 DROP POLICY same_country ON waterway.gauge_measurements;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 DROP POLICY same_country ON waterway.waterway_profiles;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 DO LANGUAGE plpgsql
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 $do$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 DECLARE
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 the_table varchar;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 condition CONSTANT text = $$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 (location).country_code =
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 (SELECT country FROM users.list_users
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 WHERE username = current_user)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 $$;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 BEGIN
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 FOREACH the_table IN ARRAY ARRAY[
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 'gauge_measurements',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 'waterway_profiles']
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 LOOP
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 CREATE POLICY same_country_insert ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 FOR INSERT TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 WITH CHECK (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 CREATE POLICY same_country_select ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 FOR SELECT TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 USING (staging_done OR %s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 CREATE POLICY same_country_update ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 FOR UPDATE TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 USING (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 CREATE POLICY same_country_delete ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 FOR DELETE TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 USING (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 END LOOP;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 END;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 $do$;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 DROP POLICY responsibility_area ON waterway.bottlenecks;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 DROP POLICY responsibility_area ON waterway.sounding_results;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 DROP POLICY responsibility_area ON waterway.fairway_dimensions;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 DO LANGUAGE plpgsql
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 $do$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 DECLARE
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 the_table varchar;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 condition CONSTANT text = $$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a)))
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 FROM users.current_user_area_utm() AS a (a))
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 $$;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 BEGIN
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 FOREACH the_table IN ARRAY ARRAY[
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 'fairway_dimensions',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 'bottlenecks',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 'sounding_results']
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 LOOP
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 CREATE POLICY responsibility_area_insert ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 FOR INSERT TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 WITH CHECK (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 -- In many cases it is more efficient to check for "staging_done" to
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 -- prevent the more expensive checks for read only access (which is
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 -- allowed for all users, when staging is done).
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 CREATE POLICY responsibility_area_select ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 FOR SELECT TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 USING (staging_done OR %s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 CREATE POLICY responsibility_area_update ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 FOR UPDATE TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 USING (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 EXECUTE format($$
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 CREATE POLICY responsibility_area_delete ON waterway.%I
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 FOR DELETE TO waterway_admin
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 USING (%s)
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 $$, the_table, condition);
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 END LOOP;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 END;
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 $do$;