Mercurial > gemma

comparison schema/manage_users_tests.sql @ 410:3f803d64a6ee

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not rely on session_user for authorization Privileges are usually checked based on current_user, which can be changed using SET ROLE, while session_user is based on the actually logged in user and can only be changed by a superuser using SET SESSION AUTHORIZATION. Using session_user for authorization purposes prevents the expected behaviour of SET ROLE. current_user_country() does not need to be SECURITY DEFINER since a while, because there is no RLS policy affected by what is mentioned in the removed comment.
author Tom Gottfried <tom@intevation.de>
date Wed, 15 Aug 2018 16:39:00 +0200
parents f5087cebc740
children 5611cf72cc92
comparison
equal deleted inserted replaced
409:cdd63547930a 410:3f803d64a6ee
136 $$ 136 $$
137 SELECT '' WHERE false -- Empty result set 137 SELECT '' WHERE false -- Empty result set
138 $$, 138 $$,
139 'Waterway admin cannot update attributes of other users in country'); 139 'Waterway admin cannot update attributes of other users in country');
140 140
141 -- The above test will pass even if the password is actually updated in case
142 -- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
143 RESET SESSION AUTHORIZATION;
144 CREATE TEMP TABLE old_pw_hash AS
145 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
146 SET SESSION AUTHORIZATION test_admin_at;
147 UPDATE users.list_users
148 SET pw = 'test_user_at2!'
149 WHERE username = 'test_user_at';
150 RESET SESSION AUTHORIZATION;
151 SELECT set_eq($$
152 SELECT rolpassword FROM old_pw_hash
153 $$,
154 $$
155 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
156 $$,
157 'Waterway admin cannot update password of other users in country');
158
159
141 SET SESSION AUTHORIZATION test_sys_admin1; 160 SET SESSION AUTHORIZATION test_sys_admin1;
142 161
143 SELECT lives_ok($$ 162 SELECT lives_ok($$
144 INSERT INTO users.list_users VALUES ( 163 INSERT INTO users.list_users VALUES (
145 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); 164 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
221 'Non-compliant password is not accepted'); 240 'Non-compliant password is not accepted');
222 241
223 -- To compare passwords, we need to run the following tests as superuser 242 -- To compare passwords, we need to run the following tests as superuser
224 RESET SESSION AUTHORIZATION; 243 RESET SESSION AUTHORIZATION;
225 244
226 CREATE TEMP TABLE old_pw_hash AS 245 UPDATE old_pw_hash SET rolpassword = (
227 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'; 246 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');
228 247
229 UPDATE users.list_users 248 UPDATE users.list_users
230 SET (rolname, username, pw, country, map_extent, email_address) 249 SET (rolname, username, pw, country, map_extent, email_address)
231 = ('waterway_user', 'test_user_at', NULL, 'AT', 250 = ('waterway_user', 'test_user_at', NULL, 'AT',
232 (SELECT map_extent FROM internal.user_profiles 251 (SELECT map_extent FROM internal.user_profiles