Mercurial > gemma

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4755
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 SELECT
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 r.rolname,
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 p.username,
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 CAST('' AS varchar) AS pw,
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 p.country,
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 p.map_extent,
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 p.email_address
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 FROM internal.user_profiles p
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 JOIN pg_roles u ON p.username = u.rolname
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 JOIN pg_auth_members a ON u.oid = a.member
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 JOIN pg_roles r ON a.roleid = r.oid
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 WHERE p.username = current_user
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 OR pg_has_role('waterway_admin', 'MEMBER')
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 AND p.country = (
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 SELECT country FROM internal.user_profiles
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 WHERE username = current_user)
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 AND r.rolname <> 'sys_admin'
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 OR pg_has_role('sys_admin', 'MEMBER')