Mercurial > gemma

annotate schema/auth.sql @ 5560:f2204f91d286

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