Mercurial > gemma

diff schema/manage_users_tests.sql @ 307:750a9c9cd965

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use SQL UPDATE to update users This implies it's not a database error anymore to try to update a non-existent user. Thus, handle this as a HTTP-404 in the backend, which is in line with what GET does. Using UPDATE here will allow to GRANT column-wise privileges. The password has become part of the view to be updatable as well.
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Aug 2018 15:49:38 +0200
parents 72062ca52746
children ac760b0f22a9
line wrap: on
line diff
--- a/schema/manage_users_tests.sql	Wed Aug 01 15:18:26 2018 +0200
+++ b/schema/manage_users_tests.sql	Wed Aug 01 15:49:38 2018 +0200
@@ -105,74 +105,79 @@
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
         'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
-    SELECT sys_admin.update_user('test2',
-        'waterway_user', 'test2_new', 'new_secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test5')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test5')
+        WHERE username = 'test2'
     $$,
     'Existing user can be updated');
 
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_non_existent',
-        'waterway_user', 'test_non_existent', '', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test5')
-    $$,
-    42704, NULL,
-    'Non-existent user cannot be updated');
-
-SELECT throws_ok($$
-    SELECT sys_admin.update_user(CAST(current_user AS varchar),
-        'waterway_user', 'test_new_name', 'secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test6')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test6')
+        WHERE username = CAST(current_user AS varchar)
     $$,
     '0A000', NULL,
     'Name of current user cannot be altered');
 
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_user_at',
-        'invalid', 'test2', 'secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test2')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('invalid', 'test2', 'secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test2')
+        WHERE username = 'test_user_at'
     $$,
     42704, NULL,
     'Valid role name has to be provided');
 
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', NULL, 'secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test3')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', NULL, 'secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test3')
+        WHERE username = 'test_user_at'
     $$,
     23502, NULL,
     'New username is mandatory');
 -- Though other arguments are mandatory, too, there are no explicit tests
 
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', 'waterway_user', 'secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test4')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test4')
+        WHERE username = 'test_user_at'
     $$,
     42710, NULL,
     'Reserved role names cannot be used as username');
 
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', 'test_user_ro', 'secret1$', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test4')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test4')
+        WHERE username = 'test_user_at'
     $$,
     23505, NULL,
     'No duplicate user name is allowed');
 
 -- Test password policy (only one rule to ensure it's also used on update)
 SELECT throws_ok($$
-    SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', 'test_user_at', 'secret', 'AT',
-        (SELECT map_extent FROM users.list_users
-            WHERE username = 'test_user_at'), 'test4')
+    UPDATE users.list_users
+        SET (rolname, username, pw, country, map_extent, email_address)
+            = ('waterway_user', 'test_user_at', 'secret', 'AT',
+                (SELECT map_extent FROM users.list_users
+                    WHERE username = 'test_user_at'), 'test4')
+        WHERE username = 'test_user_at'
     $$,
     '28P01', NULL,
     'Non-compliant password is not accepted');
@@ -183,10 +188,12 @@
 CREATE TEMP TABLE old_pw_hash AS
     SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
 
-SELECT sys_admin.update_user('test_user_at',
-    'waterway_user', 'test_user_at', NULL, 'AT',
-    (SELECT map_extent FROM internal.user_profiles
-        WHERE username = 'test_user_at'), 'xxx');
+UPDATE users.list_users
+    SET (rolname, username, pw, country, map_extent, email_address)
+        = ('waterway_user', 'test_user_at', NULL, 'AT',
+            (SELECT map_extent FROM internal.user_profiles
+                WHERE username = 'test_user_at'), 'xxx')
+    WHERE username = 'test_user_at';
 SELECT set_eq($$
     SELECT rolpassword FROM old_pw_hash
     $$,
@@ -195,10 +202,12 @@
     $$,
     'Giving NULL password does not change password');
 
-SELECT sys_admin.update_user('test_user_at',
-    'waterway_user', 'test_user_at', '', 'AT',
-    (SELECT map_extent FROM internal.user_profiles
-        WHERE username = 'test_user_at'), 'xxx');
+UPDATE users.list_users
+    SET (rolname, username, pw, country, map_extent, email_address)
+        = ('waterway_user', 'test_user_at', '', 'AT',
+            (SELECT map_extent FROM internal.user_profiles
+                WHERE username = 'test_user_at'), 'xxx')
+    WHERE username = 'test_user_at';
 SELECT set_eq($$
     SELECT rolpassword FROM old_pw_hash
     $$,
@@ -207,10 +216,12 @@
     $$,
     'Giving empty string as password does not change password');
 
-SELECT sys_admin.update_user('test_user_at',
-    'waterway_user', 'test_user_at', 'new_pw1$', 'AT',
-    (SELECT map_extent FROM internal.user_profiles
-        WHERE username = 'test_user_at'), 'xxx');
+UPDATE users.list_users
+    SET (rolname, username, pw, country, map_extent, email_address)
+        = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
+            (SELECT map_extent FROM internal.user_profiles
+                WHERE username = 'test_user_at'), 'xxx')
+    WHERE username = 'test_user_at';
 SELECT set_ne($$
     SELECT rolpassword FROM old_pw_hash
     $$,