Mercurial > gemma
changeset 334:df1fc589ad9d
Prevent Waterway Admins from updating users from their country
We need to ensure this in the trigger function, because we cannot apply
different rules for SELECT and UPDATE like with RLS policies on real tables.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 03 Aug 2018 17:23:55 +0200 |
| parents | 154e0f5bff0a |
| children | 9d69eb2f0af3 |
| files | schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
| diffstat | 3 files changed, 24 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/manage_users.sql Fri Aug 03 16:04:14 2018 +0200 +++ b/schema/manage_users.sql Fri Aug 03 17:23:55 2018 +0200 @@ -93,6 +93,14 @@ BEGIN cur_username = OLD.username; + IF cur_username <> session_user + AND NOT (pg_has_role(session_user, 'sys_admin', 'MEMBER') + OR pg_has_role(session_user, 'pw_reset', 'MEMBER')) + THEN + -- Discard row. This is what WITH CHECK in an RLS policy would do. + RETURN NULL; + END IF; + UPDATE internal.user_profiles p SET (username, country, map_extent, email_address) = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
--- a/schema/manage_users_tests.sql Fri Aug 03 16:04:14 2018 +0200 +++ b/schema/manage_users_tests.sql Fri Aug 03 17:23:55 2018 +0200 @@ -123,6 +123,21 @@ 42501, NULL, 'Waterway user cannot update arbitrary user attributes'); +SET SESSION AUTHORIZATION test_admin_at; + +SELECT results_eq($$ + UPDATE users.list_users + SET (pw, map_extent, email_address) + = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test') + WHERE country = users.current_user_country() + AND username <> current_user + RETURNING * + $$, + $$ + SELECT '' WHERE false -- Empty result set + $$, + 'Waterway admin cannot update attributes of other users in country'); + SET SESSION AUTHORIZATION test_sys_admin1; SELECT lives_ok($$
--- a/schema/run_tests.sh Fri Aug 03 16:04:14 2018 +0200 +++ b/schema/run_tests.sh Fri Aug 03 17:23:55 2018 +0200 @@ -16,7 +16,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(44)' \ + -c 'SELECT plan(45)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'