2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
19 DROP SCHEMA permission CASCADE;
22 CREATE SCHEMA permission;
24 CREATE TABLE permission.perm_list (
25 id SERIAL PRIMARY KEY,
26 code TEXT NOT NULL UNIQUE,
29 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
31 CREATE TABLE permission.grp_tree (
32 id SERIAL PRIMARY KEY,
33 name TEXT NOT NULL UNIQUE,
34 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
35 usergroup BOOL NOT NULL DEFAULT TRUE,
36 perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
40 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
42 CREATE TABLE permission.grp_penalty_threshold (
43 id SERIAL PRIMARY KEY,
44 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
46 threshold NUMERIC(8,2) NOT NULL,
47 CONSTRAINT penalty_grp_once UNIQUE (grp,penalty)
50 CREATE TABLE permission.grp_perm_map (
51 id SERIAL PRIMARY KEY,
52 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
53 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
55 grantable BOOL NOT NULL DEFAULT FALSE,
56 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
59 CREATE TABLE permission.usr_perm_map (
60 id SERIAL PRIMARY KEY,
61 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
62 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 grantable BOOL NOT NULL DEFAULT FALSE,
65 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
68 CREATE TABLE permission.usr_object_perm_map (
69 id SERIAL PRIMARY KEY,
70 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
71 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
72 object_type TEXT NOT NULL,
73 object_id TEXT NOT NULL,
74 grantable BOOL NOT NULL DEFAULT FALSE,
75 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
78 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
80 CREATE TABLE permission.usr_grp_map (
81 id SERIAL PRIMARY KEY,
82 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
83 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
84 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
87 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
89 FROM connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
90 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
91 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
93 CASE WHEN a.parent IS NULL
97 $$ LANGUAGE SQL STABLE;
99 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
100 SELECT DISTINCT ON (usr,perm) *
102 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
104 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
105 FROM permission.grp_perm_map p
107 SELECT (permission.grp_ancestors(
108 (SELECT profile FROM actor.usr WHERE id = $1)
113 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
114 FROM permission.grp_perm_map p
115 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
117 ORDER BY 2, 3, 1 DESC, 5 DESC ;
118 $$ LANGUAGE SQL STABLE;
120 CREATE TABLE permission.usr_work_ou_map (
121 id SERIAL PRIMARY KEY,
122 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
123 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
124 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
127 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
129 r_usr actor.usr%ROWTYPE;
130 r_perm permission.usr_perm_map%ROWTYPE;
133 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
135 IF r_usr.active = FALSE THEN
139 IF r_usr.super_user = TRUE THEN
143 FOR r_perm IN SELECT *
144 FROM permission.usr_perms(iuser) p
145 JOIN permission.perm_list l
147 WHERE (l.code = tperm AND p.grantable IS TRUE)
151 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
152 WHERE id = r_usr.home_ou;
165 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
167 r_usr actor.usr%ROWTYPE;
168 r_perm permission.usr_perm_map%ROWTYPE;
171 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
173 IF r_usr.active = FALSE THEN
177 IF r_usr.super_user = TRUE THEN
181 FOR r_perm IN SELECT *
182 FROM permission.usr_perms(iuser) p
183 JOIN permission.perm_list l
189 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
190 WHERE id = r_usr.home_ou;
203 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
205 r_woum permission.usr_work_ou_map%ROWTYPE;
206 r_usr actor.usr%ROWTYPE;
207 r_perm permission.usr_perm_map%ROWTYPE;
210 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
212 IF r_usr.active = FALSE THEN
216 IF r_usr.super_user = TRUE THEN
220 FOR r_perm IN SELECT *
221 FROM permission.usr_perms(iuser) p
222 JOIN permission.perm_list l
228 FOR r_woum IN SELECT *
229 FROM permission.usr_work_ou_map
234 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
235 WHERE id = r_woum.work_ou;
249 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( iuser INT, tperm TEXT, obj_type TEXT, obj_id TEXT, target_ou INT ) RETURNS BOOL AS $$
251 r_usr actor.usr%ROWTYPE;
255 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
257 IF r_usr.active = FALSE THEN
261 IF r_usr.super_user = TRUE THEN
265 SELECT TRUE INTO res FROM permission.usr_object_perm_map WHERE usr = r_usr.id AND object_type = obj_type AND object_id = obj_id;
271 IF target_ou > -1 THEN
272 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
280 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
281 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
284 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
286 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
287 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE