docs: def _get_permissions_query(self, session, identifier):
          """
          select domain, json_agg(parts) as permissions from
              (select domain, row_to_json(r) as parts from
                      (select domain, action, array_agg(distinct target) as target from
                          (select (case when domain is null then '*' else domain end) as domain,
                                  (case when target is null then '*' else target end) as target,
                                  array_agg(distinct (case when action is null then '*' else action end)) as action
                             from permission
                            group by domain, target
                           ) x
                        group by domain, action)
                r) parts
          group by domain;
          """
          thedomain = case([(Domain.name == None, '*')], else_=Domain.name)
          theaction = case([(Action.name == None, '*')], else_=Action.name)
          theresource = case([(Resource.name == None, '*')], else_=Resource.name)
  
          action_agg = func.array_agg(theaction.distinct())
  
          stmt1 = (
              session.query(Permission.domain_id,
                            thedomain.label('domain'),
                            Permission.resource_id,
                            theresource.label('resource'),
                            action_agg.label('action')).
              select_from(User).
              join(role_membership_table, User.pk_id == role_membership_table.c.user_id).
              join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id).
              join(Permission, role_permission_table.c.permission_id == Permission.pk_id).
              outerjoin(Domain, Permission.domain_id == Domain.pk_id).
              outerjoin(Action, Permission.action_id == Action.pk_id).
              outerjoin(Resource, Permission.resource_id == Resource.pk_id).
              filter(User.identifier == identifier).
              group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name)).subquery()
  
          stmt2 = (session.query(stmt1.c.domain,
                                 stmt1.c.action,
                                 func.array_agg(stmt1.c.resource.distinct()).label('resource')).
                   select_from(stmt1).
                   group_by(stmt1.c.domain, stmt1.c.action)).subquery()
  
          stmt3 = (session.query(stmt2.c.domain,
                                 func.row_to_json(as_row(stmt2)).label('parts')).
                   select_from(stmt2)).subquery()
  
          final = (session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text)).
                   select_from(stmt3).
                   group_by(stmt3.c.domain))
  
          return final