Mercurial > gemma

annotate schema/manage_users_tests.sql @ 319:ac760b0f22a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add special role for password reset As password reset is exposed without requiring a login, let this role have privileges limited to reseting passwords, and only reseting passwords.
author Tom Gottfried <tom@intevation.de>
date Thu, 02 Aug 2018 13:06:39 +0200
parents 750a9c9cd965
children 363983d5c567
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- pgTAP test script for user management functions
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
7 SET SESSION AUTHORIZATION test_admin_at;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
8
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
9 SELECT throws_ok($$
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
10 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
11 'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0')
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
12 $$,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
13 42501, NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
14 'Less privileged user cannot call function in schema sys_admin');
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
15
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
16 --
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
17 -- Role listing
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
18 --
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
19 SET SESSION AUTHORIZATION test_user_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
20 SELECT results_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
21 SELECT username FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
22 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
23 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
24 SELECT CAST(current_user AS varchar)
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
25 $$,
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
26 'User should only see his own profile');
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
27
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
28 SET SESSION AUTHORIZATION test_admin_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
29 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
30 SELECT DISTINCT country FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
31 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
32 ARRAY['AT'],
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
33 'Waterway admin should only see profiles of his country');
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
34
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
35 SET SESSION AUTHORIZATION test_sys_admin1;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
36 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
37 SELECT count(*) FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
38 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
39 ARRAY[4],
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
40 'System admin can see all users');
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
41
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
42 --
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
43 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
44 --
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 SELECT lives_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
46 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
47 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
52 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
53 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 'Valid role name has to be provided');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
59 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
60 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 -- Though other arguments are mandatory, too, there are no explicit tests
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
67 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
68 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 $$,
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
70 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
71 'Reserved role names cannot be used as username');
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
72
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
73 SELECT throws_ok($$
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
74 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
75 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
76 $$,
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
80 -- Test password policy
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
81 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
82 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
83 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
84 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
85 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
86 'Password with less than 8 characters is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
87
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
88 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
89 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
90 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
91 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
92 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
93 'Password without non-alphanumeric character is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
94
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
95 SELECT throws_ok($$
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
96 SELECT sys_admin.create_user(
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
97 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
98 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
99 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
100 'Password without digit is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
101
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
102 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
103 -- Role update
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
104 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
105 SELECT lives_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
106 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
107 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
108 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
109 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
110 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
111 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
112 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
113 WHERE username = 'test2'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
114 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
115 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
116
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
117 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
118 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
119 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
120 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
121 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
122 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
123 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
124 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
125 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
126 '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
127
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
128 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
129 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
130 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
131 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
132 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
133 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
134 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
135 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
136 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
137 '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
138
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
139 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
140 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
141 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
142 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
143 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
144 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
145 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
146 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
147 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
148 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
149 -- 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
150
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
151 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
152 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
153 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
154 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
155 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
156 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
157 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
158 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
159 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
160 '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
161
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
162 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
163 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
164 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
165 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
166 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
167 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
168 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
169 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
170 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
171 '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
172
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
173 -- 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
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_user_at', 'secret', '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'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
180 WHERE username = 'test_user_at'
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
181 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
182 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
183 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
184
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
185 -- 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
186 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
187
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
188 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
189 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
190
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
191 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
192 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
193 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
194 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
195 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
196 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
197 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
198 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
199 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
200 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
201 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
202 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
203 '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
204
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
205 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
206 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
207 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
208 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
209 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
210 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
211 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
212 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
213 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
214 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
215 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
216 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
217 '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
218
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
219 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
220 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
221 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
222 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
223 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
224 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
225 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
226 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
227 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
228 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
229 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
230 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
231 '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
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
233 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
234
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
235 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
236 -- Role deletion
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
237 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
238 -- 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
239 -- 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
240 SELECT lives_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
241 SELECT sys_admin.create_user(
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
242 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
243 SELECT sys_admin.delete_user('test3')
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
244 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
245 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
246
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
247 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
248 SELECT sys_admin.delete_user('test_non_existent')
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
249 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
250 42704, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
251 'Non-existent user cannot be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
252
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
253 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
254 SELECT sys_admin.delete_user(CAST(current_user AS varchar))
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
255 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
256 55006, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
257 'Current user cannot be deleted');
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
258
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
259
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
260 --
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
261 -- Password reset
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
262 --
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
263
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
264 -- Workaround broken relocatability of pgtap (otherwise we could
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
265 -- 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
266 RESET SESSION AUTHORIZATION;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
267 GRANT USAGE ON SCHEMA public TO pw_reset;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
268
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
269 SET SESSION AUTHORIZATION test_pw_reset;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
270
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
271 SELECT isnt_empty($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
272 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
273 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
274 '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
275
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
276 SELECT results_eq($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
277 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
278 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
279 RETURNING email_address
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
280 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
281 $$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
282 SELECT email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
283 WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
284 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
285 'Special role can update password');
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
286
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
287 SELECT throws_ok($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
288 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
289 SET username = 'test_rename', email_address = 'test'
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 42501, NULL,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
292 'Special role cannot update arbitrary user attributes');