Mercurial > gemma

comparison schema/auth.sql @ 113:25b28fd0e256

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved schema stuff to own subfolder.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Jun 2018 16:14:59 +0200
parents auth.sql@767f3edc502c
children d349db18bece
comparison
equal deleted inserted replaced
112:894f633e2d3f 113:25b28fd0e256
1 BEGIN;
2
3 --
4 -- Roles, privileges and policies for the WAMOS database
5 --
6 SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
7
8 -- We do not want any users to be able to create any objects
9 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
10
11 --
12 -- Primary WAMOS roles (SRS table 3)
13 --
14 CREATE ROLE waterway_user;
15 CREATE ROLE waterway_admin IN ROLE waterway_user;
16 CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin;
17
18 --
19 -- Privileges for waterway_user
20 --
21 GRANT USAGE ON SCHEMA wamos, wamos_waterway, wamos_fairway TO waterway_user;
22 GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
23 TO waterway_user;
24 GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;
25
26 --
27 -- Extended privileges for waterway_admin
28 --
29 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
30 TO waterway_admin;
31 -- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
32 GRANT INSERT, UPDATE ON templates, user_templates TO waterway_admin;
33 GRANT SELECT ON responsibility_areas TO waterway_admin;
34
35 --
36 -- Extended privileges for sys_admin
37 --
38 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos
39 TO sys_admin;
40
41 --
42 -- RLS policies for waterway_user
43 --
44
45 -- Staging area
46 CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$
47 DECLARE the_table varchar;
48 BEGIN
49 FOREACH the_table IN ARRAY ARRAY[
50 'bottlenecks', 'sounding_results']
51 -- TODO: add all relevant tables here
52 LOOP
53 EXECUTE format('CREATE POLICY hide_staging ON %I '
54 'FOR SELECT TO waterway_user USING (staging_done)', the_table);
55 EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table);
56 END LOOP;
57 END;
58 $$ LANGUAGE plpgsql;
59 SELECT create_hide_staging_policy();
60 DROP FUNCTION create_hide_staging_policy;
61
62 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
63 USING (username = current_user);
64 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
65
66 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
67 USING (id IN(SELECT template_id FROM user_templates
68 WHERE username = current_user));
69 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
70
71 --
72 -- RLS policies for waterway_admin
73 --
74
75 -- Staging area
76 -- TODO: add all relevant tables here
77 CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin
78 USING (ST_Within(area, (SELECT area FROM responsibility_areas
79 WHERE country = (SELECT country FROM user_profiles))));
80 CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin
81 USING (ST_Within(area, (SELECT area FROM responsibility_areas
82 WHERE country = (SELECT country FROM user_profiles))));
83
84 CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
85 USING (id IN(SELECT template_id FROM user_templates ut
86 JOIN user_profiles p ON ut.username = p.username
87 WHERE p.country = (SELECT country FROM user_profiles)));
88
89 COMMIT;