Mercurial > gemma
view schema/updates/1312/01.hide_sys_admins.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | dfd990a4ac64 |
children |
line wrap: on
line source
CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS SELECT r.rolname, p.username, CAST('' AS varchar) AS pw, p.country, p.map_extent, p.email_address FROM internal.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 WHERE p.username = current_user OR pg_has_role('waterway_admin', 'MEMBER') AND p.country = ( SELECT country FROM internal.user_profiles WHERE username = current_user) AND r.rolname <> 'sys_admin' OR pg_has_role('sys_admin', 'MEMBER')