Question

A Jira user belongs to a set of groups:

How do we find that list of groups for user 'jturner' using SQL (Postgres)?

Answer

With this SQL:

WITH RECURSIVE members AS (
SELECT lower_parent_name
,lower_child_name
FROM cwd_membership
WHERE child_name='jturner'
UNION
select m.lower_parent_name
,m.lower_child_name
FROM cwd_membership m
INNER JOIN members ON members.lower_parent_name=m.lower_child_name
) SELECT DISTINCT lower_parent_name from members;

What's going on?

The headache here is nested groups.

You may know that group membership info is stored in the cwd_membership  table. The following query would return all group memberships of a user if there weren't any nested groups:

jira=> select * from cwd_membership where child_name='jturner' ;
┌───────┬───────────┬──────────┬─────────────────┬────────────┬───────────────────────────┬───────────────────────────┬────────────┬──────────────────┬──────────────┐
│ id │ parent_id │ child_id │ membership_type │ group_type │ parent_name │ lower_parent_name │ child_name │ lower_child_name │ directory_id │
├───────┼───────────┼──────────┼─────────────────┼────────────┼───────────────────────────┼───────────────────────────┼────────────┼──────────────────┼──────────────┤
│ 68581 │ 19488 │ 18636 │ GROUP_USER │ ␀ │ Change (SNC) │ change (snc) │ jturner │ jturner │ 10000 │
│ 69381 │ 19259 │ 18636 │ GROUP_USER │ ␀ │ confluence-administrators │ confluence-administrators │ jturner │ jturner │ 10000 │
│ 70007 │ 19821 │ 18636 │ GROUP_USER │ ␀ │ crowd-administrators │ crowd-administrators │ jturner │ jturner │ 10000 │
│ 72833 │ 18720 │ 18636 │ GROUP_USER │ ␀ │ jabber-all │ jabber-all │ jturner │ jturner │ 10000 │
│ 73448 │ 19900 │ 18636 │ GROUP_USER │ ␀ │ jira-administrators │ jira-administrators │ jturner │ jturner │ 10000 │
│ 78561 │ 19838 │ 18636 │ GROUP_USER │ ␀ │ posix-jumphost-it │ posix-jumphost-it │ jturner │ jturner │ 10000 │
│ 86486 │ 19052 │ 18636 │ GROUP_USER │ ␀ │ atlassian-administrators │ atlassian-administrators │ jturner │ jturner │ 10000 │
└───────┴───────────┴──────────┴─────────────────┴────────────┴───────────────────────────┴───────────────────────────┴────────────┴──────────────────┴──────────────┘
(7 rows)

But in this case there are nested groups (from LDAP), and our query hasn't returned the full set.

In cwd_membership  a nested group is represented as a cwd_membership row with a membership_type  of GROUP_GROUP . For instance, jira-administrators  is a sub group of jira-admins :

jira=> select * from cwd_membership where membership_type='GROUP_GROUP' and lower_child_name='jira-administrators';
┌───────┬───────────┬──────────┬─────────────────┬────────────┬─────────────┬───────────────────┬─────────────────────┬─────────────────────┬──────────────┐
│ id │ parent_id │ child_id │ membership_type │ group_type │ parent_name │ lower_parent_name │ child_name │ lower_child_name │ directory_id │
├───────┼───────────┼──────────┼─────────────────┼────────────┼─────────────┼───────────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ 73472 │ 18511 │ 19900 │ GROUP_GROUP │ ␀ │ jira-admins │ jira-admins │ jira-administrators │ jira-administrators │ 10000 │
└───────┴───────────┴──────────┴─────────────────┴────────────┴─────────────┴───────────────────┴─────────────────────┴─────────────────────┴──────────────┘
(1 row)

Membership of a sub-group ( jira-administrators  ) implies membership of the parent ( jira-admins  ).

We could find all parent groups of our groups with this query:

jira=> select
lower_parent_name
from cwd_membership 
where membership_type='GROUP_GROUP' and lower_child_name IN (
select lower_parent_name from cwd_membership where lower_child_name='jturner'
);
┌───────────────────────────┐
│ lower_parent_name │
├───────────────────────────┤
│ confluence-administrators │
│ jira-users │
│ confluence-users │
│ jira-admins │
└───────────────────────────┘
(4 rows)

but what about groups-of-groups?

Enter Postgres recusive queries.  I won't provide a tutorial. It may help to see the parent/child relationships of every result:

jira=> WITH RECURSIVE members AS 
  ( SELECT lower_parent_name ,lower_child_name
      FROM cwd_membership
      WHERE child_name='jturner' UNION 
      select m.lower_parent_name ,m.lower_child_name FROM cwd_membership m INNER JOIN members ON members.lower_parent_name=m.lower_child_name
  )
SELECT DISTINCT lower_parent_name, lower_child_name from members;
┌───────────────────────────┬──────────────────────────┐
│ lower_parent_name │ lower_child_name │
├───────────────────────────┼──────────────────────────┤
│ jira-users │ jabber-all │
│ crowd-administrators │ jturner │
│ jira-administrators │ jturner │
│ confluence-administrators │ atlassian-administrators │
│ jabber-all │ jturner │
│ atlassian-administrators │ jturner │
│ confluence-users │ jabber-all │
│ change (snc) │ jturner │
│ posix-jumphost-it │ jturner │
│ confluence-administrators │ jturner │
│ jira-admins │ jira-administrators │
└───────────────────────────┴──────────────────────────┘
(11 rows)
  • No labels