Mercurial > gemma

diff schema/manage_users.sql @ 247:946baea3d280

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add view to list user profiles with role To be able to GRANT privileges ON ALL TABLES IN SCHEMA, use auth.sql last during database setup.
author Tom Gottfried <tom@intevation.de>
date Thu, 26 Jul 2018 19:33:42 +0200
parents 531d1f8a2b4b
children 92470caf81fd
line wrap: on
line diff
--- a/schema/manage_users.sql	Thu Jul 26 19:22:07 2018 +0200
+++ b/schema/manage_users.sql	Thu Jul 26 19:33:42 2018 +0200
@@ -3,6 +3,14 @@
 -- exposing it to privileged users
 --
 
+CREATE OR REPLACE VIEW sys_admin.list_users AS
+    SELECT r.rolname, p.*
+        FROM users.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;
+
+
 CREATE OR REPLACE FUNCTION sys_admin.create_user(
        userrole varchar,
        username users.user_profiles.username%TYPE,