gemma: schema/manage_users_tests.sql annotate

annotate schema/manage_users_tests.sql @ 342:c6bd6ed18942

Use INSTEAD OF trigger for user deletion As we already have this for updating, it's more symmetric to make the whole thing look like a real table. TODO: make it happen also for user creation.

author	Tom Gottfried <tom@intevation.de>
date	Mon, 06 Aug 2018 12:37:06 +0200
parents	df1fc589ad9d
children	5b03f420957d

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 --
327 363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	105
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	106 SET SESSION AUTHORIZATION test_user_at;
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	107
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	108 SELECT results_eq($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	109 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	110 SET (pw, map_extent, email_address)
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	111 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	112 RETURNING username
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	113 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	114 $$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	115 SELECT CAST('test_user_at' AS varchar)
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	116 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	117 'Waterway user can update own password, map extent and email address');
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	118
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	119 SELECT throws_ok($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	120 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	121 SET username = 'test_rename', rolname = 'test'
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	122 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	123 42501, NULL,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	124 'Waterway user cannot update arbitrary user attributes');
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	125
334 df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	126 SET SESSION AUTHORIZATION test_admin_at;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	127
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	128 SELECT results_eq($$
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	129 UPDATE users.list_users
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	130 SET (pw, map_extent, email_address)
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	131 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	132 WHERE country = users.current_user_country()
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	133 AND username <> current_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	134 RETURNING *
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	135 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	136 $$
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	137 SELECT '' WHERE false -- Empty result set
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	138 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	139 'Waterway admin cannot update attributes of other users in country');
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	140
327 363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	141 SET SESSION AUTHORIZATION test_sys_admin1;
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	142
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	143 SELECT lives_ok($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	144 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	145 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	146 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	147 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	148 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	149 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	150 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	151 WHERE username = 'test2'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	152 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	153 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	154
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	155 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	156 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	157 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	158 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	159 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	160 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	161 WHERE username = CAST(current_user AS varchar)
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	162 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	163 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	164 '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	165
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	166 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	167 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	168 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	169 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	170 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	171 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	172 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	173 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	174 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	175 '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	176
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	177 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	178 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	179 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	180 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	181 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	182 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	183 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	184 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	185 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	186 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	187 -- 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	188
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	189 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	190 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	191 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	192 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	193 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	194 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	195 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	196 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	197 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	198 '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	199
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	200 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	201 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	202 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	203 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	204 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	205 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	206 WHERE username = 'test_user_at'
225 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 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	209 '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	210
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	211 -- 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	212 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	213 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	214 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	215 = ('waterway_user', 'test_user_at', 'secret', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	216 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	217 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	218 WHERE username = 'test_user_at'
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	219 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	220 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	221 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	222
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	223 -- 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	224 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	225
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	226 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	227 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	228
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	229 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	230 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	231 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	232 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	233 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	234 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	235 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	236 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	237 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	238 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	239 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	240 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	241 '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	242
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	243 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	244 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	245 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	246 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	247 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	248 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	249 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	250 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	251 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	252 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	253 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	254 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	255 '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	256
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	257 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	258 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	259 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	260 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	261 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	262 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	263 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	264 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	265 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	266 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	267 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	268 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	269 '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	270
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	271 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	272
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	273 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	274 -- Role deletion
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	275 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	276 -- 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	277 -- 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	278 SELECT lives_ok($$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	279 SELECT sys_admin.create_user(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	280 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	281 DELETE FROM users.list_users WHERE username = 'test3'
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	282 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	283 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	284
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	285 SELECT throws_ok($$
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	286 DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar)
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	287 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	288 55006, NULL,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	289 'Current user cannot be deleted');
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	290
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	291
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	292 --
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	293 -- Password reset
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	294 --
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	295
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	296 -- Workaround broken relocatability of pgtap (otherwise we could
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	297 -- put pgtap in its own schema and GRANT USAGE to PUBLIC on it)
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	298 RESET SESSION AUTHORIZATION;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	299 GRANT USAGE ON SCHEMA public TO pw_reset;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	300
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	301 SET SESSION AUTHORIZATION test_pw_reset;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	302
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	303 SELECT isnt_empty($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	304 SELECT username, email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	305 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	306 'Special role can see users with their email addresses');
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	307
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	308 SELECT results_eq($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	309 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	310 SET pw = 'user_at2!' WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	311 RETURNING email_address
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	312 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	313 $$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	314 SELECT email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	315 WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	316 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	317 'Special role can update password');
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	318
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	319 SELECT throws_ok($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	320 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	321 SET username = 'test_rename', email_address = 'test'
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	322 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	323 42501, NULL,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	324 'Special role cannot update arbitrary user attributes');

Mercurial > gemma

annotate schema/manage_users_tests.sql @ 342:c6bd6ed18942