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

1: Best example package for using the OpenACS permission system, on PostgreSQL?

Posted by Andrew Piskorski on 03/18/24 07:11 PM

Which OpenACS package should I look at, for the best examples of how to use and integrate with the OpenACS permission system, on PostgreSQL?

I am familiar with the original design of the permission system from 20+ years ago, on Oracle with lots of PL/SQL. But I'd like to learn whatever the currently recommended approach is, rather than accidentally copying obsolescent ways of doing things.

2: Re: Best example package for using the OpenACS permission system, on PostgreSQL? (response to 1)

Posted by Antonio Pisano on 03/26/24 10:17 AM

Dear Andrew,

when it comes to it, permissions are just a handful of procs. Depending on your use case, having a look at the automated tests in acs-tcl/tcl/test/test-permissions-procs.tcl might be all you need. I have just provided some additional test for inheritance in [1].

One additional advanced feature that does not appear very often in the codebase is the possibility to define custom permissions that e.g. inherit from the standard read, write, admin and so on. One example of this can be found in the chat package.

Let me know if I can be of more help

Ciao

Antonio

[1] https://cvs.openacs.org/changelog/OpenACS?cs=oacs-5-10%3Aantoniop%3A20240326091006

3: Re: Best example package for using the OpenACS permission system, on PostgreSQL? (response to 2)

Posted by Antonio Pisano on 03/27/24 12:48 PM

I have extended the new test to include also the definition of custom privileges

https://cvs.openacs.org/changelog/OpenACS?cs=oacs-5-10%3Aantoniop%3A20240327114640

All the best

4: Re: Best example package for using the OpenACS permission system, on PostgreSQL? (response to 1)

Posted by Gustaf Neumann on 03/27/24 05:59 PM

Hi Andrew,

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

Is this the kind of information you are looking for?

All the best
-g

5: Re: Best example package for using the OpenACS permission system, on PostgreSQL? (response to 1)

Posted by Andrew Piskorski on 03/29/24 07:39 PM

Thanks for those tips, Gustaf and Antonio.

Yes, it looks like things are mostly the same as with Oracle, but I'm definitely new to the PostgreSQL approach to doing things in OpenACS.

I've been using acs_permission.permission_p from SQL but wasn't familiar with the option to switch to acs_permission.permission_p_recursive_array for large queries. That might be helpful in the future.

When I create a new custom sub-type of acs_object, it looks like the basic approach is still the same, I create MyNewObjectType__new and MyNewObjectType__delete PL/pgSQL functions, and use those as needed.

I think OpenACS expects me to always document my PL/pgSQL functions by calling define_function_args. I'm not quite sure how or where that gets used, but there are adequate examples of how to call it spread around the various packages.

I noticed that many (older?) PostgreSQL packages (like forums) run PL/pgSQL code (e.g., calls to acs_object_type__create_type) from their *-create.sql files by creating, running, and then dropping an inline_0 helper function. It looks like that's no longer necessary though, as other packages just run the same PL/pgSQL function in a select statement, which is certainly more convenient.

For editing permissions on my acs_objects, I see that OpenACS includes a general purpose perm-include widget to do that, which several packages re-use by sticking an include line like this into their own ADP page:

<include src="/packages/acs-subsite/www/permissions/perm-include" &="object_id" &="return_url">

That perm-include widget is certainly fine and helpful, but it can only handle one object at a time. Is there any built-in UI for editing permissions on multiple objects? Or is that left to custom pages specific to each package that wants something like that? (I assume the latter.)

6: Re: Best example package for using the OpenACS permission system, on PostgreSQL? (response to 5)

Posted by Gustaf Neumann on 03/30/24 08:16 PM

I noticed that many (older?) PostgreSQL packages (like forums) run PL/pgSQL code (e.g., calls to acs_object_type__create_type) from their *-create.sql files by creating, running, and then dropping an inline_0 helper function.

The inline_0 helpers are relicts from earlier versions of PostgreSQL. The trick with the helper can be replaced by DO, which supports anonymous code blocks. DO was introduced by PostgreSQL 9.6.

... define_function_args ...

This function is used to guide calls from Tcl to these functions. If you do not intend to call your own stored functions/procedures from Tcl, this is not needed. Since Tcl does not have function resolution based on types (like e.g. plpgsql) it makes clear what function to call, ... and it provides argument names, when SQL functions are defined without argument names, etc.