Mercurial > gemma

comparison schema/auth.sql @ 268:72062ca52746

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make user_profiles table invisible for users users.list_users should be the single point to access user profile data. Keeping user_profiles visible would imply having to maintain RLS policies that are otherwise obsolete. Tests run as superuser still use user_profiles, because list_users does not show any data to a superuser.
author Tom Gottfried <tom@intevation.de>
date Mon, 30 Jul 2018 11:38:09 +0200
parents 13ad969a9138
children 750a9c9cd965
comparison
equal deleted inserted replaced
267:7f030ec3472d 268:72062ca52746
33 -- 33 --
34 -- RLS policies for waterway_user 34 -- RLS policies for waterway_user
35 -- 35 --
36 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing 36 -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing
37 -- data modifications generally. 37 -- data modifications generally.
38 -- Sometimes using 'username IN(SELECT username FROM user_profiles)' instead 38 -- Sometimes using 'username IN(SELECT username FROM users.list_users)' instead
39 -- of 'username = current_user', because waterway_admin is intentionally 39 -- of 'username = current_user', because waterway_admin is intentionally
40 -- allowed more with these policies (note that the subselect implies different 40 -- allowed more with these policies (note that the subselect implies different
41 -- policies on user_profiles depending on current_user). 41 -- filtering on list_users depending on current_user).
42 -- 42 --
43 43
44 -- Staging area 44 -- Staging area
45 CREATE FUNCTION create_hide_staging_policy() 45 CREATE FUNCTION create_hide_staging_policy()
46 RETURNS void 46 RETURNS void
60 $$ 60 $$
61 LANGUAGE plpgsql; 61 LANGUAGE plpgsql;
62 SELECT create_hide_staging_policy(); 62 SELECT create_hide_staging_policy();
63 DROP FUNCTION create_hide_staging_policy; 63 DROP FUNCTION create_hide_staging_policy;
64 64
65 CREATE POLICY see_yourself ON users.user_profiles FOR SELECT TO waterway_user
66 USING (username = current_user);
67 ALTER TABLE users.user_profiles ENABLE ROW LEVEL SECURITY;
68
69 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user 65 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
70 USING (username IN(SELECT username FROM users.user_profiles)); 66 USING (username IN(SELECT username FROM users.list_users));
71 ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; 67 ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY;
72 68
73 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user 69 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user
74 USING (template_name IN(SELECT template_name FROM users.user_templates)) 70 USING (template_name IN(SELECT template_name FROM users.user_templates))
75 WITH CHECK (true); 71 WITH CHECK (true);
88 CREATE POLICY responsibility_area ON waterway.sounding_results 84 CREATE POLICY responsibility_area ON waterway.sounding_results
89 FOR ALL TO waterway_admin 85 FOR ALL TO waterway_admin
90 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas 86 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
91 WHERE country = current_user_country()))); 87 WHERE country = current_user_country())));
92 88
93 CREATE POLICY country_profiles ON users.user_profiles
94 FOR SELECT TO waterway_admin
95 USING (country = current_user_country());
96
97 COMMIT; 89 COMMIT;