SQL Select Query for Roles/Permissions of a User

I have a database design with multiple many-to-many relationships, and am trying to write a SELECT statement that retrieves a result set with ONE ROW PER USER. To do this, I'd like to aggregate the permissions into a single column.

In the application's database design, a user can have multiple permissions, a role can have multiple permissions, and a user can also have multiple roles, thereby compounding the user's permissions.

Here are the relevant tables:

// 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

(NOT GOOD) I've created a query that successfully aggregates the user permissions, as it's just the single aggregate. However, I can't seem to figure out how to get the 2-dimensional aggregate when adding on the roles' permissions.

(BETTER) From the following query, I'd like to at least have a 6th column called "roles_permissions_array" that in pseudo-code is represented by: "For each role found in the 'user_role_array' column, select the array of all permissions."

(BEST) To take it further, it'd be nice to also have a version of the query that consolidates ALL permissions (both direct-user permissions and role-permissions) into a single column (de-duplicated), so we can see a single source of all permissions per user. In this scenario, the output columns would be: "user_id", "user_name", "user_email", "user_role_array", and "user_permissions_array". The last of these would be a consolidated array of all user permissions AND all role permissions

Here was the beginning of my attempt:

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;

But I just get an error: "...column "p_role_id" specified in USING clause does not exist in left table". I suspect even if I got past this error, I might still not get back the desired result set. Any ideas?

Solution

The question is not 100% clear to me, but I interpret it as follows:

There are users, roles and permissions.
A role is a set of permissions (roles are not necessarily disjoint sets).
Multiple roles may be assigned to the same user.
Additionally, multiple permissions might be directly granted to the user (not via a role).
You want to get:
- all user information
- names of all assigned roles
- deduplicated names of all permissions (no matter if direct or via role).
All you tables live in the users schema.

Try this:

SELECT
    u.id AS user_id,
    u.username AS user_name,
    u.email AS user_email,
    ARRAY_AGG(DISTINCT r.name ORDER BY r.name) AS user_role_array,
    ARRAY_AGG(DISTINCT p.name ORDER BY p.name) AS user_permission_array
FROM users.user u
LEFT JOIN users.user_role ur ON ur.user_id = u.id
LEFT JOIN users.role r ON r.id = ur.role_id
LEFT JOIN users.role_permission rp ON rp.role_id = r.id
LEFT JOIN users.user_permission up ON up.user_id = u.id
LEFT JOIN users.permission p ON p.id IN (rp.permission_id, up.permission_id)
GROUP BY u.id
ORDER BY u.username;