Mercurial > gemma

comparison schema/auth.sql @ 182:4df4e4bf480e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Beautify SQL E.g. indent with 4 spaces more consequently.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Jul 2018 16:48:27 +0200
parents 0423eab4ad45
children 5dc8e734487a
comparison
equal deleted inserted replaced
181:e509eccff303 182:4df4e4bf480e
11 -- 11 --
12 -- Privileges for waterway_user 12 -- Privileges for waterway_user
13 -- 13 --
14 GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user; 14 GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user;
15 GRANT SELECT ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway 15 GRANT SELECT ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
16 TO waterway_user; 16 TO waterway_user;
17 GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user; 17 GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;
18 18
19 -- 19 --
20 -- Extended privileges for waterway_admin 20 -- Extended privileges for waterway_admin
21 -- 21 --
22 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway 22 GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
23 TO waterway_admin; 23 TO waterway_admin;
24 -- TODO: will there ever be UPDATEs or can we drop that due to historicisation? 24 -- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
25 GRANT INSERT, UPDATE, DELETE ON templates, user_templates TO waterway_admin; 25 GRANT INSERT, UPDATE, DELETE ON templates, user_templates TO waterway_admin;
26 GRANT SELECT ON responsibility_areas TO waterway_admin; 26 GRANT SELECT ON responsibility_areas TO waterway_admin;
27 27
28 -- 28 --
29 -- Extended privileges for sys_admin 29 -- Extended privileges for sys_admin
30 -- 30 --
31 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma 31 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma
32 TO sys_admin; 32 TO sys_admin;
33 33
34 -- 34 --
35 -- RLS policies for waterway_user 35 -- RLS policies for waterway_user
36 -- 36 --
37 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing 37 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing
41 -- allowed more with these policies (note that the subselect implies different 41 -- allowed more with these policies (note that the subselect implies different
42 -- policies on user_profiles depending on current_user). 42 -- policies on user_profiles depending on current_user).
43 -- 43 --
44 44
45 -- Staging area 45 -- Staging area
46 CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$ 46 CREATE FUNCTION create_hide_staging_policy()
47 RETURNS void
48 AS $$
47 DECLARE the_table varchar; 49 DECLARE the_table varchar;
48 BEGIN 50 BEGIN
49 FOREACH the_table IN ARRAY ARRAY[ 51 FOREACH the_table IN ARRAY ARRAY[
50 'bottlenecks', 'sounding_results'] 52 'bottlenecks', 'sounding_results']
51 -- TODO: add all relevant tables here 53 -- TODO: add all relevant tables here
53 EXECUTE format('CREATE POLICY hide_staging ON %I ' 55 EXECUTE format('CREATE POLICY hide_staging ON %I '
54 'FOR SELECT TO waterway_user USING (staging_done)', the_table); 56 'FOR SELECT TO waterway_user USING (staging_done)', the_table);
55 EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table); 57 EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table);
56 END LOOP; 58 END LOOP;
57 END; 59 END;
58 $$ LANGUAGE plpgsql; 60 $$
61 LANGUAGE plpgsql;
59 SELECT create_hide_staging_policy(); 62 SELECT create_hide_staging_policy();
60 DROP FUNCTION create_hide_staging_policy; 63 DROP FUNCTION create_hide_staging_policy;
61 64
62 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user 65 CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
63 USING (username = current_user); 66 USING (username = current_user);
64 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; 67 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
65 68
66 CREATE POLICY user_templates ON user_templates FOR ALL TO waterway_user 69 CREATE POLICY user_templates ON user_templates FOR ALL TO waterway_user
67 USING (username IN(SELECT username FROM user_profiles)); 70 USING (username IN(SELECT username FROM user_profiles));
68 ALTER TABLE user_templates ENABLE ROW LEVEL SECURITY; 71 ALTER TABLE user_templates ENABLE ROW LEVEL SECURITY;
69 72
70 CREATE POLICY user_templates ON templates FOR ALL TO waterway_user 73 CREATE POLICY user_templates ON templates FOR ALL TO waterway_user
71 USING (template_name IN(SELECT template_name FROM user_templates)) 74 USING (template_name IN(SELECT template_name FROM user_templates))
72 WITH CHECK (true); 75 WITH CHECK (true);
73 ALTER TABLE templates ENABLE ROW LEVEL SECURITY; 76 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
74 77
75 -- 78 --
76 -- RLS policies for waterway_admin 79 -- RLS policies for waterway_admin
77 -- 80 --
78 81
79 -- Security-definer function to get current users country, which allows to 82 -- Security-definer function to get current users country, which allows to
80 -- restrict the view on user_profiles by country without infinite recursion 83 -- restrict the view on user_profiles by country without infinite recursion
81 CREATE FUNCTION current_user_country() 84 CREATE FUNCTION current_user_country()
82 RETURNS gemma.user_profiles.country%TYPE 85 RETURNS gemma.user_profiles.country%TYPE
83 AS $$ SELECT country FROM user_profiles WHERE username = session_user $$ 86 AS $$ SELECT country FROM user_profiles WHERE username = session_user $$
84 LANGUAGE SQL 87 LANGUAGE SQL
85 SECURITY DEFINER 88 SECURITY DEFINER
86 STABLE PARALLEL SAFE; 89 STABLE PARALLEL SAFE;
87 90
88 -- Staging area 91 -- Staging area
89 -- TODO: add all relevant tables here 92 -- TODO: add all relevant tables here
90 CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin 93 CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin
91 USING (ST_Within(area, (SELECT area FROM responsibility_areas 94 USING (ST_Within(area, (SELECT area FROM responsibility_areas
92 WHERE country = current_user_country()))); 95 WHERE country = current_user_country())));
93 CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin 96 CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin
94 USING (ST_Within(area, (SELECT area FROM responsibility_areas 97 USING (ST_Within(area, (SELECT area FROM responsibility_areas
95 WHERE country = current_user_country()))); 98 WHERE country = current_user_country())));
96 99
97 CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin 100 CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin
98 USING (country = current_user_country()); 101 USING (country = current_user_country());
99 102
100 COMMIT; 103 COMMIT;