Mercurial > gemma

diff schema/manage_users_tests.sql @ 410:3f803d64a6ee

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not rely on session_user for authorization Privileges are usually checked based on current_user, which can be changed using SET ROLE, while session_user is based on the actually logged in user and can only be changed by a superuser using SET SESSION AUTHORIZATION. Using session_user for authorization purposes prevents the expected behaviour of SET ROLE. current_user_country() does not need to be SECURITY DEFINER since a while, because there is no RLS policy affected by what is mentioned in the removed comment.
author Tom Gottfried <tom@intevation.de>
date Wed, 15 Aug 2018 16:39:00 +0200
parents f5087cebc740
children 5611cf72cc92
line wrap: on
line diff
--- a/schema/manage_users_tests.sql	Wed Aug 15 15:57:36 2018 +0200
+++ b/schema/manage_users_tests.sql	Wed Aug 15 16:39:00 2018 +0200
@@ -138,6 +138,25 @@
     $$,
     'Waterway admin cannot update attributes of other users in country');
 
+-- The above test will pass even if the password is actually updated in case
+-- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
+RESET SESSION AUTHORIZATION;
+CREATE TEMP TABLE old_pw_hash AS
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
+SET SESSION AUTHORIZATION test_admin_at;
+UPDATE users.list_users
+    SET pw = 'test_user_at2!'
+    WHERE username = 'test_user_at';
+RESET SESSION AUTHORIZATION;
+SELECT set_eq($$
+    SELECT rolpassword FROM old_pw_hash
+    $$,
+    $$
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
+    $$,
+    'Waterway admin cannot update password of other users in country');
+
+
 SET SESSION AUTHORIZATION test_sys_admin1;
 
 SELECT lives_ok($$
@@ -223,8 +242,8 @@
 -- To compare passwords, we need to run the following tests as superuser
 RESET SESSION AUTHORIZATION;
 
-CREATE TEMP TABLE old_pw_hash AS
-    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
+UPDATE old_pw_hash SET rolpassword = (
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');
 
 UPDATE users.list_users
     SET (rolname, username, pw, country, map_extent, email_address)