New attempt at fixing the modeling. Superseeds #3, at least for now.

To filter results based on permissions, what AccessControl really cares is if they're are permitted, rather than where they are in the hierarchy. The current model works well for this, but is not optmized. The problem is that it doesn't scale well when the number of principals and roles grows. The proposed solution here is not an attempt to "fix" this, but to alleviate it.

Current model

Currently, what tells if a node is permitted for a principal is the presence of a suitable assignment, which is one that provides a role with the necessary permission(s), which in turn are obtained from the app model configuration. Because there's a hierarchy of nodes, the assignments are replicated to child (descendant) nodes through a process known in the source code as propagation. To differentiate real assignments, that is, those created explicitly in response to some user action, from propagated (effective) assignments, besides the keys to node, principal and role, there's an additional key to the parent assignment. This conveys information in each assignment record about from which ancestry path that assignment came into existence. If the parent pointer is null, then the assignment is real; otherwise it is effective.

Then, each time a record is created or placed below some other, all of the parent node assignments are propagated to the newly positioned or created node. If this node happens to already have children (descendants), then the process of propagation proceeds using the information contained in the table ac_parents until there's nothing more to propagate. A similar process happens also when a real assignment is created; the descendant nodes receive propagated assignments.

Since the number of assignments tend to grow a lot, there's too much index contention during propagation, causing timeouts and even deadlocks. Besides, indexes are too big; the assignment table must have a unique index for all its four keys, including the possibly-null parent key, and two of them are bigint. And this is due to the great duplication in real vs effective assignments, since both have the same principal and role, only varying the node.

Alternative model

The alternative model removes the duplication between real and effective assignments by moving the effective assignments to a new table called ac_effective_assignments. This table has a pointer to the originating (real) assignment and to the node to which the assignment is propagated (both bigint). A unique index exists for these two keys. All assignments remaining in ac_assignments are real, and the parent id key is not needed and can be dropped after successful migration to the new model. The number of real assignments doesn't tend to grow too much, so it's possible to handle them in pure Ruby.

The process of appending a record below some other in the hierarchy or introducing new assignments will be almost the same. The difference is that the propagation happens in the new table (using information from real assignments combined with ac_parents). The originating assigmnents (the ones from the initial parent record or being explicitly assigned) can be kept constant during the whole process of propagation, which may simplify the process a bit.

For queries, the filtering will use the information in this new table, as all real assignments will have a mirrored effective one, making the new table alone enough to get permitted node ids (as long the interesting real assignments have been previously computed in a scalar array or injected in a subquery, for a particular set of roles and principals).

The expectation is that the process will be simpler and hopefully generate less contention, due to the reduced duplication and smaller, null-free indexes to update.

tecnologiaenegocios / access_control

New modeling #5

Current model

Alternative model