Mercurial > gemma
diff schema/manage_users.sql @ 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 | 72062ca52746 |
line wrap: on
line diff
--- 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(