Mercurial > gemma
changeset 269:0b2d9f96ddb8
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 | 02aaff4b4a66 |
files | schema/manage_users.sql |
diffstat | 1 files changed, 3 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/manage_users.sql Mon Jul 30 11:38:09 2018 +0200 +++ b/schema/manage_users.sql Mon Jul 30 12:26:54 2018 +0200 @@ -38,20 +38,15 @@ CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS - WITH cur AS ( - SELECT rolname - FROM pg_roles r JOIN pg_auth_members a ON r.oid = a.roleid - WHERE member = ( - SELECT oid FROM pg_roles WHERE rolname = current_user)) SELECT r.rolname, p.* - FROM cur, internal.user_profiles p + 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 cur.rolname = 'waterway_admin' + OR pg_has_role('waterway_admin', 'MEMBER') AND p.country = current_user_country() - OR cur.rolname = 'sys_admin'; + OR pg_has_role('sys_admin', 'MEMBER'); CREATE OR REPLACE FUNCTION sys_admin.create_user(