create or replace function check_user_privs text eid int returns boole

 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
create or replace function check_user_privs(u text, eid int)
returns boolean as $$
declare
c int;
managerID int;
begin
c = 0;
select employee_id into managerID
from employees
where email = u;
with recursive cte as
(
select employee_id, 0 as lvl
from employees
where employee_id = managerID
union all
select e.employee_id, cte.lvl + 1 as lvl
from employees e inner join cte
on manager_id = cte.employee_id
)
select count(*) into c from cte where employee_id=eid;
if c > 0 or eid = managerID then
return true;
else
return false;
end if;
end;
$$ language plpgsql security definer;
alter policy emp_access on employees
using(check_user_privs(current_user, employee_id))
with check (check_user_privs(current_user, employee_id));
-- check
select employee_id, manager_id from employees;