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:

select
  (
    '# ' || (
      select
        name
      from
        auth_group
      where
        id = auth_group_original.id
    ) || '

' || (
      select
        count(*)
      from
        auth_user_groups
      where
        auth_user_groups.group_id = auth_group_original.id
    ) || ' members:

' || (
      select
        string_agg(auth_user.username, ', ')
      from
        auth_user
        join auth_user_groups on auth_user_groups.user_id = auth_user.id
      where
        auth_user_groups.group_id = auth_group_original.id
    ) || '

Permissions: 

' || (
      select
        coalesce(
          string_agg(
            '* ' || auth_permission.name,
            '
'
          ),
          'No permissions'
        )
      from
        auth_permission
        join auth_group_permissions on auth_group_permissions.permission_id = auth_permission.id
      where
        auth_group_permissions.group_id = auth_group_original.id
    )
  ) as markdown
from
  auth_group auth_group_original

The output looks like this:

Groups_and_permissions

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:

select
  string_agg(
    '* ' || auth_permission.name,
    '
'
  )
from
  auth_permission
  join auth_group_permissions on auth_group_permissions.permission_id = auth_permission.id
where
  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:

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

Created 2021-06-03T09:15:07-07:00, updated 2021-06-04T12:07:08-07:00 · History · Edit