Mercurial > gemma

annotate schema/updates/1312/01.hide_sys_admins.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents dfd990a4ac64
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')