diff options
Diffstat (limited to 'sql/functions/auth.get_all_roles_for_user.sql')
| -rw-r--r-- | sql/functions/auth.get_all_roles_for_user.sql | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/sql/functions/auth.get_all_roles_for_user.sql b/sql/functions/auth.get_all_roles_for_user.sql new file mode 100644 index 0000000..712bd4b --- /dev/null +++ b/sql/functions/auth.get_all_roles_for_user.sql @@ -0,0 +1,163 @@ +drop function auth.get_all_roles_for_user ( + p_user_id bigint +); + +create or replace function auth.get_all_roles_for_user ( + p_user_id bigint +) +returns setof auth.user_roles_t +as $$ +begin + if auth.superuser_p(p_user_id) = true::bool then + return query + with all_user_roles as ( + select null::bigint as user_role_group_id, + p_user_id as user_id, + null::bigint as role_group_id, + null as role_group_name, + null as role_group_description, + null::bigint as role_group_role_id, + null::bigint as role_id, + '_Public' as role_name, + null as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + union + select null::bigint as user_role_group_id, + p_user_id as user_id, + rg.id as role_group_id, + rg.name as role_group_name, + rg.description as role_group_description, + rgr.id as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + from auth.role_groups rg + inner join auth.role_groups_roles rgr + on rgr.role_group_id = rg.id + inner join auth.roles r + on rgr.role_id = r.id + ) + select a.user_role_group_id, + a.user_id, + a.role_group_id, + a.role_group_name, + a.role_group_description, + a.role_group_role_id, + a.role_id, + a.role_name, + a.role_description + from ( + select r.user_role_group_id as user_role_group_id, + r.user_id as user_id, + r.role_group_id as role_group_id, + r.role_group_name as role_group_name, + r.role_group_description as role_group_description, + r.role_group_role_id as role_group_role_id, + r.role_id as role_id, + r.role_name as role_name, + r.role_description as role_description, + r.role_id_exclude as role_id_exclude, + r.role_id_append as role_id_append + from all_user_roles r + ) a + order by role_name asc; + else + return query + with all_user_roles as ( + select null::bigint as user_role_group_id, + p_user_id as user_id, + null::bigint as role_group_id, + null as role_group_name, + null as role_group_description, + null::bigint as role_group_role_id, + null::bigint as role_id, + '_Public' as role_name, + null as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + union + select urg.id as user_role_group_id, + urg.user_id, + rg.id as role_group_id, + rg.name as role_group_name, + rg.description as role_group_description, + rgr.id as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + from auth.users_role_groups urg + inner join auth.role_groups rg + on urg.role_group_id = rg.id + inner join auth.role_groups_roles rgr + on rgr.role_group_id = rg.id + inner join auth.roles r + on rgr.role_id = r.id + union + select null::bigint as user_role_group_id, + ure.user_id, + null::bigint as role_group_id, + null as role_group_name, + null role_group_description, + null::bigint as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description role_description, + ure.role_id as role_id_exclude, + null::bigint as role_id_append + from auth.users_roles_exclude ure + inner join auth.roles r + on ure.role_id = r.id + where ure.user_id = p_user_id + union + select null::bigint as user_role_group_id, + ura.user_id, + null::bigint as role_group_id, + null as role_group_name, + null role_group_description, + null::bigint as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description role_description, + null::bigint as role_id_exclude, + ura.role_id as role_id_append + from auth.users_roles_append ura + inner join auth.roles r + on ura.role_id = r.id + where ura.user_id = p_user_id + ) + select a.user_role_group_id, + a.user_id, + a.role_group_id, + a.role_group_name, + a.role_group_description, + a.role_group_role_id, + a.role_id, + a.role_name, + a.role_description + from ( + select r.user_role_group_id as user_role_group_id, + r.user_id as user_id, + r.role_group_id as role_group_id, + r.role_group_name as role_group_name, + r.role_group_description as role_group_description, + r.role_group_role_id as role_group_role_id, + r.role_id as role_id, + r.role_name as role_name, + r.role_description as role_description, + r.role_id_exclude as role_id_exclude, + r.role_id_append as role_id_append + from all_user_roles r + where user_id = p_user_id + ) a + where a.role_id_exclude is null + order by role_name asc; + end if; + + return; +end; +$$ language plpgsql; |