Mercurial > gemma

diff 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
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1312/01.hide_sys_admins.sql	Fri Oct 18 17:55:12 2019 +0200
@@ -0,0 +1,19 @@
+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')