1 DROP SCHEMA permission CASCADE;
4 CREATE SCHEMA permission;
6 CREATE TABLE permission.perm_list (
8 code TEXT NOT NULL UNIQUE,
11 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
13 CREATE TABLE permission.grp_tree (
14 id SERIAL PRIMARY KEY,
15 name TEXT NOT NULL UNIQUE,
16 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT,
17 usergroup BOOL NOT NULL DEFAULT TRUE,
18 perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
22 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
24 CREATE TABLE permission.grp_perm_map (
25 id SERIAL PRIMARY KEY,
26 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
27 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
29 grantable BOOL NOT NULL DEFAULT FALSE,
30 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
33 CREATE TABLE permission.usr_perm_map (
34 id SERIAL PRIMARY KEY,
35 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
36 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
38 grantable BOOL NOT NULL DEFAULT FALSE,
39 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
42 CREATE TABLE permission.usr_object_perm_map (
43 id SERIAL PRIMARY KEY,
44 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
45 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
46 object_type TEXT NOT NULL,
47 object_id TEXT NOT NULL,
48 grantable BOOL NOT NULL DEFAULT FALSE,
49 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
52 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
54 CREATE TABLE permission.usr_grp_map (
55 id SERIAL PRIMARY KEY,
56 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
57 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
58 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
61 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
63 FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.')
64 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
65 JOIN permission.grp_tree a ON a.id = t.keyid
67 CASE WHEN a.parent IS NULL
71 $$ LANGUAGE SQL STABLE;
73 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
74 SELECT DISTINCT ON (usr,perm) *
76 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
78 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
79 FROM permission.grp_perm_map p
81 SELECT (permission.grp_ancestors(
82 (SELECT profile FROM actor.usr WHERE id = $1)
87 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
88 FROM permission.grp_perm_map p
89 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
91 ORDER BY 2, 3, 1 DESC, 5 DESC ;
92 $$ LANGUAGE SQL STABLE;
94 CREATE TABLE permission.usr_work_ou_map (
95 id SERIAL PRIMARY KEY,
96 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
97 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
98 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
101 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
103 r_usr actor.usr%ROWTYPE;
104 r_perm permission.usr_perm_map%ROWTYPE;
107 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
109 IF r_usr.active = FALSE THEN
113 IF r_usr.super_user = TRUE THEN
117 FOR r_perm IN SELECT *
118 FROM permission.usr_perms(iuser) p
119 JOIN permission.perm_list l
121 WHERE (l.code = tperm AND p.grantable IS TRUE)
125 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
126 WHERE id = r_usr.home_ou;
139 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
141 r_usr actor.usr%ROWTYPE;
142 r_perm permission.usr_perm_map%ROWTYPE;
145 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
147 IF r_usr.active = FALSE THEN
151 IF r_usr.super_user = TRUE THEN
155 FOR r_perm IN SELECT *
156 FROM permission.usr_perms(iuser) p
157 JOIN permission.perm_list l
163 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
164 WHERE id = r_usr.home_ou;
177 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
179 r_woum permission.usr_work_ou_map%ROWTYPE;
180 r_usr actor.usr%ROWTYPE;
181 r_perm permission.usr_perm_map%ROWTYPE;
184 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
186 IF r_usr.active = FALSE THEN
190 IF r_usr.super_user = TRUE THEN
194 FOR r_perm IN SELECT *
195 FROM permission.usr_perms(iuser) p
196 JOIN permission.perm_list l
202 FOR r_woum IN SELECT *
203 FROM permission.usr_work_ou_map
208 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
209 WHERE id = r_woum.work_ou;
223 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 $$
225 r_usr actor.usr%ROWTYPE;
229 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
231 IF r_usr.active = FALSE THEN
235 IF r_usr.super_user = TRUE THEN
239 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;
245 IF target_ou > -1 THEN
246 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
254 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
255 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
258 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
260 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
261 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE