Mercurial > gemma
annotate schema/updates/1312/01.hide_sys_admins.sql @ 4755:dfd990a4ac64
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 |
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') |