Mercurial > gemma
changeset 267:7f030ec3472d
Merged
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Mon, 30 Jul 2018 11:08:17 +0200 |
parents | a2f7049daf7f (current diff) 13ad969a9138 (diff) |
children | 72062ca52746 d1b0d964af09 4d37c8646123 |
files | |
diffstat | 4 files changed, 46 insertions(+), 20 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Mon Jul 30 11:06:50 2018 +0200 +++ b/schema/auth.sql Mon Jul 30 11:08:17 2018 +0200 @@ -79,17 +79,6 @@ -- RLS policies for waterway_admin -- --- Security-definer function to get current users country, which allows to --- restrict the view on user_profiles by country without infinite recursion -CREATE FUNCTION current_user_country() - RETURNS users.user_profiles.country%TYPE - AS $$ - SELECT country FROM users.user_profiles WHERE username = session_user - $$ - LANGUAGE SQL - SECURITY DEFINER - STABLE PARALLEL SAFE; - -- Staging area -- TODO: add all relevant tables here CREATE POLICY responsibility_area ON waterway.bottlenecks
--- a/schema/manage_users.sql Mon Jul 30 11:06:50 2018 +0200 +++ b/schema/manage_users.sql Mon Jul 30 11:08:17 2018 +0200 @@ -24,12 +24,33 @@ LANGUAGE plpgsql; -CREATE OR REPLACE VIEW sys_admin.list_users AS +-- Security-definer function to get current users country, which allows to +-- restrict the view on user_profiles by country without infinite recursion +CREATE FUNCTION current_user_country() + RETURNS users.user_profiles.country%TYPE + AS $$ + SELECT country FROM users.user_profiles WHERE username = session_user + $$ + LANGUAGE SQL + SECURITY DEFINER + STABLE PARALLEL SAFE; + + +CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS + WITH cur AS ( + SELECT rolname + FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid + WHERE member = ( + SELECT oid FROM pg_roles WHERE rolname = current_user)) SELECT r.rolname, p.* - FROM users.user_profiles p + FROM cur, users.user_profiles p JOIN pg_roles u ON p.username = u.rolname JOIN pg_auth_members a ON u.oid = a.member - JOIN pg_roles r ON a.roleid = r.oid; + JOIN pg_roles r ON a.roleid = r.oid + WHERE p.username = current_user + OR cur.rolname = 'waterway_admin' + AND p.country = current_user_country() + OR cur.rolname = 'sys_admin'; CREATE OR REPLACE FUNCTION sys_admin.create_user(
--- a/schema/manage_users_tests.sql Mon Jul 30 11:06:50 2018 +0200 +++ b/schema/manage_users_tests.sql Mon Jul 30 11:08:17 2018 +0200 @@ -13,15 +13,31 @@ 42501, NULL, 'Less privileged user cannot call function in schema sys_admin'); -SET SESSION AUTHORIZATION test_sys_admin1; - -- -- Role listing -- -SELECT isnt_empty($$ - SELECT * FROM sys_admin.list_users +SET SESSION AUTHORIZATION test_user_at; +SELECT results_eq($$ + SELECT username FROM users.list_users + $$, + $$ + SELECT CAST(current_user AS varchar) $$, - 'List of users can be queried'); + 'User should only see his own profile'); + +SET SESSION AUTHORIZATION test_admin_at; +SELECT set_eq($$ + SELECT DISTINCT country FROM users.list_users + $$, + ARRAY['AT'], + 'Waterway admin should only see profiles of his country'); + +SET SESSION AUTHORIZATION test_sys_admin1; +SELECT set_eq($$ + SELECT count(*) FROM users.list_users + $$, + ARRAY[4], + 'System admin can see all users'); -- -- Role creation
--- a/schema/run_tests.sh Mon Jul 30 11:06:50 2018 +0200 +++ b/schema/run_tests.sh Mon Jul 30 11:08:17 2018 +0200 @@ -15,7 +15,7 @@ psql -qXv ON_ERROR_STOP= -v -d gemma_test \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(40)' \ + -c 'SELECT plan(42)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'