Mercurial > gemma

comparison schema/manage_users_tests.sql @ 307:750a9c9cd965

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use SQL UPDATE to update users This implies it's not a database error anymore to try to update a non-existent user. Thus, handle this as a HTTP-404 in the backend, which is in line with what GET does. Using UPDATE here will allow to GRANT column-wise privileges. The password has become part of the view to be updatable as well.
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Aug 2018 15:49:38 +0200
parents 72062ca52746
children ac760b0f22a9
comparison
equal deleted inserted replaced
306:70592a18c5c6 307:750a9c9cd965
103 -- Role update 103 -- Role update
104 -- 104 --
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 UPDATE users.list_users
109 'waterway_user', 'test2_new', 'new_secret1$', 'AT', 109 SET (rolname, username, pw, country, map_extent, email_address)
110 (SELECT map_extent FROM users.list_users 110 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
111 WHERE username = 'test_user_at'), 'test5') 111 (SELECT map_extent FROM users.list_users
112 WHERE username = 'test_user_at'), 'test5')
113 WHERE username = 'test2'
112 $$, 114 $$,
113 'Existing user can be updated'); 115 'Existing user can be updated');
114 116
115 SELECT throws_ok($$ 117 SELECT throws_ok($$
116 SELECT sys_admin.update_user('test_non_existent', 118 UPDATE users.list_users
117 'waterway_user', 'test_non_existent', '', 'AT', 119 SET (rolname, username, pw, country, map_extent, email_address)
118 (SELECT map_extent FROM users.list_users 120 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
119 WHERE username = 'test_user_at'), 'test5') 121 (SELECT map_extent FROM users.list_users
120 $$, 122 WHERE username = 'test_user_at'), 'test6')
121 42704, NULL, 123 WHERE username = CAST(current_user AS varchar)
122 'Non-existent user cannot be updated');
123
124 SELECT throws_ok($$
125 SELECT sys_admin.update_user(CAST(current_user AS varchar),
126 'waterway_user', 'test_new_name', 'secret1$', 'AT',
127 (SELECT map_extent FROM users.list_users
128 WHERE username = 'test_user_at'), 'test6')
129 $$, 124 $$,
130 '0A000', NULL, 125 '0A000', NULL,
131 'Name of current user cannot be altered'); 126 'Name of current user cannot be altered');
132 127
133 SELECT throws_ok($$ 128 SELECT throws_ok($$
134 SELECT sys_admin.update_user('test_user_at', 129 UPDATE users.list_users
135 'invalid', 'test2', 'secret1$', 'AT', 130 SET (rolname, username, pw, country, map_extent, email_address)
136 (SELECT map_extent FROM users.list_users 131 = ('invalid', 'test2', 'secret1$', 'AT',
137 WHERE username = 'test_user_at'), 'test2') 132 (SELECT map_extent FROM users.list_users
133 WHERE username = 'test_user_at'), 'test2')
134 WHERE username = 'test_user_at'
138 $$, 135 $$,
139 42704, NULL, 136 42704, NULL,
140 'Valid role name has to be provided'); 137 'Valid role name has to be provided');
141 138
142 SELECT throws_ok($$ 139 SELECT throws_ok($$
143 SELECT sys_admin.update_user('test_user_at', 140 UPDATE users.list_users
144 'waterway_user', NULL, 'secret1$', 'AT', 141 SET (rolname, username, pw, country, map_extent, email_address)
145 (SELECT map_extent FROM users.list_users 142 = ('waterway_user', NULL, 'secret1$', 'AT',
146 WHERE username = 'test_user_at'), 'test3') 143 (SELECT map_extent FROM users.list_users
144 WHERE username = 'test_user_at'), 'test3')
145 WHERE username = 'test_user_at'
147 $$, 146 $$,
148 23502, NULL, 147 23502, NULL,
149 'New username is mandatory'); 148 'New username is mandatory');
150 -- Though other arguments are mandatory, too, there are no explicit tests 149 -- Though other arguments are mandatory, too, there are no explicit tests
151 150
152 SELECT throws_ok($$ 151 SELECT throws_ok($$
153 SELECT sys_admin.update_user('test_user_at', 152 UPDATE users.list_users
154 'waterway_user', 'waterway_user', 'secret1$', 'AT', 153 SET (rolname, username, pw, country, map_extent, email_address)
155 (SELECT map_extent FROM users.list_users 154 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
156 WHERE username = 'test_user_at'), 'test4') 155 (SELECT map_extent FROM users.list_users
156 WHERE username = 'test_user_at'), 'test4')
157 WHERE username = 'test_user_at'
157 $$, 158 $$,
158 42710, NULL, 159 42710, NULL,
159 'Reserved role names cannot be used as username'); 160 'Reserved role names cannot be used as username');
160 161
161 SELECT throws_ok($$ 162 SELECT throws_ok($$
162 SELECT sys_admin.update_user('test_user_at', 163 UPDATE users.list_users
163 'waterway_user', 'test_user_ro', 'secret1$', 'AT', 164 SET (rolname, username, pw, country, map_extent, email_address)
164 (SELECT map_extent FROM users.list_users 165 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
165 WHERE username = 'test_user_at'), 'test4') 166 (SELECT map_extent FROM users.list_users
167 WHERE username = 'test_user_at'), 'test4')
168 WHERE username = 'test_user_at'
166 $$, 169 $$,
167 23505, NULL, 170 23505, NULL,
168 'No duplicate user name is allowed'); 171 'No duplicate user name is allowed');
169 172
170 -- Test password policy (only one rule to ensure it's also used on update) 173 -- Test password policy (only one rule to ensure it's also used on update)
171 SELECT throws_ok($$ 174 SELECT throws_ok($$
172 SELECT sys_admin.update_user('test_user_at', 175 UPDATE users.list_users
173 'waterway_user', 'test_user_at', 'secret', 'AT', 176 SET (rolname, username, pw, country, map_extent, email_address)
174 (SELECT map_extent FROM users.list_users 177 = ('waterway_user', 'test_user_at', 'secret', 'AT',
175 WHERE username = 'test_user_at'), 'test4') 178 (SELECT map_extent FROM users.list_users
179 WHERE username = 'test_user_at'), 'test4')
180 WHERE username = 'test_user_at'
176 $$, 181 $$,
177 '28P01', NULL, 182 '28P01', NULL,
178 'Non-compliant password is not accepted'); 183 'Non-compliant password is not accepted');
179 184
180 -- To compare passwords, we need to run the following tests as superuser 185 -- To compare passwords, we need to run the following tests as superuser
181 RESET SESSION AUTHORIZATION; 186 RESET SESSION AUTHORIZATION;
182 187
183 CREATE TEMP TABLE old_pw_hash AS 188 CREATE TEMP TABLE old_pw_hash AS
184 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'; 189 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
185 190
186 SELECT sys_admin.update_user('test_user_at', 191 UPDATE users.list_users
187 'waterway_user', 'test_user_at', NULL, 'AT', 192 SET (rolname, username, pw, country, map_extent, email_address)
188 (SELECT map_extent FROM internal.user_profiles 193 = ('waterway_user', 'test_user_at', NULL, 'AT',
189 WHERE username = 'test_user_at'), 'xxx'); 194 (SELECT map_extent FROM internal.user_profiles
195 WHERE username = 'test_user_at'), 'xxx')
196 WHERE username = 'test_user_at';
190 SELECT set_eq($$ 197 SELECT set_eq($$
191 SELECT rolpassword FROM old_pw_hash 198 SELECT rolpassword FROM old_pw_hash
192 $$, 199 $$,
193 $$ 200 $$
194 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' 201 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
195 $$, 202 $$,
196 'Giving NULL password does not change password'); 203 'Giving NULL password does not change password');
197 204
198 SELECT sys_admin.update_user('test_user_at', 205 UPDATE users.list_users
199 'waterway_user', 'test_user_at', '', 'AT', 206 SET (rolname, username, pw, country, map_extent, email_address)
200 (SELECT map_extent FROM internal.user_profiles 207 = ('waterway_user', 'test_user_at', '', 'AT',
201 WHERE username = 'test_user_at'), 'xxx'); 208 (SELECT map_extent FROM internal.user_profiles
209 WHERE username = 'test_user_at'), 'xxx')
210 WHERE username = 'test_user_at';
202 SELECT set_eq($$ 211 SELECT set_eq($$
203 SELECT rolpassword FROM old_pw_hash 212 SELECT rolpassword FROM old_pw_hash
204 $$, 213 $$,
205 $$ 214 $$
206 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' 215 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
207 $$, 216 $$,
208 'Giving empty string as password does not change password'); 217 'Giving empty string as password does not change password');
209 218
210 SELECT sys_admin.update_user('test_user_at', 219 UPDATE users.list_users
211 'waterway_user', 'test_user_at', 'new_pw1$', 'AT', 220 SET (rolname, username, pw, country, map_extent, email_address)
212 (SELECT map_extent FROM internal.user_profiles 221 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
213 WHERE username = 'test_user_at'), 'xxx'); 222 (SELECT map_extent FROM internal.user_profiles
223 WHERE username = 'test_user_at'), 'xxx')
224 WHERE username = 'test_user_at';
214 SELECT set_ne($$ 225 SELECT set_ne($$
215 SELECT rolpassword FROM old_pw_hash 226 SELECT rolpassword FROM old_pw_hash
216 $$, 227 $$,
217 $$ 228 $$
218 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at' 229 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'