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
84 FOR g_list IN SELECT *
85 FROM permission.grp_ancestors(
93 FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth
94 FROM permission.grp_perm_map p
95 WHERE p.grp = g_list.id LOOP
103 FROM permission.usr_grp_map
104 WHERE usr = iuser LOOP
106 FOR g_list IN SELECT *
107 FROM permission.grp_ancestors( grp.grp ) LOOP
109 FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth
110 FROM permission.grp_perm_map p
111 JOIN permission.usr_grp_map m ON (m.grp = p.grp)
112 WHERE m.grp = g_list.id LOOP
124 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$
126 r_usr actor.usr%ROWTYPE;
127 r_perm permission.usr_perm_map%ROWTYPE;
130 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
132 FOR r_perm IN SELECT *
133 FROM permission.usr_perms(iuser) p
134 JOIN permission.perm_list l
136 WHERE l.code = tperm LOOP
139 FROM actor.org_unit_descendants(target,r_perm.depth)
140 WHERE id = r_usr.home_ou;