# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1532626422 -7200
# Node ID 946baea3d28069f8c72f3ac63f089814d1923626
# Parent  299568ad3c377f3f61fbf145b66f120d385b99c3
Add view to list user profiles with role

To be able to GRANT privileges ON ALL TABLES IN SCHEMA,
use auth.sql last during database setup.

diff -r 299568ad3c37 -r 946baea3d280 schema/auth.sql
--- a/schema/auth.sql	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/auth.sql	Thu Jul 26 19:33:42 2018 +0200
@@ -27,6 +27,7 @@
 GRANT INSERT, UPDATE, DELETE
     ON users.responsibility_areas TO sys_admin;
 GRANT USAGE ON SCHEMA sys_admin TO sys_admin;
+GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
 GRANT UPDATE ON sys_admin.system_config TO sys_admin;
 
 --
diff -r 299568ad3c37 -r 946baea3d280 schema/install-db.sh
--- a/schema/install-db.sh	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/install-db.sh	Thu Jul 26 19:33:42 2018 +0200
@@ -71,8 +71,11 @@
 
 psql -p "$port" -f "$BASEDIR/roles.sql"
 createdb -p "$port" "$db"
-psql -p "$port" -f "$BASEDIR/gemma.sql" \
-     -f "$BASEDIR/auth.sql" -f "$BASEDIR/manage_users.sql" -d "$db"
+psql -p "$port" -d "$db" \
+     -f "$BASEDIR/gemma.sql" \
+     -f "$BASEDIR/manage_users.sql" \
+     -f "$BASEDIR/auth.sql"
+
 if [[ $demo -eq 1 ]] ; then
   psql -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \
        -d "$db" 
diff -r 299568ad3c37 -r 946baea3d280 schema/manage_users.sql
--- a/schema/manage_users.sql	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/manage_users.sql	Thu Jul 26 19:33:42 2018 +0200
@@ -3,6 +3,14 @@
 -- exposing it to privileged users
 --
 
+CREATE OR REPLACE VIEW sys_admin.list_users AS
+    SELECT r.rolname, p.*
+        FROM 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;
+
+
 CREATE OR REPLACE FUNCTION sys_admin.create_user(
        userrole varchar,
        username users.user_profiles.username%TYPE,
diff -r 299568ad3c37 -r 946baea3d280 schema/manage_users_tests.sql
--- a/schema/manage_users_tests.sql	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/manage_users_tests.sql	Thu Jul 26 19:33:42 2018 +0200
@@ -16,6 +16,14 @@
 SET SESSION AUTHORIZATION test_sys_admin1;
 
 --
+-- Role listing
+--
+SELECT isnt_empty($$
+    SELECT * FROM sys_admin.list_users
+    $$,
+    'List of users can be queried');
+
+--
 -- Role creation
 --
 SELECT lives_ok($$
diff -r 299568ad3c37 -r 946baea3d280 schema/run_tests.sh
--- a/schema/run_tests.sh	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/run_tests.sh	Thu Jul 26 19:33:42 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(35)' \
+    -c 'SELECT plan(36)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'