SQL Select Query for Roles/Permissions of a User

Question

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?

You say a role can have multiple permissions. Do you have table role_permission(role_id,permission_id)? This table used in query. — ValNik
– ValNik, Commented May 27, 2024 at 22:15
As the error message tells you, there is no p_role_id on the left hand side of your last join, but the join condition is USING (p_role_id). To be honest, I think there is nowhere a p_role_id. — Islingre
– Islingre, Commented May 27, 2024 at 22:49
@ValNik - you are correct - I neglected to mention the role_permissions table, which does contain the role_id and permission_id columns as you suspected. I've updated the question to include this. Thanks! — wdc92
– wdc92, Commented May 28, 2024 at 11:14

Islingre · Accepted Answer · 2024-05-27 23:06:02Z

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;

This answers my "best" scenario, and is much cleaner and easy to understand. Thank you!!!

ValNik · Accepted Answer · 2024-05-28 04:53:19Z

I suggest a slightly different order of joining and grouping tables.
First we collect all the permissions for user (user_id,permission_id) assigned directly to user or thru role.
Then join permission and role names and aggregate to arrays.
Finally, join user table.

See example

select user_id,username,email ,user_role_array ,user_permission_array from( select user_id ,ARRAY_AGG(DISTINCT r.name ORDER BY r.name) FILTER (WHERE perm_type='r') AS user_role_array ,ARRAY_AGG(DISTINCT p.name ORDER BY p.name) AS user_permission_array from( -- all user permissions - direct and thru role select up.user_id,permission_id, 0 role_id,'u' perm_type from user_permission up union all select ur.user_id,rp.permission_id,ur.role_id,'r' perm_type from user_role ur left join role_permission rp on ur.role_id=rp.role_id )allP left join permission p on p.id=allP.permission_id left join role r on r.id=allP.role_id group by user_id )uarr left join "user" u on u.id=uarr.user_id

Demo

Collectives™ on Stack Overflow

SQL Select Query for Roles/Permissions of a User

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related