Mercurial > gemma

annotate schema/updates/1427/01.fix_rls_policies.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
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$;