Mercurial > gemma

comparison schema/manage_users.sql @ 269:0b2d9f96ddb8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Replace CTE with access privilege inquiry function call.
author Tom Gottfried <tom@intevation.de>
date Mon, 30 Jul 2018 12:26:54 +0200
parents 72062ca52746
children 750a9c9cd965
comparison
equal deleted inserted replaced
268:72062ca52746 269:0b2d9f96ddb8
36 SECURITY DEFINER 36 SECURITY DEFINER
37 STABLE PARALLEL SAFE; 37 STABLE PARALLEL SAFE;
38 38
39 39
40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS 40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
41 WITH cur AS (
42 SELECT rolname
43 FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid
44 WHERE member = (
45 SELECT oid FROM pg_roles WHERE rolname = current_user))
46 SELECT r.rolname, p.* 41 SELECT r.rolname, p.*
47 FROM cur, internal.user_profiles p 42 FROM internal.user_profiles p
48 JOIN pg_roles u ON p.username = u.rolname 43 JOIN pg_roles u ON p.username = u.rolname
49 JOIN pg_auth_members a ON u.oid = a.member 44 JOIN pg_auth_members a ON u.oid = a.member
50 JOIN pg_roles r ON a.roleid = r.oid 45 JOIN pg_roles r ON a.roleid = r.oid
51 WHERE p.username = current_user 46 WHERE p.username = current_user
52 OR cur.rolname = 'waterway_admin' 47 OR pg_has_role('waterway_admin', 'MEMBER')
53 AND p.country = current_user_country() 48 AND p.country = current_user_country()
54 OR cur.rolname = 'sys_admin'; 49 OR pg_has_role('sys_admin', 'MEMBER');
55 50
56 51
57 CREATE OR REPLACE FUNCTION sys_admin.create_user( 52 CREATE OR REPLACE FUNCTION sys_admin.create_user(
58 userrole varchar, 53 userrole varchar,
59 username internal.user_profiles.username%TYPE, 54 username internal.user_profiles.username%TYPE,