Mercurial > gemma
annotate schema/auth.sql @ 5298:4bc14bea3fc9
Fixed check for bottleneck existence in fa import.
The contains() function for bottleneck lists (slice) is build upon
sort.SearchStrings() which in turn requires the slice to be sorted.
Until now the bottleneck list was sorted by ordering in the database
request, which depends on LC_COLLATE and might not meet the sort
packages expectations.
Therefor we now use sort.Strings() to sort it based on the same
semantics as expected by SearchStrings().
author | wilde@azure1.rgb.intevation.de |
---|---|
date | Wed, 11 Nov 2020 10:57:22 +0100 |
parents | 90b0a14dd58b |
children | 18969a4d31b6 |
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; |
5111
90b0a14dd58b
Enable jobs to be removed by the import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5025
diff
changeset
|
64 GRANT DELETE ON import.imports, import.import_logs TO sys_admin; |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
313
diff
changeset
|
65 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
66 -- |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
67 -- 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
|
68 -- |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
69 -- 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
|
70 GRANT USAGE ON SCHEMA public TO metamorph; |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
313
diff
changeset
|
71 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 -- |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 -- RLS policies for waterway_user |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 -- |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
179
diff
changeset
|
75 -- 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
|
76 -- data modifications generally. |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
77 -- 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
|
78 -- 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
|
79 -- 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
|
80 -- 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
|
81 -- |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 -- Staging area |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
84 CREATE POLICY hide_staging ON users.stretches |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
85 FOR SELECT TO waterway_user USING (staging_done); |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
86 ALTER TABLE users.stretches ENABLE ROW LEVEL SECURITY; |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
87 |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
88 DO LANGUAGE plpgsql |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
89 $$ |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 DECLARE the_table varchar; |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 BEGIN |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 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
|
93 'gauge_measurements', |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
94 'waterway_profiles', |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
95 'fairway_dimensions', |
4676
5b9ba358a4e7
Add missing RLS policies for sections
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
96 'sections', |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
97 'bottlenecks', |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
98 'sounding_results'] |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 LOOP |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
100 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
|
101 '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
|
102 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
|
103 '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
|
104 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
|
105 the_table); |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
106 END LOOP; |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 END; |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
108 $$; |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
109 |
5013
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
110 -- Tables without staging area |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
111 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
|
112 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
|
113 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
|
114 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
|
115 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
|
116 |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
117 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
118 -- |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
119 -- RLS policies for templates |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
120 -- |
2267
37ae1bee3e4a
Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2252
diff
changeset
|
121 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
|
122 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
|
123 |
37ae1bee3e4a
Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2252
diff
changeset
|
124 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
|
125 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
|
126 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
127 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
|
128 USING (true); |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
129 |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
130 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; |
96
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 -- |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
133 -- RLS policies for waterway_admin |
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 |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
136 -- 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
|
137 |
5025
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
138 DO LANGUAGE plpgsql |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
139 $do$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
140 DECLARE |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
141 the_table varchar; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
142 condition CONSTANT text = $$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
143 (location).country_code = |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
144 (SELECT country FROM users.list_users |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
145 WHERE username = current_user) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
146 $$; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
147 BEGIN |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
148 FOREACH the_table IN ARRAY ARRAY[ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
149 'gauge_measurements', |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
150 'waterway_profiles'] |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
151 LOOP |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
152 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
153 CREATE POLICY same_country_insert ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
154 FOR INSERT TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
155 WITH CHECK (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
156 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
157 -- 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:
5016
diff
changeset
|
158 -- 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:
5016
diff
changeset
|
159 -- allowed for all users, when staging is done). |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
160 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
161 CREATE POLICY same_country_select ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
162 FOR SELECT TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
163 USING (staging_done OR %s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
164 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
165 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
166 CREATE POLICY same_country_update ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
167 FOR UPDATE TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
168 USING (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
169 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
170 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
171 CREATE POLICY same_country_delete ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
172 FOR DELETE TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
173 USING (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
174 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
175 END LOOP; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
176 END; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
177 $do$; |
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
|
178 |
5025
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
179 DO LANGUAGE plpgsql |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
180 $do$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
181 DECLARE |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
182 the_table varchar; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
183 condition CONSTANT text = $$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
184 (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:
5016
diff
changeset
|
185 FROM users.current_user_area_utm() AS a (a)) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
186 $$; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
187 BEGIN |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
188 FOREACH the_table IN ARRAY ARRAY[ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
189 'fairway_dimensions', |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
190 'bottlenecks', |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
191 'sounding_results'] |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
192 LOOP |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
193 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
194 CREATE POLICY responsibility_area_insert ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
195 FOR INSERT TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
196 WITH CHECK (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
197 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
198 -- 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:
5016
diff
changeset
|
199 -- 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:
5016
diff
changeset
|
200 -- allowed for all users, when staging is done). |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
201 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
202 CREATE POLICY responsibility_area_select ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
203 FOR SELECT TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
204 USING (staging_done OR %s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
205 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
206 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
207 CREATE POLICY responsibility_area_update ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
208 FOR UPDATE TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
209 USING (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
210 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
211 EXECUTE format($$ |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
212 CREATE POLICY responsibility_area_delete ON waterway.%I |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
213 FOR DELETE TO waterway_admin |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
214 USING (%s) |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
215 $$, the_table, condition); |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
216 END LOOP; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
217 END; |
4c658a8f34da
Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
218 $do$; |
1842
56357561938f
Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1834
diff
changeset
|
219 |
4788
9e077ca97505
Added epic comments on responsibility_area and same_country policies.
Sascha Wilde <wilde@intevation.de>
parents:
4785
diff
changeset
|
220 -- 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
|
221 -- 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
|
222 -- 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
|
223 -- sections in the system anyway. |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4676
diff
changeset
|
224 CREATE POLICY same_country ON waterway.sections |
4676
5b9ba358a4e7
Add missing RLS policies for sections
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
225 FOR ALL TO waterway_admin |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4676
diff
changeset
|
226 USING (country = ( |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4676
diff
changeset
|
227 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
|
228 |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
229 CREATE POLICY sys_admin ON users.stretches |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
230 FOR ALL TO sys_admin |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
231 USING (true); |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4236
diff
changeset
|
232 |
1834
06d162ac0b9f
Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents:
1832
diff
changeset
|
233 -- |
5013
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
234 -- Tables without staging area |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
235 -- |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
236 -- 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
|
237 -- being added in SELECT queries. |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
238 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
|
239 FOR INSERT TO waterway_admin |
5014
388947a3050d
Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents:
5013
diff
changeset
|
240 WITH CHECK ((SELECT ST_Covers(a, |
388947a3050d
Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents:
5013
diff
changeset
|
241 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
|
242 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
|
243 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
|
244 FOR UPDATE TO waterway_admin |
5014
388947a3050d
Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents:
5013
diff
changeset
|
245 USING ((SELECT ST_Covers(a, |
388947a3050d
Inline SQL function in row level security policies
Tom Gottfried <tom@intevation.de>
parents:
5013
diff
changeset
|
246 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
|
247 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
|
248 |
7dff1015283d
Add row level security policies for waterway axis
Tom Gottfried <tom@intevation.de>
parents:
4842
diff
changeset
|
249 -- |
1834
06d162ac0b9f
Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents:
1832
diff
changeset
|
250 -- 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
|
251 -- |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
252 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
253 CREATE POLICY same_country ON import.imports |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
254 FOR ALL TO waterway_admin |
4158
5466562cca60
Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents:
3956
diff
changeset
|
255 -- 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
|
256 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
|
257 WHERE lu.username = imports.username)); |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
258 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
|
259 |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
260 -- 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
|
261 -- 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
|
262 CREATE POLICY import_all ON import.imports |
1872
f63712670c25
Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents:
1842
diff
changeset
|
263 FOR ALL TO sys_admin |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
264 USING (true); |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
265 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
266 -- 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
|
267 -- bypassing row level security. |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
268 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
|
269 tablename varchar, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
270 kv anyelement) |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
271 RETURNS boolean |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
272 AS $$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
273 DECLARE columnname varchar; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
274 DECLARE ret boolean; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
275 BEGIN |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
276 columnname = (SELECT column_name |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
277 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
|
278 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
|
279 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
|
280 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
|
281 tablename, columnname) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
282 INTO ret |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
283 USING kv; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
284 RETURN ret; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
285 END; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
286 $$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
287 LANGUAGE plpgsql |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
288 SECURITY DEFINER |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
289 STABLE PARALLEL SAFE; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
290 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
291 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
|
292 FOR ALL TO waterway_admin |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
293 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
|
294 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
|
295 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
|
296 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
|
297 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
298 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
|
299 FOR ALL TO waterway_admin |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
300 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
|
301 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
|
302 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
|
303 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
|
304 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
305 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
|
306 FOR ALL TO waterway_admin |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
307 -- 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
|
308 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
|
309 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
|
310 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
311 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
|
312 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
|
313 USING (true); |
eeb291c3f9b0
Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1636
diff
changeset
|
314 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
315 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
|
316 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
317 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
|
318 FOR ALL TO waterway_admin |
4161
64cd18281c76
Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
4158
diff
changeset
|
319 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
|
320 WHERE id = import_configuration_id)) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
321 WITH CHECK ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
322 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
|
323 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
|
324 WHERE id = import_configuration_id)); |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
325 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
|
326 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
327 COMMIT; |