Mercurial > gemma

comparison schema/manage_users_tests.sql @ 268:72062ca52746

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
267:7f030ec3472d 268:72062ca52746
105 SELECT lives_ok($$ 105 SELECT lives_ok($$
106 SELECT sys_admin.create_user( 106 SELECT sys_admin.create_user(
107 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); 107 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
108 SELECT sys_admin.update_user('test2', 108 SELECT sys_admin.update_user('test2',
109 'waterway_user', 'test2_new', 'new_secret1$', 'AT', 109 'waterway_user', 'test2_new', 'new_secret1$', 'AT',
110 (SELECT map_extent FROM users.user_profiles 110 (SELECT map_extent FROM users.list_users
111 WHERE username = 'test_user_at'), 'test5') 111 WHERE username = 'test_user_at'), 'test5')
112 $$, 112 $$,
113 'Existing user can be updated'); 113 'Existing user can be updated');
114 114
115 SELECT throws_ok($$ 115 SELECT throws_ok($$
116 SELECT sys_admin.update_user('test_non_existent', 116 SELECT sys_admin.update_user('test_non_existent',
117 'waterway_user', 'test_non_existent', '', 'AT', 117 'waterway_user', 'test_non_existent', '', 'AT',
118 (SELECT map_extent FROM users.user_profiles 118 (SELECT map_extent FROM users.list_users
119 WHERE username = 'test_user_at'), 'test5') 119 WHERE username = 'test_user_at'), 'test5')
120 $$, 120 $$,
121 42704, NULL, 121 42704, NULL,
122 'Non-existent user cannot be updated'); 122 'Non-existent user cannot be updated');
123 123
124 SELECT throws_ok($$ 124 SELECT throws_ok($$
125 SELECT sys_admin.update_user(CAST(current_user AS varchar), 125 SELECT sys_admin.update_user(CAST(current_user AS varchar),
126 'waterway_user', 'test_new_name', 'secret1$', 'AT', 126 'waterway_user', 'test_new_name', 'secret1$', 'AT',
127 (SELECT map_extent FROM users.user_profiles 127 (SELECT map_extent FROM users.list_users
128 WHERE username = 'test_user_at'), 'test6') 128 WHERE username = 'test_user_at'), 'test6')
129 $$, 129 $$,
130 '0A000', NULL, 130 '0A000', NULL,
131 'Name of current user cannot be altered'); 131 'Name of current user cannot be altered');
132 132
133 SELECT throws_ok($$ 133 SELECT throws_ok($$
134 SELECT sys_admin.update_user('test_user_at', 134 SELECT sys_admin.update_user('test_user_at',
135 'invalid', 'test2', 'secret1$', 'AT', 135 'invalid', 'test2', 'secret1$', 'AT',
136 (SELECT map_extent FROM users.user_profiles 136 (SELECT map_extent FROM users.list_users
137 WHERE username = 'test_user_at'), 'test2') 137 WHERE username = 'test_user_at'), 'test2')
138 $$, 138 $$,
139 42704, NULL, 139 42704, NULL,
140 'Valid role name has to be provided'); 140 'Valid role name has to be provided');
141 141
142 SELECT throws_ok($$ 142 SELECT throws_ok($$
143 SELECT sys_admin.update_user('test_user_at', 143 SELECT sys_admin.update_user('test_user_at',
144 'waterway_user', NULL, 'secret1$', 'AT', 144 'waterway_user', NULL, 'secret1$', 'AT',
145 (SELECT map_extent FROM users.user_profiles 145 (SELECT map_extent FROM users.list_users
146 WHERE username = 'test_user_at'), 'test3') 146 WHERE username = 'test_user_at'), 'test3')
147 $$, 147 $$,
148 23502, NULL, 148 23502, NULL,
149 'New username is mandatory'); 149 'New username is mandatory');
150 -- Though other arguments are mandatory, too, there are no explicit tests 150 -- Though other arguments are mandatory, too, there are no explicit tests
151 151
152 SELECT throws_ok($$ 152 SELECT throws_ok($$
153 SELECT sys_admin.update_user('test_user_at', 153 SELECT sys_admin.update_user('test_user_at',
154 'waterway_user', 'waterway_user', 'secret1$', 'AT', 154 'waterway_user', 'waterway_user', 'secret1$', 'AT',
155 (SELECT map_extent FROM users.user_profiles 155 (SELECT map_extent FROM users.list_users
156 WHERE username = 'test_user_at'), 'test4') 156 WHERE username = 'test_user_at'), 'test4')
157 $$, 157 $$,
158 42710, NULL, 158 42710, NULL,
159 'Reserved role names cannot be used as username'); 159 'Reserved role names cannot be used as username');
160 160
161 SELECT throws_ok($$ 161 SELECT throws_ok($$
162 SELECT sys_admin.update_user('test_user_at', 162 SELECT sys_admin.update_user('test_user_at',
163 'waterway_user', 'test_user_ro', 'secret1$', 'AT', 163 'waterway_user', 'test_user_ro', 'secret1$', 'AT',
164 (SELECT map_extent FROM users.user_profiles 164 (SELECT map_extent FROM users.list_users
165 WHERE username = 'test_user_at'), 'test4') 165 WHERE username = 'test_user_at'), 'test4')
166 $$, 166 $$,
167 23505, NULL, 167 23505, NULL,
168 'No duplicate user name is allowed'); 168 'No duplicate user name is allowed');
169 169
170 -- Test password policy (only one rule to ensure it's also used on update) 170 -- Test password policy (only one rule to ensure it's also used on update)
171 SELECT throws_ok($$ 171 SELECT throws_ok($$
172 SELECT sys_admin.update_user('test_user_at', 172 SELECT sys_admin.update_user('test_user_at',
173 'waterway_user', 'test_user_at', 'secret', 'AT', 173 'waterway_user', 'test_user_at', 'secret', 'AT',
174 (SELECT map_extent FROM users.user_profiles 174 (SELECT map_extent FROM users.list_users
175 WHERE username = 'test_user_at'), 'test4') 175 WHERE username = 'test_user_at'), 'test4')
176 $$, 176 $$,
177 '28P01', NULL, 177 '28P01', NULL,
178 'Non-compliant password is not accepted'); 178 'Non-compliant password is not accepted');
179 179
183 CREATE TEMP TABLE old_pw_hash AS 183 CREATE TEMP TABLE old_pw_hash AS
184 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'; 184 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
185 185
186 SELECT sys_admin.update_user('test_user_at', 186 SELECT sys_admin.update_user('test_user_at',
187 'waterway_user', 'test_user_at', NULL, 'AT', 187 'waterway_user', 'test_user_at', NULL, 'AT',
188 (SELECT map_extent FROM users.user_profiles 188 (SELECT map_extent FROM internal.user_profiles
189 WHERE username = 'test_user_at'), 'xxx'); 189 WHERE username = 'test_user_at'), 'xxx');
190 SELECT set_eq($$ 190 SELECT set_eq($$
191 SELECT rolpassword FROM old_pw_hash 191 SELECT rolpassword FROM old_pw_hash
192 $$, 192 $$,
193 $$ 193 $$
195 $$, 195 $$,
196 'Giving NULL password does not change password'); 196 'Giving NULL password does not change password');
197 197
198 SELECT sys_admin.update_user('test_user_at', 198 SELECT sys_admin.update_user('test_user_at',
199 'waterway_user', 'test_user_at', '', 'AT', 199 'waterway_user', 'test_user_at', '', 'AT',
200 (SELECT map_extent FROM users.user_profiles 200 (SELECT map_extent FROM internal.user_profiles
201 WHERE username = 'test_user_at'), 'xxx'); 201 WHERE username = 'test_user_at'), 'xxx');
202 SELECT set_eq($$ 202 SELECT set_eq($$
203 SELECT rolpassword FROM old_pw_hash 203 SELECT rolpassword FROM old_pw_hash
204 $$, 204 $$,
205 $$ 205 $$
207 $$, 207 $$,
208 'Giving empty string as password does not change password'); 208 'Giving empty string as password does not change password');
209 209
210 SELECT sys_admin.update_user('test_user_at', 210 SELECT sys_admin.update_user('test_user_at',
211 'waterway_user', 'test_user_at', 'new_pw1$', 'AT', 211 'waterway_user', 'test_user_at', 'new_pw1$', 'AT',
212 (SELECT map_extent FROM users.user_profiles 212 (SELECT map_extent FROM internal.user_profiles
213 WHERE username = 'test_user_at'), 'xxx'); 213 WHERE username = 'test_user_at'), 'xxx');
214 SELECT set_ne($$ 214 SELECT set_ne($$
215 SELECT rolpassword FROM old_pw_hash 215 SELECT rolpassword FROM old_pw_hash
216 $$, 216 $$,
217 $$ 217 $$