Mercurial > gemma

annotate schema/auth.sql @ 4364:496bbf0f618c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Move definition of published services to default configuration We rely on existance of these layers in some places, e.g. default configuration, already in some places. Further, default configuration setup is, in contrast to demo data, part of automatic database tests.
author Tom Gottfried <tom@intevation.de>
date Mon, 09 Sep 2019 17:39:57 +0200
parents 27ed6f709195
children 5e38667f740c
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
6
2915
69292eb68984 Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents: 2818
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottried <tom@intevation.de>
1318
50dd4ff7907e Added missing copyright headers and authors in schema related files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1301
diff changeset
13 -- * Sascha Wilde <sascha.wilde@intevation.de>
50dd4ff7907e Added missing copyright headers and authors in schema related files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1301
diff changeset
14 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1229
diff changeset
15
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 BEGIN;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
19 -- Roles, privileges and policies for the GEMMA database
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 -- We do not want any users to be able to create any objects
313
10b93a8ee057 Lock out the PUBLIC more rigorously
Tom Gottfried <tom@intevation.de>
parents: 312
diff changeset
23 REVOKE ALL ON SCHEMA public FROM PUBLIC;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 -- Privileges for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 --
4236
27ed6f709195 Remove unused systemconf.feature_colours from backend
Bernhard Reiter <bernhard@intevation.de>
parents: 4161
diff changeset
28 GRANT USAGE ON SCHEMA public, users, waterway, sys_admin, caching TO waterway_user;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
29 GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2267
diff changeset
30 GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user;
3626
73fd4dd9e569 grant read permission on system_config for waterway_users
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
31 GRANT SELECT ON sys_admin.system_config TO waterway_user;
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 320
diff changeset
32 GRANT UPDATE (pw, map_extent, email_address) ON users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 320
diff changeset
33 TO waterway_user;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 -- Extended privileges for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 --
1630
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
38 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
f6218f11062a Cleanup GRANTs for waterway_admin
Tom Gottfried <tom@intevation.de>
parents: 1628
diff changeset
39 TO waterway_admin;
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
40
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
41 GRANT INSERT, UPDATE, DELETE ON
2147
b66cfcde8ff7 Print templates: Stubbed endpoints under /api/templates/print/{name}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2079
diff changeset
42 users.templates TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
44 GRANT USAGE ON SCHEMA import TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
45 GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
46 GRANT UPDATE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
47 import.imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
48 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
49 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
50 GRANT DELETE ON
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
51 import.track_imports, import.import_configuration,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
52 import.import_configuration_attributes
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
53 TO waterway_admin;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
54
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
58 GRANT INSERT, UPDATE, DELETE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
59 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
60 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
61 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
62 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
63 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
64 GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
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
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
84 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
85 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 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
89 'gauge_measurements',
1895
048580ec843d Fixed RLS for stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1873
diff changeset
90 'stretches',
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
91 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
92 'fairway_dimensions',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
93 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
94 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
96 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
97 '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
98 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
99 '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
100 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
101 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 END;
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
104 $$;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
106 --
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
107 -- RLS policies for templates
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
108 --
2267
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
109 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
110 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
111
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2252
diff changeset
112 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
113 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
114
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
115 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
116 USING (true);
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
117
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
118 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 --
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 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125 -- 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
126
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
127 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
128 FOR ALL TO waterway_admin
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2921
diff changeset
129 USING ((location).country_code
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2921
diff changeset
130 = (SELECT country FROM users.list_users WHERE username = current_user)
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2921
diff changeset
131 );
1780
48791416bea5 (Approved) gauge measurement import: Fixed row level security.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1735
diff changeset
132
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
133 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
134 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
135 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
136
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
137 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
138 FOR ALL TO waterway_admin
2915
69292eb68984 Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents: 2818
diff changeset
139 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
140
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
141 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
142 FOR ALL TO waterway_admin
2915
69292eb68984 Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents: 2818
diff changeset
143 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
144
1842
56357561938f Added RLS for responsibility area on fairway dimensions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1834
diff changeset
145 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
146 FOR ALL TO waterway_admin
2915
69292eb68984 Simplify function and move to appropriate schema
Tom Gottfried <tom@intevation.de>
parents: 2818
diff changeset
147 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
148
1834
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
149 --
06d162ac0b9f Sync access to import config attributes to import config
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
150 -- 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
151 --
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
152
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
153 CREATE POLICY same_country ON import.imports
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
154 FOR ALL TO waterway_admin
4158
5466562cca60 Remove utility function with possibly bad performance impact
Tom Gottfried <tom@intevation.de>
parents: 3956
diff changeset
155 -- 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
156 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
157 WHERE lu.username = imports.username));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
158 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
159
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
160 -- 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
161 -- 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
162 CREATE POLICY import_all ON import.imports
1872
f63712670c25 Simplify RLS policies on import jobs
Tom Gottfried <tom@intevation.de>
parents: 1842
diff changeset
163 FOR ALL TO sys_admin
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
164 USING (true);
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
165
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
166 -- 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
167 -- bypassing row level security.
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
168 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
169 tablename varchar,
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
170 kv anyelement)
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
171 RETURNS boolean
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
172 AS $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
173 DECLARE columnname varchar;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
174 DECLARE ret boolean;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
175 BEGIN
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
176 columnname = (SELECT column_name
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
177 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
178 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
179 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
180 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
181 tablename, columnname)
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
182 INTO ret
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
183 USING kv;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
184 RETURN ret;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
185 END;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
186 $$
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
187 LANGUAGE plpgsql
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
188 SECURITY DEFINER
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
189 STABLE PARALLEL SAFE;
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
190
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
191 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
192 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
193 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
194 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
195 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
196 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
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.track_imports
1798
40cbfd268aa9 Row level security for import jobs
Tom Gottfried <tom@intevation.de>
parents: 1782
diff changeset
199 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
200 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
201 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
202 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
203 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
204
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
205 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
206 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
207 -- 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
208 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
209 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
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_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
212 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
213 USING (true);
eeb291c3f9b0 Configured imports: Allow sys_admin access to import_configuation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1636
diff changeset
214
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
215 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
216
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
217 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
218 FOR ALL TO waterway_admin
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents: 4158
diff changeset
219 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
220 WHERE id = import_configuration_id))
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1872
diff changeset
221 WITH CHECK (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
222 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
223 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
224 WHERE id = import_configuration_id));
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
225 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
226
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227 COMMIT;