1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
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;
|
