# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1532697976 -7200
# Node ID 92470caf81fdf774bc57ac52ef0516bb54320190
# Parent  ab9859981ee342ca3d2286da9ee65523b2ad1702
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.

diff -r ab9859981ee3 -r 92470caf81fd schema/manage_users.sql
--- 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;
 $$
diff -r ab9859981ee3 -r 92470caf81fd schema/manage_users_tests.sql
--- 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');
diff -r ab9859981ee3 -r 92470caf81fd schema/run_tests.sh
--- 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()'
diff -r ab9859981ee3 -r 92470caf81fd schema/tap_tests_data.sql
--- 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');