Mercurial > gemma

changeset 262:92470caf81fd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add database function to check password against policy Though it is currently only used in sys_admin-exclusive functions, it is exposed to normal users because there is nothing to hide and users should be able to change (and check) their password, too.
author Tom Gottfried <tom@intevation.de>
date Fri, 27 Jul 2018 15:26:16 +0200
parents ab9859981ee3
children 13ad969a9138 d2fb83291c68
files schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh schema/tap_tests_data.sql
diffstat 4 files changed, 81 insertions(+), 23 deletions(-) [+]
line wrap: on
line diff
--- a/schema/manage_users.sql	Fri Jul 27 14:55:47 2018 +0200
+++ b/schema/manage_users.sql	Fri Jul 27 15:26:16 2018 +0200
@@ -1,8 +1,29 @@
 --
 -- Functions encapsulating user management functionality and
--- exposing it to privileged users
+-- exposing it to appropriately privileged users
 --
 
+CREATE OR REPLACE FUNCTION users.check_password(
+    pw varchar
+    )
+    RETURNS varchar
+AS $$
+DECLARE
+    min_len CONSTANT int = 8;
+BEGIN
+    IF char_length(pw) < min_len
+        OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
+        OR pw NOT SIMILAR TO '%[[:digit:]]%'
+    THEN
+        RAISE invalid_password USING MESSAGE = 'Invalid password';
+    ELSE
+        RETURN pw;
+    END IF;
+END;
+$$
+    LANGUAGE plpgsql;
+
+
 CREATE OR REPLACE VIEW sys_admin.list_users AS
     SELECT r.rolname, p.*
         FROM users.user_profiles p
@@ -30,7 +51,10 @@
     INSERT INTO users.user_profiles VALUES (
         username, country, map_extent, email_address);
     EXECUTE format(
-        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
+        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
+        username,
+        userrole,
+        users.check_password(pw));
 END;
 $$
     LANGUAGE plpgsql
@@ -83,7 +107,9 @@
     IF new_pw IS NOT NULL AND new_pw <> ''
     THEN
         EXECUTE format(
-            'ALTER ROLE %I PASSWORD %L', cur_username, new_pw);
+            'ALTER ROLE %I PASSWORD %L',
+            cur_username,
+            users.check_password(new_pw));
     END IF;
 END;
 $$
--- a/schema/manage_users_tests.sql	Fri Jul 27 14:55:47 2018 +0200
+++ b/schema/manage_users_tests.sql	Fri Jul 27 15:26:16 2018 +0200
@@ -8,7 +8,7 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0')
+        'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0')
     $$,
     42501, NULL,
     'Less privileged user cannot call function in schema sys_admin');
@@ -28,20 +28,20 @@
 --
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1')
+        'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
     $$,
     'New waterway user can be added');
 
 SELECT throws_ok($$
     SELECT sys_admin.create_user(
-        'invalid', 'test2', 'secret', 'AT', NULL, 'test2')
+        'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
     $$,
     42704, NULL,
     'Valid role name has to be provided');
 
 SELECT throws_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', NULL, 'secret', 'AT', NULL, 'test3')
+        'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
     $$,
     23502, NULL,
     'username is mandatory');
@@ -49,26 +49,48 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4')
+        'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
     $$,
     42710, NULL,
     'Reserved role names cannot be used as username');
 
 SELECT throws_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'test_user_at', 'secret', 'AT', NULL, 'test4')
+        'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
     $$,
     23505, NULL,
     'No duplicate user name is allowed');
 
+-- Test password policy
+SELECT throws_ok($$
+    SELECT sys_admin.create_user(
+        'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
+    $$,
+    '28P01', NULL,
+    'Password with less than 8 characters is not accepted');
+
+SELECT throws_ok($$
+    SELECT sys_admin.create_user(
+        'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
+    $$,
+    '28P01', NULL,
+    'Password without non-alphanumeric character is not accepted');
+
+SELECT throws_ok($$
+    SELECT sys_admin.create_user(
+        'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
+    $$,
+    '28P01', NULL,
+    'Password without digit is not accepted');
+
 --
 -- Role update
 --
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2');
+        'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
     SELECT sys_admin.update_user('test2',
-        'waterway_user', 'test2_new', 'new_secret', 'AT',
+        'waterway_user', 'test2_new', 'new_secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test5')
     $$,
@@ -85,7 +107,7 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.update_user(CAST(current_user AS varchar),
-        'waterway_user', 'test_new_name', 'secret', 'AT',
+        'waterway_user', 'test_new_name', 'secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test6')
     $$,
@@ -94,7 +116,7 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.update_user('test_user_at',
-        'invalid', 'test2', 'secret', 'AT',
+        'invalid', 'test2', 'secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test2')
     $$,
@@ -103,7 +125,7 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', NULL, 'secret', 'AT',
+        'waterway_user', NULL, 'secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test3')
     $$,
@@ -113,7 +135,7 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', 'waterway_user', 'secret', 'AT',
+        'waterway_user', 'waterway_user', 'secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test4')
     $$,
@@ -122,13 +144,23 @@
 
 SELECT throws_ok($$
     SELECT sys_admin.update_user('test_user_at',
-        'waterway_user', 'test_user_ro', 'secret', 'AT',
+        'waterway_user', 'test_user_ro', 'secret1$', 'AT',
         (SELECT map_extent FROM users.user_profiles
             WHERE username = 'test_user_at'), 'test4')
     $$,
     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.user_profiles
+            WHERE username = 'test_user_at'), 'test4')
+    $$,
+    '28P01', NULL,
+    'Non-compliant password is not accepted');
+
 -- To compare passwords, we need to run the following tests as superuser
 RESET SESSION AUTHORIZATION;
 
@@ -160,7 +192,7 @@
     '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',
+    'waterway_user', 'test_user_at', 'new_pw1$', 'AT',
     (SELECT map_extent FROM users.user_profiles
         WHERE username = 'test_user_at'), 'xxx');
 SELECT set_ne($$
@@ -180,7 +212,7 @@
 -- See also comments in function definition.
 SELECT lives_ok($$
     SELECT sys_admin.create_user(
-        'waterway_user', 'test3', 'secret', 'AT', NULL, 'test3');
+        'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
     SELECT sys_admin.delete_user('test3')
     $$,
     'Existing user can be deleted');
--- a/schema/run_tests.sh	Fri Jul 27 14:55:47 2018 +0200
+++ b/schema/run_tests.sh	Fri Jul 27 15:26:16 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(36)' \
+    -c 'SELECT plan(40)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql	Fri Jul 27 14:55:47 2018 +0200
+++ b/schema/tap_tests_data.sql	Fri Jul 27 15:26:16 2018 +0200
@@ -9,13 +9,13 @@
     ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));
 
 SELECT sys_admin.create_user(
-    'waterway_user', 'test_user_at', 'user_at', 'AT', NULL, 'xxx');
+    'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx');
 SELECT sys_admin.create_user(
-    'waterway_user', 'test_user_ro', 'user_ro', 'RO', NULL, 'xxy');
+    'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy');
 SELECT sys_admin.create_user(
-    'waterway_admin', 'test_admin_at', 'admin_at', 'AT', NULL, 'yyy');
+    'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
 SELECT sys_admin.create_user(
-    'sys_admin', 'test_sys_admin1', 'sys_admin1', 'AT', NULL, 'zzz');
+    'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
 
 INSERT INTO limiting_factors VALUES ('depth'), ('width');