Mercurial > gemma

annotate schema/auth.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 37ae1bee3e4a
children fc7d828695c9
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
6
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
7 -- Copyright (C) 2018 by via donau
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 --
840
0f61bfc21041 Added end point to get style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents: 737
diff changeset
28 GRANT USAGE ON SCHEMA public, users, waterway, systemconf 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;
840
0f61bfc21041 Added end point to get style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents: 737
diff changeset
30 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
31 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
32 TO waterway_user;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33
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 -- Extended privileges for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 --
1630
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
37 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
38 TO waterway_admin;
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
39 -- 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
40 -- imported data due to historicisation? Special tables like
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
41 -- 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
42
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
43 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
44 users.templates TO waterway_admin;
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
45 -- 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
46 ALTER TABLE users.templates ALTER COLUMN country
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
47 SET DEFAULT users.current_user_country();
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
49 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
50 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
51 GRANT UPDATE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
52 import.imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
53 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
54 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
55 GRANT DELETE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
56 import.track_imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
57 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
58 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
59
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
63 GRANT INSERT, UPDATE, DELETE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
64 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
65 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
66 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
67 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
68 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
69 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
70 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
71
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
72 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
73 -- 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
74 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
75 -- 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
76 GRANT USAGE ON SCHEMA public TO metamorph;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
77
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
81 -- 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
82 -- data modifications generally.
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
83 -- 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
84 -- 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
85 -- 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
86 -- 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
87 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 -- Staging area
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
90 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
91 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 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
95 'gauge_measurements',
1895
048580ec843d Fixed RLS for stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1873
diff changeset
96 'stretches',
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
97 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
98 'fairway_dimensions',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
99 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
100 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
102 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
103 'FOR SELECT TO waterway_user USING (staging_done)', 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
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
110 --
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
111 -- RLS policies for templates
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
112 --
2267
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
113 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
114 USING (country IS NULL OR country = users.current_user_country());
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
115
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
116 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
117 USING (country = users.current_user_country());
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
118
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
119 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
120 USING (true);
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
121
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
122 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125 -- RLS policies for waterway_admin
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 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 -- 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
130
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
131 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
132 FOR ALL TO waterway_admin
1782
30c76168e6d0 RLS gauge_measurements: Another attempt.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1780
diff changeset
133 USING ((fk_gauge_id).country_code = users.current_user_country());
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
134
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
135 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
136 FOR ALL TO waterway_admin
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
137 USING ((location).country_code = users.current_user_country());
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
138
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
139 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
140 FOR ALL TO waterway_admin
1229
d395b2940a82 Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1168
diff changeset
141 USING (utm_covers(area));
d395b2940a82 Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1168
diff changeset
142
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
143 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
144 FOR ALL TO waterway_admin
1229
d395b2940a82 Improved performance of row level security check on users responsibility areas
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1168
diff changeset
145 USING (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
146
1842
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
147 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
148 FOR ALL TO waterway_admin
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
149 USING (utm_covers(area));
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
150
2252
f8f2c4d88939 sysadmin users shall be able to create new stretches w/o restrictions.
Sascha Wilde <wilde@intevation.de>
parents: 2230
diff changeset
151 CREATE POLICY sys_admin ON waterway.stretches
1913
2e0e7c1589c2 Fixed RLS policy for waterway.stretches.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1895
diff changeset
152 FOR ALL TO sys_admin
2252
f8f2c4d88939 sysadmin users shall be able to create new stretches w/o restrictions.
Sascha Wilde <wilde@intevation.de>
parents: 2230
diff changeset
153 USING (true);
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
154
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
155 --
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
156 -- 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
157 --
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
158
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
159 CREATE POLICY same_country ON import.imports
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
160 FOR ALL TO waterway_admin
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
161 USING (users.current_user_country() = (
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
162 SELECT country FROM users.list_users lu
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
163 WHERE lu.username = imports.username));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
164 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
165
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
166 -- 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
167 -- 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
168 CREATE POLICY import_all ON import.imports
1872
f63712670c25 Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents: 1842
diff changeset
169 FOR ALL TO sys_admin
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
170 USING (true);
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
171
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
172 -- 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
173 -- bypassing row level security.
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
174 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
175 tablename varchar,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
176 kv anyelement)
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
177 RETURNS boolean
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
178 AS $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
179 DECLARE columnname varchar;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
180 DECLARE ret boolean;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
181 BEGIN
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
182 columnname = (SELECT column_name
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
183 FROM information_schema.key_column_usage k
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
184 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
185 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
186 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
187 columnname, tablename)
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
188 INTO ret
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
189 USING kv;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
190 RETURN ret;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
191 END;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
192 $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
193 LANGUAGE plpgsql
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
194 SECURITY DEFINER
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
195 STABLE PARALLEL SAFE;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
196
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
197 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
198 FOR ALL TO waterway_admin
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
199 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
200 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
201 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
202 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
203
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
204 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
205 FOR ALL TO waterway_admin
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
206 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
207 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
208 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
209 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
210
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
211 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
212 FOR ALL TO waterway_admin
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
213 USING (
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
214 users.current_user_country() = (
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1354
diff changeset
215 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
216 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
217
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
218 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
219 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
220 USING (true);
eeb291c3f9b0 Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1636
diff changeset
221
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
222 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
223
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
224 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
225 FOR ALL TO waterway_admin
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
226 USING (import_configuration_id IN (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
227 SELECT id FROM import.import_configuration))
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
228 WITH CHECK (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
229 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
230 OR import_configuration_id IN (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
231 SELECT id FROM import.import_configuration));
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
232 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
233
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
234 COMMIT;