SQL-запрос выбора ролей/разрешений пользователя

У меня есть база данных с несколькими отношениями «многие ко многим», и я пытаюсь написать оператор SELECT, который извлекает набор результатов с ОДНОЙ СТРОКОЙ НА КАЖДОГО ПОЛЬЗОВАТЕЛЯ. Для этого я хотел бы объединить разрешения в один столбец.

В конструкции базы данных приложения пользователь может иметь несколько разрешений, роль может иметь несколько разрешений, а пользователь также может иметь несколько ролей, тем самым усугубляя разрешения пользователя.

Вот соответствующие таблицы:

// Main tables
user:
- id
- username
- email

role:
- id
- name
- display_name

permission:
- id
- name
- display_name

// Many-to-many connector tables
user_role:
- user_id
- role_id

user_permission:
- user_id
- permission_id

role_permission:
- role_id
- permission_id

(НЕ ХОРОШО) Я создал запрос, который успешно объединяет разрешения пользователей, поскольку это всего лишь один агрегат. Однако я не могу понять, как получить двумерный агрегат при добавлении разрешений ролей.

(ЛУЧШЕ) Из следующего запроса я хотел бы иметь по крайней мере шестой столбец с именем «roles_permissions_array», который в псевдокоде представлен следующим образом: «Для каждой роли, найденной в столбце «user_role_array», выберите массив всех разрешений ."

(ЛУЧШИЙ) Чтобы пойти дальше, было бы неплохо также иметь версию запроса, которая объединяет ВСЕ разрешения (как разрешения прямого пользователя, так и разрешения ролей) в один столбец (дедуплицированный), чтобы мы могли видеть единый источник всех разрешений для каждого пользователя. В этом сценарии выходные столбцы будут следующими: "user_id", "user_name", "user_email", "user_role_array" и "user_permissions_array". Последним из них будет консолидированный массив всех разрешений пользователя И всех разрешений ролей.

Вот начало моей попытки:

SELECT DISTINCT 
    -- "user" columns
    u.id as "user_id", u.user_name as "user_user_name", u.email as "user_email",
    p_agg.user_permissions_array,
    r_agg.user_role_array
    FROM users."user" u
-- REGULAR JOINS
LEFT JOIN users.user_role ur on ur.user_id = u.id
LEFT JOIN users.role_permission rp on ur.role_id = rp.role_id
-- START AGGREGATES
LEFT JOIN (
    SELECT array_agg(r.name) as user_role_array, ur.user_id
    FROM users.user_role ur
    JOIN users.role r on r.id = ur.role_id
    GROUP BY ur.user_id
) r_agg using(user_id)
LEFT JOIN (
    SELECT array_agg(p.name) as user_permissions_array, up.user_id
    FROM users.user_permission up
    JOIN users.permission p on up.permission_id = p.id
    GROUP BY up.user_id
) p_agg using(user_id)
-- rp_agg_parent
-- NOT WORKING FROM HERE DOWN
LEFT JOIN (
    SELECT array_agg(role_permission_array) as two_dimensional_role_permissions_array
    FROM (
        SELECT p.role_id, r.name as "role_name", array_agg(p.permission_name) as "role_permission_array"
        FROM (
            SELECT r.id as "role_id", r.name as "role_name", p.name as "permission_name"
            FROM users.role_permission rp
            JOIN users.role r on r.id = rp.role_id
            JOIN users.permission p on p.id = rp.permission_id
            GROUP BY p.name, r.name, r.id
            ORDER BY p.name
        ) as p
        JOIN users.user_role ur on ur.role_id = p.role_id
        JOIN users.role r on r.id = ur.role_id
        GROUP BY r.name, p.role_id
        order by role_name
    ) AS rp_agg_sub
    JOIN users.role_permission rp on rp.role_id = rp_agg_sub.role_id
    GROUP BY rp_agg_sub.role_name
) rp_agg_parent using(p_role_id)
order by u.user_name;

Но я просто получаю сообщение об ошибке: "...column "p_role_id" specified in USING clause does not exist in left table". Я подозреваю, что даже если бы я преодолел эту ошибку, я все равно не смог бы вернуть желаемый набор результатов. Есть идеи?