# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1533207999 -7200
# Node ID ac760b0f22a98d1c396283048c6ac3c8dd22fb16
# Parent  1a2dfd9351e9e24997d968d6e9f9dca24df05d43
Add special role for password reset

As password reset is exposed without requiring a login, let this role
have privileges limited to reseting passwords, and only reseting passwords.

diff -r 1a2dfd9351e9 -r ac760b0f22a9 controllers/pwreset.go
--- a/controllers/pwreset.go	Thu Aug 02 12:48:59 2018 +0200
+++ b/controllers/pwreset.go	Thu Aug 02 13:06:39 2018 +0200
@@ -23,9 +23,9 @@
 
 const (
 	userExistsSQL = `SELECT email_address
-    FROM users.list_users WHERE username = $1`
+    FROM pw_reset.list_users WHERE username = $1`
 
-	updatePasswordSQL = `UPDATE users.list_users
+	updatePasswordSQL = `UPDATE pw_reset.list_users
     SET pw = $1 WHERE username = $2`
 )
 
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/auth.sql
--- a/schema/auth.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/auth.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -30,6 +30,15 @@
 GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
 GRANT UPDATE ON sys_admin.system_config TO sys_admin;
 
+
+--
+-- Special privileges for pw_reset
+--
+GRANT USAGE ON SCHEMA pw_reset TO pw_reset;
+GRANT SELECT (username, email_address) ON pw_reset.list_users TO pw_reset;
+GRANT UPDATE (pw) ON pw_reset.list_users TO pw_reset;
+
+
 --
 -- RLS policies for waterway_user
 --
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/demo-data/roles.sql
--- a/schema/demo-data/roles.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/demo-data/roles.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -11,4 +11,7 @@
 CREATE ROLE oana IN ROLE waterway_user LOGIN PASSWORD 'oa2Na2';
 CREATE ROLE vanja IN ROLE waterway_user LOGIN PASSWORD 'va2Nja3';
 
+-- User for password reset
+CREATE ROLE paul IN ROLE pw_reset LOGIN PASSWORD 'pw2Reset4';
+
 COMMIT;
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/gemma.sql
--- a/schema/gemma.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/gemma.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -164,6 +164,11 @@
 ALTER TABLE internal.user_profiles ADD
     country char(2) NOT NULL REFERENCES users.responsibility_areas;
 
+
+-- Namespace intended to be the only one that pw_reset can access
+CREATE SCHEMA pw_reset;
+
+
 -- Namespace for waterway data that can change in a running system
 CREATE SCHEMA waterway
 
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/install-db.sh
--- a/schema/install-db.sh	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/install-db.sh	Thu Aug 02 13:06:39 2018 +0200
@@ -98,7 +98,7 @@
   if [[ $a == "yes" ]] ; then
     dropdb -p "$port" "$db"
     for r in `psql -p $port -t -c '\du' | awk -F '|' \
-          '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin/ \
+          '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset/ \
            {print $1}'`
     do
       dropuser -p "$port" "$r"
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/manage_users.sql
--- a/schema/manage_users.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/manage_users.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -52,6 +52,7 @@
         WHERE p.username = current_user
             OR pg_has_role('waterway_admin', 'MEMBER')
                 AND p.country = users.current_user_country()
+            OR pg_has_role('pw_reset', 'MEMBER')
             OR pg_has_role('sys_admin', 'MEMBER');
 
 
@@ -156,3 +157,7 @@
 $$
     LANGUAGE plpgsql
     SECURITY DEFINER;
+
+
+CREATE OR REPLACE VIEW pw_reset.list_users AS
+    SELECT username, pw, email_address FROM users.list_users;
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/manage_users_tests.sql
--- a/schema/manage_users_tests.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/manage_users_tests.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -255,3 +255,38 @@
     $$,
     55006, NULL,
     'Current user cannot be deleted');
+
+
+--
+-- Password reset
+--
+
+-- Workaround broken relocatability of pgtap (otherwise we could
+-- put pgtap in its own schema and GRANT USAGE to PUBLIC on it)
+RESET SESSION AUTHORIZATION;
+GRANT USAGE ON SCHEMA public TO pw_reset;
+
+SET SESSION AUTHORIZATION test_pw_reset;
+
+SELECT isnt_empty($$
+    SELECT username, email_address FROM pw_reset.list_users
+    $$,
+    'Special role can see users with their email addresses');
+
+SELECT results_eq($$
+    UPDATE pw_reset.list_users
+        SET pw = 'user_at2!' WHERE username = 'test_user_at'
+        RETURNING email_address
+    $$,
+    $$
+    SELECT email_address FROM pw_reset.list_users
+        WHERE username = 'test_user_at'
+    $$,
+    'Special role can update password');
+
+SELECT throws_ok($$
+    UPDATE pw_reset.list_users
+        SET username = 'test_rename', email_address = 'test'
+    $$,
+    42501, NULL,
+    'Special role cannot update arbitrary user attributes');
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/roles.sql
--- a/schema/roles.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/roles.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -4,3 +4,10 @@
 CREATE ROLE waterway_user;
 CREATE ROLE waterway_admin IN ROLE waterway_user;
 CREATE ROLE sys_admin IN ROLE waterway_admin;
+
+--
+-- Special roles
+--
+
+-- A role that is intended to be used for password reset only
+CREATE ROLE pw_reset;
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/run_tests.sh
--- a/schema/run_tests.sh	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/run_tests.sh	Thu Aug 02 13:06:39 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(39)' \
+    -c 'SELECT plan(42)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'
diff -r 1a2dfd9351e9 -r ac760b0f22a9 schema/tap_tests_data.sql
--- a/schema/tap_tests_data.sql	Thu Aug 02 12:48:59 2018 +0200
+++ b/schema/tap_tests_data.sql	Thu Aug 02 13:06:39 2018 +0200
@@ -17,6 +17,8 @@
 SELECT sys_admin.create_user(
     'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
 
+CREATE ROLE test_pw_reset IN ROLE pw_reset LOGIN PASSWORD 'ppp';
+
 INSERT INTO limiting_factors VALUES ('depth'), ('width');
 
 INSERT INTO waterway.gauges (