-- -- acs-kernel/sql/acs-permissions-create.sql -- -- The ACS core permissioning system. The knowledge level of system -- allows you to define a hierarchichal system of privilages. The -- operational level allows you to grant to any party a privilege on -- any object. -- -- @author Rafael Schloming (rhs@mit.edu) -- -- @creation-date 2000-08-13 -- -- @cvs-id $Id: acs-permissions-create.sql,v 1.39 2015/07/27 12:51:21 antoniop Exp $ -- --------------------------------------------- -- KNOWLEDGE LEVEL: PRIVILEGES AND ACTIONS -- --------------------------------------------- create table acs_privileges ( privilege varchar(100) not null constraint acs_privileges_privilege_pk primary key, pretty_name varchar(100), pretty_plural varchar(100) ); create table acs_privilege_hierarchy ( privilege varchar(100) not null constraint acs_priv_hier_priv_fk references acs_privileges (privilege), child_privilege varchar(100) not null constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege), constraint acs_privilege_hierarchy_pk primary key (privilege, child_privilege) ); create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege); create table acs_privilege_hierarchy_index ( privilege varchar(100) not null constraint acs_priv_hier_priv_fk references acs_privileges (privilege), child_privilege varchar(100) not null constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege), tree_sortkey varbit ); create index priv_hier_sortkey_idx on acs_privilege_hierarchy_index (tree_sortkey); -- Added table to materialize view that previously used -- acs_privilege_descendant_map name -- -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 create table acs_privilege_descendant_map ( privilege varchar(100) not null constraint acs_priv_hier_priv_fk references acs_privileges (privilege), descendant varchar(100) not null constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege) ); -- DRB: Empirical testing showed that even with just 61 entries in the new table -- this index sped things up by roughly 15% create index acs_priv_desc_map_idx on acs_privilege_descendant_map(descendant); -- Gustaf (Jan 2009): interesting enough, the index above is never -- used on openacs.org and can be most likely dropped. The index below -- (together with acs_obj_ctx_idx_object_id_idx) makes real-world -- applications more than a factor of 10 faster (openacs/download and -- openacs/download/one-revision?revision_id=2089636) create index acs_priv_desc_map_privilege_idx on acs_privilege_descendant_map (privilege); -- This trigger is used to create a pseudo-tree hierarchy that -- can be used to emulate tree queries on the acs_privilege_hierarchy table. -- The acs_privilege_hierarchy table maintains the permissions structure, but -- it has a complication in that the same privileges can exist in more than one -- path in the tree. As such, tree queries cannot be represented by the -- usual tree query methods used for openacs. -- DCW, 2001-03-15. -- usage: queries directly on acs_privilege_hierarchy don't seem to occur -- in many places. Rather it seems that acs_privilege_hierarchy is -- used to build the view: acs_privilege_descendant_map. I did however -- find one tree query in content-perms.sql that looks like the -- following: -- select privilege, child_privilege from acs_privilege_hierarchy -- connect by prior privilege = child_privilege -- start with child_privilege = 'cm_perm' -- This query is used to find all of the ancestor permissions of 'cm_perm'. -- The equivalent query for the postgresql tree-query model would be: -- select h2.privilege -- from acs_privilege_hierarchy_index h1, -- acs_privilege_hierarchy_index h2 -- where h1.child_privilege = 'cm_perm' -- and h1.tree_sortkey between h2.tree_sortkey and tree_right(h2.tree_sortkey) -- and h2.tree_sortkey <> h1.tree_sortkey; -- Also since acs_privilege_descendant_map is simply a path enumeration of -- acs_privilege_hierarchy, we should be able to replace the above connect-by -- with: -- select privilege -- from acs_privilege_descendant_map -- where descendant = 'cm_perm' -- This would be better, since the same query could be used for both oracle -- and postgresql. -- -- procedure acs_priv_hier_ins_del_tr/0 -- CREATE OR REPLACE FUNCTION acs_priv_hier_ins_del_tr( ) RETURNS trigger AS $$ DECLARE new_value integer; new_key varbit default null; v_rec record; deleted_p boolean; BEGIN -- if more than one node was deleted the second trigger call -- will error out. This check avoids that problem. if TG_OP = 'DELETE' then select count(*) = 0 into deleted_p from acs_privilege_hierarchy_index where old.privilege = privilege and old.child_privilege = child_privilege; if deleted_p then return new; end if; end if; -- recalculate the table from scratch. delete from acs_privilege_hierarchy_index; -- first find the top nodes of the tree for v_rec in select privilege, child_privilege from acs_privilege_hierarchy where privilege NOT in (select distinct child_privilege from acs_privilege_hierarchy) LOOP -- top level node, so find the next key at this level. select max(tree_leaf_key_to_int(tree_sortkey)) into new_value from acs_privilege_hierarchy_index where tree_level(tree_sortkey) = 1; -- insert the new node insert into acs_privilege_hierarchy_index (privilege, child_privilege, tree_sortkey) values (v_rec.privilege, v_rec.child_privilege, tree_next_key(null, new_value)); -- now recurse down from this node PERFORM priv_recurse_subtree(tree_next_key(null, new_value), v_rec.child_privilege); end LOOP; -- materialize the map view to speed up queries -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 delete from acs_privilege_descendant_map; insert into acs_privilege_descendant_map (privilege, descendant) select privilege, descendant from acs_privilege_descendant_map_view; return new; END; $$ LANGUAGE plpgsql; create trigger acs_priv_hier_ins_del_tr after insert or delete on acs_privilege_hierarchy for each row execute procedure acs_priv_hier_ins_del_tr (); CREATE OR REPLACE FUNCTION acs_priv_del_tr () RETURNS trigger AS $$ BEGIN delete from acs_privilege_descendant_map where privilege = old.privilege; return old; END; $$ LANGUAGE plpgsql; create trigger acs_priv_del_tr before delete on acs_privileges for each row execute procedure acs_priv_del_tr (); -- added select define_function_args('priv_recurse_subtree','nkey,child_priv'); -- -- procedure priv_recurse_subtree/2 -- CREATE OR REPLACE FUNCTION priv_recurse_subtree( nkey varbit, child_priv varchar ) RETURNS integer AS $$ DECLARE new_value integer; v_rec record; new_key varbit; BEGIN -- now iterate over all of the children of the -- previous node. for v_rec in select privilege, child_privilege from acs_privilege_hierarchy where privilege = child_priv LOOP -- calculate the next key for this level and parent select max(tree_leaf_key_to_int(tree_sortkey)) into new_value from acs_privilege_hierarchy_index where tree_sortkey between nkey and tree_right(nkey) and tree_level(tree_sortkey) = tree_level(nkey) + 1; new_key := tree_next_key(nkey, new_value); -- insert the new child node. insert into acs_privilege_hierarchy_index (privilege, child_privilege, tree_sortkey) values (v_rec.privilege, v_rec.child_privilege, new_key); -- keep recursing down until no more children are found PERFORM priv_recurse_subtree(new_key, v_rec.child_privilege); end LOOP; -- no children found, so insert the child node as its own separate -- node. if NOT FOUND then insert into acs_privilege_hierarchy_index (privilege, child_privilege, tree_sortkey) values (child_priv, child_priv, tree_next_key(nkey, null)); end if; return null; END; $$ LANGUAGE plpgsql; comment on table acs_privileges is ' Privileges share a global namespace. This is to avoid a situation where granting the foo privilege on one type of object can have an entirely different meaning than granting the foo privilege on another type of object. '; comment on table acs_privilege_hierarchy is ' The acs_privilege_hierarchy gives us an easy way to say: The foo privilege is a superset of the bar privilege. '; -- added select define_function_args('acs_privilege__create_privilege','privilege,pretty_name;null,pretty_plural;null'); -- -- procedure acs_privilege__create_privilege/3 -- CREATE OR REPLACE FUNCTION acs_privilege__create_privilege( create_privilege__privilege varchar, create_privilege__pretty_name varchar, -- default null create_privilege__pretty_plural varchar -- default null ) RETURNS integer AS $$ DECLARE BEGIN insert into acs_privileges (privilege, pretty_name, pretty_plural) values (create_privilege__privilege, create_privilege__pretty_name, create_privilege__pretty_plural); return 0; END; $$ LANGUAGE plpgsql; -- -- procedure acs_privilege__create_privilege/1 -- CREATE OR REPLACE FUNCTION acs_privilege__create_privilege( create_privilege__privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN return acs_privilege__create_privilege(create_privilege__privilege, null, null); END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_privilege__drop_privilege','privilege'); -- -- procedure acs_privilege__drop_privilege/1 -- CREATE OR REPLACE FUNCTION acs_privilege__drop_privilege( drop_privilege__privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN delete from acs_privileges where privilege = drop_privilege__privilege; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_privilege__add_child','privilege,child_privilege'); -- -- procedure acs_privilege__add_child/2 -- CREATE OR REPLACE FUNCTION acs_privilege__add_child( add_child__privilege varchar, add_child__child_privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN insert into acs_privilege_hierarchy (privilege, child_privilege) values (add_child__privilege, add_child__child_privilege); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_privilege__remove_child','privilege,child_privilege'); -- -- procedure acs_privilege__remove_child/2 -- CREATE OR REPLACE FUNCTION acs_privilege__remove_child( remove_child__privilege varchar, remove_child__child_privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN delete from acs_privilege_hierarchy where privilege = remove_child__privilege and child_privilege = remove_child__child_privilege; return 0; END; $$ LANGUAGE plpgsql; ------------------------------------ -- OPERATIONAL LEVEL: PERMISSIONS -- ------------------------------------ create table acs_permissions ( object_id integer not null constraint acs_permissions_object_id_fk references acs_objects (object_id) on delete cascade, grantee_id integer not null constraint acs_permissions_grantee_id_fk references parties (party_id) on delete cascade, privilege varchar(100) not null constraint acs_permissions_privilege_fk references acs_privileges (privilege) on delete cascade, constraint acs_permissions_pk primary key (object_id, grantee_id, privilege) ); create index acs_permissions_grantee_idx on acs_permissions (grantee_id); create index acs_permissions_privilege_idx on acs_permissions (privilege); create index acs_permissions_object_id_idx on acs_permissions(object_id); -- Added table to materialize view that previously used -- acs_privilege_descendant_map name -- -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 -- DRB: I switched this to UNION form because the old view was incredibly -- slow and caused installation of packages to take exponentially increasing -- time. No code should be querying against this view other than the -- trigger that recreates the denormalized map anyway ... create view acs_privilege_descendant_map_view as select distinct h1.privilege, h2.child_privilege as descendant from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2 where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey) union select privilege, privilege from acs_privileges; create view acs_permissions_all as select op.object_id, p.grantee_id, p.privilege from acs_object_paths op, acs_permissions p where op.ancestor_id = p.object_id; create view acs_object_grantee_priv_map as select a.object_id, a.grantee_id, m.descendant as privilege from acs_permissions_all a, acs_privilege_descendant_map m where a.privilege = m.privilege; -- New fast version of acs_object_party_privilege_map create view acs_object_party_privilege_map as select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm, party_approved_member_map pamm where c.ancestor_id = p.object_id and pdm.privilege = p.privilege and pamm.party_id = p.grantee_id; create view all_object_party_privilege_map as select * from acs_object_party_privilege_map; -- This table acts as a mutex for inserts/deletes from acs_permissions. -- This is used since postgresql's exception handing mechanism is non- -- existant. A dup insert on acs_permissions will roll-back the -- transaction and give an error, which is not what we want. Using a -- separate table for locking allows us exclusive access for -- inserts/deletes, but does not block readers. That way we don't -- slow down permissions-checking which is known to have performance -- problems already. -- (OpenACS - DanW) create table acs_permissions_lock ( lck integer ); CREATE OR REPLACE FUNCTION acs_permissions_lock_tr () RETURNS trigger AS $$ BEGIN raise EXCEPTION 'FOR LOCKING ONLY, NO DML STATEMENTS ALLOWED'; return null; END; $$ LANGUAGE plpgsql; create trigger acs_permissions_lock_tr before insert or update or delete on acs_permissions_lock for each row execute procedure acs_permissions_lock_tr(); -- added select define_function_args('acs_permission__grant_permission','object_id,grantee_id,privilege'); -- -- procedure acs_permission__grant_permission/3 -- CREATE OR REPLACE FUNCTION acs_permission__grant_permission( grant_permission__object_id integer, grant_permission__grantee_id integer, grant_permission__privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN insert into acs_permissions (object_id, grantee_id, privilege) values (grant_permission__object_id, grant_permission__grantee_id, grant_permission__privilege); return 0; EXCEPTION when unique_violation then return 0; END; $$ LANGUAGE plpgsql; -- procedure revoke_permission -- added select define_function_args('acs_permission__revoke_permission','object_id,grantee_id,privilege'); -- -- procedure acs_permission__revoke_permission/3 -- CREATE OR REPLACE FUNCTION acs_permission__revoke_permission( revoke_permission__object_id integer, revoke_permission__grantee_id integer, revoke_permission__privilege varchar ) RETURNS integer AS $$ DECLARE BEGIN delete from acs_permissions where object_id = revoke_permission__object_id and grantee_id = revoke_permission__grantee_id and privilege = revoke_permission__privilege; return 0; END; $$ LANGUAGE plpgsql; -- Really speedy version of permission_p written by Don Baccus -- added select define_function_args('acs_permission__permission_p','object_id,party_id,privilege'); CREATE FUNCTION inline_0() RETURNS integer AS $inline_0$ BEGIN raise notice 'starting permissions recursive -- vguerra'; IF cmp_pg_version('8.4') >= 0 THEN -- recursive site_nodes recursive - START -- -- procedure acs_permission__permission_p/3 -- CREATE OR REPLACE FUNCTION acs_permission__permission_p( permission_p__object_id integer, permission_p__party_id integer, permission_p__privilege varchar ) RETURNS boolean AS $$ DECLARE exists_p boolean; BEGIN return exists (With RECURSIVE object_context(object_id, context_id) AS ( select permission_p__object_id, permission_p__object_id from acs_objects where object_id = permission_p__object_id union all select ao.object_id, case when (ao.security_inherit_p = 'f' or ao.context_id is null) then acs__magic_object_id('security_context_root') else ao.context_id end from object_context oc, acs_objects ao where ao.object_id = oc.context_id and ao.object_id != acs__magic_object_id('security_context_root') ), privilege_ancestors(privilege, child_privilege) AS ( select permission_p__privilege, permission_p__privilege union all select aph.privilege, aph.child_privilege from privilege_ancestors pa join acs_privilege_hierarchy aph on aph.child_privilege = pa.privilege ) select 1 from acs_permissions p join party_approved_member_map pap on pap.party_id = p.grantee_id join privilege_ancestors pa on pa.privilege = p.privilege join object_context oc on p.object_id = oc.context_id where pap.member_id = permission_p__party_id ); END; $$ LANGUAGE plpgsql stable; -- for tsearch perform define_function_args('acs_permission__permission_p_recursive_array','a_objects,a_party_id,a_privilege'); CREATE OR REPLACE FUNCTION acs_permission__permission_p_recursive_array( permission_p__objects integer[], permission_p__party_id integer, permission_p__privilege varchar ) RETURNS table (object_id integer, orig_object_id integer) as $$ BEGIN return query With RECURSIVE object_context(obj_id, context_id, orig_obj_id) AS ( select unnest(permission_p__objects), unnest(permission_p__objects), unnest(permission_p__objects) union all select ao.object_id, case when (ao.security_inherit_p = 'f' or ao.context_id is null) then acs__magic_object_id('security_context_root') else ao.context_id END, oc.orig_obj_id from object_context oc, acs_objects ao where ao.object_id = oc.context_id and ao.object_id != acs__magic_object_id('security_context_root') ), privilege_ancestors(privilege, child_privilege) AS ( select permission_p__privilege, permission_p__privilege union all select aph.privilege, aph.child_privilege from privilege_ancestors pa join acs_privilege_hierarchy aph on aph.child_privilege = pa.privilege ) select p.object_id, oc.orig_obj_id from acs_permissions p join party_approved_member_map pap on pap.party_id = p.grantee_id join privilege_ancestors pa on pa.privilege = p.privilege join object_context oc on p.object_id = oc.context_id where pap.member_id = permission_p__party_id ; END; $$ LANGUAGE plpgsql stable; -- recursive site_nodes END ELSE CREATE OR REPLACE FUNCTION acs_permission__permission_p( permission_p__object_id integer, permission_p__party_id integer, permission_p__privilege varchar ) RETURNS boolean AS $$ DECLARE exists_p boolean; BEGIN return exists (select 1 from acs_permissions p, party_approved_member_map m, acs_object_context_index c, acs_privilege_descendant_map h where p.object_id = c.ancestor_id and h.descendant = permission_p__privilege and c.object_id = permission_p__object_id and m.member_id = permission_p__party_id and p.privilege = h.privilege and p.grantee_id = m.party_id); END; $$ LANGUAGE plpgsql stable; END IF; raise notice 'ending permissions recursive -- vguerra'; return null; END; $inline_0$ LANGUAGE plpgsql; select inline_0(); drop function inline_0();