1

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the relations – in a reasonably efficient way?

In this system, the ancestry determines the capabilities of a role or permission. A role below building_access will inherit the rights and permissions of its parent. I can quite easily query roles or permissions and determine the entire line of ancestry for a given record.

What I am trying to do is query a role and determine the complete tree of permissions for that role. I am also trying to query all roles and determine the same list of permissions, for each and every role, aggregating it into a string.

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions"
FROM roles r
JOIN roles_permissions rp ON rp.role = r.id
JOIN permissions p ON p.id = rp.permission
WHERE r.path @> 'company_employee.warehouse_employee'
GROUP BY r.id;

Actual Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["warehouse access", "warehouse stock access"]

Intended Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["building access", "break room access", "warehouse access", "warehouse stock access"]

This query functions to give me the direct permissions for every role, but it doesn't get the ancestors of those permissions to establish the actual total permissions for the role.

My sample database is as displayed below:

CREATE EXTENSION ltree;
CREATE EXTENSION tablefunc;

CREATE TABLE roles (
  id   int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX roles_path_idx ON roles USING gist (path);

INSERT INTO roles (id, name, path) VALUES
  (1, 'company employee', 'company_employee'),
  (2, 'warehouse employee', 'company_employee.warehouse_employee'),
  (3, 'warehouse manager', 'company_employee.warehouse_employee.warehouse_manager');

CREATE TABLE permissions (
  id int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX permissions_path_idx ON permissions USING gist (path);

INSERT INTO permissions (id, name, path) VALUES
  (1, 'building access', 'building_access'),
  (2, 'break room access', 'building_access.break_room_access'),
  (3, 'warehouse access', 'building_access.warehouse_access'),
  (4, 'warehouse stock access', 'building_access.warehouse_access.warehouse_stock_access'),
  (5, 'warehouse security access', 'building_access.warehouse_access.warehouse_security_access');

CREATE TABLE roles_permissions (
  role       int REFERENCES roles,
  permission int REFERENCES permissions,
  PRIMARY KEY (role, permission)
);

INSERT INTO roles_permissions (role, permission)
VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5);
  • Why warehouse employee role have break room access permission? – McNets Dec 29 '18 at 22:13
  • draw special attention to array_agg which McNets use rather than the string_agg idea. It's far better. Also, never double quote identifiers. – Evan Carroll Dec 29 '18 at 23:18
1

Essentially, what you're saying is that

  • a role of x.y.z, has all of the permissions granted to parent x.y, and grandparent x
  • a permission of x.y.z, has all of the permissions granted to parent x.y, and grandparent x

So essentially all of the things on the left, should have all the permissions of the combined group on the right,

SELECT r1.name, array_agg(r2.name) AS role_list
FROM roles AS r1
INNER JOIN roles AS r2
  ON r2.path @> r1.path
GROUP BY r1.name;
        name        |                           role_list                           
--------------------+---------------------------------------------------------------
 company employee   | {"company employee"}
 warehouse manager  | {"company employee","warehouse employee","warehouse manager"}
 warehouse employee | {"company employee","warehouse employee"}
(3 rows)

From that point

SELECT r1.name, array_agg(p.name) AS perm_list
FROM roles AS r1
INNER JOIN roles AS r2 ON r2.path @> r1.path
INNER JOIN roles_permissions AS rp ON rp.role = r2.id
INNER JOIN permissions AS p ON rp.permission = p.id
GROUP BY r1.name;
        name        |                                                    perm_list                                                    
--------------------+-----------------------------------------------------------------------------------------------------------------
 company employee   | {"building access","break room access"}
 warehouse manager  | {"building access","break room access","warehouse access","warehouse stock access","warehouse security access"}
 warehouse employee | {"building access","break room access","warehouse access","warehouse stock access"}
(3 rows)

You can just pop back in the WHERE clause...

SELECT r1.name, array_agg(p.name) AS perm_list
FROM roles AS r1
INNER JOIN roles AS r2 ON r2.path @> r1.path
INNER JOIN roles_permissions AS rp ON rp.role = r2.id
INNER JOIN permissions AS p ON rp.permission = p.id
WHERE r1.path @> 'company_employee.warehouse_employee'
GROUP BY r1.name;

Note in this scheme we just use role_permissions to map to the permissions and they don't work themselves hierarchical. That's not present in the test-data anyway. If you look at your mapping of (1,1) that's a weird one. If you have break-room access, do you also need to know explicitly that have you have company access? If not, then you need self-joins on both sides.

My diagnosis here is that your schema is most probably needlessly complex, and you should pick either hierarchical permissions, or M2M mapping. Having both is borderline insane.

1

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates.

        select
            array_agg(distinct p2.name) permissions
        from
            permissions p1
        join
            permissions p2
            on  p2.path @> p1.path

Then I've used it for each role that match the path company_employee.warehouse_employee

select r.name,
       (select array_agg(distinct p2.name) permissions
        from   permissions p1
        join   permissions p2 on  p2.path @> p1.path
        join   roles_permissions rp on rp.permission = p1.id
        where  rp.role in (select distinct r2.id
                           from   roles r1
                           join   roles r2 on r2.path @> r1.path
                           where  r1.id = r.id)) permissions
from   roles r
where  r.path @> 'company_employee.warehouse_employee';
| "company employee"   | "{"building access","break room access"}"                         |
|----------------------|-------------------------------------------------------------------|
| "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

rextester here

As Evan Carroll has pointed out

  • If a role X.Y.Z inherits permissions from X.Y and hence from X
  • And a permission A.B.C inherits from A.B and from A

Your bridge table should avoid to assign those rows twice.

Given next data:

ID | Roles    ID | Perm  
---+-----     ---+------ 
1  | X        1  | A     
2  | X.Y      2  | A.B   
3  | X.Y.Z    3  | A.C   
              4  | A.C.D 
              5  | A.C.E

Your bridge table joins (1,1) and (1,2):

X => A            <-- Redundant
X => A.B ~> A 

In this case (1,1) is redundant because A permission is given by inheritance on A.B

And same for (2,3), (2,4):

                           Inherits from role
X.Y => A.C                 ~> X ~> A.B ~> A     <-- Redundant
X.Y => A.C.D ~> A.C ~> A   ~> X ~> A.B ~> A

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.