Mercurial > gemma

comparison schema/auth.sql @ 345:b97b3172c61a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add staging feature to more tables Added tables currently only have limited visibility for waterway_user but not yet policies for write access.
author Tom Gottfried <tom@intevation.de>
date Mon, 06 Aug 2018 15:19:05 +0200
parents 363983d5c567
children fe87457a05d7
comparison
equal deleted inserted replaced
344:e98033e3683a 345:b97b3172c61a
52 -- allowed more with these policies (note that the subselect implies different 52 -- allowed more with these policies (note that the subselect implies different
53 -- filtering on list_users depending on current_user). 53 -- filtering on list_users depending on current_user).
54 -- 54 --
55 55
56 -- Staging area 56 -- Staging area
57 CREATE FUNCTION create_hide_staging_policy() 57 DO LANGUAGE plpgsql
58 RETURNS void 58 $$
59 AS $$
60 DECLARE the_table varchar; 59 DECLARE the_table varchar;
61 BEGIN 60 BEGIN
62 FOREACH the_table IN ARRAY ARRAY[ 61 FOREACH the_table IN ARRAY ARRAY[
63 'bottlenecks', 'sounding_results'] 62 'gauge_measurements',
64 -- TODO: add all relevant tables here 63 'sections_stretches',
64 'waterway_profiles',
65 'fairway_dimensions',
66 'bottlenecks',
67 'sounding_results']
65 LOOP 68 LOOP
66 EXECUTE format('CREATE POLICY hide_staging ON waterway.%I ' 69 EXECUTE format('CREATE POLICY hide_staging ON waterway.%I '
67 'FOR SELECT TO waterway_user USING (staging_done)', the_table); 70 'FOR SELECT TO waterway_user USING (staging_done)', the_table);
68 EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY', 71 EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY',
69 the_table); 72 the_table);
70 END LOOP; 73 END LOOP;
71 END; 74 END;
72 $$ 75 $$;
73 LANGUAGE plpgsql;
74 SELECT create_hide_staging_policy();
75 DROP FUNCTION create_hide_staging_policy;
76 76
77 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user 77 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
78 USING (username IN(SELECT username FROM users.list_users)); 78 USING (username IN(SELECT username FROM users.list_users));
79 ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; 79 ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY;
80 80