Mercurial > gemma
changeset 175:3f7053e53fa6
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',