PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

(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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search