Mercurial > gemma

annotate schema/auth.sql @ 1063:7ec2133c6404

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: add area measurement. simpify code * Add a third draw mode which can only be activated when no morphology is selected and we are already in LineString mode. It adds an area calculation. Because the Polygon drawMode ends on a double click, there needs to be an extra callback for this to run identify so that the area calculation is shown all times. * Add Bernhard as author to some files and also simplify copyright note. * Remove DRAWMODES in the code to simplify as this is just one indirection used once in stores/application.js. * Use mapState instead mapGetters to get the drawMode at all places to save some code lines.
author Bernhard Reiter <bernhard@intevation.de>
date Thu, 25 Oct 2018 23:16:53 +0200
parents a04126989d91
children da0a62fcfd80
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;
1037
a04126989d91 Added endpoint to delete job from import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 871
diff changeset
26 GRANT INSERT, UPDATE, DELETE ON
a04126989d91 Added endpoint to delete job from import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 871
diff changeset
27 waterway.imports, waterway.import_logs TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
32 GRANT INSERT, UPDATE, DELETE
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
33 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
34 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
35 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
36 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
37 GRANT UPDATE ON systemconf.feature_colours TO sys_admin;
871
f0b6852c14d1 More on uploading styles to gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 846
diff changeset
38 GRANT UPDATE ON sys_admin.published_services TO sys_admin;
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
39 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
40 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
41
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
42 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
43 -- 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
44 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
45 -- 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
46 GRANT USAGE ON SCHEMA public TO metamorph;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
47
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
51 -- 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
52 -- data modifications generally.
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
53 -- 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
54 -- 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
55 -- 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
56 -- 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
57 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 -- Staging area
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
60 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
61 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 FOREACH the_table IN ARRAY ARRAY[
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
65 'gauge_measurements',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
66 'sections_stretches',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
67 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
68 'fairway_dimensions',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
69 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
70 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
72 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
73 '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
74 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
75 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 END;
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
78 $$;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
80 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
81 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
82 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
83
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
84 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
85 USING (template_name IN(SELECT template_name FROM users.user_templates))
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
86 WITH CHECK (true);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
87 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 -- TODO: add all relevant tables here
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
95 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
96 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
97 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
98 WHERE country = users.current_user_country())));
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
99 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
100 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
101 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
102 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
103
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 COMMIT;