Forum OpenACS Development: Re: Best example package for using the OpenACS permission system, on PostgreSQL?

I am not sure, what you are exactly asking for, since you are an experienced OpenACS user from the Oracle camp. Anyhow, the permission system can be accessed either via API or via DB interface.

• Since this is platform independent, cached, etc., using the permissions API is recommended for most situations. (see e.g. https://openacs.org/api-doc/proc-view?proc=permission::permission_p&source_p=1)

In certain situations, one wants to return from an SQL query only tuples adhering already to a permission check. The checking of permissions inside the DB is easiest via SQL functions, and can be done on a per-tuple basis, or as a bulk operation.

• Per-tuple DB checking: This works with PostgreSQL the same way as for Oracle, using the SQL function "acs_permission.permission_p". Example: https://github.com/openacs/openacs-core/blob/oacs-5-10/packages/acs-subsite/www/admin/site-map/mount-postgresql.xql#L51
• Bulk checking: when testing permissions on many tuples (e.g.m query returning 100K tuples or more), the filtering using "acs_permission.permission_p" becomes inefficient. For this purpose, "acs_permission.permission_p_recursive_array" can be used, which performs the check on all tuples via a single function call. This is used e.g. in search (tsearch2-driver) or as well on the site map, which can become very large as well. Example:
  https://github.com/openacs/openacs-core/blob/oacs-5-10/packages/acs-subsite/www/admin/site-map/index-postgresql.xql#L55

All the best
-g