# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1532941697 -7200
# Node ID 7f030ec3472dc6ff62e849a4c9a951715a349c56
# Parent  a2f7049daf7f0e1867df9bbe90300ad0207e5c11# Parent  13ad969a9138edc05d0825e42b039f250535c2d9
Merged

diff -r a2f7049daf7f -r 7f030ec3472d schema/auth.sql
--- a/schema/auth.sql	Mon Jul 30 11:06:50 2018 +0200
+++ b/schema/auth.sql	Mon Jul 30 11:08:17 2018 +0200
@@ -79,17 +79,6 @@
 -- 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 users.user_profiles.country%TYPE
-    AS $$
-        SELECT country FROM users.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 waterway.bottlenecks
diff -r a2f7049daf7f -r 7f030ec3472d schema/manage_users.sql
--- a/schema/manage_users.sql	Mon Jul 30 11:06:50 2018 +0200
+++ b/schema/manage_users.sql	Mon Jul 30 11:08:17 2018 +0200
@@ -24,12 +24,33 @@
     LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE VIEW sys_admin.list_users AS
+-- 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 users.user_profiles.country%TYPE
+    AS $$
+        SELECT country FROM users.user_profiles WHERE username = session_user
+    $$
+    LANGUAGE SQL
+    SECURITY DEFINER
+    STABLE PARALLEL SAFE;
+
+
+CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
+    WITH cur AS (
+        SELECT rolname
+            FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
+            WHERE member = (
+                SELECT oid FROM pg_roles WHERE rolname = current_user))
     SELECT r.rolname, p.*
-        FROM users.user_profiles p
+        FROM cur, users.user_profiles p
             JOIN pg_roles u ON p.username = u.rolname
             JOIN pg_auth_members a ON u.oid = a.member
-            JOIN pg_roles r ON a.roleid = r.oid;
+            JOIN pg_roles r ON a.roleid = r.oid
+        WHERE p.username = current_user
+            OR cur.rolname = 'waterway_admin'
+                AND p.country = current_user_country()
+            OR cur.rolname = 'sys_admin';
 
 
 CREATE OR REPLACE FUNCTION sys_admin.create_user(
diff -r a2f7049daf7f -r 7f030ec3472d schema/manage_users_tests.sql
--- a/schema/manage_users_tests.sql	Mon Jul 30 11:06:50 2018 +0200
+++ b/schema/manage_users_tests.sql	Mon Jul 30 11:08:17 2018 +0200
@@ -13,15 +13,31 @@
     42501, NULL,
     'Less privileged user cannot call function in schema sys_admin');
 
-SET SESSION AUTHORIZATION test_sys_admin1;
-
 --
 -- Role listing
 --
-SELECT isnt_empty($$
-    SELECT * FROM sys_admin.list_users
+SET SESSION AUTHORIZATION test_user_at;
+SELECT results_eq($$
+    SELECT username FROM users.list_users
+    $$,
+    $$
+    SELECT CAST(current_user AS varchar)
     $$,
-    'List of users can be queried');
+    'User should only see his own profile');
+
+SET SESSION AUTHORIZATION test_admin_at;
+SELECT set_eq($$
+    SELECT DISTINCT country FROM users.list_users
+    $$,
+    ARRAY['AT'],
+    'Waterway admin should only see profiles of his country');
+
+SET SESSION AUTHORIZATION test_sys_admin1;
+SELECT set_eq($$
+    SELECT count(*) FROM users.list_users
+    $$,
+    ARRAY[4],
+    'System admin can see all users');
 
 --
 -- Role creation
diff -r a2f7049daf7f -r 7f030ec3472d schema/run_tests.sh
--- a/schema/run_tests.sh	Mon Jul 30 11:06:50 2018 +0200
+++ b/schema/run_tests.sh	Mon Jul 30 11:08:17 2018 +0200
@@ -15,7 +15,7 @@
 psql -qXv ON_ERROR_STOP= -v -d gemma_test \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(40)' \
+    -c 'SELECT plan(42)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'