Tag: sql

Let’s say we’re setting up a data structure for security, with group membership:

create table groups (
  id int unique,
  name varchar unique,
  member_groups int[]
);

insert into groups values (1, 'admins', '{}');

-- automatically include any admins as users
insert into groups values (2, 'users', '{1}'); 

-- grant admins access to write
insert into groups values (3, 'writers', '{1}'); 

-- grant writers access to read
insert into groups values (4, 'readers', '{3}'); 

Now, we want to flatten this structure to find all transitive memberships. To do this, we take each group’s children, find their children, and concatenate the result recursively.

Note that the column order in each part of the recursive sql must be the same, as well as in the column definition on the first line.

WITH RECURSIVE all_groups(id, member_groups) AS (
    select
      id,
      member_groups
    from groups
  UNION ALL
    SELECT groups.id,
           all_groups.member_groups || groups.member_groups
    FROM all_groups
    JOIN groups
      ON all_groups.id = ANY (groups.member_groups)
  )
SELECT id, uniq(flatMap(member_groups))
FROM all_groups
GROUP BY id;

Note that here, I’m using some custom functions I’ve introduced, to match some functionality of the Scala collections API.

And this is what we get, as expected:

4,{1,3}
1,
3,{1}
2,{1}

Lets say someone adds a circular reference to this array:

insert into groups values (5, 'cycle1', '{3}', '{3, 7}');
insert into groups values (6, 'cycle2', '{3}', '{5}');
insert into groups values (7, 'cycle3', '{3}', '{6}');

We can easily fix this, by adding an additional array to track what we’ve seen already in the recursion:

WITH RECURSIVE all_groups(found, id, member_groups) AS (
    select
      array[id] found,
      id,
      member_groups
    from groups
  UNION ALL
    SELECT array[groups.id] || all_groups.found found,
          groups.id,
          all_groups.member_groups || groups.member_groups
    FROM all_groups
    JOIN groups
      ON all_groups.id = ANY (groups.member_groups)
      AND NOT (groups.id = ANY (found))
  )
SELECT id, uniq(flatMap(member_groups))
FROM all_groups
GROUP BY id;

And we’re done! Since a cycle just indicates an equivalence class of groups, they should all get the same group members, and we can detect them by seeing that they contain their own IDs:

8,{5,8}
4,{1,3}
1,
5,{5,8}
3,{1}
9,{5,8}
6,{5,8}
2,{1}
7,{5,6,8}