Mercurial > gemma

annotate schema/auth.sql @ 263:13ad969a9138

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Enable listing of users for all roles with appropriate filters Waterway users should see their own account data and their should be a single interface for account data (i.e. users.list_users). Therefore, also the RLS policy on user_profiles for waterway_admin is translated to the view. current_user_country() moved because it's needed earlier during database setup, now.
author Tom Gottfried <tom@intevation.de>
date Fri, 27 Jul 2018 19:03:56 +0200
parents 946baea3d280
children 72062ca52746
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;
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 210
diff changeset
30 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
31 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
32
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 -- RLS policies for waterway_user
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 --
180
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
36 -- 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
37 -- data modifications generally.
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
38 -- 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
39 -- 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
40 -- 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
41 -- policies on user_profiles depending on current_user).
0423eab4ad45 Improve RLS policies for template data
Tom Gottfried <tom@intevation.de>
parents: 179
diff changeset
42 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 -- Staging area
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
45 CREATE FUNCTION create_hide_staging_policy()
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
46 RETURNS void
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
47 AS $$
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 DECLARE the_table varchar;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 BEGIN
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 FOREACH the_table IN ARRAY ARRAY[
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 'bottlenecks', 'sounding_results']
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 -- TODO: add all relevant tables here
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 LOOP
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
54 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
55 '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
56 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
57 the_table);
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 END LOOP;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 END;
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
60 $$
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
61 LANGUAGE plpgsql;
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 SELECT create_hide_staging_policy();
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 DROP FUNCTION create_hide_staging_policy;
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
65 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
66 USING (username = current_user);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
67 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
68
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
69 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
70 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
71 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
72
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
73 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
74 USING (template_name IN(SELECT template_name FROM users.user_templates))
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
75 WITH CHECK (true);
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
76 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
96
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 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 -- RLS policies for waterway_admin
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 --
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 -- Staging area
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 -- TODO: add all relevant tables here
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
84 CREATE POLICY responsibility_area ON waterway.bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
85 FOR ALL TO waterway_admin
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
86 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
87 WHERE country = current_user_country())));
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
88 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
89 FOR ALL TO waterway_admin
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
90 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
91 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
92
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
93 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
94 FOR SELECT TO waterway_admin
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 180
diff changeset
95 USING (country = current_user_country());
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 COMMIT;