I need to represent users and groups, where groups can be nested within other groups.
(Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its implementation details, only its user documentation.)
Putting a group into other groups creates a hierarchy. The users of one group placed in another group gains the access privileges of both groups. For example, if you place the Executives
group in the Accounting
group, the users of the Executives
group enjoy the privileges of both groups. However, users placed only in the Accounting
group benefit from the privileges of that group only. (Admittedly, not a likely realistic business scenario, just a contrived example.)
Something like this ERD diagram, I suppose.
I know how to handle a Many-to-Many relationship of user_
to group_
. But I have no idea:
- How to represent the nested group-within-group, where a group may belong to zero, one, or more than one other group.
- How to query “Is this group X in group Y”.
- How to query “Is this user Bob in group X” where “group X may be either directly assigned to that user or inherited via group-within-group”.
Is this group-within-groups considered a “recursive” relationship? What is the correct technical term?
Possibly related: Recursive query in SQL Server and Recursive SQL query to identify
Best Answer
I'd just go straight for a self-referencing key ( adjacency list, hierarchy relationship, recursive relationship, I have no idea what the "most correct and proper" term for it is, but any of these should get the point across to a sufficiently tuned-in audience ) on what would effectively be a "principal" or "role" table, along with a membership table. While I'd draw my inspiration from SQL Server, PostgreSQL also does this for it's own security management, like what is shown through
\du
or\dg
. That said, there may even be value in considering trying to handle all of this with the built-in security management:"CTO Bill" should now be able to
SELECT
from theaccount
table, via theexecutives
role through to theaccounting
role, all completely out of the box. If an application needs to know the chained permissions, they could be exposed via\du
or some form of query against thepg_catalog
'spg_auth_members
andpg_roles
tables:If a total nuts-and-bolts solution is deemed necessary, you could build a schema similar to this:
Then use pretty much any tree-traversing method you'd like, such as a Recursive CTE, to determine various membership properties:
Edit: It's probably very much worth mentioning that management of solutions involving self-referencing keys and recursive traversals can go tits up in a hurry if caution isn't taken to ensure no cyclic references are made. If somebody were to "accidentally" make accounting a member of the executives group, the sample CTE would no longer function, and as the complexity of the hierarchy increases, so to does the likelihood of such an error. Safeguarding against cyclic references is a bit of a pain in the ass, frankly, but hiding CRUD operations against these tables in stored procedures can help immensely in cases like this one. Since you're attempting to replicate a preexisting solution, perhaps there's some guidelines in that system which can assist in developing a suitable litmus test for the new one.