I have been thinking about how CockroachDB could be used to implement a very flexible authorization system.

I have previous experience using Graph databases which differ from relational databases in several key ways -- but one big differentiator is that graph databases are good for "highly relational data" (and I don't mean relational in the RDBMS sense). Graph databases are good at answering questions like, "Given a family tree, are persons X and Y related, and if so, what is their relationship?", or "In my LinkedIn professional network, am I connected to person X, and if so, how many 'hops' away are we?"

Questions like this are tricky to answer in general because the "distance" between things is not known, but they can be particularly difficult to answer in relational databases using standard data models, highly-structured schemas, and join-based queries.

For my authorization system needs, I want to be able these types of unstructured/unknown questions. For instance: "If I'm a user in a system, and I'm a member of a groups A, B, C, do I have permission to do action X" This is further complicated by the fact that I would want to allow a group hierarchy where a group can belong to another group which can belong to a tree of groups, and the necessary permission(s) can be attached to groups anywhere in that tree.

Most relational databases -- including CockroachDB -- have a structure that can help with these type of "walk the tree" problems; this structure is called a "Recursive CTE."

A normal, non-recursiveCTE (Common Table Expression) is cool in and of itself since it allows you to create a query that can be re-used more than once in a series of queries. Beyond facilitating re-use, they can also simply your queries by eliminating complex derived tables which helps to create more easily maintainable SQL code.

A recursive CTE adds another layer of goodness because it allows recursive execution of the expression.

Let's look at an example of a non-recursive CTE borrowed from the CockroachDB docs:

WITHrAS(SELECT*FROMridesWHERErevenue>98)SELECT*FROMusersASu,r-- here we're joining to the 'r' CTEWHEREr.rider_id=u.id;

To use a recursive CTE, we need a slightly different syntax. Let's have a look at an example:

WITHRECURSIVEcte(n,factorial)AS(VALUES(0,1)-- initial subqueryUNIONALLSELECTn+1,(n+1)*factorialFROMcteWHEREn<9-- recursive subquery)SELECT*FROMcte;

Recursive CTEs have to have the keyword "RECURSIVE", as well as an initial statement (a starting point) UNIONed together with a recursive query. There can also optionally be a field list which is used to explicitly name the columns returned in the CTE table-valued structure.

Now, back to the example of an authorization system. I created a GitHub repo which has an example with some example data on how such an authorization system could look in CockroachDB and how that data could be effectively queried using a recursive CTE.
https://github.com/cockroachlabs-field/perms-example

I won't repeat the contents of the GitHub repo here, but here's an example of the CTE I'm using there. We're asking the question: Does the identity 'Andy, GM' have rights to read data from the Customer table?
If the query returns results, the permission exists; if no results are returned, the permission doesn't exist.

WITHRECURSIVEroles_hierarchy_cteAS(--query all the roles for the user we care aboutSELECTr.ridASridFROMperms_example.role_instancerINNERJOINperms_example.identity_role_assignmentiraONr.rid=ira.ridWHEREira.iid='aaaaaaaa-1111-1111-1111-111111111111'-- Andy, GMUNION--walk up the tree and find any other parent rolesSELECTrh.child_ridASridFROMperms_example.role_hierarchyrhINNERJOINroles_hierarchy_cterhcONrhc.rid=rh.parent_rid)/* get all the permissions that this identity is assigned to directly */SELECTi.iid,i.identity_name,NULLASrole_name,p.permission_nameFROMperms_example.identity_instanceiINNERJOINperms_example.identity_permission_assignmentipaoni.iid=ipa.iidINNERJOINperms_example.permission_instanceponipa.pid=p.pidWHEREi.iid='aaaaaaaa-1111-1111-1111-111111111111'-- Andy, GMANDp.pid='cccccccc-1111-1111-1111-111111111111'-- 'Read Customer DataUNIONALL/* also, get any permissions that this identity picks up due to role permissions     and also role permissions from the role hierarchy */SELECTi.iid,i.identity_name,r.role_name,p.permission_nameFROMperms_example.role_instancerINNERJOINperms_example.role_permission_assignmentrpaonr.rid=rpa.ridINNERJOINperms_example.permission_instanceponrpa.pid=p.pid--get me 1 identity row so I can include that on the outputCROSSJOIN(SELECTi.iid,i.identity_nameFROMperms_example.identity_instanceiWHEREi.iid='aaaaaaaa-1111-1111-1111-111111111111'--Andy, GM)iWHEREr.ridIN(SELECTrhc.ridFROMroles_hierarchy_cterhc)ANDp.pid='cccccccc-1111-1111-1111-111111111111'-- 'view customer data;

Do you have other examples where recursive CTEs may be useful?
Post them in the comments!

Happy recursing!