Mercurial > gemma
changeset 263:13ad969a9138
Enable listing of users for all roles with appropriate filters
Waterway users should see their own account data and their should be a
single interface for account data (i.e. users.list_users). Therefore, also
the RLS policy on user_profiles for waterway_admin is translated to the
view.
current_user_country() moved because it's needed earlier during database
setup, now.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 19:03:56 +0200 |
parents | 92470caf81fd |
children | 7f030ec3472d |
files | schema/auth.sql schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
diffstat | 4 files changed, 46 insertions(+), 20 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Fri Jul 27 15:26:16 2018 +0200 +++ b/schema/auth.sql Fri Jul 27 19:03:56 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 Fri Jul 27 15:26:16 2018 +0200 +++ b/schema/manage_users.sql Fri Jul 27 19:03:56 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 Fri Jul 27 15:26:16 2018 +0200 +++ b/schema/manage_users_tests.sql Fri Jul 27 19:03:56 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 Fri Jul 27 15:26:16 2018 +0200 +++ b/schema/run_tests.sh Fri Jul 27 19:03:56 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()'