Mercurial > gemma
comparison 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 |
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 $$ |