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