1 DROP SCHEMA permission CASCADE;
4 CREATE SCHEMA permission;
6 CREATE TABLE permission.perm_list (
8 code TEXT NOT NULL UNIQUE
10 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
11 INSERT INTO permission.perm_list VALUES (DEFAULT,'EVERYTHING');
12 INSERT INTO permission.perm_list VALUES (DEFAULT,'OPAC_LOGIN');
14 CREATE TABLE permission.grp_tree (
15 id SERIAL PRIMARY KEY,
16 name TEXT NOT NULL UNIQUE,
17 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT
19 CREATE INDEX grp_tree_parent ON permission.grp_tree (parent);
20 INSERT INTO permission.grp_tree VALUES (DEFAULT,'Users');
21 INSERT INTO permission.grp_tree VALUES (DEFAULT,'Admin',1);
23 CREATE TABLE permission.grp_perm_map (
24 id SERIAL PRIMARY KEY,
25 grp INT NOT NULL REFERENCES permission.grp_tree (id),
26 perm INT NOT NULL REFERENCES permission.perm_list (id),
28 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
30 INSERT INTO permission.grp_perm_map VALUES (DEFAULT,1,2,0);
31 INSERT INTO permission.grp_perm_map VALUES (DEFAULT,2,1,0);
33 CREATE TABLE permission.usr_perm_map (
34 id SERIAL PRIMARY KEY,
35 usr INT NOT NULL REFERENCES actor.usr (id),
36 perm INT NOT NULL REFERENCES permission.perm_list (id),
38 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
41 CREATE TABLE permission.usr_grp_map (
42 id SERIAL PRIMARY KEY,
43 usr INT NOT NULL REFERENCES actor.usr (id),
44 grp INT NOT NULL REFERENCES permission.grp_tree (id),
45 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
48 INSERT INTO permission.usr_grp_map (usr,grp)
49 SELECT id, (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1) FROM actor.usr;
51 INSERT INTO permission.usr_grp_map (usr,grp)
52 SELECT 1, id FROM permission.grp_tree WHERE name = 'Admin';
54 CREATE OR REPLACE RULE add_usr_to_group AS
55 ON INSERT TO actor.usr DO ALSO
56 INSERT INTO permission.usr_grp_map (usr, grp) VALUES (
58 (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1)
62 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
64 FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.')
65 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
66 JOIN permission.grp_tree a ON a.id = t.keyid
68 CASE WHEN a.parent IS NULL
72 $$ LANGUAGE SQL STABLE;
74 CREATE OR REPLACE FUNCTION permission.usr_perms ( iuser INT ) RETURNS SETOF permission.usr_perm_map AS $$
76 u_perm permission.usr_perm_map%ROWTYPE;
77 grp permission.usr_grp_map%ROWTYPE;
78 g_list permission.grp_tree%ROWTYPE;
80 FOR u_perm IN SELECT * FROM permission.usr_perm_map WHERE usr = iuser LOOP
85 FROM permission.usr_grp_map
86 WHERE usr = iuser LOOP
88 FOR g_list IN SELECT *
89 FROM permission.grp_ancestors( grp.grp ) LOOP
91 FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth
92 FROM permission.grp_perm_map p
93 JOIN permission.usr_grp_map m ON (m.grp = p.grp)
94 WHERE m.grp = g_list.id LOOP
106 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$
108 r_usr actor.usr%ROWTYPE;
109 r_perm permission.usr_perm_map%ROWTYPE;
112 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
114 FOR r_perm IN SELECT *
115 FROM permission.usr_perms(iuser) p
116 JOIN permission.perm_list l
118 WHERE l.code = tperm LOOP
121 FROM actor.org_unit_descendants(target,r_perm.depth)
122 WHERE id = r_usr.home_ou;