gemma: schema/manage_users_tests.sql annotate

annotate schema/manage_users_tests.sql @ 410:3f803d64a6ee

Do not rely on session_user for authorization Privileges are usually checked based on current_user, which can be changed using SET ROLE, while session_user is based on the actually logged in user and can only be changed by a superuser using SET SESSION AUTHORIZATION. Using session_user for authorization purposes prevents the expected behaviour of SET ROLE. current_user_country() does not need to be SECURITY DEFINER since a while, because there is no RLS policy affected by what is mentioned in the removed comment.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 15 Aug 2018 16:39:00 +0200
parents	f5087cebc740
children	5611cf72cc92

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
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	7 --
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	8 -- Role listing
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	9 --
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	10 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	11 SELECT results_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	12 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	13 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	14 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	15 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	16 $$,
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	17 '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	18
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_admin_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	20 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	21 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	22 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	23 ARRAY['AT'],
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	24 '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	25
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	26 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	27 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	28 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	29 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	30 ARRAY[4],
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	31 '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	32
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	33 --
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	34 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	35 --
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	36 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	37 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	38 '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	39 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	40 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	41
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	42 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	43 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	44 '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	45 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	46 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	47 '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	48
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	49 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	50 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	51 '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	52 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	53 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	54 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	55 -- 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	56
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	57 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	58 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	59 '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	60 $$,
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	61 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	62 '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	63
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	64 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	65 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	66 '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	67 $$,
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	68 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	69 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	70
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	71 SELECT throws_ok($$
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	72 INSERT INTO users.list_users VALUES (
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	73 'waterway_user',
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	74 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ',
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	75 'secret1$', 'AT', NULL, 'test4')
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	76 $$,
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	77 23514, NULL,
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	78 'User name length is restricted to 63 bytes');
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 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($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	82 INSERT INTO users.list_users VALUES (
262 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($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	89 INSERT INTO users.list_users VALUES (
262 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($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	96 INSERT INTO users.list_users VALUES (
262 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
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	141 -- The above test will pass even if the password is actually updated in case
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	142 -- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	143 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	144 CREATE TEMP TABLE old_pw_hash AS
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	145 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	146 SET SESSION AUTHORIZATION test_admin_at;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	147 UPDATE users.list_users
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	148 SET pw = 'test_user_at2!'
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	149 WHERE username = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	150 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	151 SELECT set_eq($$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	152 SELECT rolpassword FROM old_pw_hash
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	153 $$,
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	154 $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	155 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	156 $$,
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	157 'Waterway admin cannot update password of other users in country');
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	158
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	159
327 363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	160 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	161
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	162 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	163 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	164 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	165 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	166 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	167 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	168 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	169 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	170 WHERE username = 'test2'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	171 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	172 'Existing user can be updated');
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 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	175 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	176 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	177 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	178 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	179 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	180 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	181 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	182 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	183 '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	184
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	185 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	186 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	187 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	188 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	189 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	190 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	191 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	192 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	193 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	194 '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	195
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	196 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	197 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	198 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	199 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	200 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	201 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	202 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	203 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	204 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	205 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	206 -- 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	207
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	208 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	209 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	210 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	211 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	212 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	213 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	214 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	215 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	216 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	217 '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	218
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	219 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	220 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	221 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	222 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	223 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	224 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	225 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	226 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	227 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	228 '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	229
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	230 -- 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	231 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	232 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	233 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	234 = ('waterway_user', 'test_user_at', 'secret', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	235 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	236 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	237 WHERE username = 'test_user_at'
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	238 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	239 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	240 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	241
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	242 -- 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	243 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	244
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	245 UPDATE old_pw_hash SET rolpassword = (
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	246 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	247
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	248 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	249 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	250 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	251 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	252 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	253 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	254 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	255 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	256 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	257 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	258 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	259 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	260 '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	261
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	262 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	263 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	264 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	265 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	266 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	267 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	268 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	269 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	270 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	271 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	272 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	273 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	274 '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	275
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	276 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	277 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	278 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	279 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	280 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	281 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	282 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	283 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	284 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	285 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	286 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	287 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	288 '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	289
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	290 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	291
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	292 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	293 -- Role deletion
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	294 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	295 -- 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	296 -- 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	297 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	298 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	299 '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	300 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	301 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	302 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	303
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	304 SELECT throws_ok($$
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	305 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	306 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	307 55006, NULL,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	308 'Current user cannot be deleted');
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	309
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	310
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	311 --
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	312 -- Password reset
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
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	315 -- Workaround broken relocatability of pgtap (otherwise we could
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	316 -- 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	317 RESET SESSION AUTHORIZATION;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	318 GRANT USAGE ON SCHEMA public TO pw_reset;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	319
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	320 SET SESSION AUTHORIZATION test_pw_reset;
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	321
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	322 SELECT isnt_empty($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	323 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	324 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	325 '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	326
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	327 SELECT results_eq($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	328 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	329 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	330 RETURNING email_address
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	331 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	332 $$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	333 SELECT email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	334 WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	335 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	336 'Special role can update password');
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	337
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	338 SELECT throws_ok($$
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	339 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	340 SET username = 'test_rename', email_address = 'test'
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	341 $$,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	342 42501, NULL,
ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	343 'Special role cannot update arbitrary user attributes');

Mercurial > gemma

annotate schema/manage_users_tests.sql @ 410:3f803d64a6ee