Mercurial > gemma

annotate schema/auth.sql @ 5016:cf25b23e3eec

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Keep historic data of waterway axis ... and accordingly configure the respective layer as WMS-T.
author Tom Gottfried <tom@intevation.de>
date Fri, 13 Mar 2020 17:34:59 +0100
parents 388947a3050d
children 4c658a8f34da
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
6
2915
69292eb68984 Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents: 2818
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottried <tom@intevation.de>
1318
50dd4ff7907e Added missing copyright headers and authors in schema related files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1301
diff changeset
13 -- * Sascha Wilde <sascha.wilde@intevation.de>
50dd4ff7907e Added missing copyright headers and authors in schema related files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1301
diff changeset
14 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
15
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 BEGIN;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
19 -- Roles, privileges and policies for the GEMMA database
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 -- We do not want any users to be able to create any objects
313
10b93a8ee057 Lock out the PUBLIC more rigorously
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
23 REVOKE ALL ON SCHEMA public FROM PUBLIC;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 -- Privileges for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 --
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4161
diff changeset
28 GRANT USAGE ON SCHEMA public, users, waterway, sys_admin, caching TO waterway_user;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
29 GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2267
diff changeset
30 GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user;
3626
73fd4dd9e569 grant read permission on system_config for waterway_users
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
31 GRANT SELECT ON sys_admin.system_config TO waterway_user;
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 320
diff changeset
32 GRANT UPDATE (pw, map_extent, email_address) ON users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 320
diff changeset
33 TO waterway_user;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 -- Extended privileges for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 --
1630
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
38 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
39 TO waterway_admin;
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
40
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
41 GRANT INSERT, UPDATE, DELETE ON
2147
b66cfcde8ff7 Print templates: Stubbed endpoints under /api/templates/print/{name}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2079
diff changeset
42 users.templates TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
44 GRANT USAGE ON SCHEMA import TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
45 GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
46 GRANT UPDATE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
47 import.imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
48 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
49 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
50 GRANT DELETE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
51 import.track_imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
52 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
53 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
54
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 --
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
58 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users TO sys_admin;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
59 GRANT USAGE ON SCHEMA sys_admin TO sys_admin;
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 210
diff changeset
60 GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
3631
9709081719ae Added missing insert grant to sys_admin.system_config for sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3626
diff changeset
61 GRANT INSERT, UPDATE ON sys_admin.system_config TO sys_admin;
871
f0b6852c14d1 More on uploading styles to gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 846
diff changeset
62 GRANT UPDATE ON sys_admin.published_services TO sys_admin;
3956
4f9a1ff2c2ee Reworked password reset to be single mailed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3666
diff changeset
63 GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
64
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
65 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
66 -- Privileges assigned directly to metamorph
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
67 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
68 -- Needed for GeoServer's system inspection run before session startup SQL
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
69 GRANT USAGE ON SCHEMA public TO metamorph;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
70
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
74 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
75 -- data modifications generally.
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
76 -- Sometimes using 'EXISTS(SELECT 1 FROM list_users WHERE username = ...)'
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
77 -- instead of 'username = current_user', since waterway_admin is intentionally
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
78 -- allowed more with these policies (note that the subselect implies different
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
79 -- filtering on list_users depending on current_user).
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
80 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 -- Staging area
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
83 CREATE POLICY hide_staging ON users.stretches
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
84 FOR SELECT TO waterway_user USING (staging_done);
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
85 ALTER TABLE users.stretches ENABLE ROW LEVEL SECURITY;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
86
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
87 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
88 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 FOREACH the_table IN ARRAY ARRAY[
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
92 'gauge_measurements',
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
93 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
94 'fairway_dimensions',
4676
5b9ba358a4e7 Add missing RLS policies for sections
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
95 'sections',
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
96 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
97 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
99 EXECUTE format('CREATE POLICY hide_staging ON waterway.%I '
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 'FOR SELECT TO waterway_user USING (staging_done)', the_table);
2921
96ac7a980ec2 Allow sys_admin to access tables in staging area without restrictions
Tom Gottfried <tom@intevation.de>
parents: 2915
diff changeset
101 EXECUTE format('CREATE POLICY sys_admin ON waterway.%I '
96ac7a980ec2 Allow sys_admin to access tables in staging area without restrictions
Tom Gottfried <tom@intevation.de>
parents: 2915
diff changeset
102 'FOR ALL TO sys_admin USING (true)', the_table);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
103 EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY',
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
104 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 END;
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
107 $$;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108
5013
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
109 -- Tables without staging area
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
110 CREATE POLICY hide_nothing ON waterway.waterway_axis
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
111 FOR SELECT TO waterway_user USING (true);
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
112 CREATE POLICY sys_admin ON waterway.waterway_axis
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
113 FOR ALL TO sys_admin USING (true);
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
114 ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY;
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
115
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
116
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
117 --
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
118 -- RLS policies for templates
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
119 --
2267
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
120 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user
2818
89f5af7e14ad Due to performance problems with users.current_user_country() inlined it as
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2542
diff changeset
121 USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user));
2267
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
122
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
123 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin
2818
89f5af7e14ad Due to performance problems with users.current_user_country() inlined it as
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2542
diff changeset
124 USING (country = (SELECT country FROM users.list_users WHERE username = current_user));
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
125
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
126 CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
127 USING (true);
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
128
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
129 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
134
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 -- Staging area
1229
d395b2940a82 Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1168
diff changeset
136
4788
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
137 -- In many cases it is more efficient to check for "staging_done" to
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
138 -- prevent the more expensive checks for read only access (which is
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
139 -- allowed for all users, when staging is done).
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
140 CREATE POLICY same_country ON waterway.gauge_measurements
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
141 FOR ALL TO waterway_admin
4785
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
142 USING (staging_done
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
143 OR (location).country_code =
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
144 (SELECT country FROM users.list_users
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
145 WHERE username = current_user))
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
146 WITH CHECK ((location).country_code =
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
147 (SELECT country FROM users.list_users
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
148 WHERE username = current_user));
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
149
2079
9318973487a1 Waterway profiles import: Implemented the parsing of the data lines of the CSV file and stored the values into the database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1995
diff changeset
150 CREATE POLICY same_country ON waterway.waterway_profiles
9318973487a1 Waterway profiles import: Implemented the parsing of the data lines of the CSV file and stored the values into the database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1995
diff changeset
151 FOR ALL TO waterway_admin
4785
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
152 USING (staging_done
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
153 OR (location).country_code =
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
154 (SELECT country FROM users.list_users
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
155 WHERE username = current_user))
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
156 WITH CHECK ((location).country_code =
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
157 (SELECT country FROM users.list_users
1fef9d8e7eb2 Improved performance for RLS on read access.
Sascha Wilde <wilde@intevation.de>
parents: 4740
diff changeset
158 WHERE username = current_user));
2079
9318973487a1 Waterway profiles import: Implemented the parsing of the data lines of the CSV file and stored the values into the database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1995
diff changeset
159
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
160 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
161 FOR ALL TO waterway_admin
5014
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
162 USING (staging_done
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
163 OR (SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
164 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
165 FROM users.current_user_area_utm() AS a (a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
166 WITH CHECK ((SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
167 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
168 FROM users.current_user_area_utm() AS a (a)));
1229
d395b2940a82 Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1168
diff changeset
169
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
170 CREATE POLICY responsibility_area ON waterway.sounding_results
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
171 FOR ALL TO waterway_admin
5014
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
172 USING (staging_done
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
173 OR (SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
174 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
175 FROM users.current_user_area_utm() AS a (a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
176 WITH CHECK ((SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
177 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
178 FROM users.current_user_area_utm() AS a (a)));
175
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
179
1842
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
180 CREATE POLICY responsibility_area ON waterway.fairway_dimensions
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
181 FOR ALL TO waterway_admin
5014
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
182 USING (staging_done
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
183 OR (SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
184 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
185 FROM users.current_user_area_utm() AS a (a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
186 WITH CHECK ((SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
187 ST_Transform(CAST(area AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
188 FROM users.current_user_area_utm() AS a (a)));
1842
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
189
4788
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
190 -- In the case of sections differentiating between read and write
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
191 -- access is not neccessary: the country code based access check is
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
192 -- quiet cheap in this case and there are only (relatively) few
9e077ca97505 Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents: 4785
diff changeset
193 -- sections in the system anyway.
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4676
diff changeset
194 CREATE POLICY same_country ON waterway.sections
4676
5b9ba358a4e7 Add missing RLS policies for sections
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
195 FOR ALL TO waterway_admin
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4676
diff changeset
196 USING (country = (
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4676
diff changeset
197 SELECT country FROM users.list_users WHERE username = current_user));
4676
5b9ba358a4e7 Add missing RLS policies for sections
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
198
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
199 CREATE POLICY sys_admin ON users.stretches
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
200 FOR ALL TO sys_admin
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
201 USING (true);
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4236
diff changeset
202
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
203 --
5013
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
204 -- Tables without staging area
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
205 --
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
206 -- Use three policies instead of one FOR ALL to avoid costly expressions
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
207 -- being added in SELECT queries.
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
208 CREATE POLICY responsibility_area_insert ON waterway.waterway_axis
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
209 FOR INSERT TO waterway_admin
5014
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
210 WITH CHECK ((SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
211 ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
212 FROM users.current_user_area_utm() AS a (a)));
5013
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
213 CREATE POLICY responsibility_area_update ON waterway.waterway_axis
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
214 FOR UPDATE TO waterway_admin
5014
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
215 USING ((SELECT ST_Covers(a,
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
216 ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
388947a3050d Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents: 5013
diff changeset
217 FROM users.current_user_area_utm() AS a (a)));
5013
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
218
7dff1015283d Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents: 4842
diff changeset
219 --
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
220 -- RLS policies for imports and import config
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
221 --
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
222
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
223 CREATE POLICY same_country ON import.imports
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
224 FOR ALL TO waterway_admin
4158
5466562cca60 Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents: 3956
diff changeset
225 -- Relies on a user seeing only users from his own country:
5466562cca60 Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents: 3956
diff changeset
226 USING (EXISTS(SELECT 1 FROM users.list_users lu
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
227 WHERE lu.username = imports.username));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
228 ALTER table import.imports ENABLE ROW LEVEL SECURITY;
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
229
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
230 -- The job running the import queue is running as sys_admin and login users
1872
f63712670c25 Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents: 1842
diff changeset
231 -- with that role should be able to run imports without restrictions anyhow
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
232 CREATE POLICY import_all ON import.imports
1872
f63712670c25 Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents: 1842
diff changeset
233 FOR ALL TO sys_admin
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
234 USING (true);
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
235
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
236 -- For the given table, check whether the given value is used as primary key,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
237 -- bypassing row level security.
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
238 CREATE OR REPLACE FUNCTION import.is_new_key(
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
239 tablename varchar,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
240 kv anyelement)
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
241 RETURNS boolean
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
242 AS $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
243 DECLARE columnname varchar;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
244 DECLARE ret boolean;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
245 BEGIN
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
246 columnname = (SELECT column_name
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
247 FROM information_schema.constraint_column_usage k
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
248 JOIN information_schema.table_constraints USING (constraint_name)
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
249 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY');
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
250 EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)',
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
251 tablename, columnname)
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
252 INTO ret
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
253 USING kv;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
254 RETURN ret;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
255 END;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
256 $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
257 LANGUAGE plpgsql
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
258 SECURITY DEFINER
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
259 STABLE PARALLEL SAFE;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
260
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
261 CREATE POLICY parent_allowed ON import.import_logs
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
262 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
263 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
264 WITH CHECK (import.is_new_key('imports', import_id)
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
265 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
266 ALTER table import.import_logs ENABLE ROW LEVEL SECURITY;
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
267
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
268 CREATE POLICY parent_allowed ON import.track_imports
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
269 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
270 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
271 WITH CHECK (import.is_new_key('imports', import_id)
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
272 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
273 ALTER table import.track_imports ENABLE ROW LEVEL SECURITY;
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
274
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
275 CREATE POLICY import_configuration_policy ON import.import_configuration
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
276 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
277 -- Relies on a user seeing only users from his own country:
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
278 USING (EXISTS(SELECT 1 FROM users.list_users lu
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
279 WHERE lu.username = import_configuration.username));
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
280
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
281 CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration
1735
eeb291c3f9b0 Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1636
diff changeset
282 FOR ALL TO sys_admin
eeb291c3f9b0 Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1636
diff changeset
283 USING (true);
eeb291c3f9b0 Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1636
diff changeset
284
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
285 ALTER table import.import_configuration ENABLE ROW LEVEL SECURITY;
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
286
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
287 CREATE POLICY parent_allowed ON import.import_configuration_attributes
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
288 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
289 USING (EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
290 WHERE id = import_configuration_id))
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
291 WITH CHECK (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
292 import.is_new_key('import_configuration', import_configuration_id)
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
293 OR EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
294 WHERE id = import_configuration_id));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
295 ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY;
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
296
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
297 COMMIT;