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
|
create or replace function pgq.grant_perms(x_queue_name text)
returns integer as $$
-- ----------------------------------------------------------------------
-- Function: pgq.grant_perms(1)
--
-- Make event tables readable by public.
--
-- Parameters:
-- x_queue_name - Name of the queue.
--
-- Returns:
-- nothing
-- ----------------------------------------------------------------------
declare
q record;
i integer;
tbl_perms text;
seq_perms text;
begin
select * from pgq.queue into q
where queue_name = x_queue_name;
if not found then
raise exception 'Queue not found';
end if;
if true then
-- safe, all access must go via functions
seq_perms := 'select';
tbl_perms := 'select';
else
-- allow ordinery users to directly insert
-- to event tables. dangerous.
seq_perms := 'select, update';
tbl_perms := 'select, insert';
end if;
-- tick seq, normal users don't need to modify it
execute 'grant ' || seq_perms
|| ' on ' || q.queue_tick_seq || ' to public';
-- event seq
execute 'grant ' || seq_perms
|| ' on ' || q.queue_event_seq || ' to public';
-- parent table for events
execute 'grant select on ' || q.queue_data_pfx || ' to public';
-- real event tables
for i in 0 .. q.queue_ntables - 1 loop
execute 'grant ' || tbl_perms
|| ' on ' || q.queue_data_pfx || '_' || i
|| ' to public';
end loop;
return 1;
end;
$$ language plpgsql security definer;
|
