Forum OpenACS Development: Permission management reform for PostgreSQL

Dear all,

Over the last days, i was working towards finishing, what Victor Guerra started in 2013 for permission handling under PostgreSQL. Victor introduced recursive queries (sometimes called CTE) to overcome the scalability and performance problem for permission checking, implemented in the pgpsql functions acs_permission__permission_p (permission checking for single objects) and acs_permission__permission_p_recursive_array (permission checking for multiple objects). These changes made the huge materialized table acs_object_context_index mostly useless. However, these tables are still maintained via triggers, since they were used by some other views (e.g. for reverse lookups of permissions). On our production system, acs_object_context_index has a size of more than 50GB (25GB table + 34GB indices).

With the recent changes in the oacs-5-9 branch, these table and views are not used anymore and have been replaced where necessary by functions (acs_permission.permissions_all and acs_permission.parties_with_object_privilege). Using functions with clear interfaces has also the advantage, that maybe other/better data/index-structures could be used in the future without much interference with application packages. In detail, i replaced all usages of these tables/views (mostly legacy stuff) from the 90 packages in the oacs-5-9 branch and from the 20 additional packages installed on These changes lead already to performance improvements by using the recursive queries instead of the old tables.

The following tables/views depending on acs_object_context_index are now obsolete in oacs-5-9 on PostgreSQL installations:
- acs_object_context_index
- acs_object_contexts
- acs_object_grantee_priv_map
- acs_object_party_privilege_map
- acs_object_paths
- acs_permissions_all
- all_object_party_privilege_map

Furthermore the following tables/views/functions are obsolete (not used) for PostgreSQL installations
- acs_privilege_descendant_map
- acs_privilege_descendant_map_view
- acs_privilege_hierarchy_index
- priv_recurse_subtree
where only the first is available in Oracle as a recursive query, the other ones are pg-only. In addition, the triggers for the various materialized tables are not needed.

Although these tables/views/functions are obsolete, these will NOT be dropped automatically via upgrades in the near future, since there might be installations with custom packages depending on some of these. Rather than dropping it, we will provide an extra upgrade script, which could be run manually on large installations at a time, which a site-admin can decide, e.g. when there is interest in the performance gains and there are no direct usages of the mentioned artifacts. Below are two commands which can be used in the "packages" directory to check usages of the obsolete database artifacts on custom installations.

The upgrade script will be provided here in the near future. I am not sure, whether the obsolete tables/views/triggers should be installed on new installs, but maybe, providing a legacy package might be an option, when somebody needs it.

Note, that these changes effect only PostgreSQL installations, the tables/views in Oracle are not effected.

 egrep -R 'acs_object_context_index|acs_object_contexts|acs_object_grantee_priv_map|acs_object_party_privilege_map|acs_object_paths|acs_permissions_all|all_object_party_privilege_map' *|egrep -v 'oracle|upgrade|acs-core-docs|intermedia-driver|acs-kernel|--'

egrep -R 'acs_privilege_descendant_map|acs_privilege_descendant_map_view|acs_privilege_hierarchy_index|priv_recurse_subtree' *|egrep -v 'oracle|upgrade|acs-core-docs|intermedia-driver|acs-kernel|--'

Posted by Gustaf Neumann on
Dear all, runs since a few days with the object context index (and the whole update machinery) removed. As noted above, here is the upgrade scripts for PostgreSQL for site-admins who want to drop the useless tables and triggers already now.

The plan should be to still create/maintain these tables for new installations for the time being, but to drop these in the next major release.

all the best

-- 1) Make sure to upgrade to at least acs-kernel 5.9.1d13
-- 2) Make a database dump as a backup; dropping is faster than recreating!

-- Drop the unused (potentially huge) materialized view
-- "acs_object_context_index" and its maintenance infrastructure.
DROP TABLE IF EXISTS acs_object_context_index CASCADE;
DROP TRIGGER IF EXISTS acs_objects_context_id_in_tr ON acs_objects CASCADE;
DROP TRIGGER IF EXISTS acs_objects_context_id_up_tr ON acs_objects CASCADE;
DROP FUNCTION IF EXISTS acs_objects_context_id_in_tr();
DROP FUNCTION IF EXISTS acs_objects_context_id_up_tr();

-- Drop the two (!) materialized views for the privilege hierarchy.
DROP TABLE IF EXISTS acs_privilege_descendant_map;
DROP TABLE IF EXISTS acs_privilege_hierarchy_index CASCADE;

DROP TRIGGER  IF EXISTS acs_priv_hier_ins_del_tr ON acs_privilege_hierarchy;
DROP FUNCTION IF EXISTS acs_priv_hier_ins_del_tr();

DROP TRIGGER  IF EXISTS acs_priv_del_tr ON acs_privileges;
DROP FUNCTION IF EXISTS acs_priv_del_tr();

DROP FUNCTION IF EXISTS priv_recurse_subtree(varbit, varchar);

-- Create "acs_privilege_descendant_map" as view (similar to the
-- Oracle implementation)
-- The clause after the first UNION ALL is just here to return the
-- identity column on the highest hierarchy ("admin, admin"). 
CREATE OR REPLACE VIEW acs_privilege_descendant_map AS
WITH RECURSIVE privilege_desc(parent, child) AS (
   SELECT child_privilege as parent, child_privilege as child FROM acs_privilege_hierarchy
   SELECT privilege as parent, privilege as child FROM
   (SELECT privilege FROM acs_privilege_hierarchy
    SELECT child_privilege FROM acs_privilege_hierarchy) identity
   SELECT h.privilege as parent, pd.child
   FROM acs_privilege_hierarchy h, privilege_desc pd
   WHERE pd.parent = h.child_privilege
) SELECT * FROM privilege_desc;