Mercurial > gemma
annotate schema/manage_users_tests.sql @ 233:531d1f8a2b4b
Fix spelling
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 15:02:19 +0200 |
parents | 4859aa6c96be |
children | 55dce2f649bc |
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( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
11 'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0') |
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 |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
16 SET SESSION AUTHORIZATION test_sys_admin1; |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
18 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
19 -- Role creation |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
20 -- |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 SELECT lives_ok($$ |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
22 SELECT sys_admin.create_user( |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 'New waterway user can be added'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 SELECT throws_ok($$ |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
28 SELECT sys_admin.create_user( |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 'invalid', 'test2', 'secret', 'AT', NULL, 'test2') |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 42704, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 '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
|
33 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 SELECT throws_ok($$ |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
35 SELECT sys_admin.create_user( |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3') |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 23502, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 'username is mandatory'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 -- 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
|
41 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 SELECT throws_ok($$ |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
43 SELECT sys_admin.create_user( |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 $$, |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
46 42710, NULL, |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
47 '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
|
48 |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
49 SELECT throws_ok($$ |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
50 SELECT sys_admin.create_user( |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
51 'waterway_user', 'test_user_at', 'secret', 'AT', NULL, 'test4') |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
52 $$, |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 23505, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 'No duplicate user name is allowed'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 SELECT throws_ok($$ |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
185
diff
changeset
|
57 SELECT sys_admin.create_user( |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 'waterway_user', 'test2', 'secret', 'AT', NULL, 'xxx') |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 23505, NULL, |
233 | 61 'No duplicate e-mail address is allowed'); |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
62 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
63 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
64 -- Role update |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
65 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
66 SELECT lives_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
67 SELECT sys_admin.create_user( |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
68 'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
69 SELECT sys_admin.update_user('test2', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
70 'waterway_user', 'test2_new', 'new_secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
71 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
72 WHERE username = 'test_user_at'), 'test5') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
73 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
74 'Existing user can be updated'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
75 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
76 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
77 SELECT sys_admin.update_user('test_non_existent', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
78 'waterway_user', 'test_non_existent', '', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
79 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
80 WHERE username = 'test_user_at'), 'test5') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
81 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
82 42704, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
83 'Non-existent user cannot be updated'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
84 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
85 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
86 SELECT sys_admin.update_user(CAST(current_user AS varchar), |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
87 'waterway_user', 'test_new_name', 'secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
88 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
89 WHERE username = 'test_user_at'), 'test6') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
90 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
91 '0A000', NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
92 '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
|
93 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
94 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
95 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
96 'invalid', 'test2', 'secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
97 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
98 WHERE username = 'test_user_at'), 'test2') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
99 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
100 42704, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
101 '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
|
102 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
103 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
104 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
105 'waterway_user', NULL, 'secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
106 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
107 WHERE username = 'test_user_at'), 'test3') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
108 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
109 23502, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
110 'New username is mandatory'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
111 -- 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
|
112 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
113 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
114 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
115 'waterway_user', 'waterway_user', 'secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
116 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
117 WHERE username = 'test_user_at'), 'test4') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
118 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
119 42710, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
120 '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
|
121 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
122 SELECT throws_ok($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
123 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
124 'waterway_user', 'test_user_ro', 'secret', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
125 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
126 WHERE username = 'test_user_at'), 'test4') |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
127 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
128 23505, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
129 '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
|
130 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
131 -- 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
|
132 RESET SESSION AUTHORIZATION; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
133 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
134 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
|
135 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
|
136 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
137 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
138 'waterway_user', 'test_user_at', NULL, 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
139 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
140 WHERE username = 'test_user_at'), 'xxx'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
141 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
142 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
|
143 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
144 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
145 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
|
146 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
147 '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
|
148 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
149 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
150 'waterway_user', 'test_user_at', '', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
151 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
152 WHERE username = 'test_user_at'), 'xxx'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
153 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
154 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
|
155 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
156 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
157 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
|
158 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
159 '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
|
160 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
161 SELECT sys_admin.update_user('test_user_at', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
162 'waterway_user', 'test_user_at', 'new_pw', 'AT', |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
163 (SELECT map_extent FROM users.user_profiles |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
164 WHERE username = 'test_user_at'), 'xxx'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
165 SELECT set_ne($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
166 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
|
167 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
168 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
169 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
|
170 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
171 '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
|
172 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
173 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
|
174 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
175 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
176 -- Role deletion |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
177 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
178 -- 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
|
179 -- 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
|
180 SELECT lives_ok($$ |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
181 SELECT sys_admin.create_user( |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
182 'waterway_user', 'test3', 'secret', 'AT', NULL, 'test3'); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
183 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
|
184 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
185 'Existing user can be deleted'); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
186 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
187 SELECT throws_ok($$ |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
188 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
|
189 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
190 42704, NULL, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
191 '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
|
192 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
193 SELECT throws_ok($$ |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
194 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
|
195 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
196 55006, NULL, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
197 'Current user cannot be deleted'); |