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 IF EXISTS 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);
30 CREATE TRIGGER maintain_perm_i18n_tgr
31 AFTER UPDATE ON permission.perm_list
32 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_id_tracking('ppl');
34 CREATE TABLE permission.grp_tree (
35 id SERIAL PRIMARY KEY,
36 name TEXT NOT NULL UNIQUE,
37 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
38 usergroup BOOL NOT NULL DEFAULT TRUE,
39 perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
41 application_perm TEXT,
42 hold_priority INT NOT NULL DEFAULT 0
44 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
46 CREATE TABLE permission.grp_penalty_threshold (
47 id SERIAL PRIMARY KEY,
48 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 threshold NUMERIC(8,2) NOT NULL,
52 CONSTRAINT penalty_grp_once UNIQUE (grp,penalty,org_unit)
55 CREATE TABLE permission.grp_perm_map (
56 id SERIAL PRIMARY KEY,
57 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
58 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
60 grantable BOOL NOT NULL DEFAULT FALSE,
61 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
64 CREATE TABLE permission.usr_perm_map (
65 id SERIAL PRIMARY KEY,
66 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
67 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
69 grantable BOOL NOT NULL DEFAULT FALSE,
70 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
73 CREATE TABLE permission.usr_object_perm_map (
74 id SERIAL PRIMARY KEY,
75 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
76 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
77 object_type TEXT NOT NULL,
78 object_id TEXT NOT NULL,
79 grantable BOOL NOT NULL DEFAULT FALSE,
80 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
83 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
85 CREATE TABLE permission.usr_grp_map (
86 id SERIAL PRIMARY KEY,
87 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
88 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
89 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
92 CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$
93 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
96 SELECT ou.parent, ouad.distance+1
97 FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id)
98 WHERE ou.parent IS NOT NULL
100 SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
101 $$ LANGUAGE SQL ROWS 1;
103 CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
104 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
107 SELECT pgt.parent, gad.distance+1
108 FROM permission.grp_tree pgt JOIN grp_ancestors_distance gad ON (pgt.id = gad.id)
109 WHERE pgt.parent IS NOT NULL
111 SELECT * FROM grp_ancestors_distance;
112 $$ LANGUAGE SQL STABLE ROWS 1;
114 CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
115 WITH RECURSIVE grp_descendants_distance(id, distance) AS (
118 SELECT pgt.id, gdd.distance+1
119 FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON (pgt.parent = gdd.id)
121 SELECT * FROM grp_descendants_distance;
122 $$ LANGUAGE SQL STABLE ROWS 1;
124 CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$
125 WITH RECURSIVE descendant_depth AS (
128 FROM permission.grp_tree gr
133 FROM permission.grp_tree gr
134 JOIN descendant_depth dd ON (dd.id = gr.parent)
135 ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id);
136 $$ LANGUAGE SQL STABLE ROWS 1;
138 CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$
140 FROM permission.grp_ancestors($1)
143 FROM permission.grp_descendants($1);
144 $$ LANGUAGE SQL STABLE ROWS 1;
146 CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
148 FROM permission.grp_ancestors($1)
151 FROM permission.grp_ancestors($2);
152 $$ LANGUAGE SQL STABLE ROWS 1;
154 CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
156 FROM permission.grp_ancestors($1)
159 FROM permission.grp_ancestors($2);
160 $$ LANGUAGE SQL STABLE ROWS 1;
162 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
163 SELECT DISTINCT ON (usr,perm) *
165 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
167 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
168 FROM permission.grp_perm_map p
170 SELECT (permission.grp_ancestors(
171 (SELECT profile FROM actor.usr WHERE id = $1)
176 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
177 FROM permission.grp_perm_map p
178 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
180 ORDER BY 2, 3, 4 ASC, 5 DESC ;
181 $$ LANGUAGE SQL STABLE ROWS 10;
183 CREATE TABLE permission.usr_work_ou_map (
184 id SERIAL PRIMARY KEY,
185 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
186 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
187 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
190 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
192 r_usr actor.usr%ROWTYPE;
193 r_perm permission.usr_perm_map%ROWTYPE;
196 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
198 IF r_usr.active = FALSE THEN
202 IF r_usr.super_user = TRUE THEN
206 FOR r_perm IN SELECT *
207 FROM permission.usr_perms(iuser) p
208 JOIN permission.perm_list l
210 WHERE (l.code = tperm AND p.grantable IS TRUE)
214 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
215 WHERE id = r_usr.home_ou;
228 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
230 r_usr actor.usr%ROWTYPE;
231 r_perm permission.usr_perm_map%ROWTYPE;
234 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
236 IF r_usr.active = FALSE THEN
240 IF r_usr.super_user = TRUE THEN
244 FOR r_perm IN SELECT *
245 FROM permission.usr_perms(iuser) p
246 JOIN permission.perm_list l
252 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
253 WHERE id = r_usr.home_ou;
266 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
268 r_woum permission.usr_work_ou_map%ROWTYPE;
269 r_usr actor.usr%ROWTYPE;
270 r_perm permission.usr_perm_map%ROWTYPE;
273 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
275 IF r_usr.active = FALSE THEN
279 IF r_usr.super_user = TRUE THEN
283 FOR r_perm IN SELECT *
284 FROM permission.usr_perms(iuser) p
285 JOIN permission.perm_list l
291 FOR r_woum IN SELECT *
292 FROM permission.usr_work_ou_map
297 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
298 WHERE id = r_woum.work_ou;
312 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 $$
314 r_usr actor.usr%ROWTYPE;
318 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
320 IF r_usr.active = FALSE THEN
324 IF r_usr.super_user = TRUE THEN
328 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;
334 IF target_ou > -1 THEN
335 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
343 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
344 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
347 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
349 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
350 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
356 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
360 RETURNS SETOF INTEGER AS $$
362 -- Return a set of all the org units for which a given user has a given
363 -- permission, granted directly (not through inheritance from a parent
366 -- The permissions apply to a minimum depth of the org unit hierarchy,
367 -- for the org unit(s) to which the user is assigned. (They also apply
368 -- to the subordinates of those org units, but we don't report the
369 -- subordinates here.)
371 -- For purposes of this function, the permission.usr_work_ou_map table
372 -- defines which users belong to which org units. I.e. we ignore the
373 -- home_ou column of actor.usr.
375 -- The result set may contain duplicates, which should be eliminated
376 -- by a DISTINCT clause.
385 n_curr_depth INTEGER;
388 -- Check for superuser
398 return; -- No user? No permissions.
401 -- Super user has all permissions everywhere
411 RETURN NEXT n_work_ou;
416 -- Translate the permission name
417 -- to a numeric permission id
427 RETURN; -- No such permission
430 -- Find the highest-level org unit (i.e. the minimum depth)
431 -- to which the permission is applied for this user
433 -- This query is modified from the one in permission.usr_perms().
435 SELECT INTO n_min_depth
439 FROM permission.usr_perm_map upm
440 WHERE upm.usr = user_id
441 AND (upm.perm = n_perm OR upm.perm = -1)
444 FROM permission.grp_perm_map gpm
445 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
447 SELECT (permission.grp_ancestors(
448 (SELECT profile FROM actor.usr WHERE id = user_id)
453 FROM permission.grp_perm_map p
454 WHERE (p.perm = n_perm OR p.perm = -1)
456 SELECT (permission.grp_ancestors(m.grp)).id
457 FROM permission.usr_grp_map m
458 WHERE m.usr = user_id
463 RETURN; -- No such permission for this user
466 -- Identify the org units to which the user is assigned. Note that
467 -- we pay no attention to the home_ou column in actor.usr.
473 permission.usr_work_ou_map
476 LOOP -- For each org unit to which the user is assigned
478 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
479 -- We take it on faith that this depth agrees with the actual hierarchy
480 -- defined in actor.org_unit.
485 actor.org_unit_type type
486 INNER JOIN actor.org_unit ou
487 ON ( ou.ou_type = type.id )
492 CONTINUE; -- Maybe raise exception?
495 -- Compare the depth of the work org unit to the
496 -- minimum depth, and branch accordingly
498 IF n_depth = n_min_depth THEN
500 -- The org unit is at the right depth, so return it.
502 RETURN NEXT n_work_ou;
503 ELSIF n_depth > n_min_depth THEN
505 -- Traverse the org unit tree toward the root,
506 -- until you reach the minimum depth determined above
508 n_curr_depth := n_depth;
509 n_curr_ou := n_work_ou;
510 WHILE n_curr_depth > n_min_depth LOOP
511 SELECT INTO n_curr_ou
519 n_curr_depth := n_curr_depth - 1;
522 -- This can happen only if the hierarchy defined in
523 -- actor.org_unit is corrupted, or out of sync with
524 -- the depths defined in actor.org_unit_type.
525 -- Maybe we should raise an exception here, instead
526 -- of silently ignoring the problem.
533 IF n_curr_ou IS NOT NULL THEN
534 RETURN NEXT n_curr_ou;
538 -- The permission applies only at a depth greater than the work org unit.
539 -- Use connectby() to find all dependent org units at the specified depth.
543 FROM actor.org_unit_descendants_distance(n_work_ou)
545 distance = n_min_depth - n_depth
547 RETURN NEXT n_curr_ou;
556 $$ LANGUAGE 'plpgsql' ROWS 1;
559 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
563 RETURNS SETOF INTEGER AS $$
565 -- Return a set of all the org units for which a given user has a given
566 -- permission, granted either directly or through inheritance from a parent
569 -- The permissions apply to a minimum depth of the org unit hierarchy, and
570 -- to the subordinates of those org units, for the org unit(s) to which the
573 -- For purposes of this function, the permission.usr_work_ou_map table
574 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
576 -- The result set may contain duplicates, which should be eliminated
577 -- by a DISTINCT clause.
584 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
587 -- The permission applies only at a depth greater than the work org unit.
591 FROM actor.org_unit_descendants(n_head_ou)
593 RETURN NEXT n_child_ou;
600 $$ LANGUAGE 'plpgsql' ROWS 1;
603 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
607 RETURNS SETOF INTEGER AS $$
608 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
609 $$ LANGUAGE 'sql' ROWS 1;
612 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
616 RETURNS SETOF INTEGER AS $$
617 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
618 $$ LANGUAGE 'sql' ROWS 1;
620 CREATE TABLE permission.grp_tree_display_entry (
621 id SERIAL PRIMARY KEY,
622 position INTEGER NOT NULL,
623 org INTEGER NOT NULL REFERENCES actor.org_unit (id)
624 DEFERRABLE INITIALLY DEFERRED,
625 grp INTEGER NOT NULL REFERENCES permission.grp_tree (id)
626 DEFERRABLE INITIALLY DEFERRED,
627 CONSTRAINT pgtde_once_per_org UNIQUE (org, grp)
630 ALTER TABLE permission.grp_tree_display_entry
631 ADD COLUMN parent integer REFERENCES permission.grp_tree_display_entry (id)
632 DEFERRABLE INITIALLY DEFERRED;