Building a Markdown summary of Django group permissions#

Django SQL Dashboard can display rendered markdown generated by a SQL query.

Here’s a query I figured out today for showing a summary of all Django groups, their members and the list of permissions those groups hold:

1
select
2
  (
3
    '# ' || (
4
      select
5
        name
6
      from
7
        auth_group
8
      where
9
        id = auth_group_original.id
10
    ) || '
11

12
' || (
13
      select
14
        count(*)
15
      from
16
        auth_user_groups
17
      where
18
        auth_user_groups.group_id = auth_group_original.id
19
    ) || ' members:
20

21
' || (
22
      select
23
        string_agg(auth_user.username, ', ')
24
      from
25
        auth_user
26
        join auth_user_groups on auth_user_groups.user_id = auth_user.id
27
      where
28
        auth_user_groups.group_id = auth_group_original.id
29
    ) || '
30

31
Permissions:
32

33
' || (
34
      select
35
        coalesce(
36
          string_agg(
37
            '* ' || auth_permission.name,
38
            '
39
'
40
          ),
41
          'No permissions'
42
        )
43
      from
44
        auth_permission
45
        join auth_group_permissions on auth_group_permissions.permission_id = auth_permission.id
46
      where
47
        auth_group_permissions.group_id = auth_group_original.id
48
    )
49
  ) as markdown
50
from
51
  auth_group auth_group_original

The output looks like this:

The first time I ran this I got back null for the Markdown for one of the records - it turned out that one of my groups had no permissions at all, so this query:

1
select
2
  string_agg(
3
    '* ' || auth_permission.name,
4
    '
5
'
6
  )
7
from
8
  auth_permission
9
  join auth_group_permissions on auth_group_permissions.permission_id = auth_permission.id
10
where
11
  auth_group_permissions.group_id = auth_group_original.id

Returned null - and concatenating null to everything else produced null too.

The fix was to add a coalesce() - which returns the first not-null argument - like this:

1
select coalesce(string_agg(...), 'No permissions')