Role-based access control has been hugely influential in how we do authorization. So much that it's almost difficult to find an application that makes authorization decisions for users but does not have a concept of roles. Thus, a complete solution for doing application-level authorization decisions in the database must support application-level roles.
There are two major takes on how roles should be used. On one hand, we have what I'll call big roles, like User, Administrator, Support, and so on. Users usually have a single role. On the other hand, we have small roles, like the ones in Solaris' RBAC: Printer Management or File System Security, and so on, each letting users run a handful of privileged commands. Users usually have a whole bunch of roles, and get the permissions of the union of the permissions of the roles.
In Postgresql, there is support for role-based access control. Users have roles, and roles have permissions. We've used it when creating users in the procedure, which among other things does something like CREATE USER db_username WITH PASSWORD password IN ROLE kroll_role_user. Permissions are then given to the role kroll_role_user, and not to individual users. We also use the search path functionality to ensure that when users ask for a named table, they get a similarly named view in the schema we set up for them.
These two things, database roles and search paths, can be used to implement application-level big roles. By giving different classes of users different database roles, and creating view schemas that match what that role is supposed to be permitted, we can do things like allowing a moderator to set the sticky field of a row in the threads table, while still restricting the view used by regular users to reading threads and inserting new threads with sticky set to false.
If we modify the application to play along, we can give users access to several roles, and by prefixing table names with the schema for a role, the application can specify what authority it is asserting for each operation. That way, the user can operate under the rules of one role by default, but escalating when doing specific operations. A bit like sudo.
So what about small roles? I haven't studied it closely yet, but I believe that they could for the most part be implemented reasonably cleanly using WHERE clauses on the views. Any authorization rule that depends on information that is stored in the database should be possible to formulate as a WHERE clause (e.g. if we have some authorization matrix that the application already uses, then we can select the row for the current user, the column for the current operation, and check that the operation is permitted).
What we get from the database can be seen as data-level permissions, and schema-level permissions. By data-level, I mean that it operates on a row-by-row basis, where we can say that even if a user can sometimes be permitted to update a row in a table, a particular row may not be permitted (e.g. users can edit rows in the posts table if and only if they are specified in the author column). The schema-level permissions are things like allowing users to read from users, but not write. I believe that schema-level permissions match big roles in the application, and data-level matches small roles, but saying anything definitely on this will require studies of real-world role usage in web applications.
There may be situations where you want to combine multiple schema-level roles (i.e. when they do not match the big role idea directly), and you cannot have users select one at login, nor have the application escalate. One example could be if you have one role for letting customer service representatives see details about the specific issues that they handle and one role for giving access to summaries of entire categories of issues for statistics generation, and then have a page for displaying summary information about a specific issue. In that case you probably can't decide which schema to use beforehand. In these cases, combination roles can be created, which give the permissions of both roles. In the general case, this is not feasible since the number of roles combining two roles would be the square of the number of roles, and there could in principle be a need for any combination of roles, leading to O(nn) combination roles. In practice, it's more likely that it's one or two roles (remember, this is schema-level roles we're talking about here) that need to be combined with ordinary roles. In that case, combination roles should be possible to use.
Combination roles can easily be implemented by having the search path for users with combination roles specify first the combination role, and then one of the ordinary ones. That way, if the ordinary role has sufficient rules for a view, then the combination role schema can just omit the view. For views like the one in the example above, a simple union between the views of the ordinary roles and a rule for inserting/updating etc via the corresponding view in the schema of one of the ordinary roles should suffice. It's only when both roles can modify the view and it's not clear from the context of the application which authority the user is asserting when doing the update that the combination role gets complicated. I don't see this as a real-world problem, but more experimentation will tell.
This concludes the implementation part of the series. Next up will be a post on unsolved problems, and performance test results.
