Mercurial > gemma

annotate schema/auth.sql @ 846:6902032757e4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added end point to set style (colour) information for feature.
author Sascha Wilde <wilde@intevation.de>
date Fri, 28 Sep 2018 11:51:06 +0200
parents 0f61bfc21041
children f0b6852c14d1
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 BEGIN;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
4 -- Roles, privileges and policies for the GEMMA database
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 -- 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
8 REVOKE ALL ON SCHEMA public FROM PUBLIC;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Privileges for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 --
840
0f61bfc21041 Added end point to get style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents: 737
diff changeset
13 GRANT USAGE ON SCHEMA public, users, waterway, systemconf TO waterway_user;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
14 GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
840
0f61bfc21041 Added end point to get style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents: 737
diff changeset
15 GRANT SELECT ON systemconf.feature_colours TO waterway_user;
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 320
diff changeset
16 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
17 TO waterway_user;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 -- Extended privileges for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
22 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA waterway TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 -- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
24 GRANT INSERT, UPDATE, DELETE ON
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
25 users.templates, users.user_templates TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
30 GRANT INSERT, UPDATE, DELETE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
31 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
32 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
33 GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
34 GRANT UPDATE ON sys_admin.system_config TO sys_admin;
846
6902032757e4 Added end point to set style (colour) information for feature.
Sascha Wilde <wilde@intevation.de>
parents: 840
diff changeset
35 GRANT UPDATE ON systemconf.feature_colours TO sys_admin;
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
36 GRANT INSERT, DELETE ON sys_admin.password_reset_requests TO sys_admin;
737
ea0eb6ef98ce Granted rights to sys_admin on waterway.sounding_results_contour_lines.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 478
diff changeset
37 GRANT INSERT, DELETE, UPDATE ON waterway.sounding_results_contour_lines TO sys_admin;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
38
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
39 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
40 -- 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
41 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
42 -- 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
43 GRANT USAGE ON SCHEMA public TO metamorph;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
44
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
48 -- 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
49 -- data modifications generally.
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
50 -- Sometimes using 'username IN(SELECT username FROM users.list_users)' instead
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
51 -- of 'username = current_user', because waterway_admin is intentionally
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
52 -- 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
53 -- 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
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 -- Staging area
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
57 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
58 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 FOREACH the_table IN ARRAY ARRAY[
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
62 'gauge_measurements',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
63 'sections_stretches',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
64 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
65 'fairway_dimensions',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
66 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
67 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
69 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
70 'FOR SELECT TO waterway_user USING (staging_done)', the_table);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
71 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
72 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 END;
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
75 $$;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
77 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
78 USING (username IN(SELECT username FROM users.list_users));
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
79 ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY;
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
80
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
81 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
82 USING (template_name IN(SELECT template_name FROM users.user_templates))
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
83 WITH CHECK (true);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
84 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 -- TODO: add all relevant tables here
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
92 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
93 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
94 USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
95 WHERE country = users.current_user_country())));
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
96 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
97 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
98 USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas
312
0745b4d336c4 Place functions in more matching schemas
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
99 WHERE country = users.current_user_country())));
175
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
100
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 COMMIT;