Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

A recursive SQL query for printing a user's groups.

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