Mercurial > gemma
annotate schema/auth.sql @ 3548:f3102fa16a69
client: data availability/accuracy: inject mapId into layer/styles factory to fix display issue with two maps
This layer displays features based on which other layers are visible. To detect those other layers visibility realiably, the layer
needs to be aware about the map it belongs to.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Fri, 31 May 2019 12:45:30 +0200 |
parents | ec6163c6687d |
children | 73fd4dd9e569 |
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 -- |
2542
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2267
diff
changeset
|
28 GRANT USAGE ON SCHEMA public, users, waterway, systemconf, 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; |
840
0f61bfc21041
Added end point to get style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents:
737
diff
changeset
|
31 GRANT SELECT ON systemconf.feature_colours 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; |
f6218f11062a
Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents:
1628
diff
changeset
|
40 -- TODO: will there ever be UPDATEs and DELETEs or can we drop that for |
f6218f11062a
Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents:
1628
diff
changeset
|
41 -- imported data due to historicisation? Special tables like |
f6218f11062a
Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents:
1628
diff
changeset
|
42 -- import_configuration will further need UPDATE and DELETE privileges. |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
43 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
44 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
|
45 users.templates TO waterway_admin; |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
46 -- Ensure templates are associated to the users country, if none is given |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
47 ALTER TABLE users.templates ALTER COLUMN country |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
48 SET DEFAULT users.current_user_country(); |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
50 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
|
51 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
|
52 GRANT UPDATE ON |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
53 import.imports, import.import_configuration, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
54 import.import_configuration_attributes |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
55 TO waterway_admin; |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
56 GRANT DELETE ON |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
57 import.track_imports, import.import_configuration, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
58 import.import_configuration_attributes |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
59 TO waterway_admin; |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
60 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 -- |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 -- Extended privileges for sys_admin |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
64 GRANT INSERT, UPDATE, DELETE |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
65 ON users.list_users, users.responsibility_areas TO sys_admin; |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
66 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
|
67 GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin; |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
68 GRANT UPDATE ON sys_admin.system_config TO sys_admin; |
846
6902032757e4
Added end point to set style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents:
840
diff
changeset
|
69 GRANT UPDATE ON systemconf.feature_colours TO sys_admin; |
871
f0b6852c14d1
More on uploading styles to gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
846
diff
changeset
|
70 GRANT UPDATE ON sys_admin.published_services TO sys_admin; |
478
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
463
diff
changeset
|
71 GRANT INSERT, DELETE 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
|
72 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
73 -- |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
74 -- 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
|
75 -- |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
76 -- 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
|
77 GRANT USAGE ON SCHEMA public TO metamorph; |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
313
diff
changeset
|
78 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 -- |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 -- RLS policies for waterway_user |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 -- |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
179
diff
changeset
|
82 -- 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
|
83 -- data modifications generally. |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
263
diff
changeset
|
84 -- Sometimes using 'username IN(SELECT username FROM users.list_users)' instead |
180
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
179
diff
changeset
|
85 -- of 'username = current_user', because waterway_admin is intentionally |
0423eab4ad45
Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents:
179
diff
changeset
|
86 -- 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
|
87 -- 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
|
88 -- |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 -- Staging area |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
91 DO LANGUAGE plpgsql |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
92 $$ |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 DECLARE the_table varchar; |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 BEGIN |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 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
|
96 'gauge_measurements', |
1895
048580ec843d
Fixed RLS for stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1873
diff
changeset
|
97 'stretches', |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
98 'waterway_profiles', |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
99 'fairway_dimensions', |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
100 'bottlenecks', |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
101 'sounding_results'] |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 LOOP |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
103 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
|
104 '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
|
105 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
|
106 '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
|
107 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
|
108 the_table); |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
109 END LOOP; |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 END; |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
111 $$; |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
113 -- |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
114 -- RLS policies for templates |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
115 -- |
2267
37ae1bee3e4a
Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2252
diff
changeset
|
116 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
|
117 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
|
118 |
37ae1bee3e4a
Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2252
diff
changeset
|
119 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
|
120 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
|
121 |
2230
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
122 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
|
123 USING (true); |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
124 |
4374d942b23d
Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents:
2147
diff
changeset
|
125 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
126 |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
127 -- |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
128 -- RLS policies for waterway_admin |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
129 -- |
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 -- Staging area |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
132 -- TODO: add all relevant tables here |
1229
d395b2940a82
Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
133 |
1780
48791416bea5
(Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1735
diff
changeset
|
134 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
|
135 FOR ALL TO waterway_admin |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
2921
diff
changeset
|
136 USING ((location).country_code |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
2921
diff
changeset
|
137 = (SELECT country FROM users.list_users WHERE username = current_user) |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
2921
diff
changeset
|
138 ); |
1780
48791416bea5
(Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1735
diff
changeset
|
139 |
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
|
140 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
|
141 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
|
142 USING ((location).country_code = (SELECT country FROM users.list_users 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
|
143 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
144 CREATE POLICY responsibility_area ON waterway.bottlenecks |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
145 FOR ALL TO waterway_admin |
2915
69292eb68984
Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents:
2818
diff
changeset
|
146 USING (users.utm_covers(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
|
147 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
148 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
|
149 FOR ALL TO waterway_admin |
2915
69292eb68984
Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents:
2818
diff
changeset
|
150 USING (users.utm_covers(area)); |
175
3f7053e53fa6
Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents:
172
diff
changeset
|
151 |
1842
56357561938f
Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1834
diff
changeset
|
152 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
|
153 FOR ALL TO waterway_admin |
2915
69292eb68984
Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents:
2818
diff
changeset
|
154 USING (users.utm_covers(area)); |
1842
56357561938f
Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1834
diff
changeset
|
155 |
1834
06d162ac0b9f
Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents:
1832
diff
changeset
|
156 -- |
06d162ac0b9f
Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents:
1832
diff
changeset
|
157 -- 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
|
158 -- |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
159 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
160 CREATE POLICY same_country ON import.imports |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
161 FOR ALL TO waterway_admin |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
162 USING (users.current_user_country() = ( |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
163 SELECT country FROM users.list_users lu |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
164 WHERE lu.username = imports.username)); |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
165 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
|
166 |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
167 -- 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
|
168 -- 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
|
169 CREATE POLICY import_all ON import.imports |
1872
f63712670c25
Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents:
1842
diff
changeset
|
170 FOR ALL TO sys_admin |
1798
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
171 USING (true); |
40cbfd268aa9
Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents:
1782
diff
changeset
|
172 |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
173 -- 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
|
174 -- bypassing row level security. |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
175 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
|
176 tablename varchar, |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
177 kv anyelement) |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
178 RETURNS boolean |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
179 AS $$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
180 DECLARE columnname varchar; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
181 DECLARE ret boolean; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
182 BEGIN |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
183 columnname = (SELECT column_name |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
184 FROM information_schema.key_column_usage k |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
185 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
|
186 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
187 EXECUTE format('SELECT NOT $1 = ANY(SELECT %I FROM import.%I)', |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
188 columnname, tablename) |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
189 INTO ret |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
190 USING kv; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
191 RETURN ret; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
192 END; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
193 $$ |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
194 LANGUAGE plpgsql |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
195 SECURITY DEFINER |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
196 STABLE PARALLEL SAFE; |
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
197 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
198 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
|
199 FOR ALL TO waterway_admin |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
200 USING (import_id IN (SELECT id FROM import.imports)) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
201 WITH CHECK (import.is_new_key('imports', import_id) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
202 OR import_id IN (SELECT id FROM import.imports)); |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
203 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
|
204 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
205 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
|
206 FOR ALL TO waterway_admin |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
207 USING (import_id IN (SELECT id FROM import.imports)) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
208 WITH CHECK (import.is_new_key('imports', import_id) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
209 OR import_id IN (SELECT id FROM import.imports)); |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
210 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
|
211 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
212 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
|
213 FOR ALL TO waterway_admin |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1354
diff
changeset
|
214 USING ( |
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
|
215 (SELECT country FROM users.list_users WHERE username = current_user) = ( |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1354
diff
changeset
|
216 SELECT country FROM users.list_users lu |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
217 WHERE lu.username = import.import_configuration.username)); |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1354
diff
changeset
|
218 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
219 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
|
220 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
|
221 USING (true); |
eeb291c3f9b0
Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1636
diff
changeset
|
222 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
223 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
|
224 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
225 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
|
226 FOR ALL TO waterway_admin |
06d162ac0b9f
Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents:
1832
diff
changeset
|
227 USING (import_configuration_id IN ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
228 SELECT id FROM import.import_configuration)) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
229 WITH CHECK ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
230 import.is_new_key('import_configuration', import_configuration_id) |
1873
9f8f7d3fd655
Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents:
1872
diff
changeset
|
231 OR import_configuration_id IN ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
232 SELECT id FROM import.import_configuration)); |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
233 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
|
234 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
235 COMMIT; |