gemma: schema/manage_users_tests.sql annotate

annotate schema/manage_users_tests.sql @ 263:13ad969a9138

Enable listing of users for all roles with appropriate filters Waterway users should see their own account data and their should be a single interface for account data (i.e. users.list_users). Therefore, also the RLS policy on user_profiles for waterway_admin is translated to the view. current_user_country() moved because it's needed earlier during database setup, now.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 27 Jul 2018 19:03:56 +0200
parents	92470caf81fd
children	72062ca52746

rev	line source
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	1 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	2 -- pgTAP test script for user management functions
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	3 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	4
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	5 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	6
224 57dfab80973c By convention, prefix all test users with 'test' Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	7 SET SESSION AUTHORIZATION test_admin_at;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	8
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	9 SELECT throws_ok($$
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	10 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	11 'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	12 $$,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	13 42501, NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	14 'Less privileged user cannot call function in schema sys_admin');
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	15
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	16 --
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	17 -- Role listing
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	18 --
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	19 SET SESSION AUTHORIZATION test_user_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	20 SELECT results_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	21 SELECT username FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	22 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	23 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	24 SELECT CAST(current_user AS varchar)
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	25 $$,
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	26 'User should only see his own profile');
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	27
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	28 SET SESSION AUTHORIZATION test_admin_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	29 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	30 SELECT DISTINCT country FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	31 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	32 ARRAY['AT'],
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	33 'Waterway admin should only see profiles of his country');
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	34
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	35 SET SESSION AUTHORIZATION test_sys_admin1;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	36 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	37 SELECT count(*) FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	38 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	39 ARRAY[4],
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	40 'System admin can see all users');
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	41
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	42 --
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	43 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	44 --
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	45 SELECT lives_ok($$
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	46 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	47 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	48 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	49 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	50
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	51 SELECT throws_ok($$
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	52 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	53 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	54 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	55 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	56 'Valid role name has to be provided');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	57
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	58 SELECT throws_ok($$
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	59 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	60 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	61 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	62 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	63 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	64 -- Though other arguments are mandatory, too, there are no explicit tests
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	65
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	66 SELECT throws_ok($$
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 185 diff changeset	67 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	68 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	69 $$,
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	70 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	71 'Reserved role names cannot be used as username');
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	72
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	73 SELECT throws_ok($$
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	74 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	75 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	76 $$,
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	77 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	78 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	79
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	80 -- Test password policy
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	81 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	82 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	83 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	84 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	85 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	86 'Password with less than 8 characters is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	87
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	88 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	89 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	90 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	91 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	92 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	93 'Password without non-alphanumeric character is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	94
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	95 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	96 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	97 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	98 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	99 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	100 'Password without digit is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	101
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	102 --
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	103 -- Role update
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	104 --
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	105 SELECT lives_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	106 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	107 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	108 SELECT sys_admin.update_user('test2',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	109 'waterway_user', 'test2_new', 'new_secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	110 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	111 WHERE username = 'test_user_at'), 'test5')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	112 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	113 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	114
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	115 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	116 SELECT sys_admin.update_user('test_non_existent',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	117 'waterway_user', 'test_non_existent', '', 'AT',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	118 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	119 WHERE username = 'test_user_at'), 'test5')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	120 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	121 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	122 'Non-existent user cannot be updated');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	123
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	124 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	125 SELECT sys_admin.update_user(CAST(current_user AS varchar),
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	126 'waterway_user', 'test_new_name', 'secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	127 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	128 WHERE username = 'test_user_at'), 'test6')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	129 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	130 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	131 'Name of current user cannot be altered');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	132
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	133 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	134 SELECT sys_admin.update_user('test_user_at',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	135 'invalid', 'test2', 'secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	136 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	137 WHERE username = 'test_user_at'), 'test2')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	138 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	139 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	140 'Valid role name has to be provided');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	141
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	142 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	143 SELECT sys_admin.update_user('test_user_at',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	144 'waterway_user', NULL, 'secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	145 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	146 WHERE username = 'test_user_at'), 'test3')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	147 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	148 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	149 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	150 -- Though other arguments are mandatory, too, there are no explicit tests
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	151
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	152 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	153 SELECT sys_admin.update_user('test_user_at',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	154 'waterway_user', 'waterway_user', 'secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	155 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	156 WHERE username = 'test_user_at'), 'test4')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	157 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	158 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	159 'Reserved role names cannot be used as username');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	160
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	161 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	162 SELECT sys_admin.update_user('test_user_at',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	163 'waterway_user', 'test_user_ro', 'secret1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	164 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	165 WHERE username = 'test_user_at'), 'test4')
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	166 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	167 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	168 'No duplicate user name is allowed');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	169
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	170 -- Test password policy (only one rule to ensure it's also used on update)
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	171 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	172 SELECT sys_admin.update_user('test_user_at',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	173 'waterway_user', 'test_user_at', 'secret', 'AT',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	174 (SELECT map_extent FROM users.user_profiles
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	175 WHERE username = 'test_user_at'), 'test4')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	176 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	177 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	178 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	179
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	180 -- To compare passwords, we need to run the following tests as superuser
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	181 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	182
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	183 CREATE TEMP TABLE old_pw_hash AS
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	184 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	185
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	186 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	187 'waterway_user', 'test_user_at', NULL, 'AT',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	188 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	189 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	190 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	191 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	192 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	193 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	194 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	195 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	196 'Giving NULL password does not change password');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	197
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	198 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	199 'waterway_user', 'test_user_at', '', 'AT',
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	200 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	201 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	202 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	203 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	204 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	205 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	206 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	207 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	208 'Giving empty string as password does not change password');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	209
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	210 SELECT sys_admin.update_user('test_user_at',
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	211 'waterway_user', 'test_user_at', 'new_pw1$', 'AT',
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	212 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	213 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	214 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	215 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	216 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	217 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	218 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	219 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	220 'Giving a non-empty password string changes password');
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	221
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	222 SET SESSION AUTHORIZATION test_sys_admin1;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	223
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	224 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	225 -- Role deletion
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	226 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	227 -- Note: backend termination is not tested in the following.
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	228 -- See also comments in function definition.
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	229 SELECT lives_ok($$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	230 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	231 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	232 SELECT sys_admin.delete_user('test3')
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	233 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	234 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	235
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	236 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	237 SELECT sys_admin.delete_user('test_non_existent')
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	238 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	239 42704, NULL,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	240 'Non-existent user cannot be deleted');
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	241
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	242 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	243 SELECT sys_admin.delete_user(CAST(current_user AS varchar))
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	244 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	245 55006, NULL,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	246 'Current user cannot be deleted');

Mercurial > gemma

annotate schema/manage_users_tests.sql @ 263:13ad969a9138