Mercurial > gemma

annotate schema/auth.sql @ 238:2b39bf2bf1fd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
If no error code was given in a JSONResult assume Status OK (200).
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 26 Jul 2018 17:09:43 +0200
parents a0e2c6bb3cb3
children 946baea3d280
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
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
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 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
13 GRANT USAGE ON SCHEMA users, waterway TO waterway_user;
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;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 -- Extended privileges for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
19 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
20 -- 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
21 GRANT INSERT, UPDATE, DELETE ON
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
22 users.templates, users.user_templates TO waterway_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
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 -- Extended privileges for sys_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
27 GRANT INSERT, UPDATE, DELETE
210
a0e2c6bb3cb3 Remove obsolete GRANT on user_profiles
Tom Gottfried <tom@intevation.de>
parents: 195
diff changeset
28 ON users.responsibility_areas TO sys_admin;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
29 GRANT USAGE ON SCHEMA sys_admin TO sys_admin;
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
30 GRANT UPDATE ON sys_admin.system_config TO sys_admin;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
35 -- 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
36 -- data modifications generally.
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
37 -- Sometimes using 'username IN(SELECT username FROM user_profiles)' instead
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
38 -- 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
39 -- allowed more with these policies (note that the subselect implies different
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
40 -- policies on user_profiles depending on current_user).
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
41 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 -- Staging area
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
44 CREATE FUNCTION create_hide_staging_policy()
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
45 RETURNS void
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
46 AS $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 FOREACH the_table IN ARRAY ARRAY[
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 'bottlenecks', 'sounding_results']
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 -- TODO: add all relevant tables here
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
53 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
54 '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
55 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
56 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 END;
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
59 $$
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
60 LANGUAGE plpgsql;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 SELECT create_hide_staging_policy();
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 DROP FUNCTION create_hide_staging_policy;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
64 CREATE POLICY see_yourself ON users.user_profiles FOR SELECT TO waterway_user
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
65 USING (username = current_user);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
66 ALTER TABLE users.user_profiles ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
68 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
69 USING (username IN(SELECT username FROM users.user_profiles));
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
70 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
71
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
72 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
73 USING (template_name IN(SELECT template_name FROM users.user_templates))
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
74 WITH CHECK (true);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
75 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80
175
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
81 -- Security-definer function to get current users country, which allows to
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
82 -- restrict the view on user_profiles by country without infinite recursion
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
83 CREATE FUNCTION current_user_country()
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
84 RETURNS users.user_profiles.country%TYPE
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
85 AS $$
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
86 SELECT country FROM users.user_profiles WHERE username = session_user
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
87 $$
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
88 LANGUAGE SQL
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
89 SECURITY DEFINER
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
90 STABLE PARALLEL SAFE;
175
3f7053e53fa6 Allow waterway_admin to see profiles of users in same country
Tom Gottfried <tom@intevation.de>
parents: 172
diff changeset
91
96
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
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
96 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
97 WHERE country = 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
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
100 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
101 WHERE country = 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
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
103 CREATE POLICY country_profiles ON users.user_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
104 FOR SELECT TO waterway_admin
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
105 USING (country = current_user_country());
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 COMMIT;