223 words
1 minute
Building a Markdown summary of Django group permissions
Anubhav Gain
2024-05-15
sql
/
/
sql
/
python
/
git
/
database
/
web

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')
Building a Markdown summary of Django group permissions
https://mranv.pages.dev/posts/building-a-markdown-summary-of-django-group-permissions/
Author
Anubhav Gain
Published at
2024-05-15
License
CC BY-NC-SA 4.0
© 2026 Anubhav Gain. All Rights Reserved. RSS Sitemap Built with Astro View Source