Mercurial > gemma

annotate schema/auth.sql @ 1168:930fdd8b474f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Track successfull imports in a separate table to be able to remove them later.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 14 Nov 2018 12:23:10 +0100
parents da0a62fcfd80
children d395b2940a82
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
1168
930fdd8b474f Track successfull imports in a separate table to be able to remove them later.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1132
diff changeset
27 waterway.imports, waterway.import_logs, waterway.track_imports 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;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
40
463
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 -- 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
43 --
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
44 -- 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
45 GRANT USAGE ON SCHEMA public TO metamorph;
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 313
diff changeset
46
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
50 -- 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
51 -- data modifications generally.
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 263
diff changeset
52 -- 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
53 -- 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
54 -- 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
55 -- 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
56 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 -- Staging area
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
59 DO LANGUAGE plpgsql
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
60 $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 FOREACH the_table IN ARRAY ARRAY[
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
64 'gauge_measurements',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
65 'sections_stretches',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
66 'waterway_profiles',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
67 'fairway_dimensions',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
68 'bottlenecks',
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
69 'sounding_results']
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
71 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
72 '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
73 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
74 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 END;
345
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
77 $$;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
79 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
80 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
81 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
82
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
83 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
84 USING (template_name IN(SELECT template_name FROM users.user_templates))
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
85 WITH CHECK (true);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
86 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87
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 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 --
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 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 -- TODO: add all relevant tables here
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
94 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
95 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
96 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
97 WHERE country = users.current_user_country())));
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
98 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
99 FOR ALL TO waterway_admin
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
100 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
101 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
102
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 COMMIT;