Mercurial > gemma

comparison schema/updates/1312/01.hide_sys_admins.sql @ 4755:dfd990a4ac64

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Hide sys_admin accounts to waterway admins Since imports and import configurations are authorized based on the visibility of the user that created the import, that way waterway admins are no longer allowed to see imports and import configurations created by sys_admins.
author Tom Gottfried <tom@intevation.de>
date Fri, 18 Oct 2019 17:55:12 +0200
parents
children
comparison
equal deleted inserted replaced
4754:64979fec89a7 4755:dfd990a4ac64
1 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
2 SELECT
3 r.rolname,
4 p.username,
5 CAST('' AS varchar) AS pw,
6 p.country,
7 p.map_extent,
8 p.email_address
9 FROM internal.user_profiles p
10 JOIN pg_roles u ON p.username = u.rolname
11 JOIN pg_auth_members a ON u.oid = a.member
12 JOIN pg_roles r ON a.roleid = r.oid
13 WHERE p.username = current_user
14 OR pg_has_role('waterway_admin', 'MEMBER')
15 AND p.country = (
16 SELECT country FROM internal.user_profiles
17 WHERE username = current_user)
18 AND r.rolname <> 'sys_admin'
19 OR pg_has_role('sys_admin', 'MEMBER')