Mercurial > gemma

changeset 175:3f7053e53fa6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Allow waterway_admin to see profiles of users in same country Not doing this prevented the manage_templates POLICY from working as expected, because user_profiles is JOINed in its USING clause.
author Tom Gottfried <tom@intevation.de>
date Fri, 13 Jul 2018 14:56:32 +0200
parents 68fd656c3d55
children 0c0826063561
files schema/auth.sql schema/tap_tests.sql
diffstat 2 files changed, 15 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Thu Jul 12 17:43:11 2018 +0200
+++ b/schema/auth.sql	Fri Jul 13 14:56:32 2018 +0200
@@ -65,18 +65,30 @@
 -- RLS policies for waterway_admin
 --
 
+-- Security-definer function to get current users country, which allows to
+-- restrict the view on user_profiles by country without infinite recursion
+CREATE FUNCTION current_user_country()
+       RETURNS gemma.user_profiles.country%TYPE
+       AS $$ SELECT country FROM user_profiles WHERE username = session_user $$
+       LANGUAGE SQL
+       SECURITY DEFINER
+       STABLE PARALLEL SAFE;
+
 -- Staging area
 -- TODO: add all relevant tables here
 CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin
        USING (ST_Within(area, (SELECT area FROM responsibility_areas
-           WHERE country = (SELECT country FROM user_profiles))));
+           WHERE country = current_user_country())));
 CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin
        USING (ST_Within(area, (SELECT area FROM responsibility_areas
-           WHERE country = (SELECT country FROM user_profiles))));
+           WHERE country = current_user_country())));
+
+CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin
+       USING (country = current_user_country());
 
 CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
        USING (id IN(SELECT template_id FROM user_templates ut
                     JOIN user_profiles p ON ut.username = p.username
-                    WHERE p.country = (SELECT country FROM user_profiles)));
+                    WHERE p.country = current_user_country()));
 
 COMMIT;
--- a/schema/tap_tests.sql	Thu Jul 12 17:43:11 2018 +0200
+++ b/schema/tap_tests.sql	Fri Jul 13 14:56:32 2018 +0200
@@ -66,8 +66,6 @@
                       4326))', 42501, NULL,
                  'Waterway admin cannot insert data outside his region');
 
--- XXX: Why does this fail? POLICY manage_templates should allow to see
--- the template of waterway_user.
 SELECT isnt_empty('SELECT * FROM templates t
                    JOIN user_templates ut ON t.id = template_id
                    WHERE username <> current_user',