Mercurial > gemma

changeset 225:8b9cae6d3a21

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add database function to update role and user profile
author Tom Gottfried <tom@intevation.de>
date Wed, 25 Jul 2018 18:26:54 +0200
parents 57dfab80973c
children 63dd5216eee4
files schema/gemma.sql schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh
diffstat 4 files changed, 170 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Jul 25 13:34:46 2018 +0200
+++ b/schema/gemma.sql	Wed Jul 25 18:26:54 2018 +0200
@@ -149,7 +149,8 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE user_templates (
-        username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
+        username varchar NOT NULL
+            REFERENCES user_profiles ON DELETE CASCADE ON UPDATE CASCADE,
         template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
         PRIMARY KEY (username, template_name)
     )
--- a/schema/manage_users.sql	Wed Jul 25 13:34:46 2018 +0200
+++ b/schema/manage_users.sql	Wed Jul 25 18:26:54 2018 +0200
@@ -27,3 +27,57 @@
 $$
     LANGUAGE plpgsql
     SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION sys_admin.update_user(
+       username users.user_profiles.username%TYPE,
+       new_userrole varchar,
+       new_username users.user_profiles.username%TYPE,
+       new_pw varchar,
+       new_country users.user_profiles.country%TYPE,
+       new_map_extent users.user_profiles.map_extent%TYPE,
+       new_email_adress users.user_profiles.email_adress%TYPE
+    )
+    RETURNS void
+AS $$
+DECLARE
+    cur_username name;
+    cur_userrole name;
+BEGIN
+    cur_username = username;
+
+    UPDATE users.user_profiles p
+        SET (username, country, map_extent, email_adress)
+        = (new_username, new_country, new_map_extent, new_email_adress)
+        WHERE p.username = cur_username;
+
+    IF new_username <> cur_username
+    THEN
+        EXECUTE format(
+            'ALTER ROLE %I RENAME TO %I', username, new_username);
+        cur_username = new_username;
+    END IF;
+
+    cur_userrole = rolname FROM pg_roles r
+        JOIN pg_auth_members a ON r.oid = a.roleid
+        WHERE member = (
+            SELECT oid FROM pg_roles WHERE rolname = cur_username);
+    IF new_userrole <> cur_userrole
+    THEN
+        EXECUTE format(
+            'REVOKE %I FROM %I', cur_userrole, cur_username);
+    END IF;
+    -- GRANT new_userrole unconditionally to ensure it's an error to upgrade
+    -- a non-existent cur_username (GRANTing a role twice is not an error)
+    EXECUTE format(
+        'GRANT %I TO %I', new_userrole, cur_username);
+
+    IF new_pw IS NOT NULL AND new_pw <> ''
+    THEN
+        EXECUTE format(
+            'ALTER ROLE %I PASSWORD %L', cur_username, new_pw);
+    END IF;
+END;
+$$
+    LANGUAGE plpgsql
+    SECURITY DEFINER;
--- a/schema/manage_users_tests.sql	Wed Jul 25 13:34:46 2018 +0200
+++ b/schema/manage_users_tests.sql	Wed Jul 25 18:26:54 2018 +0200
@@ -15,6 +15,9 @@
 
 SET SESSION AUTHORIZATION test_sys_admin1;
 
+--
+-- Role creation
+--
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
         'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1')
@@ -56,3 +59,113 @@
     $$,
     23505, NULL,
     'No duplicate e-mail adress is allowed');
+
+--
+-- Role update
+--
+SELECT lives_ok($$
+    SELECT sys_admin.create_user(
+        'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2');
+    SELECT sys_admin.update_user('test2',
+        'waterway_user', 'test2_new', 'new_secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test5')
+    $$,
+    '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.user_profiles
+            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', 'secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test6')
+    $$,
+    '0A000', NULL,
+    'Name of current user cannot be altered');
+
+SELECT throws_ok($$
+    SELECT sys_admin.update_user('test_user_at',
+        'invalid', 'test2', 'secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test2')
+    $$,
+    42704, NULL,
+    'Valid role name has to be provided');
+
+SELECT throws_ok($$
+    SELECT sys_admin.update_user('test_user_at',
+        'waterway_user', NULL, 'secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test3')
+    $$,
+    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', 'secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test4')
+    $$,
+    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', 'secret', 'AT',
+        (SELECT map_extent FROM users.user_profiles
+            WHERE username = 'test_user_at'), 'test4')
+    $$,
+    23505, NULL,
+    'No duplicate user name is allowed');
+
+-- 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';
+
+SELECT sys_admin.update_user('test_user_at',
+    'waterway_user', 'test_user_at', NULL, 'AT',
+    (SELECT map_extent FROM users.user_profiles
+        WHERE username = 'test_user_at'), 'xxx');
+SELECT set_eq($$
+    SELECT rolpassword FROM old_pw_hash
+    $$,
+    $$
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
+    $$,
+    '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 users.user_profiles
+        WHERE username = 'test_user_at'), 'xxx');
+SELECT set_eq($$
+    SELECT rolpassword FROM old_pw_hash
+    $$,
+    $$
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
+    $$,
+    'Giving empty string as password does not change password');
+
+SELECT sys_admin.update_user('test_user_at',
+    'waterway_user', 'test_user_at', 'new_pw', 'AT',
+    (SELECT map_extent FROM users.user_profiles
+        WHERE username = 'test_user_at'), 'xxx');
+SELECT set_ne($$
+    SELECT rolpassword FROM old_pw_hash
+    $$,
+    $$
+    SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
+    $$,
+    'Giving a non-empty password string changes password');
--- a/schema/run_tests.sh	Wed Jul 25 13:34:46 2018 +0200
+++ b/schema/run_tests.sh	Wed Jul 25 18:26:54 2018 +0200
@@ -19,7 +19,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(23)' \
+    -c 'SELECT plan(33)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'