OpenSourcery

We discovered a problem while QA'ing emC against a huge client database. The page which displays who has access to a client would take a full minute to load. This was deemed unacceptable, and a solution was found.

The elementalclinic permissions system works on the idea that there are clients, personnel (staff members), roles to which personnel are members, and permissions for roles to access clients. Every staff member is given a 'primary role' that only they are a member of. Personnel become members of roles when their primary role is added as a member of a system role. This also allows for nested roles where a staff member is a member of system role a, which is a member of system role b.

Nested roles are useful when you want to share permissions between roles. For instance there is an all_clients role which grants access to all clients. The Admin role, being superuser, should also have access to all clients. With nested roles you simply make admin a member of all_clients. You can then add admins to admins, and non-admins who need all_client access to all_clients.

The problem with nested roles comes when you need to check if a staff member has permissions to view a specific client. You need to build a list of all roles the staff member is a direct or indirect member of. You then need to check if any of these roles grants access to the client. On the access page this needed to be done for each staff member.

To solve this problem I decided to leverage postgres. In the initial revision of the permissions system the database simply held the roles, direct memberships, and permissions. Traversing the roles and assessing client permissions for specific users was left entirely to the code. In the new revision I decided the database would maintain a complete map of memberships, both direct and nested. In addition the database would provide several views to simplify obtaining the results we need.

The focus of the changes is with the table that hold memberships. Initially it's schema was very simple (Old way):

 21 CREATE TABLE personnel_role_member(
 22     rec_id        SERIAL NOT NULL PRIMARY KEY,
 23     role_id       INTEGER REFERENCES personnel_role( rec_id ) NOT NULL,
 24     member_id INTEGER REFERENCES personnel_role( rec_id ) NOT NULL,
 25     -- Make sure duplicate memberships do not occur.
 26     UNIQUE( role_id, member_id ),
 27     -- Roles should not be members of themselves.
 28     CHECK( member_id != role_id )
 29 );

Here is the new schema for tracking role memberships:

 21 CREATE TABLE role_membership(
 22     rec_id         INTEGER UNIQUE NOT NULL PRIMARY KEY DEFAULT nextval('role_membership_rec_id_seq'),
 23     role_id        INTEGER NOT NULL REFERENCES personnel_role( rec_id ) ON DELETE CASCADE,
 24     member_id      INTEGER NOT NULL REFERENCES personnel_role( rec_id ) ON DELETE CASCADE,
 25     direct_cause   INTEGER REFERENCES role_membership( rec_id ) ON DELETE CASCADE,
 26     indirect_cause INTEGER REFERENCES role_membership( rec_id ) ON DELETE CASCADE,
 27     -- Roles should not be members of themselves.
 28     CHECK( member_id != role_id ),
 29     CHECK( direct_cause != indirect_cause ),
 30     -- Duplicates are pointless.
 31     UNIQUE( role_id, member_id, direct_cause, indirect_cause )
 32 );

The main thing to note in the new schema are the 'direct_cause' and 'indirect_cause' fields. These reference other entrees in the same table. The point of this change is to track both direct and indirect memberships in one table. When a membership is added a trigger fires which adds an entree for all resulting indirect memberships.

The trigger code is fairly complicated, so I am posting a simplified psudo-code version. This trigger is fired whenever an insert occurs on the role_membership table. Note: In this psudo-code 'us' or 'we' refers to the role that is becoming a member.

  Check for recursive memberships, raise an exception if found.
 
  Add memberships to all the roles the role we just became a member of is a member of. Direct cause is the new membership, indirect cause is the existing membership
 
  Add memberships to the role we just became a member of to the roles that are members of us. Direct cause is the existing membership, indirect cause is the new membership.

This trigger also fires off for any indirect memberships added by the trigger recursively. This trigger results in a table that always has a complete list of both direct and indirect memberships.

From this table it is a simple matter to create views for just direct or indirect members. Views for which roles have access to which clients is also fairly trivial. We can even create a view that shows client permissions with the reason(membership) that grants them.

Here are links to the original permissions schema, as well as the migration to the new system.
Original
Migration
And here is some additional documentation
README